ram ram - 1 year ago 49
SQL Question

how to limit mysql rows to select newest 50 rows

how to limit mysql rows to select newest 50 rows and have a next button such that next 50 rows are selected without knowing the exact number of rows... i mean there may be a increment in number of rows in table ... well i will explain it clearly ... i was developing a web app as my project on document management system... using php mysql html ... everything is done set but ... while retriving the documents i mean there may be thousands of documents ... all the documents what ever in my info table are retiving at a time in home page which was not looking good... so i would like to add pages on such that only newest 50 documents are placed in first page next 50 are in second and so on... but how come i know the exact number of rows every time and i cannot change the code every time a new document added so... numrows may not be useful i think... help me out please...

Answer Source

What you are looking for is called pagination, and the easiest way to implement a simple pagination is using LIMIT x , y in your SQL queries.

You don't really need the total ammount of rows you have, you just need two numbers:

  • The ammount of elemments you have already queried, so you know where you have to continue the next query.
  • The ammount of elements you want to list each query (for example 50, as you suggested).

Let's say you want to query the first 50 elements, you should insert at the end of your query LIMIT 0,50, after that you'll need to store somewhere the fact that you have already queried 50 elements, so the next time you change the limit to LIMIT 50,50 (starting from element number 50 and query the 50 following elements).

The order depends on the fields you are making when the entries are inserted. Normally you can update your table and add the field created TIMESTAMP DEFAULT CURRENT_TIMESTAMP and then just use ORDER BY created, because from now on your entries will store the exact time they were created in order to look for the most recent ones (If you have an AUTO_INCREMENT id you can look for the greater values aswell).

This could be an example of this system using php and MySQL:

$page = 1;
if(!empty($_GET['page'])) {
    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
    if(false === $page) {
        $page = 1;

// set the number of items to display per page
$items_per_page = 50;

// build query
$offset = ($page - 1) * $items_per_page;
$sql = "SELECT * FROM your_table LIMIT " . $offset . "," . $items_per_page;

I found this post really useful when I first try to make this pagination system, so I recommend you to check it out (is the source of the example aswell).

Hope this helped you and sorry I coudn't provide you a better example since I don't have your code.