Is This Possible?

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?

Re: Is This Possible?

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.



//Aho

Re: Is This Possible?

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?

You haven't told us whether you want to limit the output by number of
records or by distance from the target.

But check out the posts found by ths search:

http://groups.google.co.uk/group/comp.databases.mysql/search ?group=comp.databases.mysql&q=abs&qt_g=1

Re: Is This Possible?

On Fri, 03 Nov 2006 07:02:04 +0100, "J.O. Aho"
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!