Getting previous and next record in database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
p3rk5
Forum Commoner
Posts: 34
Joined: Thu Jan 29, 2009 10:19 pm

Getting previous and next record in database

Post by p3rk5 »

My goal is to have a previous and next link displayed in the page of the record. My first solution was too increment and decrement by 1 and place these values into links. However, there are gaps in the id's in the database and when viewing the first and last records, the links would be pointing to false records. I also tried using these two queries but I also recieved errors on the first and last records:

SELECT ID FROM `tracks` WHERE `id` > $id LIMIT 1
SELECT ID FROM `tracks` WHERE `id` < $id LIMIT 1

Which gave the error "mysql_result() [function.mysql-result]: Unable to jump to row 0"

Any help is much appreciated!
Last edited by p3rk5 on Tue Feb 17, 2009 7:41 pm, edited 2 times in total.
alpinec
Forum Newbie
Posts: 5
Joined: Tue Feb 17, 2009 11:40 am

Re: Getting previous and next record in database

Post by alpinec »

You must write code to check whether the ID has 1 for you as you try to get ID of 0 of course that we will give you an error!
p3rk5
Forum Commoner
Posts: 34
Joined: Thu Jan 29, 2009 10:19 pm

Re: Getting previous and next record in database

Post by p3rk5 »

I don't quite understand. Could you explain more in-depth?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Getting previous and next record in database

Post by VladSun »

This one will not work properly:
p3rk5 wrote:[sql]SELECT ID FROM `tracks` WHERE `id` > $id LIMIT 1SELECT ID FROM `tracks` WHERE `id` < $id LIMIT 1[/sql]
You need:
[sql]SELECT min(`id`) FROM `tracks` WHERE `id` > $id SELECT max(`id`) FROM `tracks` WHERE `id` < $id[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
p3rk5
Forum Commoner
Posts: 34
Joined: Thu Jan 29, 2009 10:19 pm

Re: Getting previous and next record in database

Post by p3rk5 »

Thanks! That solved the issue. Another question, would there be a way to order the results alphabetically? I tried adding ORDER by `artist` ASC to the query for the previous record and ORDER by `artist` DESC for the next record but it didn't help. For example on view.php it displays the results alphabetically by artist but when viewing a single song the previous and next links refer to the next id in the table instead of getting the next song by that artist.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Getting previous and next record in database

Post by VladSun »

Post your query and we will discuss it ;)
There are 10 types of people in this world, those who understand binary and those who don't
p3rk5
Forum Commoner
Posts: 34
Joined: Thu Jan 29, 2009 10:19 pm

Re: Getting previous and next record in database

Post by p3rk5 »

Code: Select all

$prev_id = mysql_result(mysql_query("SELECT min(`id`) FROM `tracks` WHERE `id` > '$db_id' ORDER by `artist`,`song` ASC"),0);
$next_id = mysql_result(mysql_query("SELECT max(`id`) FROM `tracks` WHERE `id` < '$db_id' ORDER by `artist`,`song` DESC"),0);
if (!empty($prev_id)) {
$prev = '<a href="index.php?id=music&page=view&song_id='.$prev_id.'" class="boldlink">Previous Song</a>';
} else {
    $prev = '<b>Previous Song</b>';
} if (!empty($next_id)) {
    $next = '<a href="index.php?id=music&page=view&song_id='.$next_id.'" class="boldlink">Next Song</a>';
} else {
    $next = '<b>Next Song</b>';
}
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Getting previous and next record in database

Post by VladSun »

