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

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

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 2

there is a function to know what was the last id inserted in the current connection

mysql_query('INSERT INTO FOO(a) VALUES(\'b\')');
$id = mysql_insert_id();

plus using max is a bad idea because it could lead to problems if your code is used at same time in two different sessions.

That function is called mysql_insert_id

Answer 3

With PDO:

$pdo->lastInsertId();

With Mysqli:

$mysqli->insert_id;

---

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.

Answer 4

It's ok. Also you can use LAST_INSERT_ID()

Answer 5

What you wrote would get you the greatest id assuming they were unique and auto-incremented that would be fine assuming you are okay with inviting concurrency issues.
Since you're using MySQL as your database, there is the specific function LAST_INSERT_ID() which only works on the current connection that did the insert.
PHP offers a specific function for that too called mysql_insert_id.

Answer 6

Try this should work fine:

$link = mysqli_connect("localhost", "my_user", "my_password", "world");

$query = "INSERT blah blah blah...";
$result = mysqli_query($link, $query);

echo mysqli_insert_id($link);

Answer 7

Clean and Simple -

$selectquery="SELECT id FROM tableName ORDER BY id DESC LIMIT 1";
$result = $mysqli->query($selectquery);
$row = $result->fetch_assoc();
echo $row['id'];

Answer 8

It's ok to use mysql_insert_id(), but there is one specific note about using it, you must call it after executed INSERT query, means in the same script session. If you use it otherwise it wouldn't work correctly.

Answer 9

To get last inserted id in codeigniter After executing insert query just use one function called insert_id() on database, it will return last inserted id

Ex:

$this->db->insert('mytable',$data);
echo $this->db->insert_id(); //returns last inserted id

in one line

echo $this->db->insert('mytable',$data)->insert_id();

Answer 10

You can get the latest inserted id by the in built php function mysql_insert_id();

$id = mysql_insert_id();

you an also get the latest id by

$id = last_insert_id();

Answer 11

It's sad not to see any answers with an example.

Using Mysqli::$insert_id:

$sql="INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3)";
$mysqli->query($sql);
$last_inserted_id=$mysqli->insert_id; // returns last ID

Using PDO::lastInsertId:

$sql="INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3)";
$database->query($sql);
$last_inserted_id=$database->lastInsertId(); // returns last ID

Answer 12

NOTE: if you do multiple inserts with one statement mysqli::insert_id will not be correct.

The table:

create table xyz (id int(11) auto_increment, name varchar(255), primary key(id));

Now if you do:

insert into xyz (name) values('one'),('two'),('three');

The mysqli::insert_id will be 1 not 3.

To get the correct value do:

mysqli::insert_id + mysqli::affected_rows) - 1

This has been document but it is a bit obscure.

Answer 13

I prefer use a pure MySQL syntax to get last auto_increment id of the table I want.

php mysql_insert_id() and mysql last_insert_id() give only last transaction ID.

If you want last auto_incremented ID of any table in your schema (not only last transaction one), you can use this query

SELECT AUTO_INCREMENT FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'my_database' 
    AND TABLE_NAME = 'my_table_name';

That's it.

Answer 14

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 15

Use mysqli as mysql is depricating

<?php
$mysqli = new mysqli("localhost", "yourUsername", "yourPassword", "yourDB");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
// Conside employee table with id,name,designation
$query = "INSERT INTO myCity VALUES (NULL, 'Ram', 'Developer')";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

/* close connection */
$mysqli->close();
?>

Answer 16

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.