ORDER BY specific list?

[EchoFool]

Hey,

 

I been trying to make a MYSQL query to order by a specific list but i cannot get it to work this is whati  tried:

 

$Get = mysql_query("SELECT *, 

(SELECT
CASE WHEN `AccountType` = '1 Month' THEN 1
CASE WHEN `AccountType` = '3 Month' THEN 2
CASE WHEN `AccountType` = '1 Year ' THEN 3
CASE WHEN `AccountType` = '2 Year' THEN 4
ELSE 5 END) as sort

FROM table

WHERE Deleted='0' 

ORDER BY sort ASC")

  Or die(mysql_error());

 

 

But currently i get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN `AccountType` = '3 Month' THEN 2CASE WHEN `AccountType` = '1 ' at line 3

 

 

Im trying to get the rows to sort by :

 

1 month

3 month

1 year

2 year

{every thing else}

 

 

What have i got wrong? Hope you can help!

[cags]

I've never used CASE WHEN syntax before, so please feel free to ignore me if I'm talking rubbish, but check the reference manually it seems to me like the syntax should be more along the lines of...

 

CASE AccountType
      WHEN '1 Month' THEN SELECT 1;
      WHEN '3 Month' THEN SELECT 2;
      WHEN '1 Year' THEN SELECT 3;
      WHEN '2 Year' THEN SELECT 4;
END CASE

 

[fenway]

You should just use ORDER BY FIELD() instead.

[EchoFool]

You should just use ORDER BY FIELD() instead.

 

You mean like this:  ?

 

$Get = mysql_query("SELECT *  FROM table
                                  WHERE Deleted='0'
                                     ORDER BY AccountType ('1 Month','2 Month','1 Year','2 Year') ")

  Or die(mysql_error());

[fenway]

Yup... as long as there are no other values for AccountType, that will work -- a non-match will return 0, so it gets more complicated.

[emopoops]

i agreee with the above poster. more thaan anything

[EchoFool]

I get this error:

 

FUNCTION AccountType does not exist

 

For:

 

ORDER BY Flagged DESC, Redeemed ASC, AccountType ('1 Month','2 Month','1 Year','2 Year')

[emopoops]

ur asking for something that u don have. the work around is take the thrid variable then check each individial one until u finish which one is wrong check

[PFMaBiSmAd]

Your query is missing the actual FIELD() statement.

[EchoFool]

So:

ORDER BY Flagged DESC, Redeemed ASC, Field(AccountType ('1 Month','2 Month','1 Year','2 Year'))

 

 

Like that?

[fenway]

No:

 

ORDER BY Flagged DESC, Redeemed ASC, Field(AccountType ,'1 Month','2 Month','1 Year','2 Year')

[EchoFool]

Oh thanks - works perfect !