Loading DB values into array for natsort() function

[ywickham]

Yes, I did see an earlier post on loading database values into an array, but I have a twist to it. The reason I'm even loading my recordset into an array is because I want to do a natural sort on an element of the recordset.

I have the following query:

"SELECT camera_id, manufacturer_name, model_name
FROM camera_general
ORDER BY model_name"

Regular sorting on the model_name gives me results as follows:

Canon PowerShot A10
Canon PowerShot A100
Canon PowerShot A30
FujiFilm FinePix 1300
FujiFilm FinePix 1400
FujiFilm MX-1200
FujiFilm MX-1700 Zoom
FujiFilm MX-600 Zoom

When I need to display in the following order:

Canon PowerShot A10
Canon PowerShot A30
Canon PowerShot A100
FujiFilm FinePix 1300
FujiFilm FinePix 1400
FujiFilm MX-1200
FujiFilm MX-600 Zoom
FujiFilm MX-1700 Zoom

I was told that I should load the results into an array and then use the natsort() function:

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

But looking through all my books, I'm not making the connection as to how my results into an array, and apply the natsort() function to the model_name element. Sample code anyone, or a huge push in the right direction, would be greatly appreciated?

Thanks!

[largo]

Okay, firstly, this is not a newbie question 🙂

I looked at mysql docs for help here but couldn't find anything terribly useful.

This is how I successfully did it on one of our databases, hopefully it will help you apply it to your system. Others are welcome to improve on the code, I am sure it's not the best way (first sweep!).

$query = "select project_name, project_id, id, domainname from projects ORDER BY project_name";

// I added some test projects here called 
//      test10
//      test100
//      test30

$result = mysql_query($query) or die(mysql_error($linkid));

$c=0;
while ($rst = mysql_fetch_array($result)) {
    $gen_array[$c][pr_name] = $rst[project_name];
    $gen_array[$c][project_id] = $rst[project_id];
    $c++;
    // You can add other results, this was a test.
}

$v=0;
while ($v<$c) {
    // put in separate array to run natsort.
    $gen2_array[] = $gen_array[$v][pr_name];
    $v++;   

}

natsort($gen2_array);

foreach($gen2_array as $key => $val) {
    // here we can link by key.
    echo $val . " Project ID: " . $gen_array[$key][project_id] . "<br>";
}

Hope this helps.

p.s: I tested this and it works, please let me know if it sorts your issue out. (pun intended).

-m.

[ywickham]

I guess I just felt it was a newbie question, since no one else seemed to be asking for the same information. 🙂

Your solution works great, modified for my db. Now I just have to work on integrating it with my current script.

Thanks again!