使用SQL和PHP获取下一篇文章

Question

I've made a very rough and basic CMS as a learning exercise. My next challenge is to create a pagination (next post, previous post).

Ideally, I need to get the next post, if post_status is set to published.

At the moment I get a post like so:

SELECT post_title, post_content, post_id, post_status FROM posts WHERE post_id='$id' AND post_status='published'

In my mind, I would increment the $id on the PHP side, like $id++. So if I'm on post_id=19, I try and get post_id=20. However, what happens if that post is not set to published — how do I get the next entry, WHERE post_status='published'? This could be 21, 23, etc.

Answer 1

You can do this way, say you have an

$id = 19;


SELECT post_title,
post_content, 
post_id, 
post_status 
FROM posts 
WHERE post_status='published'
AND post_id > '$id' 
order by post_id
LIMIT 1

This will check posts having id > 19 with post_status='published' and then get the next to 19 by ordering and limit 1

UPDATE As danielsmile commented on getting the previous one for getting last one before $id = 19; would be pretty same as above

SELECT post_title,
    post_content, 
    post_id, 
    post_status 
    FROM posts 
    WHERE post_status='published'
    AND post_id < '$id' 
    order by post_id DESC
    LIMIT 1