How Can I Efficiently Get the Total Count of Results for Paginated MySQL Queries Using OFFSET and LIMIT?

Determining Total Results for Paginated MySQL Queries with Offset and Limit

When implementing pagination in PHP/MySQL, determining the total number of results is crucial for calculating page count. Instead of running the query twice, consider utilizing SQL_CALC_FOUND_ROWS.

Example:

SELECT SQL_CALC_FOUND_ROWS *
FROM directory_listings
WHERE category_id = ?
LIMIT ? OFFSET ?

This query retrieves the desired number of results while also calculating the total number of qualifying rows.

// Execute the query with pagination parameters
$results = $db->query($query, [
    'category_id' => $categoryId,
    'limit' => $limit,
    'offset' => $offset
]);

// Retrieve the found row count
$foundRows = $db->query("SELECT FOUND_ROWS() AS num_rows")->row();

// Calculate total pages
$totalPages = ceil($foundRows->num_rows / $limit);

Benefits:

• Efficient: Only one query execution instead of multiple.
• Accurate: Provides accurate count of all qualifying rows, regardless of pagination.

Note:

SQL_CALC_FOUND_ROWS must be used in conjunction with LIMIT to accurately calculate the row count and avoid miscounts.

The above is the detailed content of How Can I Efficiently Get the Total Count of Results for Paginated MySQL Queries Using OFFSET and LIMIT?. For more information, please follow other related articles on the PHP Chinese website!