phpmysqliinsert-id

Last insert value always returns zero


Trying to get the id of the last insert using a prepared statement. mysqli_insert_id always returns zero.

$sql = "INSERT INTO classes (id_course, id_organization, start_date, end_date, status, id_creator, assign_interval) VALUES (?, ?, ?, ?, ?, ?, 'monthly')";
if ($stmt = mysqli_prepare($link, $sql)) {
    if (!mysqli_stmt_bind_param($stmt, "ssssss", $course, $idOrg, $thisClass["startDate"], $endDate, $thisClass["status"], $idUser)) {
        echo "Error:could not bind parameters";
    }
    # CODE MISSING...
    {
        mysqli_stmt_execute($stmt);
        $idClass = mysqli_insert_id($stmt);
    }
} 
else 
{
    echo 'ERROR: Could not prepare statement';
}

$sql = "INSERT INTO class_modules (id_class, id_module) select ?,id_module from modules m where m.id_course = ?";
if ($stmt = mysqli_prepare($link, $sql)) {
    if (!mysqli_stmt_bind_param($stmt, "ss", $idClass, $course)) {
        echo "Error:could not bind parameters";
    }
    if (!mysqli_stmt_execute($stmt)) {
        echo "Error: could not update class modules:";
    }
} 
else 
{
    echo 'ERROR: Could not prepare statement';
}

Solution

  • I strogly recommend to use object-oriented approach. The reason for this is that you have confused two functions are mixed their parameters. If you used OOP, this mistake would be less likely to happen.

    There are 4 ways to get the auto-generated ID from MySQLi prepared statement:

    var_dump($stmt->insert_id);
    var_dump($mysqli->insert_id);
    var_dump(mysqli_stmt_insert_id($stmt));
    var_dump(mysqli_insert_id($mysqli));
    

    As you can see the first two are much cleaner and easier to understand and spot a mistake. What you have done is confused the last two. You used mysqli_insert_id() but you passed in the statement as an argument. Either change the function name or pass the mysqli object instead. The best option would be to use OOP and get the property value.