Getting Next Valid Row and Previous if exists

Pezmc

Trying to create the good old "previous" and "next" buttons in PHP for a database of news.
News is just taken from the database where the ID posted (get) is equal to the ID and published = 1 (whether its online)

Say I have this database
Id Title Published
1 News Item 1
3 News Item 1
6 News Item 0
7 News Item 1
9 News Item 1

How if the current id is 1 can I show only next and link to id 3?
or if the ID is 3 show previous (linking to one) and next linking to 7
or if the ID is 7 show previous (linking to three) and next linking to 9
or if 9 show just previous (linking to 7)

Sorry rather hard concept to explain!

Hope you can help!

Pezmc

Bump

cretaceous

what you need to do is get all the id entries from the database and put them into an array so you get :

array number 1 id 21
array number 2 id 37
array number 3 id 741
array number 4 id 5

so you then know what is the before and after by checking where in the array your current value is and you put those values into your buttons

sorry not even pseudocode, but that should give you the idea..

key terms: associative array, array, key , value

laserlight

Using :id as a placeholder for the id of the current item, you could get the previous and next with two queries:

SELECT * FROM News WHERE id < :id AND published = 1 ORDER BY id DESC LIMIT 1;
SELECT * FROM News WHERE id > :id AND published = 1 ORDER BY id LIMIT 1;

I would not recommend retrieving the id fields of all the entries just to get the previous and next.

cretaceous

far be it for me to disagree with laserlight but if you get all the ids and put in array you can do things like :
"item 8 of 99"
and it makes it easier to do arbitrary "order by"

so first select is :SELECT id from ...

and second SELECT gets only the row you are showing (but gets all the data not just the id)

laserlight

cretaceous wrote:
if you get all the ids and put in array you can do things like :
"item 8 of 99"

That could be accomplished by:

SELECT COUNT(*) + 1 FROM News WHERE id < :id AND published = 1;
SELECT COUNT(*) FROM News WHERE published = 1;

cretaceous wrote:
and it makes it easier to do arbitrary "order by"

That can be true, but it is typically better to just get the database engine to do it, making use of indices and such.