PHP/MySQL Get autoincremented value after insert

Question

In my mysql table i have an id-column which is set to autoincrement.

then i do queries like this:

INSERT INTO table (id, foo) VALUES ('', 'bar')

how can i then safely find out which id was generated with this insert?

if i just query the last id this might not be safe, since another insert could have happened in the meantime, right?

Wrong. The LAST ID will be the last id that has been inserted using that connection. Unless you share the connection, that will be the statement that just happened. So `INSERT INTO; SELECT LAST_INSERT_ID` will be good. — Konerak, Jan 02 '12 at 14:20
possible duplicate of [php/MySQL insert row then get 'id'](http://stackoverflow.com/questions/897356/php-mysql-insert-row-then-get-id) — hakre, Jan 02 '12 at 14:21

score 16 · Accepted Answer · edited Aug 01 '19 at 08:16

16

There's a PHP and also a MySQL function for this: mysqli_insert_id() and PDO::lastInsertId().

http://php.net/manual/en/function.mysql-insert-id.php

edited Aug 01 '19 at 08:16

oldboy

5,729
6
38
86

answered Jan 02 '12 at 14:19

sascha

4,671
3
36
54

3

It should be noted that the PHP method is being deprecated, so it would be wise to use the MySQL method from here on out. – RCNeil Apr 08 '13 at 23:52
@RCNeil hey guys, say you have multiple concurrent visitors on your website, wouldnt using `mysqli_insert_id()` run into issues if two `inserts` are executed almost immediately after one another for different users? – oldboy Aug 01 '19 at 07:35
If the query is executed successfully, the `mysqli_insert_id()` function will return the ID generated from that specific query, so you can assign it to a variable and continue to do something with it right after and be fine. It's not pulling "the last ID put in the table", it's pulling the ID that was just made with that specific query in that specific connection - https://stackoverflow.com/a/38957080/884953 – RCNeil Aug 01 '19 at 16:44

score 4 · Answer 2 · answered Jan 02 '12 at 14:20

4

Use LAST_INSERT_ID() in SQL

    SELECT LAST_INSERT_ID();

Use mysql_insert_id() in PHP

answered Jan 02 '12 at 14:20

Emmanuel N

7,350
2
26
36

1

wouldnt `mysqli_insert_id()` run into issues if there are multiple concurrent users on your website, and two `inserts` are executed almost immediately one after the other? is there a way to combine the `SELECT LAST_INSERT_ID()` with the `INSERT INTO...` statement, and then fetch the value with PHP, to avoid this? – oldboy Aug 01 '19 at 07:38

score 3 · Answer 3 · answered Apr 09 '13 at 00:55

If you are using PHP to get the auto_incremented value that is returned after an INSERT statement, try using the MySQLi insert_id function. The older mysql_insert_id() version is being deprecated in PHP.

An example below:

 <?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE myCity LIKE City");

$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);

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

/* drop table */
$mysqli->query("DROP TABLE myCity");

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

score 2 · Answer 4 · answered Mar 23 '20 at 11:24

Be Careful when using mysql_insert_id() specially if you have multiple connections to the Database. Because It doesn't get the value of the query you've inserted. it gets the latest id of the table. It may be a row another query has inserted. Only use this function if you access Database in one connection.

https://www.php.net/manual/en/function.mysql-insert-id.php

score 2 · Answer 5 · answered Jan 02 '12 at 14:20

2

http://php.net/manual/en/function.mysql-insert-id.php

answered Jan 02 '12 at 14:20

mat

1,367
1
13
18

1

While this may theoretically answer the question, [it would be preferable](http://meta.stackexchange.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Bill the Lizard Jan 02 '12 at 16:46

score 2 · Answer 6 · edited Sep 02 '13 at 00:46

2

If you use mysql_query("..."), then mysql_insert_id() is the function you need. If you use something else to do queries, then the corresponding documentation should be checked

edited Sep 02 '13 at 00:46

Ben

54,723
49
178
224

answered Jan 02 '12 at 14:21

Alfabravo

7,493
6
46
82

score 1 · Answer 7 · answered Oct 18 '20 at 01:51

1

Simple method

Insert data :

$sql = "INSERT INTO table (id, foo) VALUES ('', 'bar')";
$conn->query($sql)

Get ID :

return $conn->insert_id;

answered Oct 18 '20 at 01:51

Farhan Yudhi Fatah

365
3
5

score 1 · Answer 8 · edited Aug 01 '19 at 08:17

1

mysqli_insert_id();

You can also simply run this query:

INSERT INTO table (foo) VALUES ('bar')

edited Aug 01 '19 at 08:17

oldboy

5,729
6
38
86

answered Jan 02 '12 at 14:20

dynamic

46,985
55
154
231

score 1 · Answer 9 · answered Jan 02 '12 at 14:20

1

Since it's PHP, mysql_insert_id should do the trick

answered Jan 02 '12 at 14:20

Hobo

7,536
5
40
50

score 1 · Answer 10 · answered Jan 02 '12 at 14:21

1

Take a look at the rather good example at http://php.net/manual/en/function.mysql-insert-id.php

answered Jan 02 '12 at 14:21

stimms

42,945
30
96
149

score 0 · Answer 11 · edited Jul 18 '13 at 07:34

0

also in PDO after the execute command as the example below:

$lastId = $dbh->lastInsertId();

click for REF

edited Jul 18 '13 at 07:34

answered Jul 18 '13 at 07:16

Amigo

47
6

apparently [there are issues](https://www.php.net/manual/en/pdo.lastinsertid.php#122009) with this approach – oldboy Aug 01 '19 at 07:44

PHP/MySQL Get autoincremented value after insert

11 Answers11