[SOLVED] Getting previous and next record in database

[tail]

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

$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:

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:

$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:

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.

[chriso20]

What's $db_id?

 

ASC/DESC is irrelevant if you're limiting to 1 by the way.

 

Also don't SELECT *, it's quicker to SELECT id. SELECT * is returning everything, artist, id, song.. etc... not just id which is what you want.

 

But yeah, what's $db_id?

[tail]

$db_id is the id of the current record being viewed.

[aliento]

$next= '$db_id+1;

SELECT * FROM `tracks` WHERE `id` = '$next'

[aliento]

or

$next= '$db_id+1;

$next_id = mysql_fetch_array(mysql_query("SELECT * FROM `tracks` WHERE `id` = '$next' "));

[sasa]

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

[tail]

or

$next= '$db_id+1;

$next_id = mysql_fetch_array(mysql_query("SELECT * FROM `tracks` WHERE `id` = '$next' "));

Thanks for the quick replies! I wish it was that simple though, however there are gaps in the id's in my database due to deletions and etc. so that would not work.

[tail]

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

My code now reads

$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>';
}

However, the id's are not being acquired in the right order. Also, some are being skipped.This might be due to the fact that some artists in the database, when ordered by id, are not in alphabetical order perhaps? I'm not sure though as I'm not very advanced with MySQL queries. If you need to see a demo of what's happening, here is the page. You need to click on the play button to view the individual result.

[aliento]

ah ok. you only know the current id so :

 

$dq = mysql_query("SELECT * FROM `tracks` WHERE `id` = '$db_id' ORDER by `artist`,`song` DESC LIMIT 2");

while($db_return = mysql_fetch_row($database_q))

{

$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($database_q))

{

$previous['artist']=$db_return['artist']';

$previous['song']=$db_return['song'];

}

 

I am sure that there is an easier code out there but that code i learned to use.

[tail]

So after getting the value of the next artist and song, then I query the database like this:

$next_query = 'SELECT `id` FROM `tracks` WHERE `artist`='$next['artist'], `song`='$next['song']';
$prev_query = 'SELECT `id` FROM `tracks` WHERE `artist`='$previous['artist'], `song`='$previous['song']';

Is that correct?

[aliento]

yes it seems right to me

[sasa]

ups try this

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

 

[tail]

yes it seems right to me

I'm now getting this error:" Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING" with this code:

$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)) {
$prev['artist'] = $db_return['artist'];
$prev['song'] = $db_return['song'];
}
$next_id = mysql_fetch_array(mysql_query("SELECT `id` FROM `tracks` WHERE `artist`='$next['artist']',`song`='$next['song']"));
$prev_id = mysql_fetch_array(mysql_query("SELECT `id` FROM `tracks` WHERE `artist`='$prev['artist']',`song`='$prev['song']"));

[tail]

ups try this

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

 

I get this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

[aliento]

try to google the problems and find through others posts the solution if you are in hurry

the white_space is the empty gap between the begining of the file and <?php or the end and ?> no empty spaces before the <?php and  ?> - the end

 

[tail]

Google doesn't seem to be my friend :-\ Thanks for all the help so far. Anyone know any way of doing this?

[sasa]

(
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

[tail]

Thank you so much! However that code is only working to get the next id. I figured that switching the >'s to <'s would solve the problem, however this just gets the id of the first song when ordered by artist and song. Here is a link to the first record when sorted by artist and song and you can see what I mean.

[sasa]

yes and change order to

ORDER BY  `artist` DESC ,  `song` DSSC

[tail]

Thanks so much for all your help! The script is now working flawlessly! I can't tell you enough how much this has been frustrating me. Thanks again!