0

I know this question has been asked many times before and I feel kinda stupid, but I can't get it to work. So I'm trying to get the insert_id after calling a prepared statement that inserts a single row of data. The return value is always 0. What am I doing wrong?

I'm using PHP-8.2, so execute_query() is a valid function. This is also inside a transaction, but I've already tested it without the transaction and it's the same result. Also the insert_id is not in the result (tested that for my sanity)

$this->db->execute_query('CALL preparedINSERTStatement(?, ?)', [$value1, $value2]);
$id = $this->db->insert_id;

If you need more information that I didn't include feel free to ask!

mannaris
  • 63
  • 8

1 Answers1

0

The value of insert_id is returned in the OK packet sent by MariaDB server to the client.

For each SQL command inside a stored procedure the server sends an OK packet. Additionally an OK packet will be sent at the end, which indicates if the stored procedure was executed successfully. This last ok packet overwrites the previous OK packet: Since there was no data inserted, the insert_id is zero.

If you need to retrieve the insert_id from a statement which was executed inside a stored procedure use SELECT last_insert_id().

Georg Richter
  • 5,970
  • 2
  • 9
  • 15
  • Wouldn't that be a race condition? – mannaris Aug 15 '23 at 08:48
  • No - last_insert_id() returns the last inserted id for the current connection. Unless you share your connection over multiple threads (which is not recommended) there won't be any race condition. – Georg Richter Aug 15 '23 at 08:55