Solved

# MYSQL Superguru question. SELECT WHERE statement to output the closest 3 numbers of value entered?

Posted on 2010-09-06
700 Views
I wanted to send a value to a page where it would return the closest 3 numbers to that value from a given row.

For example , I had an entered value of 1000
On the existing table row , the values were:

200
500
800
900
700
1100

Ideally, I would get the output of:
800
900
1100

Since these are the 3 closest numbers.

Is this possible?

0
Question by:colonelblue
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 5
• 4
• +4

LVL 27

Accepted Solution

Cornelia Yoder earned 200 total points
ID: 33614606
SELECT  * FROM Table ORDER BY ABS(fieldname-1000) LIMIT 3
0

LVL 83

Expert Comment

ID: 33614615
Why aren't 700, 800, 900 the closest numbers?  In any case, I would do 'ORDER BY' that column because by definition, numbers can't be closer to those farther away.  I'm not sure how to tell MySQL to pick the ones that are closest to each other.
0

Author Comment

ID: 33614984
Hello and thank you both..

As per your question Dave, let's say I wanted to find a 5 digit Zipcode ( in the data )  that was closest to the Zipcode entered.
Now I know it won't be the most accurate but the logic would be that zipcode11200 will be closest to 22500 then it is to 42015 which is by and large true if I am basing it on the same state.

So is there a way to modify the code to make it output anything that falls in the range of -500 and +500 of the value? I suppose it would be a double query in one?
Where the first would look for anything less than 500 and another query that is anything more than 500 and them output both results?

0

LVL 27

Expert Comment

ID: 33615069
SELECT  * FROM Table WHERE ABS(Zipcode-500) < 0
0

LVL 27

Expert Comment

ID: 33615077
Oops, forget that last, it doesn't work.

This will

SELECT * FROM Table WHERE Zipcode BETWEEEN (\$target+500) AND (\$target-500)
0

LVL 13

Expert Comment

ID: 33615115
but that gives all not the top 3

.nice question btw...
0

LVL 83

Expert Comment

ID: 33615186
If you really want zip codes, you should sign up for the USPS API.  I don't think your scheme will work.  The zip codes around me here are definitely not in closest order.  Between zipcodes 95762 and 95776 are twelve zipcodes that start with 956xx and 958xx and none that start with 957xx.
0

LVL 27

Assisted Solution

Cornelia Yoder earned 200 total points
ID: 33615193
The original question asked for the top 3, and I gave a query for that.   The second question just above asked for ALL within a +/-500 range, and I gave that.   Is there a problem pnuts?

You can still add the ORDER BY .... LIMIT 3
0

Author Comment

ID: 33615406

Hello and thank you especially yodercm! *bows down*

In Joomla there is an add on called Chronoforms and ChronoConnectivity.

Query statements are entered into a box labeled WHERE SQL: which then is an open text box for php and queries
..
This my present query in php

<?php
\$zipcode1 = JRequest::getVar('zipcode1', '', 'get');
echo "WHERE `plz` = '\$zipcode1' ";
?>

It works.
It returns all the zipcodes that equal the url variable zipcode1.

This is what I did to modify it with your code:
<?php
\$zipcode1 = JRequest::getVar('zipcode1', '', 'get');
echo "WHERE 'plz' BETWEEEN '(\$zipcode1+500)' AND '(\$zipcode1-500)'";
?>

I must have done something wrong with the syntax.
I get this error from it:

You have an error in your SQL syntax; check the manual that corresponds  to your MySQL server version for the right syntax to use near 'BETWEEEN  '(+500)' AND '(-500)'' at line 1 SQL=SELECT count(*) FROM  jml_eventlist_venues WHERE 'plz' BETWEEEN '(+500)' AND '(-500)'You have  an error in your SQL syntax; check the manual that corresponds to your  MySQL server version for the right syntax to use near 'BETWEEEN '(+500)'  AND '(-500)'' at line 1 SQL=SELECT count(*) FROM jml_eventlist_venues  WHERE 'plz' BETWEEEN '(+500)' AND '(-500)'

