The first step you need to do is store the longitude and latitude of the sites in the database. When you've done that you can convert the posted address of a client to longitude and latitude as well:
function getLongitudeAndLatitude($address) {
$url = 'http://maps.googleapis.com/maps/api/geocode/xml?sensor=false&language=nl&address=' . str_replace(' ','+', $address);
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$response = curl_exec($ch);
curl_close($ch);
$obj = simplexml_load_string($response);
$lat = null;
$lon = null;
if ($obj) {
$obj = @$obj->result;
$lat = (@$obj->geometry->location->lat.'');
$lon = (@$obj->geometry->location->lng.'');
}
return array( 'latitude' => $lat, 'longitude' => $lon,);
}
With this data you then can calculate the distance between the client and the sites. To do this you can create a routine in mysql
CREATE DEFINER=`user`@`localhost` FUNCTION `haversine`(`lat1` FLOAT, `lon1` FLOAT, `lat2` FLOAT, `lon2` FLOAT) RETURNS float
NO SQL
DETERMINISTIC
COMMENT 'Returns the distance in degrees on the Earth between two known points of latitude and longitude'
BEGIN
RETURN 111.045*DEGREES(ACOS(
COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) *
COS(RADIANS(lon2) - RADIANS(lon1)) +
SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
));
END
With this routine you then can create a SQL that returns the nearest site
$user_location = getLongitudeAndLatitude($_POST['txt_address']);
if (isset($user_location['latitude']) && isset($user_location['longitude'])) {
$stmt = $pdo->prepare('SELECT *, haversine(:lat1, :lon1, site.latitude, site.longitude) as distance FROM site ORDER BY distance LIMIT 1');
$stmt->execute([':lat1' => $user_location['latitude'], ':lon1' => $user_location['longitude']);
$res = $stmt->fetch(PDO::FETCH_ASSOC);
var_dump($res);
}
