How to get the number rows that has been actually changed

1. Comments (1)

One must remember that when using mysqli::affected_rows property, it will return only the number of rows that were actually changed. If a row were found but the new data is the same as old, MySQL won't update the row and won't count this row towards the number.

Luckily, mysqli has an unique mysqli_info() function which returns separate results for the the number of rows found and affected by a DML query. In any other driver, including PDO, you can have either one or another but not both at once. Although it returns a string, a simple code could be written to parse its result into a neat array.

So, a bit more readable version of this code can be made into a simple function like this

function mysqli_info_array($mysqli) {
    preg_match_all('~: (\d+)~', $mysqli->info, $matches); 
    return [
        'matched' => (int)$matches[1][0],
        'changed' => (int)$matches[1][1],
        'warnings' => (int)$matches[1][2],
    ];
}

and after that one will be able check the number of found and affected rows separately. For example, given we are updating just a single row,

$db->query("update test set i=2");
$info = mysqli_info_array($db);
if ($info['matched'] === 0) {
    $result = "No rows were matched";
} elseif($info['changed'] === 0) {
    $result = "Rows were found but not updated";
} else {
    $result = "Rows were found and updated";
}