How to get the number rows that have been inserted, changed or skipped

1. Comments (1)

When MySQL executes a Data Modification Query, it provides some additional info on the outcome, which is usually displayed in the console. This info is also being exposed through MySQL C API and this way also available in PHP's mysqli module. Although the numbers returned aren't explained on that page, I made an educated guess that for the INSERT..ON DUPLICATED KEY UPDATE query the Records: property represents the total number of rows in the INSERT query, and Duplicates: represents the number of rows that were updated due to duplicated primary/unique key.

This function can be useful in situations when we want to tell how many rows were actually updated, as opposed to how many rows were found (MySQL doesn't consider a row as updated if the new data for it is exactly the same as current). And it becomes even more sophisticated with ON DUPLICATE KEY UPDATE queries where we may want to count rows that were inserted, updated, or remained the same.

Unfortunately, the data supplied by this function is not readily usable, being just a text string. Besides, to get the numbers for ON DUPLICATED query, some calculations have to be made based on the affected_rows value, which, as MySQL documentation says, can return one of three different values for each row:

• 1 if a new row was inserted
• 2 if existing row was updated
• 0 if existing row kept its current values

And on top of that, the info gets returned in different formats for different queries. Also, as if that is not enough, there is a bug described below.

So I decided to create a replacement that should provide all the required data at once. The function can be used with INSERT, UPDATE and ALTER TABLE statements only. For delete, we only need the number of rows which is already provided by affected_rows property.

function mysqli_info_array($mysqli) {
    $info = $mysqli->info;
    $affected = $mysqli->affected_rows;
    $error = "mysqli_info_array() only supports INSERT or UPDATE query";

    // a bug: if only one row inserted, mysqli_info() returns null :(
    if ($info === null) {
        $result = match ($affected) {
            -1 => ['records' => 0, 'inserted' => 0, 'warnings' => 0, 'updated' => 0, 'skipped' => 0],
            0  => ['records' => 1, 'inserted' => 0, 'warnings' => 0, 'updated' => 0, 'skipped' => 1],
            1  => ['records' => 1, 'inserted' => 1, 'warnings' => 0, 'updated' => 0, 'skipped' => 0],
            2  => ['records' => 1, 'inserted' => 0, 'warnings' => 0, 'updated' => 1, 'skipped' => 0],
            default => throw new RuntimeException($error),
        };
    } else {
        if (preg_match("!Records: (\d+)  Duplicates: (\d+)  Warnings: (\d+)!", $info, $parsed)) {
            $result['records'] = $parsed[1];
            $result['updated'] = $parsed[2];
            $result['warnings'] = $parsed[3];
            $result['inserted'] = $affected - $result['updated'] * 2;
            $result['skipped'] =  $result['records'] - $result['updated'] - $result['inserted'];
        } elseif (preg_match("!Rows matched: (\d+)  Changed: (\d+)  Warnings: (\d+)!", $info, $parsed)) {
            $result['records'] = $parsed[1];
            $result['updated'] = $parsed[2];
            $result['warnings'] = $parsed[3];
            $result['inserted'] = 0;
            $result['skipped'] =  $result['records'] - $result['updated'];
        } else {
            throw new RuntimeException($error);
        }
    }
    $result['affected'] = $affected;
    $result['info'] = $info;
    return $result;
}

Here, the 'records' property represents either the number of rows that were present in the INSERT query or the number of matched rows for the updated query. Other numbers are self-explanatory in my understanding.

A live demo can be found here: https://phpize.online/s/Nd