1

When I run the following statement in the mySQL WorkBencg I get a status message saying "0 row(s) affected Rows matched: 1 Changed: 0 Warnings: 0":

UPDATE `videos`
   SET `status` = 'Deleted'
 WHERE `users_id` = 11
   AND `artists_id` = 4
   AND `tracks_id` = 0
   AND `slug` in ( '20201203113235.538254-yCClIi886vs' )

Which, to my understanding indicates that the update was syntactically correct and a matching record was found, but not updated because the matching row's status column already has the 'Deleted' value.

When I run the following in PHP, I get a 0 count no rows were changed, which interns causes $message to be set to a message indicating that the update had failed:

$stmt   = 'UPDATE `videos` ' .
             'SET `status` = \'Deleted\' ' .
           'WHERE `users_id` = ? ' .
             'AND `artists_id` = ? ' .
             'AND `tracks_id` = 0' .
             'AND `slug` in ( ? )';

$values = [ 11, 4, '20201203113235.538254-yCClIi886vs' ];

if( ( $count = $conn->executeUpdate( $stmt, $values ) ) < 1 )

    $message = 'Your video information was not successfully marked for deletion!';

 else
 ⫶

Is there a way that I can get the individual affected rows, matched rows, and changed rows values in PHP? This way I could tell that the Updated matched the where criteria without needing to prefetch the row to determine this and that status was already set to 'Deleted'?

If not, is there a way to force the Update to overwrite the status column with the set value, even if it already has that value, and return the number of rows that were overwritten?

As a work-around, I added a call to errorCode in the if-statement that executes the Update statement as follows:

if( ( ( $count = $conn->executeUpdate( $stmt, $values ) ) < 1 ) ||
    ( $conn->errorCode() !== '0000' ) )

The $conn->errorInfo()[ 0 ] !== '00000' and $conn->errorCode() !== '00000' ) ) functions return error information/code for the last executed mySQL statement, and a code of '00000' found in the first element of the array returned by errorInfo or the value returned by errorCode, indicates that no error occurred.

For more details on these functions and the error codes, please see:

errorCode, errorInfo, and SQLSTATE Codes.

This isn't exactly what I'm looking for, but if I know that there wasn't an error and no rows were returned, can I infer that there as a match without needing to do a separate SELECT to look for the existence of matches?

The questib How to determine if Mysql update succeeded does not answer my question unless there really isn't another way to determine that there is a match.

Thank you

  • Are you using doctrine as an abstraction layer? – Jason K Dec 03 '20 at 16:41
  • I using Dbal to execute the mySQL statements. I know that this gives me access to PDO. I never went with TWIG or the query builder tools, because I didn't want that much overhead and I'm comfortable working with the mySQL statements directly. So ,doctrine, probably not. –  Dec 03 '20 at 17:53
  • Since you asked about doctrine, and I'm currently using Dbal v2, does a later version of Dbal fix my issue? I look at DBal v3, but couldn't tell if that version change the way that affected-rows/changed rows worked or not, or if there were more functions that let me get at the matched value after an Update. –  Dec 03 '20 at 20:05
  • If you don't mind, would mind looking at another question that I posted, yesterday, https://stackoverflow.com/questions/65097405/how-does-the-mysql-insert-on-duplicate-key-column-value-column-1-work ? –  Dec 03 '20 at 20:07
  • I asked about Dbal because I could not find the executeUpdate in the php documentation. I use PDO and found the same problem as you. Updating a record that does not change return a roe count of 0. I havent had much time to research it. – Jason K Dec 03 '20 at 20:34

0 Answers0