Re: ORDER BY Problem

On 12/16/17 03:28, Jim H wrote:
>
> I have PHP code containing:

As others have already pointed out, this ain't a PHP question, but a mysql, so I added comp.databases.mysql, kept comp.lang.php as you can do some fixing of the result in PHP.

> $query = "SELECT name, level, date_entered
> FROM $table
> WHERE name = 'Annual'
> ORDER BY level DESC
> LIMIT 10";
>
> Field "level" is VARCHAR(13) and can contain either all numeric
> (numeric string) or all alpha data, but for records where name =
[Quoted] [Quoted] > "Annual" the content is always all numeric (numeric string)

This is a bad design of the database, you should at least have an internal numeric id or split things up so that level is numeric and have another column for the alpha based values which is a varchar, or maybe even two different tables (most of how to fix it will just be bad guesses as we don't know how the database design looks like or which other columns you have in your table).

> And after adding another record containing level = 1174 I get the
> following undesired result:
>
> 580
> 393
> 346
> 192
> 157
> 156
> 1174
>
> What I want is:
>
> 1174
> 580
> 393
> 346
> 192
> 157
> 156
>
> What am I doing wrong?

Except the obvious and which has already been mentioned, nothing. As your column is varchar, the column will be sorted as strings.

You could store the data into an array in PHP, convert the level data to integer with help of intval()
http://php.net/manual/en/function.intval.php, then you create a function which you can use for sorting your array and use it in usort() http://php.net/manual/en/function.usort.php

Or you can do something like this (not tested it) with your query:

SELECT name, 0+level as numeric_level, date_entered

             FROM Table
             WHERE name = 'Annual'
             ORDER BY numeric_level DESC
             LIMIT 10

Keep in mind, none of these suggestions are a good solution, the best is if you redesign your database.

-- 

 //Aho