Sorting mysql data based on an array

[AtomicRax]

I'm working on a store locator style program. I first get and sort all zip codes based on a their distance to an original location. I then use that zip code array to find all stores in the mysql database, but when I get the results, they're no longer arranged by distance from the original location.. and I can't just sort them ascending or descending based on their zip codes because that doesn't sort them correctly either.

 

So I need to sort the data I get from the mysql database based on their "zip_code" column and in order from the $zip array..

 

Here's an example of what $zip looks like.. the zip code is the key and the distance from the original zip is the value [meaning the first key is the $originalZip]:

Array (
[70601] => 0
[70616] => 1.12
[70629] => 1.2
[70612] => 1.24
[70602] => 1.31
[70615] => 1.88
[70609] => 3.41
[70605] => 4.4
[70669] => 4.62
[70606] => 4.9
[70611] => 7.23
[70607] => 9.68
[70663] => 9.74
)

 

 

and then I create the $WHERE variable by each key supplied:

$WHERE = "WHERE zip_code='$originalZip'";

foreach ($zip as $key => $value) {
if ($key == $originalZip) {}
else { $WHERE .= " OR zip_code='$key'"; }
}
}

 

Then I do the query [with a paginator]:

$query = query("SELECT * FROM " . $db['prefix'] . "stores $WHERE LIMIT $from, $maxResults");

 

but when I print the data, it doesn't keep the correct sorting format..

if (mysql_num_rows($query) > 0) {
for ($i = 0; $i<mysql_num_rows($query); $i++) {
	$storeData = mysql_fetch_array($query, MYSQL_ASSOC);
	// print store information here such as $storeData['name'] $storeData['address'] $storeData['zip_code']
} }

 

Is there an easy way to sort the mysql results based on the $zip array?

[MMDE]

$mysqlhost='';
$mysqlusername='';
$mysqlpassword='';
$mysqldatabase='';
$mysqltable='';
$mysql=mysql_connect($mysqlhost,$mysqlusername,$mysqlpassword) or die(mysql_error());
mysql_select_db($mysqldatabase,$mysql) or die(mysql_error());
$query='SELECT * FROM '.$mysqltable;
echo $query;
$result=mysql_query($query,$mysql) or die(mysql_error());
mysql_close($mysql) or die(mysql_error());
$array=array();
while($row=mysql_fetch_array($result)){
$array[]=$row;
}
print_r($array);

 

Fill in the variables and try to debug the problem.

oh and of course include your order by and sorting stuff.

Just try to figure out by echoing and printing the stuff you got all the way to see where it goes wrong for you.

[AtomicRax]

My deepest apologizes, I did leave something out. I'm also trying to sort the super stores on top of the regular stores, but still maintain the original zip code sorting. The column "superstore" is equal to 1 if it is a superstore, and 0 if it's not.. so the real query is:

 

$query = query("SELECT * FROM " . $db['prefix'] . "stores $WHERE ORDER BY superstore DESC LIMIT $from, $maxResults");

 

which puts the super stores on top, but nothing is sorted correctly by zip code

[MMDE]

dude, I've tried to put together your query a couple of times, and really, unless you give us pretty much all of the code, (except for the pw n stuff), then there's not much I can do... at least give me all the stuff that has to do with mysql and how you handle the data after that. (including the unknown variables, example on how they are set and with what, so the script makes sense)

[AtomicRax]

I've made some progress; I've managed to add the distance for each array in the mysql data array. Now it appears I'm stuck on sorting a multidimensional array based on two key values of arrays inside of them...

 

I have this array:

Array
(
    [0] => Array
        (
            [superstore] => 0
            [name] => My nonSuperstore
            [address] => ...
            [city] => ...
            [zip_code] => 70601
            [phone] => ...
            [website] => ...
            [distance] => 0
        )

    [1] => Array
        (
            [superstore] => 1
            [name] => My Superstore
            [address] => ...
            [city] => ...
            [state] => ...
            [zip_code] => 70648
            [phone] => ...
            [website] => ...
            [distance] => 27.29
        )

    [2] => Array
        (
            [superstore] => 0
            [name] => My nonSuperstore
            [address] => ...
            [city] => ...
            [state] => ...
            [zip_code] => 70651
            [phone] => ...
            [website] => ...
            [distance] => 24.45
        )
)

 

I've been browsing the sort, usort, asort, multisort, etc... manual pages and different tutorials but I can't figure it

 

I need to sort the multidimensional array first by "superstore" then by "distance" .. making sure the superstores are listed above all else, and then the distance is sorted by the closest location to furthest location.. keeping the most outer key value is not important. Meaning I need to sort the above array into this:

Array
(
    [0] => Array
        (
            [superstore] => 1
            [name] => My Superstore
            [address] => ...
            [city] => ...
            [state] => ...
            [zip] => 70648
            [phone] => ...
            [website] => ...
            [distance] => 27.29
        )

    [1] => Array
        (
            [superstore] => 0
            [name] => My nonSuperstore
            [address] => ...
            [city] => ...
            [zip] => 70601
            [phone] => ...
            [website] => ...
            [distance] => 0
        )

    [2] => Array
        (
            [superstore] => 0
            [name] => My nonSuperstore
            [address] => ...
            [city] => ...
            [state] => ...
            [zip] => 70651
            [phone] => ...
            [website] => ...
            [distance] => 24.45
        )
)