How Can I Find the Total Count of Results in MySQL Pagination with Offset and Limit?

Finding Total Results with Offset and Limit in MySQL

Pagination allows users to browse through large datasets in manageable chunks. However, determining the total number of pages requires knowing the total number of results. When using offset and limit for pagination, it seems inefficient to run the same query twice, once with a limit and once without.

SQL_CALC_FOUND_ROWS: A Solution

To address this issue, MySQL provides a special keyword: SQL_CALC_FOUND_ROWS. By adding this keyword to the beginning of your query, MySQL will calculate and store the total number of rows that would have been returned without the LIMIT clause.

Example Query:

<code class="sql">SELECT SQL_CALC_FOUND_ROWS * 
FROM table_name 
WHERE category_id = '1' 
ORDER BY date_created DESC
LIMIT 10 OFFSET 20;</code>

Retrieving Total Number of Results:

After executing the above query, you can use the following PHP code to retrieve the total number of results:

<code class="php">$totalRows = $db->query('SELECT FOUND_ROWS() AS total')->row()->total;</code>

Benefits of SQL_CALC_FOUND_ROWS:

• Increased efficiency: Eliminates the need to run the query twice.
• Accuracy: Guarantees the total count is correct, even if the underlying data changes between queries.
• Simplicity: Easy to implement with a single keyword.

The above is the detailed content of How Can I Find the Total Count of Results in MySQL Pagination with Offset and Limit?. For more information, please follow other related articles on the PHP Chinese website!