I have a MySQL table in the following format with roughly 500 rows
ID¦name¦stuff¦location
My php script gets a users location, selects all of the rows from the database and then gets the distance from each rows location to the users location using the google maps API and puts it all in a json array
The problem is even processing 500 rows takes 300 seconds on average, I wanted to get it down to roughly 3-4 seconds somehow, It appears to be the google maps API that's bottlenecking the whole script.
What I'm asking is if there's a better way to handle the whole thing so that I can get the script to process everything in under 10 seconds.
the php script -
<?php
//used for benchmarking
$time_start = microtime(true);
require_once 'db.php';
//get the users location
$location = $_GET['location'];
//check the users location is valid
if (checkLocation($location) == "found") {
echo "null";
} else {
//select all of the rows
$SQLSelect = $odb -> query("SELECT * FROM `something`");
$array = array();
$count = 0;
while ($show = $SQLSelect -> fetch(PDO::FETCH_ASSOC)) {
$distance = getDistance($location, $show['location']);
$distance = $distance * 0.000621371192;
//narrow down the distance
if ($distance < 100) {
$array[$count] = array();
$array[$count]['name'] = $show['locationName'];
$array[$count]['spec'] = $show['spec'];
$array[$count]['distance'] = round($distance);
$count = $count + 1;
}
}
//sort the array based on distance
usort($array,function($a,$b) {return strnatcasecmp($a['distance'],$b['distance']);});
echo json_encode($array);
}
function getDistance($start, $end) {
$from = $start;
$to = $end;
$from = urlencode($from);
$to = urlencode($to);
$data = file_get_contents("http://maps.googleapis.com/maps/api/distancematrix/json?origins=$from&destinations=$to&language=en-EN&sensor=false");
$data = json_decode($data);
$time = 0;
$distance = 0;
foreach($data->rows[0]->elements as $road) {
$time += $road->duration->value;
$distance += $road->distance->value;
}
return $distance;
}
function checkLocation($start) {
$from = $start;
$to = 'location1';
$from = urlencode($from);
$to = urlencode($to);
$data = file_get_contents("http://maps.googleapis.com/maps/api/distancematrix/json?origins=$from&destinations=$to&language=en-EN&sensor=false");
if (strpos($data, '"status" : "NOT_FOUND"') !== false) {
return "found";
} else {
return "null";
}
}
echo '</br>Total execution time in seconds: ' . (microtime(true) - $time_start);
?>
