PDO lastInsertId is empty after inserting a row with MariaDB INSERT...RETURNING #11503
Closed as not planned
Description
Description
The following code:
<?php
$pdo = new \PDO(
'mysql:host=localhost;port=3306',
'root',
'pass',
);
$pdo->query('CREATE TABLE InsertIdTest (
id INT NOT NULL AUTO_INCREMENT,
one VARCHAR(45) NULL,
PRIMARY KEY (id)
)');
var_dump(
$pdo
->query('INSERT INTO InsertIdTest (one) VALUES ("Hello World")')
->fetchAll(\PDO::FETCH_ASSOC)
);
var_dump($pdo->lastInsertId());
var_dump(
$pdo
->query('INSERT INTO InsertIdTest (one) VALUES ("Bye Bye") RETURNING *')
->fetchAll(\PDO::FETCH_ASSOC)
);
var_dump($pdo->lastInsertId());Resulted in this output:
array(0) {
}
string(1) "1"
array(1) {
[0]=>
array(2) {
["id"]=>
int(2)
["one"]=>
string(7) "Bye Bye"
}
}
string(1) "0"
But I expected this output instead:
array(0) {
}
string(1) "1"
array(1) {
[0]=>
array(2) {
["id"]=>
int(2)
["one"]=>
string(7) "Bye Bye"
}
}
string(1) "2"
As the output shows, the last insert ID returned by PDOs lastInsertId is always "0" if the row gets inserted via a INSERT...RETURNING statement. If a SELECT LAST_INSERT_ID() statement is executed after the INSERT...RETURNING statement the correct insert ID can be retrieved. I'd expect the lastInsertId method to also return "2".
MariaDB version >= 10.5 for INSERT...RETURNING support.
PHP Version
8.2.6
Operating System
No response