SQL result based on array order

katiliosk

I'm searching my dealer database, but want to order the results based on a previously defined array of zip codes, which are sorted by the distance from the original zip code searched. The problem is that the dealers are not returned in the order of my query. It would ideal to also have the zip distance included on each dealer returned. Any help would be appreciated.

<?php
// SEARCH DEALERS FOR PROXIMITY TO ZIP CODE
	if ($zipCode_searched != null && zipCode_searched != "") {
		// ADD 10 MILES (DUE TO BAD LAT/LON DATA?)
		$zipCode_distance += 10;

	$zips = get_zips_in_range($zipCode_searched, $zipCode_distance);

	asort($zips);

	if (empty($zips)) {
		// echo 'Error: '.$z->last_error;
		die("ERROR: ZIP CODE SEARCH FAILED");
	}
	else {
		$sql = "SELECT * FROM dealerAmerica ";
		$sql .= "WHERE zipCode = '$zipCode_searched' ";

		foreach ($zips as $key => $value) {
			$sql .= "OR zipCode = '$key' ";

			// echo "Zip code <b>$key</b> is <b>$value</b> miles away from <b>$zipCode_searched</b>.<br />\n";
			// Example array data:
			// Zip code <b>98199</b> is <b>0.69</b> miles away from <b>98155</b>.
			// Zip code <b>98023</b> is <b>1.09</b> miles away from <b>98155</b>.
			// Zip code <b>98011</b> is <b>2.42</b> miles away from <b>98155</b>.
			// Zip code <b>98036</b> is <b>2.85</b> miles away from <b>98155</b>.
		}

		$sql .="AND disabled != '1'";
	}
}
?>

SELECT * FROM dealerAmerica WHERE zipCode = '98155' OR zipCode = '98199' OR zipCode = '98023' OR zipCode = '98011' OR zipCode = '98036' OR zipCode = '98043' OR zipCode = '98021' OR zipCode = '98034' OR zipCode = '98026' OR zipCode = '98012' OR zipCode = '98020' OR zipCode = '98037' OR zipCode = '98028' OR zipCode = '98206' OR zipCode = '98033' OR zipCode = '98072' OR zipCode = '98052' OR zipCode = '98204' OR zipCode = '98208' OR zipCode = '98039' OR zipCode = '98296' OR zipCode = '98275' OR zipCode = '98004' OR zipCode = '98005' OR zipCode = '98007' OR zipCode = '98203' OR zipCode = '98008' OR zipCode = '98053' OR zipCode = '98291' OR zipCode = '98346' OR zipCode = '98342' OR zipCode = '98205' OR zipCode = '98392' OR zipCode = '98040' OR zipCode = '98236' OR zipCode = '98110' OR zipCode = '98025' OR zipCode = '98019' OR zipCode = '98340' OR zipCode = '98201' OR zipCode = '98006' OR zipCode = '98061' OR zipCode = '98109' OR zipCode = '98364' OR zipCode = '98370' OR zipCode = '98014' OR zipCode = '98290' OR zipCode = '98345' OR zipCode = '98029' OR zipCode = '98056' OR zipCode = '98272' OR zipCode = '98178' OR zipCode = '98059' AND disabled != '1'

paulnaj

Hi,

