sorting lengths

lobster

Another issue.

Have an input text field where people enter a length ie. 11'7 or 13 or 12'8 etc...

I then retrieve the data and need to sort desc...this all is fine and dandy except that the 100 is at the bottom (after 1 ) instead of at the top before 99. I changed the MySQL field to an int instead of varchar and now all of the ' are gone along with any inches that were behind it.

Any idea on how I should tackle this one? DO I need to separate out the feet and inches into two seperate inputs?

bubblenut

convert it into inches and then sort on that.
in php

$parts=explode(',', $length);
$inches = ($parts[0] * 12) + $parts[1];

for each element and then do an [man]array_multisort()[/man] on the resulting array but that's a messy way of doing it. Let's see if we can do it in the query

SELECT * FROM table ORDER BY (SUBSTRING_INDEX(length, ',', 1) * 12) + SUBSTRING_INDEX(length, ',', -1)

I've got a funny feeling that won't work, if it doesn't try

SELECT *, (SUBSTRING_INDEX(length, ',', 1) * 12) + SUBSTRING_INDEX(length, ',', -1) AS inch_length FROM table ORDER BY inch_length

Let us know if it works 🙂

Bubble

lobster

Is ',' supposed to the the seperater which is ' for feet?

and I suppose I need to convert the field back to varchar

Thanks!