How can you optimize a PHP query to retrieve both the total number of records and the paginated data without executing the same query twice?
When retrieving paginated data in PHP, you often need to execute the same query twice - once to get the total number of records and again to fetch the paginated data. To optimize this process and avoid executing the query twice, you can use SQL_CALC_FOUND_ROWS in your query to get the total count, and then use FOUND_ROWS() to retrieve the total count without running the query again.
// Execute query to retrieve paginated data
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM your_table LIMIT $offset, $limit";
$result = mysqli_query($connection, $query);
// Get paginated data
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
// Get total number of records without executing the query again
$count_query = "SELECT FOUND_ROWS() as total_count";
$count_result = mysqli_query($connection, $count_query);
$total_count = mysqli_fetch_assoc($count_result)['total_count'];
// Use $data for paginated data and $total_count for total count