How to get the last inserted MySQL table id in PHP?

Question

I have a table in which new data is often inserted. I need to get the latest table id. How can i do this?

Is it like SELECT MAX(id) FROM table ?

Answer 1

If you are using PDO, use PDO::lastInsertId .

If you are using Mysqli, use mysqli::$insert_id .

If you are still using mysql:

Please do not use mysql_* functions in the new code . They are no longer supported and are officially outdated . See the red box ? Read more about prepared statements and use PDO or MySQLi - this article will help you decide which one. If you choose PDO, here is a good tutorial .

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

Answer 2

there is a function to know what was the last identifier 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 can lead to problems if your code is used simultaneously in two different sessions.

This function is called mysql_insert_id

Answer 3

This is normal. You can also use LAST_INSERT_ID ()

Answer 4

With PDO :

 $pdo->lastInsertId(); 

With Mysqli :

 $mysqli->insert_id; 

---

Please do not use mysql_* functions in the new code . They are no longer supported and are officially outdated . See the red box ? Read more about prepared statements and use PDO or MySQLi - this article will help you decide which one. If you choose PDO, here is a good tutorial .

Answer 5

Use mysql_insert_id () .

See a similar question here

Answer 6

What you wrote will give you the highest id , assuming they were unique and automatically incremented, which would be nice if you were ok with the concurrency prompt.
Since you are using MySQL as your database, there is a specific function LAST_INSERT_ID() that only works with the current connection that inserted. <ph> PHP offers a specific function for the so-called mysql_insert_id .

Answer 7

You can get the last inserted identifier using the php built-in function mysql_insert_id();

 $id = mysql_insert_id(); 

you will also get the last id

 $id = last_insert_id(); 

Answer 8

It is good to use mysql_insert_id() , but there is one specific note about using it, you must call it after executing the INSERT request, which means that the script is in the same session. If you use it otherwise, it will not work correctly.

Answer 9

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

Example:

 $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

Try it, 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 11

NOTE. If you do multiple inserts with one of the mysqli :: insert_id statements, this will be incorrect.

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');

mysqli :: insert_id will be 1 not 3.

To get the correct value, follow these steps:

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

This is a document, but it is a bit unclear.

Answer 12

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

php mysql_insert_id () and mysql last_insert_id () provide only the last transaction identifier.

If you need the last auto_incremented id of any table in your schema (and not just the last transaction), you can use this query

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

What is it.

Answer 13

It is a pity that we did not 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 14

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 15

Using the MySQLi transaction, I sometimes could not get mysqli::$insert_id because it returned 0. Especially if I used stored procedures that execute INSERT s. So there is another way in a 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 16

Use mysqli as mysql robs

 <?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 17

$ lastid = mysql_insert_id ();

Answer 18

In all these considerations, I assume that the reason for checking max id is to know which identifier should be next .. (if my maximum id is 5, then the next will be 5 + 1 = 6).

→ If this is not the reason, my best apologies

The case if the information of another INSERT user between your CHECK and INSERT will give an invalid identifier.

So, this can be resolved if you create a hash that may include a timestamp or other unique value.

Then in the same function you can insert your information with empty values ​​and your hash. This would create an identifier if you selected AUTO_INCRECEMENT.

Then in the same function you will still have your hash, and you can search for id with the same hash. And then you can fill in the empty values ​​with mysql UPDATE.

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

Good luck with that.

Answer 19

If your table has an AUTO INCREMENT column, such as UserID, Emp_ID, .. then you can use this query to get the last inserted SELECT * FROM table_name entry, where UserID = (select MAX (UserID) from table_name) In the PHP code:

 $con = mysqli_connect('localhost', 'userid', 'password', 'database_name'); if (!$con) { die('Could not connect: ' . mysqli_error($con)); } $sql = "SELECT * FROM table_name where UserID=(select MAX(UserID)from table_name)"; $result = mysqli_query($con, $sql); 

Then you can use the extracted data as your requirement

Answer 20

 mysql_query("INSERT INTO mytable (product) values ('kossu')"); printf("Last inserted record has id %d\n", ***mysql_insert_id()***);