Sorting Clothing Sizes

[jsnull]

I have clothing sizes in a dB that I pull out and need to sort them in natural sizes.

For example:

Input Sizes: Youth - S, Adult-XL, Adult - M, Adult - L, Adult - S, Adult - 3XL, Adult - 2XL, Youth - M

Sorted Sizes: Adult-S, Adult - M, Adult - L, Adult -XL, Adult - 2XL, Adult - 3XL, Youth - S, Youth - M

Looking for a very simple way to do this. Any help appreciated!

[fastsol1]

Should be able to use just a simple ORDER BY in the query. Unless the "Input sizes" is a string of data in a single row in the db. As long as each size is in it's own row in the db then use order by.

SELECT * FROM `table` ORDER BY `field` ASC

Obviously that is just an example.

[traq]

I have clothing sizes in a dB that I pull out and need to sort them in natural sizes.

… Adult-S, Adult - M, Adult - L, Adult -XL, Adult - 2XL, Adult - 3XL, Youth - S, Youth - M

Honestly, that doesn't seem "natural" to me: wouldn't Youth S, M come before Adult S?

In any case, you're kinda "backed into a corner," here: there's no straightforward solution.

How large are your result sets? If they're not too big (and never will be), then you can sort them in your application code:

<?php

#  pseudocode!  #

// when retrieving rows from your query, index them by size:
while( $list( $item,$size,$etc /*  etc. ...  */ ) = $result->fetch() ){
    $clothing[$size] = array( "item"=>$item,"size"=>$size,"etc."=>$etc /*  etc. ...  */ );
}

// then put the indexes in the desired order:
$sort_order = array( "Adult-S","Adult-M","Adult-L" /* etc. ... */ );
foreach( $sort_order as $size ){
    $sorted_clothing[$size] = $clothing[$size];
}
// clean up
unset( $clothing );

// everything's in order

This will kill you quick for large result sets, however. It would be much more efficient to let the database sort it. This will depend on how your DB is designed, however (and will probably require changes).
Can you show us your DB schema (use SHOW CREATE TABLE `table_name_goes_here`)?

[djr33]

A better way to do this is to use numerical (or any sortable) values. So use sizes 1, 2, 3, etc. Even 1.5 or 1.75 if you want (to add in more as needed). Then just sort of smallest to largest (or the opposite).

There there are two ways to convert these values to names:
1. Include a second column in your table for the size names. Basically what you have now, plus the numbers column. Redundant and not so efficient, and slightly more work to manage, but very easy to change.
2. Add a translation method from numbers to names. This is just like real language translation ("hello">"hola", etc.), but much smaller. So you could use an array in PHP to do this, or, if you're feeling like doing it the longer (and arguably more "correct") way, you could add a new database table of sizes<->numbers and work it out from there (even within MySQL then, so a query could show you directly what size something is).