How do I sort by my own order?

[DJ Unique]

Hi,

 

I have an SQL Query that doesn't seen to work in PHP:

 

select * 
from new_handset_network_variant 
where New_Handset_Network_Variant_Progress != 'Complete' and New_Handset_Network_Variant_Progress != 'Cancelled'
order by 
case New_Handset_Network_Variant_Priority
when	'High' then 1
when	'Normal' then 2
when	'Low' then 3
end

 

I want to basically show the results of the new_handset_network_variant with the items marked high priority first, then Normal, with Low priority last.

 

Can anyone help me to write a correct SQL query for this because the above doesn't seem to work.

 

Thanks

 

 

[PFMaBiSmAd]

If you post the error message to give someone all the information about your problem it would help.

 

I am not sure why there is a mysql error with that, but the following should work -

 

FIELD(New_Handset_Network_Variant_Priority,'High','Normal','Low')

[DJ Unique]

Hi, the MySQL query above works fine in SQL yog and PHP Admin, but when I use it in my PHP file I get the following error:

 

Illegal mix of collations for operation 'case'

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /web/stock/webmaster/ticket_overview2.php on line 19

 

I changed it to use the what you just suggested and I still get an error:

 

Illegal mix of collations for operation 'field'

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /web/stock/webmaster/ticket_overview2.php on line 19

 

[PFMaBiSmAd]

Posting your table definition would help.

[DJ Unique]

Here is a sample of the table:

 

New_Handset_Variant_Page_ID

New_Handset_Variant_Page_Date

New_Handset_Variant_Your_Name

New_Handset_Variant_Name

New_Handset_Variant_Status

New_Handset_Variant_Cost

New_Handset_Variant_Priority

1

2007-11-22 09:49:07

John Smith

Nokia N78

Coming Soon

£349

High

 

2

2007-12-22 09:49:07

Joe Bloggs

Nokia N95

Available Now

 

 

£299

Low

 

3

2007-13-22 09:49:07

Jane Doe

Samsung G400

Coming Soon

£179

Normal

 

 

I want to sort by priority, so I want high at the top then Normal and Low last.

 

[PFMaBiSmAd]

That's not your table definition.