You have an error in your SQL syntax; check the manual that corresponds  to your MySQL server version for the right syntax to use near 'BETWEEEN  '(+500)' AND '(-500)'' at line 1 SQL=SELECT count(*) FROM  jml_eventlist_venues WHERE 'plz' BETWEEEN '(+500)' AND '(-500)'

Your expertise is so sincerely regarded and appreciated.

0

LVL 27

Expert Comment

ID: 33617005
'BETWEEEN '(+500)'  AND '(-500)

This means the variable \$zipcode1 is not set.  Check where you think it should be set to some value and see why it's not.
0

LVL 4

Expert Comment

ID: 33617038
try with only two 'E'. BETWEEN
0

LVL 5

Assisted Solution

innotionent earned 100 total points
ID: 33617478
Zip codes are tricky.

I'd look into a paid city, state, county, zip code database. where you could search the and list zip codes that are within a certain city or county.

I've done this and it works really well if you ever need to do searches based on zip codes or in relation to counties, cities or zips.
0

LVL 110

Expert Comment

ID: 33617694
@colonelblue:  Let's be clear and specific about this.  Are we being theoretical about "any numbers" or are we talking about 5-digit USA zip codes?

Are you interested in finding numerically close values (like 22101 is close to 22102) or geographically close locations (like 22101 is close to 22007)?

Standing by, ~Ray
0

Author Comment

ID: 33618838
Hello and thank you everyone. You guys are amazing.

For Ray's question. Yes for geographical. I apologize if I came across misleading in any sense which was not my intent but not believing it was doable through this site which was using Joomla and with very limited resources ( buying a full blown app or api ) I thought a "poor man's way"of doing this is to find the nearest zipcode via a number range. In my thinking it would only apply to one state where by and large the zipcodes have a logic to them in terms of increase in the 5 digit number as a whole paralleled to the distance. If used for the US with all of the states it would not work. But since I am crunched for time and a bit desperate to get this off the punc list I tried to do with the best I could.

So indeed I am interested in finding " geographically close locations (like 22101 is close to 22007)?" But will do with what I can work with.

0

Author Comment

ID: 33618915
Any yes. Opps there was an extra E in between. Although there are no error it does not return any results.

When using the code below it returns all zipcodes LIKE the parameter.
echo "WHERE `plz` LIKE '\$zipcode1' ";

When using the below, nothing comes up.
echo "WHERE 'plz' BETWEEN '(\$zipcode1+500)' AND '(\$zipcode1-500)'";

Is there something I am doing wrong?
0

LVL 110

Assisted Solution

Ray Paseur earned 200 total points
ID: 33620226
OK, to get the locations of ZIPcodes you're getting into an area of expertise called "geolocation" -- it's what those web sites do when they ask you for your zip code and then show you the stores that are closest to you.  This is a fairly advanced topic and may not be a great place to be a beginner in PHP and MySQL, but I will try to show you the principles.

There are about 42,000 ZIP codes that you would be interested in.  APO/FPO, PO Box Only, etc., are not applicable.

First issue you must accept: A ZIP code is not a location, per se.  It is a mail carrier's route.  So in places like Manhattan the ZIP code area is amazingly small, whereas in the western states it may be hundreds of miles wide.  Nevertheless, the major geocoders from Yahoo and Google will return a geocode for a ZIP code.

A geocode is a latitude/longitude pair, always in that order.  It is expressed as a pair of signed decimal number strings, separated by a comma.

The Haversine formula is used to compute "great circle" distances between points on the globe.  You may be able to get away with plane geometry if the distances are not great.  See the code snippet.
http://en.wikipedia.org/wiki/Haversine_formula

Here is an article that may be helpful as you delve into this area of endeavor:

This search may prove helpful.
http://lmgtfy.com?q=data+base+of+zip+codes+and+locations
``````<?php // RAY_compute_distance.php
error_reporting(E_ALL);
echo "<pre>\n";

// COMPUTE THE DISTANCE BETWEEN TWO LAT/LON PAIRS

