so i'm displaying a list of feet and inches ... and they return slightly out of order ... 5' 10" comes before 5' 2"

what's the best/easiest way to handle this?

I'm guessing that you're storing these as VARCHAR (or some other text type) -- you need to convert them to numeric values.

hm. i think you're right.

i'll go try that now, thanks!

so, do you mean change the "type" of the field? or convert them to say 'decimal' before they're stored?

he means change the type of field

to what? any suggestions? i've tried a few thing and so far everything has stripped information off ... so 5' 8" becomes 5

If it were me I would probably convert them all to inches and save them in an integer field.

i think i understand

convert all feet' inches" to all inches so they sort normally, store as integers and then before displaying just convert them with a little math

i think i can handle that

thanks!

You could store the 5'9" in a separate field for display purposes and just use the inches field for ordering. It would save some processor time only doing the conversion once when you save instead of every time you display a page.

hmm, great suggestion

i was thinking about making another table (and using JOIN) for just the feet/inches and giving each one an ID ... then using the id to sort, but display the feet/inches

i'm fairly new to this kind of backend work and welcome opinions, thanks

Originally posted by: WingNut hmm, great suggestion

i was thinking about making another table (and using JOIN) for just the feet/inches and giving each one an ID ... then using the id to sort, but display the feet/inches

i'm fairly new to this kind of backend work and welcome opinions, thanks

You shouldn't need another table, trm's suggestion to convert everything to inches would do the job just fine. Personally I wouldn't bother with the pre-formatted field -- the processor time to convert it at display time would be negligible unless you're displaying thousands at a time.

I probably wouldn't go that route personally. It's sure to end up a pain trying to maintain that, I would think. I would just add an inches field to the table and keep the feet' inches" as well. You would just have to make sure when you update or add or anything to update both fields.

and yeah, like stickman said. depending on the situation it may not be worth it to store both.

any ideas as to how to convert a field that has varchar and is stored like 5' 8" to all inches?

i'm having trouble wrapping my head around what sort of sql statement would do that?

also, some are 5'8" and some are 5' 8" (note the space, not sure how much that matters?)

Ouch, horrible. If it were me, rather than try to convert it in SQL I'd dump the data to CSV (or the like), process it in code, and re-import it after. You'll drive yourself crazy trying to come up with an SQL statement to convert it.

After I'd done that, I'd hunt down whoever designed the database schema in the first place, and punch them in the throat.

How many rows in the table?

if its not too many you could write quick php script to do the job.

http://www.php.net/manual/en/function.explode.php

Explode it! The only trick is, I think you would have to do 2 explodes, to get the first bit out, and then the second bit.

If you have enough that you don't want to do it manually, you could do something in php like:

Make a connection, loop through blah blah

while ($row = mysql_fetch_array($result)) 
{
       $tempVar = $row['old_column'];

        //explode basically takes the variable, and busts it into array pieces, using the first parameter

       explode(''', $tempVar);
       $feetConverted = $tempVar[0] *12;

       //now the first value $tempVar[0] should equal the first number, like "5" the second value $tempVar[1] would be the rest, like 10". Maybe we want to explode again, this is were I'm a little shakey.. but maybe...Or you could do a string replace.

      explode('"', $tempVar[1]);
      $inchesConverted = $tempVar[0];
      $newVarToPutInDB = $feetConverted + $inchesConverted;

}

I'm sure there's some wasted lines in there, but you get the idea.

awesome! thanks!