Sort MySQL query result by a alphanumeric field

Question

I'm querying a table in a db using php. one of the fields is a column called "rank" and has data like the following:

none
1-bronze
2-silver
3-gold
...
10-ambassador
11-president

I want to be able to sort the results based on that "rank" column. any results where the field is "none" get excluded, so those don't factor in. As you can already guess, right now the results are coming back like this:

1-bronze
10-ambassador
11-president
2-silver
3-gold

Of course, I would like for it to be sorted so it is like the following:

1-bronze
2-silver
3-gold
...
10-ambassador
11-president

Right now the query is being returned as an object. I've tried different sort options like natsort, sort, array_multisort but haven't got it to work the way I'm sure it can. I would prefer keeping the results in an object form if possible. I'm passing the data on to a view in the next step. although, it's perfectly acceptable to pass the object to the view and then do the work there. so it's not an issue after all. :)

thank you for your help. i'm hoping I'm making sense.

Sounds like the number and the description should be separate columns... — Ignacio Vazquez-Abrams, Apr 12 '12 at 04:12
So there can be more than one data point in the rank column? If so, sounds like the schema was designed inccorectly. — Chris Henry, Apr 12 '12 at 04:13
Ranks 1,2,3 and bronze,silver,gold are in different field of DB right ? — GoSmash, Apr 12 '12 at 04:15
@IgnacioVazquez-Abrams ok then he should have a separate field. — GoSmash, Apr 12 '12 at 04:18
While normalization is [arguably] "the correct" way, is there any way to get a "numeric collation" in MySQL? (Collation is in charge of text ordering, after all.) — , Apr 12 '12 at 04:21
Your correct, the data should have been split into two different fields but unfortunately it wasn't. i'm just trying to work with it the best that i can. :( — Jason Shultz, Apr 12 '12 at 04:24
Maybe you should create a view that has them split properly and join against that instead. — Ignacio Vazquez-Abrams, Apr 12 '12 at 04:36

score 0 · Answer 1 · answered Apr 12 '12 at 05:25

0

Get the values in an array an sort it in PHP using natsort()

Natsort

answered Apr 12 '12 at 05:25

Naveen Kumar

4,543
1
18
36

score 0 · Accepted Answer · answered Apr 12 '12 at 06:07

How about using this function to sort. I assume if you are getting an object you should convert object to array then use this

function arraySubSort($array, $subkey, $sort = 'asort') 
{
  foreach($array as $key => $value) 
  {
    $temp[$key] = strtolower($value[$subkey]);
  }

  $sort($temp);
  foreach($temp as $key => $value) 
  {
    $result[] = $array[$key];
  }
  return $result;
}

$data = $your_array;
$field = 'ranks';
arraySubSort($data,$field);

It will sort the array with the field you assign. If you are getting multiple records its a good thing to use to sort.

score 0 · Answer 3 · edited May 23 '17 at 10:34

0

This will work -

SELECT alphanumeric, integer FROM sorting_test ORDER BY LENGTH(alphanumeric), alphanumeric

source - Natural Sort in MySQL

edited May 23 '17 at 10:34

Community

1
1

answered Apr 12 '12 at 06:16

Mukesh Soni

6,646
3
30
37

Sort MySQL query result by a alphanumeric field

3 Answers3