CODEWITHSUNDEEP
phpmysqlpaginationlimit
ronnyrr
ronnyrr

Reputation: 1551

SQL - Select with Limit fail

Currently I'm having 12 rows in my database. Between adding all of them I deleted some so the ID columns aren't ascending correctly.

I'm running the following query the create pages by using SQL $page is a $_GET which is numerically (1,2,3 etc.):

"SELECT  *
FROM    cms_guestbook
WHERE   moderated = 'Y'
ORDER BY  id DESC
LIMIT   ". (($page - 1) * 5) . ", " . (5 * $page)

Add page 1 it displays correctly and when I var_dump this query it shows: LIMIT 0, 5. Add page 2 it is doesn't display correctly. I see 7 rows (5-12), but my var_dump says: LIMIT 5, 10.

Page 3 also displays correctly row 10-12. The var_dump also says: LIMIT 10, 15.

Someone who knows what I'm doing wrong? Thanks!

Upvotes: 0

Views: 216

Answers (1)

csi
csi

Reputation: 9328

LIMIT takes 2 arguments - OFFSET, Number of Rows.

In your case, you would want...

LIMIT 0, 5
LIMIT 5, 5
LIMIT 10, 5

If you always wanted to return 5 rows but start at a different index each time.

Based on your question, change the LIMIT clause to

LIMIT   ". (($page - 1) * 5) . ", 5"

Check out MySQL Select & Limit details for more info.

Upvotes: 3

Related Questions