How to limit mysql rows to select newest 50 rows

Question

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 an 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 retrieving the documents I mean there may be thousands of documents.

All the documents whatever in my info table are retrieving 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...

There are heaps of helping articles about that. e.g. http://code.tutsplus.com/tutorials/how-to-paginate-data-with-php--net-2928 — KiwiJuicer, May 11 '16 at 13:34

score 0 · Answer 1 · answered May 11 '16 at 13:37

Search for pagination using php & mysql. That may become handy with your problem.

To limit a mysql query to fetch 50 rows use LIMIT keyword. You may need to find & store the last row id(50th row) so that you can continue with 51th to 100th rows in the next page.

Post what you have done with your code. Please refer to whathaveyoutried[dot]com

Asur · Accepted Answer · 2016-05-11T14:04:23.833

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.

thank you... this would be helpful ...but in mysql table.. document info is added and rows are updated every time a new document is added and i would like to display newest 50 first but the last number in mysql table is changed every time a new document added... so i can not change mysql query everytime in my code — ram, May 11 '16 at 13:52
@ram If you are talking about the timestamp, you don't need to add it or change it everytime you update, just change your table structure and add that field so you have a reference of when an element has been created originally. — Asur, May 11 '16 at 13:54

score 0 · Answer 3 · edited May 23 '17 at 12:02

0

check this example from another post https://stackoverflow.com/a/2616715/6257039, you could make and orber by id, or creation_date desc in your query

edited May 23 '17 at 12:02

Community

1
1

answered May 11 '16 at 14:12

caryarit ferrer

326
3
12

How to limit mysql rows to select newest 50 rows

3 Answers3