55

I want retrieve the id of a inserted row in the database, but I don't know how to do this.

I tried to return using the SQL clause RETURNING id, but not works.

How I can return the id after the insertion of a row?

Dharman
  • 26,923
  • 21
  • 73
  • 125
Renato Dinhani
  • 32,560
  • 53
  • 130
  • 195

3 Answers3

112

After calling the execute() method on the Prepared Statement, the id of the insert row will be in the insert_id attribute.

$pstm->execute();
$pstm->insert_id;
Dharman
  • 26,923
  • 21
  • 73
  • 125
Renato Dinhani
  • 32,560
  • 53
  • 130
  • 195
  • 3
    But will it return a corresponding and unique ID for each execution? Top comment under [documentation](http://php.net/manual/en/mysqli-stmt.insert-id.php#102299) says that you should use `mysqli_connection->insert_id` instead of `mysqli_stmt->insert_id` if you execute the same prepared statement several times. Thought you may be aware whether this is correct or no. – Cheslab Jan 30 '18 at 22:45
  • 1
    This only worked for me after I set my primary index column to `AUTO_INCREMENT`. – ban-geoengineering Jun 20 '18 at 18:59
  • $pstm->insert_id does not contain the id! – Sebi2020 Sep 02 '21 at 14:48
9
$newid = mysqli_insert_id($mysqli_db);

$mysqli_db is your mysqli database connection. As far as I know it shouldn't matter on which way ou inserted the row (prepared statement or direct INSERT INTO). mysqli_insert_id() should return the id of the last inserted row using this db connection.

The alternative is to make another query like SELECT LAST_INSERT_ID();.

Marco
  • 960
  • 2
  • 7
  • 22
  • 1
    It's worth mentioning this method also works in transactions (not so obvious). – Itako Jul 02 '11 at 12:13
  • This works with `PreparedStatement`? I got the id doint this: `$pstm->execute();` and after that, the `insert_id` attribute has the id: `$mysqli->insert_id;` – Renato Dinhani Jul 02 '11 at 19:36
5

You need to use:

$stmt = $db->prepare("SQL Query");
$stmt->execute();
$id = $db->lastInsertId();
Leandro Bardelli
  • 8,566
  • 13
  • 69
  • 102
JrBriones
  • 83
  • 1
  • 5