Rookie MySql Paging Question; Is a single request enough?

Question

I managed to get the paging to work, almost. I want to show the user the total number of records found and the currently displayed records. Example:

4000 found, displaying 0-100. 

I am testing this with nr 2 (because I don't have entries like 20). Therefore, I use LIMIT $start, $nr_results;

Do I have to make two queries to display the results the way I want, one query that retrieves all the records, and then do mysql_num_rows to get all the records, and then using LIMIT?

I have it:

  mysql_num_rows($qry_result); $total_pages = ceil($num_total / $res_per_page); //$res_per_page==2 and $num_total = 2 if ($p - 10 < 1) { $pagemin=1; } 

else {$ pagemin = $ p - 10; } if ($ p + 10> $ total_pages) {$ pagemax = $ total_pages; } else {$ pagemax = $ p + 10; }

Here is the request:

 SELECT mt.*, fordon.*, boende.*, elektronik.*, business.*, hem_inredning.*, hobby.* FROM classified mt LEFT JOIN fordon ON fordon.classified_id = mt.classified_id LEFT JOIN boende ON boende.classified_id = mt.classified_id LEFT JOIN elektronik ON elektronik.classified_id = mt.classified_id LEFT JOIN business ON business.classified_id = mt.classified_id LEFT JOIN hem_inredning ON hem_inredning.classified_id = mt.classified_id LEFT JOIN hobby ON hobby.classified_id = mt.classified_id ORDER BY modify_date DESC LIMIT 0, 2 

Thanks, if you need more input let me know.

Basically, Q is, do I need to make two queries?

Answer 1

yes, to get the total number of records found, you need one more query.
Although your current method of obtaining totality is unacceptable. You can use either the select count(*) ... SQL_CALC_FOUND_ROWS/FOUND_ROWS() or the SQL_CALC_FOUND_ROWS/FOUND_ROWS() function

Answer 2

You can execute the following two queries:

 SELECT SQL_CALC_FOUND_ROWS * FROM t_source LIMIT 100, 10; SELECT FOUND_ROWS(); 

The first returns the results, the second shows the number of records to be returned, there is no LIMIT .

Answer 3

An alternative to SQL_CALC_FOUND_ROWS is a query with a choice of COUNT (*). This article assumes that it can be even faster with the appropriate indexes:

 SELECT COUNT(*) FROM your_table WHERE ...