// MAN PAGE: http://en.wikipedia.org/wiki/Haversine_formula
function compute_distance(\$from_lat, \$from_lon, \$to_lat, \$to_lon, \$units='KM')
{
// CHOOSE A UNIT OF MEASURE BY THE FIRST CHARACTER
\$units = strtoupper(substr(trim(\$units),0,1));

// ENSURE THAT ALL COORDINATES ARE FLOATING POINT VALUES
\$from_lat = floatval(\$from_lat);
\$from_lon = floatval(\$from_lon);
\$to_lat   = floatval(\$to_lat);
\$to_lon   = floatval(\$to_lon);

// IF THE SAME POINT THE DISTANCE IS ZERO.  HAVERSINE FAILS ON THIS INSTANCES
if ( (\$from_lat == \$to_lat) && (\$from_lon == \$to_lon) )
{
return 0.0;
}

// COMPUTE THE DISTANCE WITH THE HAVERSINE FORMULA
\$distance
= acos
* cos(deg2rad(\$from_lon - \$to_lon))
)
;

// DISTANCE IN MILES AND KM - ADD OTHERS IF NEEDED
\$miles = (float) \$distance * 69.0;
\$km    = (float) \$miles * 1.61;

// RETURN MILES
if (\$units == 'M') return round(\$miles,1);

// RETURN KILOMETERS = MILES * 1.61
if (\$units == 'K') return round(\$km,2);

// UNITS NOT UNDERSTOOD
return('INVALID FIFTH ARGUMENT - USE MILES OR KM');
}

// SHOW THE FUNCTION IN ACTION
if (!empty(\$_GET))
{
\$distance = compute_distance(\$_GET["a_lat"], \$_GET["a_lon"], \$_GET["b_lat"], \$_GET["b_lon"], \$_GET["units"]);
echo \$distance . ' ' . \$_GET["units"] . '<br/>' . PHP_EOL;
}

// END OF PHP - PUT UP THE FORM TO RECEIVE INPUT
?>
<a target="_blank" href="http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=camp+david+geolocation&sll=39.648333,-77.465&sspn=0.025972,0.057807&ie=UTF8&z=15&iwloc=lyrftr:m,10141899780758213082,39.648361,-77.465029">Camp David</a>
<a target="_blanl" href="http://maps.google.com/maps?f=q&source=s_q&hl=en&geocode=&q=White+House+Washington,+DC&sll=39.806296,-77.097044&sspn=0.414606,0.924911&ie=UTF8&hq=White+House&hnear=White+House,+Washington,+DC&ll=38.898047,-77.036562&spn=0.025417,0.057807&z=15&iwloc=A">White House</a>
TEST IT HERE:
<form>
POINT A LAT <input name="a_lat" value="38.898047"> LON <input name="a_lon" value="-77.036562" />
POINT B LAT <input name="b_lat" value="39.737554"> LON <input name="b_lon" value="-77.464943" />
<input type="radio" name="units" value="miles" checked="checked" />Miles
<input type="radio" name="units" value="km" />Kilometers
<input type="submit" />
</form>
``````
0

LVL 110

Expert Comment

ID: 33620241
Here is a class that will call the geocoders.  You can try it and see what you get.  Hope that helps get you started, ~Ray
``````<?php // RAY_class_SimpleGeoCoder.php
error_reporting(E_ALL);
echo "<pre>" . PHP_EOL;

// API KEYS, ETC., IF REQUIRED
// require_once('local_data.php');

// A FREEFORM NAMED LOCATION STATEMENT IS OPTIONAL - PROCESSED FIRST
if (!empty(\$_GET["n"]))
{
\$location = trim(\$_GET["n"]);
}
// ADDRESS, CITY, STATE, ZIP ARE OPTIONAL - PROCESSED IF FREEFORM LOCATION IS NOT PRESENT
else
{
\$location = '';
if (!empty(\$_GET["a"])) { \$location .= \$_GET["a"] . ' '; }
if (!empty(\$_GET["c"])) { \$location .= \$_GET["c"] . ' '; }
if (!empty(\$_GET["s"])) { \$location .= \$_GET["s"] . ' '; }
if (!empty(\$_GET["z"])) { \$location .= \$_GET["z"] . ' '; }
\$location = trim(\$location);
}

