rookie MySql question about paging; Is one query enough?

Question

I have managed to get paging to work, almost. I want to display to the user, total nr of records found, and the currently displayed records. Ex:

4000 found, displaying 0-100.

I am testing this with the nr 2 (because I don't have that many records, have like 20). So I am using LIMIT $start, $nr_results;

Do I have to make two queries in order to display the results the way I want, one query fetching all records and then make a mysql_num_rows to get all records, then the one with the LIMIT ?

I have this:

 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 query:

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 have to make two queries?

Answer 1

You can make do with the following two queries:

SELECT  SQL_CALC_FOUND_ROWS *
FROM    t_source
LIMIT   100, 10;

SELECT  FOUND_ROWS();

The first one will return the results, the second one will show the number of records that would be returned be there no LIMIT clause.

Answer 2

yes, to get the total number of records found, you need another query.
Though your current method of getting total is unacceptable.
You can use either select count(*) ... query or SQL_CALC_FOUND_ROWS/FOUND_ROWS() feature

Answer 3

The alternative to the SQL_CALC_FOUND_ROWS query would be a query selecting COUNT(*). This article suggests it may even be faster when appropriate indexes are available:

SELECT COUNT(*) FROM your_table WHERE ...