Christophe Harris Christophe Harris - 1 month ago 9
SQL Question

getting the last AUTO INCREMENT - how do I specify the table for my query with mysqli_insert_id?

mysqli_insert_id
takes one parameter, the connection to the database.

But I have two INSERTs in my PHP code, both using the same connection which is defined in my
dbConnect.php
. How can I distinguish between the two ?

I am looking for the AUTO INCREMENT value in this query :

$sql = "INSERT INTO category VALUES(NULL,'{$category}', '$user_id')";


If I put this code as the second query below, then it works, probably because
mysqli_insert_id
is taking the latest value in the code, but I'd prefer to be able to specify the value I'm looking for, rather than doing it in this haphazard sort of way.

So, how do I specify the table for my query with mysqli_insert_id?

<?php require('dbConnect.php');

session_start();
$username = $_SESSION['username'];
$user_id = $_SESSION['user_id'];

// Create new record in the db, if the 'Add New Record' button is clicked

if (isset($_POST['create'])) {

$category = ($_POST['category']);
$name = ($_POST['name']);
$phonenumber = ($_POST['phonenumber']);
$address = ($_POST['address']);
$comment = ($_POST['comment']);

$sql = "INSERT INTO category VALUES(NULL,'{$category}', '$user_id')";

$sql2 = "INSERT INTO review VALUES(NULL,'$user_id', '$user_id', '{$name}','{$phonenumber}','{$address}', '{$comment}')";

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


$con->close();


?>

Answer

From the PHP main documentation on mysqli_insert_id()

Return Values:

The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

Basically in order to get both id's, you have to ask for the auto_increment from the database twice.

$sql = "INSERT INTO category VALUES(NULL,'{$category}', '$user_id')";

$sql2 = "INSERT INTO review VALUES(NULL,'$user_id', '$user_id', '{$name}','{$phonenumber}','{$address}', '{$comment}')";
$id1 = 0;
$id2 = 0;
if ($con->query($sql) === TRUE) {
    $id1 = mysqli_insert_id($con);
    if ($con->query($sql2) === TRUE) {
        $id2 = mysql_insert_id($con);
        echo "New record created successfully";
        echo "ID1 is $id1 and ID2 is $id2";
    }
}