1

Hey I have a MySQL table of stores that contains latitude and longitude in every row, When the user get in the android app he has an option to filter the stores in a radius he choose, I need to write a php code that will only select and print the stores inside the radius the user choose, for now I take an example of lower than 100, every code I searched doesn't seem to work for me, here is some of my php code:

a distance function I tried:

function distance($lat1, $lon1, $lat2, $lon2) {

  $theta = $lon1 - $lon2;
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
  $dist = acos($dist);
  $dist = rad2deg($dist);
  $miles = $dist * 60 * 1.1515;
   return ($miles * 1.609344);
}

and here is the select statement:

$my_lat = $_POST["lat"];
$my_long = $_POST["lon"];

 if($my_lat != null)
{

$stmt = $db->query("SELECT * FROM app HAVING distance(lat,lon,'$my_lat','$my_long')<100");
}
user7415791
  • 449
  • 1
  • 7
  • 11
  • 1
    Do you call distance as an SQL function in your MySQL database but you have implemented it in PHP? Either you have to query all entries and go ahead with comparing those in PHP or you implement a distance function for MySQL. – Blackbam Jan 13 '17 at 16:36
  • Possible duplicate of [Fastest Way to Find Distance Between Two Lat/Long Points](http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points) – Blackbam Jan 13 '17 at 16:37
  • @Blackbam I am just trying to pull all the data that its distance from the current location is lower from the specific radius – user7415791 Jan 13 '17 at 16:48
  • **WARNING**: Whenever possible use **prepared statements** to avoid injecting arbitrary data in your queries and creating [SQL injection bugs](http://bobby-tables.com/). These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. – tadman Jan 13 '17 at 18:06
  • Don't do this. Writing your own functions is a complete waste of time, and you'll invariably get them wrong. Use the [MySQL GIS extensions](http://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html). It's worth noting that while MySQL has basic support for GIS, if you're doing a lot of this then [Postgres with PostGIS](http://www.postgis.net) often works out better in the long run. – tadman Jan 13 '17 at 18:07

0 Answers0