0

I have a MySQL table with the following attributes: store_name lat lon

I have 1500 records.

I need to find the closest store to the user (based on IP).

I have figured the IP translation part using a 3rd party API. I know how to calculate the distance between 2 coordinates. However, I don't know how to return the store name that is the closest.

My function to calculate distance

function distance($lat1, $lon1, $lat2, $lon2) 
{
$minus = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1))*cos(deg2rad($lat2)) * cos(deg2rad($minus));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;
return $miles;
}

I can find the distance between user and each store.

Note: $lat1 and $lat1 are obtained by a 3rd party API service.

$store = mysql_query("SELECT * FROM store") or die(mysql_error());
while($row=mysql_fetch_assoc($store))
{
$lat2 = $row["lat"];
$lon2 = $row["lon"];
$distance = distance($lat1, $lon1, $lat2,$lon2);
}

How do I calculate run the distance function and store the distance value in an array and then sort the distances to find the nearest store? Is this the correct methodology? And how do I proceed with the array code? I have no experience with arrays in PHP.

Thank you.

Cody Raspien
  • 1,753
  • 5
  • 26
  • 51
  • 1
    Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Feb 04 '16 at 22:39
  • mysql_query aside (I'll fix this, thank you), how do I handle the sorting? – Cody Raspien Feb 04 '16 at 22:42
  • Share what you have tried where the array and sorting is concerned. Or did you want someone to write it for you? – Jay Blanchard Feb 04 '16 at 22:45
  • IP will not be accurate enough if you have multiple stores in one city, have a look at geolocation from the browser: https://developer.mozilla.org/en-US/docs/Web/API/Geolocation/Using_geolocation –  Feb 04 '16 at 23:00
  • What would be more efficient: selecting a list of every store and checking them all, or doing the math in the database query and only selecting the one store that matches? – miken32 Feb 04 '16 at 23:48

1 Answers1

1

You could just do the math on the database side, order your result set by distance and select the one with the lowest value.

And I took the liberty of updating your database access methods. Don't, under any circumstances, use mysql_* functions.

$lat1 = 123.245;
$lon1 = 48.123;

$dbhost = "localhost";
$dbname = "database";
$username = "user";
$password = "pass";

$db = new PDO("mysql:host=$dbhost;dbname=$dbname", $username, $password);

$query = "SELECT *, DEGREES(ACOS(SIN(RADIANS(?)) * SIN(RADIANS(`lat`)) +  COS(RADIANS(?)) * COS(RADIANS(`lat`)) * COS(RADIANS(? - `lon`)))) * 60 * 1.1515 AS distance
    FROM store
    ORDER BY distance ASC
    LIMIT 1";
$stmt = $db->prepare($query);
$stmt->execute(array($lat1, $lat1, $lon1));
$result = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($result);
miken32
  • 42,008
  • 16
  • 111
  • 154
  • Quick question - why 2 $lat1 variables passed in this line - $stmt->execute(array($lat1, $lat1, $lon1)); – Cody Raspien Feb 05 '16 at 07:32
  • You reference the user latitude twice in the formula; you need to pass in one variable for each `?` placeholder. – miken32 Feb 05 '16 at 15:23