How do I get the last inserted ID of a MySQL table

Question

I have a table into which new data is frequently inserted. I need to get the very last ID of the table. How can I do this?

Is it similar to SELECT MAX(id) FROM table?

Answer 1

$lastid = mysql_insert_id();

Answer 2

If you're using PDO, use PDO::lastInsertId.

If you're using Mysqli, use mysqli::$insert_id.

If you're still using Mysql:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

_{But if you have to, use mysql_insert_id.}

Answer 3

I tried

mysqli_insert_id($dbConnectionObj)

This returns the current connection's last inserted id so if you are managing your connections properly this should work. Worked for me at least.

Answer 4

Please use PDP and then try this

$stmt = $db->prepare("...");
$stmt->execute();
$id = $db->lastInsertId();

Answer 5

Using MySQLi transaction I sometimes wasn't able to get mysqli::$insert_id, because it returned 0. Especially if I was using stored procedures, that executing INSERTs. So there is another way within transaction:

<?php

function getInsertId(mysqli &$instance, $enforceQuery = false){
    if(!$enforceQuery)return $instance->insert_id;

    $result = $instance->query('SELECT LAST_INSERT_ID();');

    if($instance->errno)return false;

    list($buffer) = $result->fetch_row();

    $result->free();

    unset($result);

    return $buffer;
}

?>

Answer 6

By all this discussion I assume that the reason to check max id is to know what id should be next.. (if my max id is 5 then next will be 5+1=6).

>>If this is not the reason, my best apologies

Case if someone else INSERTs information between your CHECK and INSERT would give you wrong ID.

So It can be solved if you would create hash that could include timestamp or other unique value.

Then in the same function you can insert your information with empty values and your hash. That would create ID if you have AUTO_INCRECEMENT selected.

Then in the same function you would still have your hash and you could look for id with the same hash. And then you could complete populating empty values with mysql UPDATE.

This includes a bit more connections, but it is still a way to do it...

Good luck solving it.