stuck on a case statement internally providing the order by
Posted: Wed Aug 26, 2009 9:08 am
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:
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)
any ideas on this one? Thanks in advance for any help
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