PHP为SQL添加唯一ID

Question

I'm working on a small project for my English teacher, and it's my first time doing anything with databases. I have a table called Books, with four columns: Id, Title, Link, and Rating. The idea is that a user could submit the title of a book they recently read, a link to it on goodreads, and a rating out of 5 stars. (I don't have any input validating yet, but I'm going to add that after I finish up with my current problem.)

The problem happens in the Id column. I'm trying, with PHP and SQL, to grab the largest Id in the entire table, and then set the Id of the newly added book (row) to be one more than the old largest.

Here's my HTML:

<form action="bookSubmit.php" method="post">
    Book Title: <input type="text" name="title"><br>
    Goodreads Link: <input type="text" name="link"><br>
    Rating (1 to 5): <input type="number" name="rating" min="1" max="5"><br>
    <input type="submit" value="submit">
</form>

and bookSubmit.php:

<?php

$servername = "localhost";
$username = "[My username]";
$password = "[My password]";
$dbname = "PullJosh_books";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 


$newId = $conn->query("SELECT MAX(Id) FROM Books"); // Replacing this line with $newId = and then manually typing in the current highest Id works (meaning everything else is all set).
$newId++;

$sql = "INSERT INTO Books (Id, Title, Link, Rating)
VALUES ('". $newId ."', '" . $_POST['title'] . "', '" . $_POST['link'] . "', '" . $_POST['rating'] . "')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

When I run SELECT MAX(Id) FROM Books in the phpmyadmin sql tab, I get 2, which is correct.

If you happen to notice any security issues (besides not validating the input), please let me know about that as well.

Answer 1

You need not to first find the largest number in the table and then add one number to it and then submit. In your data table in mysql turn id into primary key and make it auto increment true that will always insert a unique and increased id.

syntax for the same

ALTER TABLE tbl_name ADD id INT PRIMARY KEY AUTO_INCREMENT;