How to Count the Number of Rows Returned by a MySQL Query?

Determining the Number of Rows Returned by a MySQL Query

Counting the number of rows returned by a MySQL query is essential for pagination, data analysis, and other scenarios. Here are various methods to achieve this:

Method 1: Using mysql_num_rows for Iterative Counting

For PHP applications, you can utilize the mysql_num_rows function to determine the number of rows in a result set. This function is exposed through the mysqli_num_rows function in PHP, as exemplified below:

<code class="php">$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";</code>

Method 2: COUNT(*) Function for Matching Criteria

To count the number of rows that meet specific criteria, use the COUNT(*) function in your query. For instance:

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

Method 3: SQL_CALC_FOUND_ROWS for Total Rows with LIMIT

If you're using a LIMIT clause and need the total number of rows without the limit, you can employ the SQL_CALC_FOUND_ROWS and FOUND_ROWS() functions:

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

SELECT FOUND_ROWS();</code>

Note: SQL_CALC_FOUND_ROWS is deprecated as of MySQL 8.0.17 and is recommended to be replaced with a separate query to obtain a count.

The above is the detailed content of How to Count the Number of Rows Returned by a MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!