Page 1 of 1

Getting Next Valid Row and Previous if exists

Posted: Thu Jan 29, 2009 3:54 am
by 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!

Re: Getting Next Valid Row and Previous if exists

Posted: Thu Jan 29, 2009 10:44 am
by Pezmc
Bump

Re: Getting Next Valid Row and Previous if exists

Posted: Thu Jan 29, 2009 12:58 pm
by mavieng
You can try the following code to get next and previous record from database.

Suppose your records are stored in following order in database:

ID Title
1 Title 1
3 Title 3
5 Title 5
6 Title 6
7 Title 7

Let say current record ID is : 5
$currentId = 5

Query to get previous id : "SELECT id FROM TABLENAME WHERE id < $currentId order by id DESC LIMIT 1" (it will return 3)
Query to get next id : "SELECT id FROM TABLENAME WHERE id > $currentId order by id LIMIT 1" (it will return 6)

Re: Getting Next Valid Row and Previous if exists

Posted: Thu Jan 29, 2009 7:05 pm
by Pezmc
Thanks, code I used in the end was.

Code: Select all

            $rez=mysql_query("SELECT id FROM news WHERE id < $news_id AND published = '1' ORDER BY id DESC LIMIT 1");
            if (mysql_num_rows($rez)) { 
                while ($row = mysql_fetch_array($rez)) {
                    $page_content .= "<a href='news.php?id=".$row['id']."'>Previous Story</a>\t\t";
                }
            }
            
            $page_content .= "<a href='news.php'>Other Stories</a>";
            
            $rez=mysql_query("SELECT id FROM news WHERE id > $news_id AND published = '1' ORDER BY id LIMIT 1");
            if (mysql_num_rows($rez)) { 
                while ($row = mysql_fetch_array($rez)) {
                    $page_content .= "\t\t<a href='news.php?id=".$row['id']."'>Next Story</a>";
                }
            }