// IF WE HAVE A LOCATION STRING, RUN THE GEOCODERS
if (\$location)
{
// PREPARE THE SIMPLE GEO-CODER
\$demo = new SGC;

// TEST THE YAHOO! GEOCODER
\$demo->YGC(\$location);
echo PHP_EOL . "YAHOO! ";
print_rr(\$demo);

// TEST THE GOOGLE GEOCODER
\$demo->GGC(\$location);
echo PHP_EOL . "GOOGLE ";
print_rr(\$demo);
}
// ALL DONE PROCESSING THE INPUT, PUT UP THE FORM
?>
<html>
<body>
<form method="get">
Try using a full or partial address.
Addr: <input type="text" name="a" autocomplete="off" />
City: <input type="text" name="c" autocomplete="off" />
ST:   <input type="text" name="s" autocomplete="off" size="2" />
Zip:  <input type="text" name="z" autocomplete="off" size="8" />
<input type="submit" value="GeoCode This Address" />

Or use the name of a location.
Name: <input type="text" name="n" autocomplete="off" />
<input type="submit" value="GeoCode This Location Name" />
</form>
</body>
</html>

<?php
// SIMPLE GEOCODER CLASS
class SGC
{
// DECLARE THE WORKING DATA
private \$precis;

// DECLARE THE OUTPUT DATA
public \$latitude;
public \$longitude;
public \$precision;
public \$warning;
public \$error;
public \$geocoder;

// DECLARE THE CONSTRUCTOR
public function __construct()
{
\$this->latitude  = 0.0;
\$this->longitude = 0.0;
\$this->precision = FALSE;  // WANT A VALUE OF 5 OR HIGHER, HIGHER IS BETTER, 8 IS ON THE ROOFTOP
\$this->warning   = '';
\$this->geocoder  = '';
\$this->error     = '';
unset(\$this->precis);
}

// DECLARE THE DATA-CLEANUP
private function _cleanup(\$str)
{
\$str = preg_replace('/[^\' a-zA-Z0-9&!#\$%()"+:?\/@,_\.\-]/', '', \$str);
return trim(preg_replace('/\s\s+/', ' ', \$str));
}

// DECLARE THE YAHOO! VERSION OF THE WORKHORSE
public function YGC(\$location)
{
\$loc = \$this->_cleanup(\$location);
if (empty(\$loc))
{
\$this->error = "LOCATION DATA IS EMPTY";
return FALSE;
}
if (!defined('YAHOO_API')) define('YAHOO_API', 'YAHOO_API');
\$this->geocoder = 'Yahoo!';
\$yahooUrl = "http://local.yahooapis.com/MapsService/V1/geocode?&appid=" . YAHOO_API;
\$yahooUrl .= "&location=" . urlencode(\$loc);

// EXECUTE YAHOO GEOCODER QUERY
// NOTE - USE ERROR SUPPRESSION OR IT WILL BARK OUT THE YAHOO API KEY - ON FAILURE RETURNS HTTP 400 BAD REQUEST
if (\$yfp = @fopen(\$yahooUrl, 'r'))
{
\$yahooResponse = '';
while (!feof(\$yfp))
{
\$yahooResponse .= fgets(\$yfp);
}
fclose(\$yfp);
}
// IF SOMETHING IS SICK AT YAHOO
else
{
\$this->error = "UNABLE TO OPEN \$yahooUrl";
return FALSE;
}

// EXAMINE THE RESULT
if (\$yahooResponse != '') // NOT EMPTY, WE GOT DATA
{
\$ydata = new SimpleXMLElement(\$yahooResponse);

// CHECK FOR ANY ERROR MESSAGE, IF NONE, EXTRACT THE DATA POINTS
\$yerror = \$ydata->Message;
if (\$yerror == '')
{
\$this->precis    = (string)\$ydata->Result["precision"];
\$this->warning   = (string)\$ydata->Result["warning"];
\$this->latitude  = (string)\$ydata->Result->Latitude;
\$this->longitude = (string)\$ydata->Result->Longitude;

// THESE STATEMENTS CAN BE USED TO RETURN NORMALIZED ADDRESS
\$this->city      = (string)\$ydata->Result->City;
\$this->state     = (string)\$ydata->Result->State;
\$this->zip       = (string)\$ydata->Result->Zip;

// SET PRECISION TO A NUMBER VALUE
if (\$this->precis == 'zip')     { \$this->precision = "5"; }
if (\$this->precis == 'street')  { \$this->precision = "6"; }
if (\$this->precis == 'address') { \$this->precision = "8"; }
}
else
{
\$this->error = "ERROR: \$yahooUrl SAYS \$yerror";
return FALSE;
}
}

// NO RESULT - SOMETHING IS SICK AT YAHOO
else
{
\$this->error = "NO DATA RETURNED FROM \$yahooUrl";
return FALSE;
}
return TRUE;
} // END function geocodeYahoo

// DECLARE THE GOOGLE VERSION OF THE WORKHORSE
public function GGC(\$location)
{
\$loc = \$this->_cleanup(\$location);
if (empty(\$loc))
{
\$this->error = "LOCATION DATA IS EMPTY";
return FALSE;
}

\$googleUrl .= "&q=" . urlencode(\$loc);

// EXECUTE GOOGLE GEOCODER QUERY
if (\$gfp = @fopen(\$googleUrl, 'r'))
{
while (!feof(\$gfp))
{
}
fclose(\$gfp);
}
else
{
\$this->error = "UNABLE TO OPEN \$googleUrl";
return FALSE;
}

// EXTRACT THE DATA FROM THE CSV STRING
if (\$gdata[0] != '200') // RESPONSE CODE SHOULD BE '200' -- IF 602 - BAD ZIP CODE OR UNUSABLE ADDRESS
{
\$this->error = "ERROR CODE {\$gdata[0]} FROM \$googleUrl";
return FALSE;
}
\$this->precision = \$gdata[1]; // GEOCODE ACCURACY - ZIP CODE = 5, HIGHER NUMBERS ARE BETTER
\$this->latitude  = \$gdata[2];
\$this->longitude = \$gdata[3];
return TRUE;
} // END function geocodeGoogle

} // END class SimpleGeocoder

