Php, mysql selection

Question

I have this table ATTRIBUTE

id     name       um
12    capacity  MB;GB;TB

And this table2 :

id    id_attribute  id_product    name       value     um
 1        12         40        hdd maxtor     30       GB
 2        12         41        hdd maxtor     40       GB
 3        12         42        hdd y           1       TB

How can i select from table2 in this order : 30GB 40GB 1TB?

Thanks a lot!

Answer 1

Hello,

"SELECT * FROM table2 ORDER BY um ASC, value ASC" What this does: First orders by UM, then orders by value. This only works because [by accident] GB is a string "smaller" than TB. I suggest switching these with numeric values, because MB is smaller than GB, but the string ain't.

So do another table with this fields: um_id, um_name, um_order, then replace UM with um_id.

Regards, Gabriel

Answer 2

select value, um 
from tbl

order by 

case um 
when 'KB' then 1
when 'MB' then 2
when 'GB' then 3
when 'TB' then 4
else 5
end,

value

But if you want to take into account that 1200 GB > 1 TB, and must be sorted last, do this:

select value, um 
from tbl

order by value *

case um 
when 'KB' then 1000
when 'MB' then 1000000
when 'GB' then 1000000000
when 'TB' then 1000000000000
else 1000000000000000
end

[EDIT]

Capitalizing on Jeff Beck's table:

select tbl.value, tbl.um 
from tbl
join unit /* name Jeff's table as unit */
on unit.um = tbl.um

order by 
    unit.type -- this will nicely "group" the related type
    ,tbl.value * unit.magnitude

Answer 3

You will want this to work with MB as well so what I would do is create a function to convert the two columns value and UM into the mb result. So the function would multiply the value by 1 for mb and 1000 for gb and 1000000 for tb. You can then order on the function. But this will not be ideal performance, but with this meta style db I'm not sure exactly how else to optimize it.

To expand on this you could create a translation table for all your UMs that the function worked on so it could support this for other things then just size.

So as an example add a table like

|Type  |UM |Magnitude|
______________________
|Size  |GB |1000     |
|Size  |MB |1        |
|Size  |TB |1000000  |
|Weight|g  |1        |
|Weight|kg |1000     |

Then make the function look up the UM and the magnitude and multiply it would allow you to always order correctly.

Also you can change your attribute table to point at the measurement type instead of having that ; separated list.