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

Use mysql_insert_id() function.

See similar question here

Answer 2

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 3

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 4

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 5

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 6

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();
?>