Display only the last 300 MYSQL Results with a PHP pagination system

Question

Hi I have a system where I only want to display the last 300 records from MYSQL, normally i would just write the query like this LIMIT 300

the problem i have is i am using a pagination system which writes the query like this.

    $tableName="masterip_details";      
$targetpage ="raw_data.php";    
$limit = 30; 

$query = "SELECT COUNT(*) as num FROM $tableName where type='6' AND country_code='GB'";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];

$stages = 3;
$page = mysql_escape_string($_GET['page']);
if($page){
    $start = ($page - 1) * $limit; 
}else{
    $start = 0; 
    }   

// Get page data
$query1 = "SELECT * FROM $tableName where type='6' AND country_code='GB' LIMIT $start, $limit";
$result = mysql_query($query1);

The problem is because it uses the limit to calculate the start and finish page numbers i am not sure if i can limit the number of rows to return whilst using the pagination.

Why not? `LIMIT x, y` where x = where to start (page (10 - 1) * 300 per page = 2700) and y = how many to show (300) — casraf, Jun 25 '13 at 09:51
You might like this http://stackoverflow.com/questions/8060213/how-to-count-all-records-but-only-retrieve-limit-a-specific-number-for-display — Salketer, Jun 25 '13 at 09:52
Sorry might not have understand the question correctly... I keep my first comment as it could be interesting nonetheless. If you want the LAST 300 entries, LIMIT $total_pages-300,300 — Salketer, Jun 25 '13 at 09:54
Hi Chen, I don't want to show 300 per page i want to show 30 results per page by only take the last 300 records from the system — user2516043, Jun 25 '13 at 09:55
Use a subselect to get the latest 300 records using an ORDER BY and LIMIT, then from that select the the records you want for the page with another ORDER BY / LIMIT. Messy though — Kickstart, Jun 25 '13 at 09:59
I had considered something like that, but was hoping that there may be an easier way to do it. — user2516043, Jun 25 '13 at 10:02
Can't think of a more efficient way. You could use user variables to add a sequence number in a subselect, and then in the outer query get the records where the sequence number is within the range to display. But I suspect this would be slower, unless you cached the results in a table so for the next page you could just reuse the sequence numbers (this might be worthwhile if they are pages that are not updated often but which people will scroll through repeatedly). — Kickstart, Jun 25 '13 at 10:07
But if you just want to limit it to 300 to make it easy to calculate the min and max page numbers then you could instead use SQL_CALC_FOUND_ROWS - http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows — Kickstart, Jun 25 '13 at 10:09

Goutam Pal · Accepted Answer · 2013-06-25T10:10:54.517

0

select * from (SELECT * FROM $tableName where type='6' AND country_code='GB' order by AUTO_INCERMENT_ID DESC LIMIT 300) as a order by AUTO_INCERMENT_ID ASC LIMIT $start, $limit

edited Jun 25 '13 at 10:10

answered Jun 25 '13 at 10:04

Goutam Pal

1,763
1
10
14

Thanks, but that didn't make any difference at all – user2516043 Jun 25 '13 at 10:10
What difference were you expecting? – Kickstart Jun 26 '13 at 10:21

Display only the last 300 MYSQL Results with a PHP pagination system

1 Answers1