CODEWITHSUNDEEP
phpjavascriptmysqlgoogle-maps-api-3
user1072337
user1072337

Reputation: 12935

Trying to create an array of latitudes and longitudes from cities contained in a mySQL database

I am trying to use Google Maps API to create an array of latitudes and longitudes (from a mySQL database of cities) that I can use as markers.

I am unsure of how to dynamically pull the cities in PHP and geocode them in javascript.

This is what I have so far. I know the geocoder line is incorrect, but I am not sure how to work it in. Any advice or solutions would be very helpful. 

  var geocoder = google.maps.Geocoder();

    var markers = [

    <?php

    //orgnize fans by city
    $query = "SELECT city, state, COUNT(*) fans FROM users GROUP BY city ORDER BY fans DESC";
    $result = mysql_query($query) or die(mysql_error());
    while($row = mysql_fetch_array($result)){

    //pulls the city, state code from the database and stores it as a string in $address
    $address = '"' . $row['city'] . ", " . $row['state'] . '"';


       //  "$row['city'].", ".$row['state']";


        geocoder.getLatLng($address, function(point) {
                           $latitude = point.y;
                           $longitude = point.x;
        });
        echo "{ lat: \"".$latitude.", lng: ".$longitude.", name: "'"'.$row['city'].", ".$row['state'].'"'"},\n ";
        echo "<br/>";
    }

    ?>

    ];

    //var locationNameArray = ['Portland','Seattle','Los Angeles','San Diego','Las Vegas'];

    // Create the markers ad infowindows.
    for (index in markers) addMarker(markers[index]);
    function addMarker(data) {
        // Create the marker
        var marker = new google.maps.Marker({
            position: new google.maps.LatLng(data.lat, data.lng),
            map: map,
            title: data.name
        });

Upvotes: 1

Views: 1588

Answers (1)

bitWorking
bitWorking

Reputation: 12655

In your post you are mixing PHP with Javascript. You can't use a Javascript function geocoder.getLatLng inside a PHP tag.

To fill the database with lat, lng information I suggest to use the geocoding with PHP. So you will need one table for cities.

Table city:

id_city
name
state
lat
lng

And the user table have just a foreign key to id_city:

Table users:

id_user
fi_city
...

And here is a quick and dirty example how to get the lat, lng information from Google geocoder with PHP:

$address = urlencode('New York');
$googleApi = 'http://maps.googleapis.com/maps/api/geocode/json?address=%s&sensor=false';

$json = file_get_contents(sprintf($googleApi, $address));

$resultObject = json_decode($json);

$location = $resultObject->results[0]->geometry->location;

$lat = $location->lat;
$lng = $location->lng;

echo 'Lat: '.$lat.', Lng: '.$lng;

There is no error checking but I hope you get the point. With this result you can update your database. I hope you know how to join multiple tables to get the result from users and city tables.

Upvotes: 1

Related Questions