MySQL Query Sorting with Numbers

Question

I have the following query:

$result = $mysqli->query('SELECT DISTINCT SKU_SIZE_PART1 
                          FROM SKU_DATA 
                          WHERE SKU_BRANDNAME = "'.$brand.'" 
                          ORDER BY SKU_SIZE_PART1 DESC');

while( $row = $result->fetch_assoc()){
    $sku_size1 = $row['SKU_SIZE_PART1'];

    echo $sku_size1;
}

Basically what is happening is.. the order is all messed up. This is what comes up:

9.50, 8.75, 8.00, 7.50, 7.00, 37, 35, 33, 325, 32, 315, 31, 305, 30, 295

325 should come up first, then 315 then so on.

What can I do to make this happen?

Let me guess SKU_SIZE_PART1 is text...wrong wrong wrong :-[. — JonH
– JonH, Commented Oct 4, 2011 at 19:02
Yeah you guys are right.. it is a varchar(6). This is a big tire database we bought from a company :-\ — Drew
– Drew, Commented Oct 4, 2011 at 19:27

Johan · Accepted Answer · 2011-10-04 19:15:13Z

3

You need to cast sku_size_part1 into a float.

This will slow your query down, but it will work:

$brand = mysqli_real_escape_string($brand);
$result = $mysqli->query("SELECT DISTINCT sku_size_part1
                          FROM sku_data 
                          WHERE sku_brandname = '$brand' 
                          ORDER BY CAST(sku_size_part1 AS FLOAT) DESC");

This will slow the query down, because MySQL will not be able to use an index to do the sorting, using a function prevents that.

A better solution (if possible) would be to redefine sku-size_part1 as a decimal(10,2).

-- Make a backup first --
ALTER TABLE sku_data CHANGE sku_size_part1 DECIMAL(10,2);

(Make sure the first parameter (10) and the second parameter (2) are large enough to hold all possible values.)
See: http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

edited Oct 4, 2011 at 19:15

answered Oct 4, 2011 at 19:03

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kelly Over a year ago

This works, but instead of casting, it'd be better if Drew altered the table column to a decimal.

Andrew Over a year ago

In the past when I had to do something like this, I added a new column to the database in order to leave the original data just in case. You could try ALTER TABLE sku_data ADD COLUMN sku_size_part1_dec DECIMAL NULL; And then you can do an update: UPDATE sku_data SET sku_size_part1_dec = CAST(sku_size_part1 AS DECIMAL)

Collectives™ on Stack Overflow

MySQL Query Sorting with Numbers

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related