Sets of 25 with PHP and MySQL

Question

What I'm trying to accomplish is,

Add A list of words as separate entries into a table.
Display the words in a group of 25. So if there are 25 entries, 1-25 will be shown in a list. Also, if there are 27 entries, only number 26 and number 27 will show. So it is not always the last 25 entries into the table.

I already know how to insert and pull information from a database, but I do not know how to do the proper grouping to get them in groups of 25.

Edit: How would I increase the LIMIT automatically based on the number of rows in the table?

Edit #2: This question is not a duplicate. I do not want the most recent 25. I want them in groups of 25. My question was answered below.

`LIMIT`. http://stackoverflow.com/questions/4708708/mysql-order-by-limit — Karoly Horvath, Feb 06 '14 at 00:08
It sounds like you want the last 25, but you say "it's not always the last 25"... can you clarify — helion3, Feb 06 '14 at 00:08

score 1 · Answer 1 · answered Feb 06 '14 at 00:18

You want to use this query, the order by is only if you need to order it a certain way to get the right sets of 25:

SELECT * FROM `your_table` ORDER BY `field_name` DESC LIMIT 0, 25

2nd set of 25 you will need to use this (note the first LIMIT number is different):

SELECT * FROM `your_table` ORDER BY `field_name` DESC LIMIT 25, 25

3rd set

SELECT * FROM `your_table` ORDER BY `field_name` DESC LIMIT 50, 25

The first LIMIT number is the offset, or where you want the records to start pulling from, the second is the number of records to pull. If there is only 26 and 27, the second query here will work for that. You can see that each set requires you to bump the offset up by the number of records you are requesting in this case.

Thanks! Is there any way to have the limit automatically adjust according to the number of rows in the table? — Chase Broyles, Feb 06 '14 at 00:28
You can set a variable in PHP and add 25 to it every time you load the page, for example, so you will always get the right offset, and then put that variable in the query's offset. — Ryan, Feb 06 '14 at 00:31

score 0 · Accepted Answer · answered Feb 06 '14 at 00:28

IMHO you can't do this in one query (at least in standard SQL in readable way).

You need:

Get number of entries in table (assuming table is named tbl1):
```
SELECT count(*) FROM tbl1;
```
Get number of entries to be not-selected by using integer division. The exact code depends on the language you use. For example on PHP (assuming that result of step 1 is stored in $count):
```
$offset = floor($count/25);
//or: $offset = $count - $count%25;
```

Extract needed entries with using LIMIT:

SELECT * FROM tbl1 ORDER BY <specify-order-here> LIMIT 25 OFFSET <insert-$offset-here>;

Sets of 25 with PHP and MySQL

2 Answers2