MySQL: How many records have been found or are affected by my query?
Info by Stefan Trost | Last update on 2023-01-18 | Created on 2012-05-30
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 .
About the Author
You can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile
Related Topics
MySQL: Delete Data from Table - Difference between TRUNCATE, DELETE and DROP
Tutorial | 0 Comments
SQLite: How many rows were affected by an UPDATE or DELETE?
Info | 0 Comments
MySQL: Display search results on multiple pages
Tutorial | 0 Comments
How to Replace multiple Texts at the same Time
Tutorial | 0 Comments
The Askingbox Search
Info | 0 Comments
Delphi/Lazarus: Command Line Parameter Tutorial Part 1: Sending
Tutorial | 0 Comments
MySQL: Write current Date or Time into Column
Tutorial | 0 Comments
Important Note
Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.
Participate
Ask your own question or write your own article on askingbox.com. That’s how it’s done.