Posted By: phpaddict
How can I count the number of rows that a MySQL query returned?
Getting total rows in a query result…
You could just iterate the result and count them. You don’t say what language or client library you are using, but the API does provide a mysql_num_rows function which can tell you the number of rows in a result.
This is exposed in PHP, for example, as the mysqli_num_rows function. As you’ve edited the question to mention you’re using PHP, here’s a simple example using mysqli functions:
$link = mysqli_connect("localhost", "user", "password", "database"); $result = mysqli_query($link, "SELECT * FROM table1"); $num_rows = mysqli_num_rows($result); echo "$num_rows Rowsn";
Getting a count of rows matching some criteria…
Just use COUNT(*) – see Counting Rows in the MySQL manual. For example:
SELECT COUNT(*) FROM foo WHERE bar= 'value';
Get total rows when LIMIT is used…
SELECT SQL_CALC_FOUND_ROWS * FROM foo WHERE bar="value" LIMIT 10; SELECT FOUND_ROWS();
For very large tables, this isn’t going to be particularly efficient, and you’re better off running a simpler query to obtain a count and caching it before running your queries to get pages of data.