If you want to find the next row (all fields, not just one) it's better to use:
[sql]SELECT    * FROM    TABLE WHERE    order_field > $current_value ORDER BY    order_fieldLIMIT    1[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
p3rk5
Forum Commoner
Posts: 34
Joined: Thu Jan 29, 2009 10:19 pm

Re: Getting previous and next record in database

Post by p3rk5 »

Ok I'm using this query for getting the previous ID:

Code: Select all

$prev_id = mysql_fetch_array(mysql_query("SELECT * FROM `tracks` WHERE `id` > '$db_id' ORDER by `artist`,`song` ASC LIMIT 1"));
And this to display the link:

Code: Select all

if (!empty($prev_id)) {
    $prev = '<a href="index.php?id=music&page=view&song_id='.$prev_id['id'].'" class="boldlink">Previous Song</a>';
} else {
    $prev = '<b>Previous Song</b>';
}
Query for next ID:

Code: Select all

$next_id = mysql_fetch_array(mysql_query("SELECT * FROM `tracks` WHERE `id` < '$db_id' ORDER by `artist`,`song` DESC LIMIT 1"));
And this to display the link:

Code: Select all

if (!empty($next_id)) {
    $next = '<a href="index.php?id=music&page=view&song_id='.$next_id['id'].'" class="boldlink">Next Song</a>';
} else {
    $next = '<b>Next Song</b>';
}
What I'm trying to accomplish is to have links point to the next and previous ID's ordered by artist, then song. Is it possible to do it the way I'm trying? If so, please point me in the right direction. Thanks for all the help so far.
p3rk5
Forum Commoner
Posts: 34
Joined: Thu Jan 29, 2009 10:19 pm

Re: Getting previous and next record in database

Post by p3rk5 »

I've tried a few more things, all to no avail:

Code: Select all

$prev_id = mysql_fetch_array(mysql_query("SELECT `id` FROM `tracks` WHERE `id` > '$db_id' AND `artist`>=(SELECT `artist` FROM `tracks` WHERE `id` = '$db_id') AND `song`>=(SELECT `song` FROM `tracks` WHERE `id` = '$db_id') ORDER by `artist`,`song` ASC LIMIT 1"));
$next_id = mysql_fetch_array(mysql_query("SELECT `id` FROM `tracks` WHERE `id` < '$db_id' AND `artist`<=(SELECT `artist` FROM `tracks` WHERE `id` = '$db_id') AND `song`<=(SELECT `song` FROM `tracks` WHERE `id` = '$db_id') ORDER by `artist`,`song` ASC LIMIT 1"));
if (!empty($prev_id)) {
    $prev = '<a href="index.php?id=music&page=view&song_id='.$prev_id['id'].'" class="boldlink">Previous Song</a>';
} else {
    $prev = '<b>Previous Song</b>';
} if (!empty($next_id)) {
    $next = '<a href="index.php?id=music&page=view&song_id='.$next_id['id'].'" class="boldlink">Next Song</a>';
} else {
    $next = '<b>Next Song</b>';
}

Code: Select all

$dq = mysql_query("SELECT * FROM `tracks` WHERE `id` = '$db_id' ORDER by `artist`,`song` DESC LIMIT 2");
while($db_return = mysql_fetch_row($dq)) {
    $next['artist']=$db_return['artist'];
    $next['song']=$db_return['song'];
}
$dq = mysql_query("SELECT * FROM `tracks` WHERE `id` = '$db_id' ORDER by `artist`,`song` ASC LIMIT 2");
while($db_return = mysql_fetch_row($dq)) {
    $previous['artist']=$db_return['artist'];
    $previous['song']=$db_return['song'];
}

Code: Select all

 
SELECT `id` FROM `tracks` WHERE
`id` > '$db_id' AND `artist`=(SELECT `artist` FROM `tracks` WHERE `id` = '$db_id') AND `song`=(SELECT `song` FROM `tracks` WHERE `id` = '$db_id') ORDER by `artist`,`song`, id ASC LIMIT 1 UNION SELECT `id` FROM `tracks` WHERE `artist` > (SELECT `artist` FROM `tracks` WHERE `id` = '$db_id') AND `song` > (SELECT `song` FROM `tracks` WHERE `id` = '$db_id') ORDER by `artist`,`song`, id ASC LIMIT 1
If anyone can please try to help me out with this or point me in the right direction I'd really appreciate it!
p3rk5
Forum Commoner
Posts: 34
Joined: Thu Jan 29, 2009 10:19 pm

Re: Getting previous and next record in database

Post by p3rk5 »

Got it!

Code: Select all

$prev_id = mysql_fetch_array(mysql_query("(SELECT * FROM  `tracks` WHERE  `artist` = (SELECT  `artist` FROM  `tracks` WHERE `id` =  '$db_id' ) AND song < (SELECT  `song` FROM  `tracks` WHERE  `id` =  '$db_id' )) UNION (SELECT * FROM  `tracks` WHERE  `artist` < ( SELECT  `artist` FROM  `tracks` WHERE  `id` =  '$db_id' )) ORDER BY  `artist` DESC ,  `song` DESC LIMIT 1"));
$next_id = mysql_fetch_array(mysql_query("(SELECT * FROM  `tracks` WHERE  `artist` = (SELECT  `artist` FROM  `tracks` WHERE `id` =  '$db_id' ) AND song > (SELECT  `song` FROM  `tracks` WHERE  `id` =  '$db_id' )) UNION (SELECT * FROM  `tracks` WHERE  `artist` > ( SELECT  `artist` FROM  `tracks` WHERE  `id` =  '$db_id' )) ORDER BY  `artist` ,  `song` ASC LIMIT 1"));
if (!empty($prev_id)) {
    $prev = '<a href="index.php?id=music&page=view&song_id='.$prev_id['id'].'" class="boldlink">Previous Song</a>';
} else {
    $prev = '<b>Previous Song</b>';
} if (!empty($next_id)) {
    $next = '<a href="index.php?id=music&page=view&song_id='.$next_id['id'].'" class="boldlink">Next Song</a>';
} else {
    $next = '<b>Next Song</b>';
}
Post Reply