MySQL: How many records have been found or are affected by my query?

Sometimes, we want to know in PHP, how many records were found in a MySQL search query with SELECT in total or how many rows were affected by an UPDATE or DELETE. Both parameters can be easily read out by a PHP-MySQL function. Here, I will show you how to do it:

How many records have been found?

We have an arbitrary SELECT query and we are interested in how many records have been found without going through all lines of the result:

$query = "SELECT name FROM tab WHERE zipcode = 46242";
$res = mysqli_query($db, $query);

$c = mysqli_num_rows($res);
echo "$c people have been found.";

The function mysqli_num_rows() provides us with the number of data records contained in a result set created by mysqli_query(). This information is interesting, for example, to display the number of hits for a search result or to decide whether a further page of search results is needed.

How many records are affected?

After an UPDATE or a DELETE command, we are interested in how many data records were actually affected and changed respectively deleted by the request:

Let's first look at a small example of an UPDATE command:

$query = "UPDATE tab SET town = 'Bottrop' WHERE zipcode = 46242";
$res = mysqli_db_query($db, $query);
 
$c = mysqli_affected_rows($res);
echo "$c records have been changed.";

And here is another example that provides us with the number of deleted data records after a DELETE command:

$query = "DELETE FROM tab WHERE zipcode = 46119";
$res= mysqli_query($db, $query);

$c = mysqli_affected_rows($res);
echo "$c records have been deleted.";

The function mysqli_affected_rows() gives us the number of modified records that were affected in the last MySQL query. If there was an error in this request, the return value is -1. This information is interesting, for example, to check whether any rows have been changed after an UPDATE at all or to determine how many data records a delete has removed from our database.

Incidentally, the function mysqli_affected_rows() works not only after an UPDATE or a DELETE: With this function, we can also determine the number of data records concerned for any other MySQL command such as INSERT or SELECT .