Getting previous and next record in database
Moderator: General Moderators
Getting previous and next record in database
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!
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.
Re: Getting previous and next record in database
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!
Re: Getting previous and next record in database
I don't quite understand. Could you explain more in-depth?
Re: Getting previous and next record in database
This one will not work properly:
[sql]SELECT min(`id`) FROM `tracks` WHERE `id` > $id SELECT max(`id`) FROM `tracks` WHERE `id` < $id[/sql]
You need:p3rk5 wrote:[sql]SELECT ID FROM `tracks` WHERE `id` > $id LIMIT 1SELECT ID FROM `tracks` WHERE `id` < $id LIMIT 1[/sql]
[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
Re: Getting previous and next record in database
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.
Re: Getting previous and next record in database
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
Re: Getting previous and next record in database
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>';
}Re: Getting previous and next record in database
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]
[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
Re: Getting previous and next record in database
Ok I'm using this query for getting the previous ID:
And this to display the link:
Query for next ID:
And this to display the link:
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.
Code: Select all
$prev_id = mysql_fetch_array(mysql_query("SELECT * FROM `tracks` WHERE `id` > '$db_id' ORDER by `artist`,`song` ASC LIMIT 1"));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>';
}Code: Select all
$next_id = mysql_fetch_array(mysql_query("SELECT * FROM `tracks` WHERE `id` < '$db_id' ORDER by `artist`,`song` DESC LIMIT 1"));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>';
}Re: Getting previous and next record in database
I've tried a few more things, all to no avail:
If anyone can please try to help me out with this or point me in the right direction I'd really appreciate it!
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 1Re: Getting previous and next record in database
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>';
}