Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

php

mysql

People also ask

How do I count records in MySQL database?

To get the count of all the records in MySQL tables, we can use TABLE_ROWS with aggregate function SUM. The syntax is as follows. mysql> SELECT SUM(TABLE_ROWS) ->FROM INFORMATION_SCHEMA.

How do you get a count of records in SQL query?

SELECT COUNT(*) FROM table_name; The COUNT(*) function will return the total number of items in that group including NULL values. The FROM clause in SQL specifies which table we want to list. You can also use the ALL keyword in the COUNT function.

Which is used to retrieve number of rows in MySQL table?

There are two rows in the table. Without printing all the row data, we can get just the number of rows in the table using COUNT keyword.


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.


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;

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

SELECT SQL_CALC_FOUND_ROWS *
FROM   table1
WHERE  ...;

SELECT FOUND_ROWS();

FOUND_ROWS() must be called immediately after the query.