62

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
  • 30,962
  • 25
  • 85
  • 135
Renato Dinhani
  • 35,057
  • 55
  • 139
  • 199

3 Answers3

126

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
  • 30,962
  • 25
  • 85
  • 135
Renato Dinhani
  • 35,057
  • 55
  • 139
  • 199
  • 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
  • 1
    $pstm->insert_id does not contain the id! – Sebi2020 Sep 02 '21 at 14:48
  • https://www.php.net/manual/en/mysqli-stmt.insert-id.php – Jose Manuel Abarca Rodríguez Aug 10 '22 at 21:34
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
  • 26
  • 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
6

You need to use:

$stmt = $db->prepare("SQL Query");
$stmt->execute();
$id = $db->lastInsertId();
Leandro Bardelli
  • 10,561
  • 15
  • 79
  • 116
JrBriones
  • 93
  • 1
  • 5