Page 1 of 1

stuck on a case statement internally providing the order by

Posted: Wed Aug 26, 2009 9:08 am
by chidge
Hi guys and gals

I am a bit stuck on this.

I am basically wanting to sort a query by taking the order by clause gubins (the column to order by and the direction (ASC DESC)) from another table using an inner select and a case statement.
The values "sorter" and "direction" are outputting the correct information "artist_music.date" and "DESC" respectively - according to the values in the table chosen by the case statement but I can’t seem to get them to work inside of the statement as a statement. - is this possible.

I could do this with two database calls and create a php function to make the order by choice but I think I can do this all in one statement

here’s the statement:

Code: Select all

 
"SELECT 
music.music_id,
music.artist,
music.title, 
music.playtime,             
music.image,                                         
CASE (SELECT sort.music FROM sort WHERE sort.id = $id)                                              
WHEN '1' THEN  'music.date' 
WHEN '2' THEN  'music.title'
WHEN '3' THEN  'music.artist'
WHEN '4' THEN 'music.playtime'                                  
END AS sorter,
                                            
CASE(SELECT sort.mudir FROM sort WHERE sort.id = $id)
WHEN '0' THEN 'DESC' 
WHEN '1' THEN 'ASC'
END AS direction,
                                            
 
FROM music
WHERE music.id = $id
ORDER BY sorter direction";
 
 


and I am trying to dynamically create this: (above the orderby is dependant on the values in the other table in here its showing a finished version as such)

Code: Select all

 
 
"SELECT 
music.music_id,
music.artist,
music.title, 
music.playtime,             
music.image                                 
FROM music
WHERE music.id = $id
ORDER BY music.date DESC";
 
 

any ideas on this one? Thanks in advance for any help

Re: stuck on a case statement internally providing the order by

Posted: Wed Aug 26, 2009 10:16 am
by chidge
fixed by changing the way I looked at the problem

changed the underlying table to combine the orderby clause into one (column and DESC/ASC)

removed the quotes from the column so it was a value not a string!!!!! (yes im learning)

and now using this

Code: Select all

 
"SELECT 
music.id,
music.artist,
music.title, 
music.playtime,
music.image,
music.date,                                                                          
(SELECT sort.music FROM sort WHERE sort.artist_id = $id)    AS sorter                       
 
FROM music
WHERE music.artist_id = $user_id
ORDER BY 
                                         
CASE sorter                             
WHEN 'date_asc' THEN  music.date END ASC,
CASE sorter                             
WHEN 'date_desc' THEN  music.date END DESC";
feel like im talking to myself a little but it may help someone else in the future