Solved

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

Posted on 2010-09-06
19
687 Views
Last Modified: 2012-05-10
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?

Thanks in advance.

0
Comment
Question by:colonelblue
  • 5
  • 5
  • 4
  • +4
19 Comments
 
LVL 27

Accepted Solution

by:
yodercm earned 200 total points
ID: 33614606
SELECT  * FROM Table ORDER BY ABS(fieldname-1000) LIMIT 3
0
 
LVL 82

Expert Comment

by:Dave Baldwin
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

by:colonelblue
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

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

Expert Comment

by:yodercm
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

by:p_nuts
ID: 33615115
but that gives all not the top 3

.nice question btw...
0
 
LVL 82

Expert Comment

by:Dave Baldwin
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

by:yodercm
yodercm 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

by:colonelblue
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 27

Expert Comment

by:yodercm
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

by:Fugas
ID: 33617038
try with only two 'E'. BETWEEN
0
 
LVL 5

Assisted Solution

by:innotionent
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 108

Expert Comment

by:Ray Paseur
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

by:colonelblue
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

by:colonelblue
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 108

Assisted Solution

by:Ray Paseur
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:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3350-Using-the-Google-Maps-API-in-PHP.html

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
    ( sin(deg2rad($from_lat))
    * sin(deg2rad($to_lat))
    + cos(deg2rad($from_lat))
    * cos(deg2rad($to_lat))
    * cos(deg2rad($from_lon - $to_lon))
    )
    ;
    $distance = rad2deg($distance);

    // 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>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
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>
<head>
<title>Yahoo/Google SimpleGeoCoder Demo</title>
</head>
<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->address   = (string)$ydata->Result->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;
        }

        if (!defined('GOOGLE_API')) define('GOOGLE_API', 'GOOGLE_API');
        $this->geocoder = 'Google';
        $googleUrl = "http://maps.google.com/maps/geo?key=" . GOOGLE_API . "&output=csv";
        $googleUrl .= "&q=" . urlencode($loc);

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

        // EXTRACT THE DATA FROM THE CSV STRING
        $gdata = explode(',',$googleResponse);
        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);
}

Open in new window

0
 

Author Comment

by:colonelblue
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 108

Assisted Solution

by:Ray Paseur
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now