MySQLi Last Insert Id

How will I get mysqli last insert id

$stmt = $conn->prepare("INSERT INTO table (name, email, password) VALUES(?,?,?)");
$stmt->bind_param("sss", $name, $email, $password);
$stmt->execute();
$last_id = $conn->insert_id;
echo "Last inserted ID is: " . $last_id;

Mysqli last insert id not work

Please check the definition of mysqli_insert_id:-

The mysqli_insert_id() function returns the id (generated with AUTO_INCREMENT) used in the last query.

It means either column in your table that have AUTO_INCREMENT value is not exist.

Or

No insertion of data happen programmatically on your table in the curren program flow. Since no insert query is fired in your code, it returned Zero.

Note:- Just do an insert query first in your code and then check what is the output given by mysqli_insert_id. Then you can understand easily what i am trying to say.Thanks

A working example of my local is here:-

<?php
error_reporting(E_ALL);
ini_set('display_errors',1);
function Cnn()
{
    $conn = mysqli_connect('localhost','root','','stack');
    return $conn;
}
function MyCommandInsertId($sql)
{
    $conn = Cnn();
    if(mysqli_query($conn,$sql))
    {
        $lastid = mysqli_insert_id($conn);
        return $lastid;
    }else{
        $err = mysqli_error($conn);
        return $err;
    }

}
function Insert()
{
        $sql = "INSERT INTO bom(bom_description,product_id,finish_product,bom_quantity,UOM) values('Table cleaner','','','','')";
        $st_insert_id = MyCommandInsertId($sql);

        echo $st_insert_id;

}
Insert();
?>

Output:- http://prntscr.com/9u2iyv (inserted id) and http://prntscr.com/9u2j6i(table view after insertion)

Mysqli insert_id()

You should use it as follow:

<?php
$mysqli = new mysqli(SQLI_SERVER, MYSQLI_USER, MYSQLI_PWD, MYSQLI_DBNAME);
if ($result = $mysqli->query("INSERT INTO user(f_name, l_name) VALUES('$f_name', '$l_name');")) {
   echo 'The ID is: '.$mysqli->insert_id;
}
?>

Which is correct way to get last inserted id in mysqli prepared statements procedural style?

You should use

mysqli_insert_id($link);

Because of this note on the PHP manual you referred us to

mysqli_stmt_insert_id

It should be noted that using mysqli_stmt->insert_id will not result in a unique ID being returned for each execution of a prepared insert statement. In practice, it appears that the first insertion ID is returned. If you are performing multiple inserts with the same prepared statement (one invocation of mysqli_stmt::prepare and multiple invocations of mysqli_stmt::execute() for a given statement), and need to keep the unique ID for each insert, use mysqli_connection->insert_id.

Access last insert ID mysqli

Put database connection to a variable, then start to use it.

like:

$db = database_connection();
$query = "INSERT INTO posts (post_title, post_content) VALUES ('". $args->post_title ."', '". $args->post_content ."')";
$result = mysqli_query($db, $query);
$id = $db->insert_id;

Using last inserted id in mysql multi query

Do not use mysqli_multi_query(). It is never recommended to use.

What you are probably looking for are transactions. You can execute both statements as prepared statements inside of a transaction.

try {
    $connection->begin_transaction();

    $stmt = $connection->prepare('INSERT INTO posts 
    (nparc,id_chauffeur,id_camion,lot_de_bord,triangle,pelle,balai,date)
    values(?,?,?,?,?,?,?,?)');
    $stmt->bind_param('ssssssss', $nparc, $id_chauffeur, $id_camion, $lot_de_bord, $triangle, $pelle, $balai, $get_datetime);
    $stmt->execute();

    $stmt = $connection->prepare('INSERT INTO photos
    (post_id, 64_image1, 64_image2, 64_image3, 64_image4 ,date_upload)
    values(?,?,?,?,?,?)');
    $stmt->bind_param('ssssss', $connection->insert_id, $imsrc1, $imsrc2, $imsrc3, $imsrc4, $get_datetime);
    $stmt->execute();

    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollback();
}

Make sure that you have error reporting enabled otherwise your code won't work. You must put this line before new mysqli()

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

---

Related Topics