How to Count Results in MySQL Queries?

Counting Results from MySQL Queries

The number of rows returned by a MySQL query can be retrieved using several methods:

Iterating Over Results

Simply loop through the result set and count the rows. While this approach can be straightforward, it's not very efficient.

Using MySQL Functions

MySQL provides the mysql_num_rows function to count the rows in a result. This function is exposed in client libraries, such as mysqli_num_rows in PHP:

<code class="php">$result = mysqli_query($link, "SELECT * FROM table1");
$num_rows = mysqli_num_rows($result);</code>

Using COUNT(*) for Selective Counting

To count rows matching specific criteria, use the COUNT(*) aggregate function. For example:

<code class="sql">SELECT COUNT(*) FROM foo WHERE bar= 'value';</code>

Getting Total Rows When Using LIMIT

If your query includes a LIMIT clause, you can use SQL_CALC_FOUND_ROWS and FOUND_ROWS() to retrieve the total number of rows that would have been returned without the LIMIT:

<code class="sql">SELECT SQL_CALC_FOUND_ROWS * FROM foo
   WHERE bar="value" 
   LIMIT 10;

SELECT FOUND_ROWS();</code>

Note: Using SQL_CALC_FOUND_ROWS is deprecated in MySQL 8.0.17 and higher. It's recommended to issue a separate query to calculate the count instead.

The above is the detailed content of How to Count Results in MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!