How can I count the numbers of rows that a mysql query returned?

Question

How can I count the numbers of rows that a mysql query returned? using PHP..

Answer 1

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 identically named mysql_num_rows function. As you've edited the question to mention you're using PHP, here's a bare bones sample from the manual:

$link = mysql_connect("localhost", "mysql_user", "mysql_password");
mysql_select_db("database", $link);

$result = mysql_query("SELECT * FROM table1", $link);
$num_rows = mysql_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.

Answer 2

If you want the result plus the number of rows returned do something like this. Using PHP.

$query = "SELECT * FROM Employee";
$result = mysql_query($query);
echo "There are ".mysql_num_rows($result)." Employee(s).";

Answer 3

SELECT SQL_CALC_FOUND_ROWS *
FROM   table1
WHERE  ...;

SELECT FOUND_ROWS();

FOUND_ROWS() must be called immediately after the query.

Answer 4

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 that 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();

Answer 5

Assuming you're using the mysql_ or mysqli_ functions, your question should already have been answered by others.

However if you're using PDO, there is no easy function to return the number of rows retrieved by a select statement, unfortunately. You have to use count() on the resultset (after assigning it to a local variable, usually).

Or if you're only interested in the number and not the data, PDOStatement::fetchColumn() on your SELECT COUNT(1)... result.