|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2018-11-27 15:00 UTC] 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.
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Sat Apr 27 23:01:30 2024 UTC |
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.