rookie MySql question about paging; Is one query enough?
- by Camran
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?
