How to geocode addresses from MySQL and add them like markers to the Google map?

Question

I'm using Google Maps API and Google Geocoding for geocoding addresses. I have table named 'users' and column named 'location' in MySQL database. Column 'location' contains addresses. I need to connect to the database, then geocode those addresses and add them like markers to the map.

First I connected to the database with PHP:

  <?php

    // connection to mysql

    $username="someusername";
    $password="*****";
    $database="GISwork";
    $hostname = "somehostname.com";
    $table = "users";


    $conn = mysql_connect($hostname,$username,$password) or die("Connecting to MySQL failed");

    mysql_select_db($database, $conn)
    or die("Selecting MySQL database failed");

    if (!mysql_select_db($database))
    die("Can't select database");

    // sending query
    $result = mysql_query("SELECT location FROM {$table}");
    if (!$result) {
        die("Query to show fields from table failed");
    }
    ?>

Then I defined geocoding and markers:

var userLocation = //it should be that $result from database 

        var geocoder = new google.maps.Geocoder();
            geocoder.geocode( { 'address': userLocation}, function(results, status){
               if (status == google.maps.GeocoderStatus.OK) {
                  map.setCenter(results[0].geometry.location);
                  var marker = new google.maps.Marker({map: map, position: results[0].geometry.location });
               } else {
                  alert("Geocode was not successful for the following reason: " + status);
               }
            });


    }
    google.maps.event.addDomListener(window, 'load', initialize);

My question is: a) How to pass php variable $result to the javascript variable userLocation to be readable for Google Geocoding? b) Is that function for geocoding and adding markers enough for all markers or should it be different regards to I need to import all addresses from the database (multiple markers)? c) How to add info windows to all markers?

Thank you.

Answer 1

The Official Google Maps API v3 has a good tutorial on this.

Note: Some tutorials may suggest actually writing your map page as a PHP file and outputting JavaScript for each marker you want to create, but that technique can be problematic. By using an XML file as an intermediary between your database and your Google Map, it makes for a faster initial page load, a more flexible map application, and easier debugging. You can independently verify the XML output from the database and the JavaScript parsing of the XML. And at any point, you could even decide to eliminate your database entirely and just run the map based on static XML files.

https://developers.google.com/maps/articles/phpsqlajax_v3

Careful of Geocoding on the Server Side - The 2,500 request limit is per IP address https://developers.google.com/maps/articles/geocodestrat#server