<?php
$mysqli->query('create table test (sku int primary key, qty int, price int)');
$mysqli->query('insert into test values (1, 2, 10),(2, 2, 15),(3, 3, 0)');
$insert = '(1,2,10)';
#$insert = '(1,2,11)';
#$insert = '(11,2,11)';
#$insert = '(1,2,10),(2,5,150),(3,10,22),(4,2,2)';
#$insert = '(1,3,11),(2,5,150),(3,10,22),(4,2,2)';
$sql = "insert into test values $insert on duplicate key update qty=values(qty), price=values(price)";
#$sql = "update test set qty=2 where qty=2";
#$sql = "update test set qty=2 where qty=3";
#$sql = "update test set qty=2 where qty=22";
$mysqli->query($sql);
$info = mysqli_info_array($mysqli);
echo "Rows matched for update or present in the insert query: {$info['records']}
Rows inserted: {$info['inserted']}
Rows updated: {$info['updated']}
Rows skipped: {$info['skipped']}
";
echo json_encode($info, JSON_PRETTY_PRINT);
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;
}