How select next row pagination in sql

user_13038264 0
Viewed 114

I'm sorry I'm weak for English. i echo 2 row in each page . how echo next 2 row 

SELECT *
FROM `mzmx_post`
JOIN mzmx_post_category 
WHERE mzmx_post.id = mzmx_post_category.post_id AND zmx_post_category.category_id = 5
ORDER BY id DESC 
LIMIT 2
select sql-order-by mysql sql php
3 Answers

You can use the two-arguments form of LIMIT to offset the result by a given number of rows, like:

SELECT *
FROM `mzmx_post`
JOIN mzmx_post_category ON mzmx_post.id = mzmx_post_category.post_id 
WHERE mzmx_post_category.category_id = 5
ORDER BY id DESC
LIMIT 2, 2      -- fetch records 3 and 4

This gives you the second page. If you want the third page, then:

LIMIT 4, 2

And so on.

Note that I modified your query so the joining condition between the tables is placed in the ON clause of the join rather than in the WHERE clause.

user_106767160

The basic idea is to use

LIMIT n,o

where
n is the results per page
o is the offset from the first result

for the p-th page the offset would be

o = p * n
where p = 0,1,2,....

user_118430400

Better add one extra column (e.g. mzmx_post_key bigint) of Long type in each table and have sequential value on that column. Use that column to fetch data from DB from page wise. sqL suery should look like:

SELECT *
FROM `mzmx_post`
JOIN mzmx_post_category ON mzmx_post.id = mzmx_post_category.post_id 
WHERE mzmx_post_category.category_id = 5 and mzmx_post_key> ##last record key##
ORDER BY mzmx_post_key ASC
LIMIT 2
user_108498030
Related