How to Determine the Total Result Count in MySQL with Offset and Limit?

Determining Total Result Count with MySQL Offset and Limit

When implementing pagination with offset and limit, it's crucial to determine the total number of results to calculate the necessary pages. While executing the query twice (once with and without LIMIT) may seem redundant, there are better approaches.

MySQL provides a way to retrieve the total number of results without an additional query using SQL_CALC_FOUND_ROWS. By adding this keyword before the main query, MySQL calculates the total number of rows that would have been returned without the LIMIT clause.

Here's an example:

<code class="sql">SELECT SQL_CALC_FOUND_ROWS *
FROM directory_listing
WHERE category_id = <category_id>
LIMIT offset, limit;</category_id></code>

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

$query = $this->db->query('SELECT FOUND_ROWS() AS total_count;');
$total_count = $query->first_row()->total_count;

This approach allows you to retrieve the total count efficiently without executing an additional query. By utilizing SQL_CALC_FOUND_ROWS, you can improve the performance and resource utilization of your pagination feature.

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