CODEWITHSUNDEEP
phpmysql
c00000fd
c00000fd

Reputation: 22283

mysqli_affected_rows for UPDATE sometimes returns 0 on full row match

Say, if I have MySQL query to update a row:

$res = mysqli_query($link, 
    "UPDATE table SET val=1 WHERE id=5");

if($res)
{
    if(mysqli_affected_rows($link) > 0)
    {
        echo("Updated something!");
    }
    else
        echo("Didn't update");
}
else
    echo("Error");

How do I change it to distinguish between these 3 conditions:

  1. Found item with id=5 and set it.
  2. Did not find item with id=5.
  3. Error

In the way it's written now, if my database column val already has 1 in that row, mysqli_affected_rows will return 0.

Upvotes: 0

Views: 455

Answers (1)

Nick
Nick

Reputation: 147206

You can use mysqli_info to get the information you need to distinguish between the two cases. mysqli_info($link) after an UPDATE query will return a string something like

Rows matched: 1 Changed: 1 Warnings: 0

which you can then parse, for example using preg_match:

// $info = mysqli_info($link);
$info = 'Rows matched: 12 Changed: 8 Warnings: 0';
preg_match('/Rows matched: (\d+) Changed: (\d+)/', $info, $matches);
list(, $matched, $changed) = $matches;
echo "$matched rows matched, $changed rows changed\n";

Output:

12 rows matched, 8 rows changed

You can then use the values in $matched and $changed to distinguish between your two cases.

Upvotes: 2

Related Questions