Page 1 of 1

Getting previous and next record in database

Posted: Tue Feb 17, 2009 2:27 pm
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!

Re: Getting previous and next record in database

Posted: Tue Feb 17, 2009 3:04 pm
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!

Re: Getting previous and next record in database

Posted: Tue Feb 17, 2009 3:07 pm
by p3rk5
I don't quite understand. Could you explain more in-depth?

Re: Getting previous and next record in database

Posted: Tue Feb 17, 2009 3:22 pm
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]

Re: Getting previous and next record in database

Posted: Wed Feb 18, 2009 1:02 pm
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.

Re: Getting previous and next record in database

Posted: Wed Feb 18, 2009 2:10 pm
by VladSun
Post your query and we will discuss it ;)

Re: Getting previous and next record in database

Posted: Wed Feb 18, 2009 3:03 pm
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>';
}

Re: Getting previous and next record in database

Posted: Wed Feb 18, 2009 4:30 pm
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]

Re: Getting previous and next record in database

Posted: Fri Feb 20, 2009 2:15 pm
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.

Re: Getting previous and next record in database

Posted: Sat Feb 21, 2009 12:14 pm
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!

Re: Getting previous and next record in database

Posted: Sat Feb 21, 2009 11:50 pm
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>';
}