How can I count the numbers of rows that a MySQL query returned?
In the event you have to solve the problem with simple SQL you might use an inline view.
select count(*) from (select * from foo) as x;
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 Rows\n";
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...
If you'd used a LIMIT clause but want to know how many rows you'd get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();
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.
SELECT SQL_CALC_FOUND_ROWS *
FROM table1
WHERE ...;
SELECT FOUND_ROWS();
FOUND_ROWS()
must be called immediately after the query.
If your SQL query has a LIMIT
clause and you want to know how many results total are in that data set you can use SQL_CALC_FOUND_ROWS
followed by SELECT FOUND_ROWS();
This returns the number of rows A LOT more efficiently than using COUNT(*)
Example (straight from MySQL docs):
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();