Bug #77210 [Fbk->Opn]: PDO::lastInsertId returns 0
Edit report at https://bugs.php.net/bug.php?id=77210&edit=1
ID: 77210
User updated by: fcools at digilive dot nl
Reported by: fcools at digilive dot nl
Summary: PDO::lastInsertId returns 0
-Status: Feedback
+Status: Open
Type: Bug
Package: PDO MySQL
Operating System: MS Windows 8 v6.3 build 9600
PHP Version: 7.2.12
Block user comment: N
Private report: N
New Comment:
I'm happy to correct the code. My apologies.
Errors were created while adapting the code for this report.
I don't know how edit the original code as I don't see another option than adding a
comment.
Hopefully I'm doing the right thing by adding the corrected code as a comment (without any
errors or unknown references.
Test script:
---------------
<?php
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';
$options = array(
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => false,
\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4',
\PDO::MYSQL_ATTR_LOCAL_INFILE => true
);
try {
$dbh = new PDO($dsn, $user, $password, $options);
} catch (\PDOException $e) {
exit('Connection failed: ' . $e->getMessage());
}
$statement = <<<SQL
UPDATE my_table
SET
my_name = :my_name,
my_id = LAST_INSERT_ID(my_id)
WHERE my_id = :my_id;
SQL;
try {
$sth = $dbh->prepare($statement);
$sth->bindValue(':my_name', 'Foo');
$sth->bindValue(':my_id', 12, PDO::PARAM_INT);
$sth->execute();
if ($dbh->lastInsertId() == 0) {
echo 'Id not found!';
} else {
echo 'Row Successfully updated.';
}
} catch (\PDOException $e) {
echo 'Transaction failed!';
}
Expected result:
----------------
PDO::lastInsertId returns the value of expr of LAST_INSERT_ID(expr).
In case of my Test Script: 12
'Row Successfully updated.' is echoed.
Actual result:
--------------
PDO::lastInsertId returns 0.
'Id not found!' is echoed.
Previous Comments:
------------------------------------------------------------------------
[2018-11-27 15:04:19] danack@php.net
Please can you provide a working reproduction test script. Your code has reference to $this when not
in a class, and has missing semi-colons.
------------------------------------------------------------------------
[2018-11-27 15:00:44] fcools at digilive dot nl
Description:
------------
Considering the attached Test Script...
The my_id = LAST_INSERT_ID(my_id) part in my SET clause, sets the value of Maria DB's
LAST_INSERT_ID() to the value of my_id of the updated row.
Executing SELECT LAST_INSERT_ID(); in my sql client confirms the value is set (result = 12).
In php I use PDO::lastInsertId to get this value and if it's 0, a matching row doesn't
exist. This way I can make a difference between a 'my_id doesn't exist' error and a
silent UPDATE of nothing.
This works fine in PHP 5.6.23/MariaDB 10.1.13, but now I'm at PHP 7.2.11 or 7.2.12/MariaDB
10.1.36 and the returnvalue of PDO::lastInsertId remains zero while the row is updated indeed.
As per mysql/MariaDB manual:
If one gives an argument to LAST_INSERT_ID(), then it will return the value of the expression and
the next call to LAST_INSERT_ID() will return the same value.
and
The mysql_insert_id() C API function can also be used to get the value. See Section 28.7.7.38,
âmysql_insert_id()â.
I can confirm the code still works with PHP v7.1.8
Test script:
---------------
<?php
$statement = <<<SQL
UPDATE my_table
SET
my_name = :my_name,
my_id = LAST_INSERT_ID(my_id)
WHERE my_id = :my_id;
SQL;
try {
$sth = $this->dbh->prepare($statement);
$sth->bindValue(':my_name', 'Foo');
$sth->bindValue(':my_id', 12, PDO::PARAM_INT);
$sth->execute();
if ($this->dbh->lastInsertId() == 0) {
echo 'Id not found!';
} else {
echo 'Row Successfully updated.'
} catch (\PDOException $e) {
echo 'Transaction failed!';
}
Expected result:
----------------
PDO::lastInsertId returns the value of expr of LAST_INSERT_ID(expr).
In case of my Test Script: 12
'Row Successfully updated.' is echoed.
Actual result:
--------------
PDO::lastInsertId returns 0.
'Id not found!' is echoed.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=77210&edit=1
Thread (6 messages)