Use the ORDER BY clause (the order of the AND's in the SQL string is not relevant).

Also, you could use the WHERE ... IN ... statement rather than a list of AND's ...

<?php 
if ($zipCode_searched != null && zipCode_searched != "") {
	// ADD 10 MILES (DUE TO BAD LAT/LON DATA?)
	$zipCode_distance += 10;

$zips = get_zips_in_range($zipCode_searched, $zipCode_distance);

if (empty($zips)) {
	// echo 'Error: '.$z->last_error;
	die("ERROR: ZIP CODE SEARCH FAILED");
} else {
	// Shove zipcode onto the end
	array_push($zips, $zipCode_searched);

	$sql = '
	SELECT * FROM dealerAmerica 
	WHERE zipCode IN ("'.join('", "', $zips).'")
	AND disabled != "1"
	ORDER BY zipCode, ASC
	';
}
}
?>

Paul 🙂

katiliosk

Not sure if I missed something, but that doesn't seem to work since the join command only returns the distances, not the zips. Even if it did return the zips, there would still be no sql field to sort on distance.

Just to confirm, I have an array with the array index as the zip code and index's value as the distance (from $zipCode_searched).

I have no problem getting all the correct dealers from the DB, but I'm unable to sort on the distance (which is located in the array). Here is the code again with a portion of the result below...

$sql = "SELECT * FROM dealerAmerica ";
$sql .= "WHERE zipCode = '$zipCode_searched' ";
foreach ($zips as $key => $value) {
	echo "Zip code <b>$key</b> is ";
	echo "<b>$value</b> miles away from ";
	echo "<b>$zipCode_us</b>.<br />\n";

$sql .= "OR zipCode = '$key' ";
}
$sql .= " AND disabled != '1'";
die("<br />" . $sql);

Zip code 98199 is 0.69 miles away from 98155.
Zip code 98023 is 1.09 miles away from 98155.
Zip code 98011 is 2.42 miles away from 98155.

SELECT * FROM dealerAmerica 
WHERE zipCode = '98155' 
OR zipCode = '98199' 
OR zipCode = '98023' 
OR zipCode = '98011' 
AND disabled != '1' ORDER BY zipCode, ASC

Is there a way to have "0.69" associated with all records that match the zipCode as "98199"?

If not, I'm looking to at least order on the distance values.

rachel2004

Instead of using the PHP array to do the math and trying to get the the db to output in the same key order as the PHP array, why not do the math in the db as well? Here's a simple example:

SELECT (pointA - point😎 AS distance,
blah, blah
FROM Table
ORDER BY distance;

Besides the benefit of getting the ORDER BY based on distances, you get a performance increase the database is faster for these calculations than the method you are doing.

paulnaj

Originally posted by katiliosk
Not sure if I missed something, but that doesn't seem to work since the join command only returns the distances, not the zips. Even if it did return the zips, there would still be no sql field to sort on distance.

Oh yes,

EDIT: Should have been more precise ...

<?php 
if ($zipCode_searched != null && zipCode_searched != "") {
	// ADD 10 MILES (DUE TO BAD LAT/LON DATA?)
	$zipCode_distance += 10;

$zips = get_zips_in_range($zipCode_searched, $zipCode_distance);

if (empty($zips)) {
	// echo 'Error: '.$z->last_error;
	die("ERROR: ZIP CODE SEARCH FAILED");
} else {
	$sql = '
	SELECT * FROM dealerAmerica 
	WHERE zipCode = "'.$zipCode_searched.'" 
	OR zipCode IN ("'.join('", "', $zips).'")
	AND disabled != "1"
	ORDER BY zipCode, ASC
	';
}
}
?>

Paul.

katiliosk

Again, I apologize if I'm overlooking something in the above posts, but the problem is that the above suggestions do not sort on the distance ($zip array's $value). Instead, they seem to focus on sorting the actual zip codes, which is irrelevant.

Here is another attempt at clarifying the problem...

// TEST WITH SEATTLE ZIP
$zipCode_searched = '98104';
$zipCode_distance += 10;

$z = new zipcode_class;
$zips = $z->get_zips_in_range($zipCode_us, $zipCode_distance);

foreach ($zips  as $key => $value) {
	echo "zips[$key] = $value<br />\n";
}

The above array returns the following data for nearby zip codes and their distance from 98104...
zips[98012] = 5.82
zips[98014] = 17.09
zips[98019] = 15.39
zips[98020] = 5.85
zips[98021] = 3.6
zips[98023] = 1.09

There is no problem selecting all the nearby dealers in my database using this query...

$sql = "SELECT * FROM dealerAmerica ";
$sql .= "WHERE zipCode = '$zipCode_us' ";
foreach ($zips  as $key => $value) {
	$sql .= "OR zipCode = '$key' ";
}
$sql .= " AND disabled != '1'";
//$sql .= " ORDER BY zipCode, ASC";

Ideally I'd like to add the distance data to the mysql result and sort on that. Any ideas?