Order MYSQL Results by Size Order in Php??

[Wazuke]

Hi Guys,

Looking to pull out available sizes for a product from the MySQL database, and then order them in Size order.

 

 

EG: Ordering the result of: XL,2XL,M,L,3XL,XS,S

to: 3XL,2XL,XL,L,M,S,XS

 

Any help would be appreciated.

 

Thanks,

 

[callesson]

Add a colum with name Size and if the size is 3XL insert 7 to the Size colum. 2XL=6, XL=5, L=4,M=3,S=2,XS=1

Then just ORDER BY Size Desc or what is it.. Simple way to make it

[Mchl]

ORDER BY FIELD(size,'3XL','2XL','XL','L','M','S','XS')

[ignace]

ORDER BY FIELD(size,'3XL','2XL','XL','L','M','S','XS')

 

Care to elaborate (field() is new to me)? field() returns the index position of size in the '3XL','2XL','XL','L','M','S','XS' list which is thus 0-7 then how does for example ORDER BY 3 help as it would just order by the 3rd column?

[Wazuke]

ORDER BY FIELD(size,'3XL','2XL','XL','L','M','S','XS')

 

Worked a treat matey, thank you!

[Mchl]

ORDER BY FIELD(size,'3XL','2XL','XL','L','M','S','XS')

 

Care to elaborate (field() is new to me)? field() returns the index position of size in the '3XL','2XL','XL','L','M','S','XS' list which is thus 0-7 then how does for example ORDER BY 3 help as it would just order by the 3rd column?

 

Magick

 

size, FIELD(size,'3XL','2XL','XL','L','M','S','XS')

--------------------------------------------

3XL, 1

2XL, 2

XL , 3

L  , 4

M  , 5

S  , 6

XS , 7

[ignace]

ORDER BY FIELD(size,'3XL','2XL','XL','L','M','S','XS')

 

Care to elaborate (field() is new to me)? field() returns the index position of size in the '3XL','2XL','XL','L','M','S','XS' list which is thus 0-7 then how does for example ORDER BY 3 help as it would just order by the 3rd column?

 

Magick

 

size, FIELD(size,'3XL','2XL','XL','L','M','S','XS')

--------------------------------------------

3XL, 1

2XL, 2

XL , 3

L  , 4

M  , 5

S  , 6

XS , 7

 

Thx Mchl Magick indeed I now understand how it works and I'll remember to use that whenever I have a fixed list. Got any resource that contains more of such Magick? Book? Tut?

[Mchl]

internet

 

I can never remember how this is used, so when I need I do this:

http://lmgtfy.com/?q=mysql+custom+sort

[ignace]

internet

 

I can never remember how this is used, so when I need I do this:

http://lmgtfy.com/?q=mysql+custom+sort

 

That's not what I meant. I surely hoped there would be some specific document/book that discussed these advanced MySQL features/uses and I actually wanted to know the title of this document/book.

 

Although your lmgtfy turned up some good results

[Mchl]

I'm subscribed to Planet MySQL. Don't have tome to read through all of it, but it sure is a good way to learn some tricks.

Other than that it's MySQL Docummentation