Posted by Tyrone Slothrop on 11/03/06 15:51

On Fri, 03 Nov 2006 07:02:04 +0100, "J.O. Aho" <user@example.net>
wrote:

>Tyrone Slothrop wrote:
>> I need to write a query which will find those records which have the
>> closest values to a zip code posted through a form. The values may be
>> greater than or less than the requested value.
>>
>> I had written a function which accomplishes this which uses multiple
>> queries many years ago. As I am now revising the site in a major
>> redesign, the thought occurred to me that there must be a better way.
>>
>> Any ideas?
>
>If you have number only zip codes (not all countries has that), then you can
>use a simple interval limitation (as numbers close to another usually are
>close in geographical terms too, but not always true).
>
>
>$query="SELECT * FROM ZipTable WHERE (ZipCode > $ZipNumber - $Range) AND
>(ZipCode < $ZipNumber + $Range)";
>
>Where we use two variables set by the php-script, $ZipNumber is the zip code
>we are searching for and $Range is the value how much larger or smaller the
>zip code is for those which are "near".
>
>It's of course a question how you determine what is close, so this simple
>query may not be what you was looking for.

Assuming that all zips are integers (this is for U.S. only), this
might work:

SELECT *, ABS(zip-$zip) AS diff FROM table ORDER BY diff LIMIT 10;

Where $zip is the value passed by the form.

Am going to give it a try now.

Thanks for the inspiration!

[Back to original message]