-1

I am using Google Maps API v3 to show all stored locations from the database as a marker. What I am trying to do is show a list beneath the map with the closest locations(let's say within 10km) and their name, description & distance to user's current location, ordered by distance. I can't figure out how to build the query. I currently have this code which shows all places in the database:

$q = "SELECT name, image, description, price, lat, lng, rating, owner FROM places"; 
        $res1 = mysqli_query($linkNew, $q);

            while($row1 = mysqli_fetch_array($res1)){
                echo "<div class='place'>";
                echo "<h3 class='placeTitle'>" . $row1[0] . "</h2><br />";
                echo "<img src='" . $row1[1] . "' class='placeImage' width='30' height='30' />";    
                echo "<p class='placeDescription'>" .$row1[2] . "</p><br />";
                echo "<p class='placePrice'>€ " .$row1[3] . "/night</p><br />";
                echo "<p class='placeRating'>" .$row1[6] . "/5</p><br />";
                echo "<p class='placeOwner'>Owner: <a href='#'><b>" .$row1[7] . "</b></a></p><br />";
                echo "</div>";
            }
WimVanhorenbeeck
  • 1
  • 1
  • 5
  • possible duplicate of [Google Maps Api v3 - find nearest markers](http://stackoverflow.com/questions/4057665/google-maps-api-v3-find-nearest-markers) – Blazemonger May 02 '14 at 14:30

1 Answers1

3
$sql = "SELECT *, ( 3959 * acos( cos( radians(" . $lat . ") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" . $lng . ") ) + sin( radians(" . $lat . ") ) * sin( radians( lat ) ) ) ) AS distance FROM your_table HAVING distance < 5";

Where $lat and $lng are the coordinates of your center point, and lat/lng are your table columns. The above will list the locations within a 5 nm range. Replace 3959 by 6371 to change to kilometers.

This link could be useful: https://developers.google.com/maps/articles/phpsqlsearch_v3

To pick the user location:

if (navigator.geolocation) {

    navigator.geolocation.getCurrentPosition(function(position) {

        userLat = position.coords.latitude;
        userLng = position.coords.longitude;
    });
}
MrUpsidown
  • 21,592
  • 15
  • 77
  • 131
  • Thank you! Is there a way to pick up the geolocation somehow and use that in this query as center point? – WimVanhorenbeeck May 02 '14 at 13:37
  • You seem to be advocating simply concatenating values into the query string. This should be avoided in favor of using parameterized queries, or you risk SQL Injection (granted, if the types are a `numeric` of some sort this is mitigated... but it's the principle of the thing). – Clockwork-Muse May 02 '14 at 14:03
  • This is to explain how to build the query. It is not supposed to be a training course on security. – MrUpsidown May 02 '14 at 14:14