How to get affected rows using mysqli?

Question

After running a query using mysqli, I want to know whether it actually affected any rows. For example,

$res = mysqli("UPDATE table SET name='name' where id=1");

How to get count of affected rows?

Answer 1

An excerpt from my mysqli tutorial:

The number of rows affected by the INSERT, UPDATE and DELETE query could be quite useful. What is interesting, mysqli has not one but two facilities that can return such a number.
One of them is familiar affected_rows property:

$db->query("DELETE FROM users");
echo $db->affected_rows();

But one must remember that MySQL will return here 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 count such a row towards that 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],
   ];
}

One can easily 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";
}

Answer 2

Remove $mysqli->close(); And make use of

$mysqli->query("INSERT INTO Table (name, value) VALUES ('$name', '$value')"); 
echo $mysqli->affected_rows;