// UNRELATED FUNCTION TO MAKE THE OUTPUT SHOW THE PUBLIC INFORMATION ONLY
function print_rr(\$thing)
{
\$str = print_r(\$thing, TRUE);
\$arr = explode(PHP_EOL, \$str);
foreach (\$arr as \$ptr => \$txt)
{
if (preg_match('/:private]/', \$txt))
{
unset(\$arr[\$ptr]);
}
}
echo implode(PHP_EOL, \$arr);
}
``````
0

Author Comment

ID: 33623258
Thank you Ray_Paseur. I wish I was more than a novice coder. I can barely make heads or tails of it and why I use Joomla. I so need to get this project esp in these times.
Prior to me posting this reply I had just been told that they want a zipcode serach locator just like this one at http://www.mykidsplate.com/index.php.
I apologize that this thread has digressed from the original question and subject.

0

LVL 110

Assisted Solution

Ray Paseur earned 200 total points
ID: 33623440
No apology expected or needed.  This is an area for a professional who has experience in geolocation-aware applications.  The web site you showed is a classic subscription-based site with "find the closest" capability.

Your application needs to know the location of all your stores.  Then you need to geo-locate your clients' zip code, compute the distance and display the results.  It's a well-understood design, and it's a fairly substantial application development project -- custom code for each business enterprise.

Wish you well (and I recommend you hire a developer with the right experience), ~Ray
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
###### Suggested Courses
Course of the Month3 days, 18 hours left to enroll

#### 691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.