I remember the first time I wanted to pull addresses from a MySQL database and dynamically map them using Google Maps API. I went through the Google Maps API Documentation, followed several online tutorials and used code other developers provided online but I couldn’t achieve what I wanted. After several days of hair pulling and heavy coffee drinking, I put together what I had learned from all that and voila! I got it to work.

Prerequisites

Yes, I also hate the Prerequisites section of tutorials. I understand you. ::sigh:: Here they are:

1. Go to http://code.google.com/apis/maps and sign up for a Google API key. In order to do this, you must have a Google Account to get a Maps API key.
2. Setup a MySQL database with a latitude column and a longitude column, both columns can be data type text.
3. Geocode the addresses you want to map and place them in the latitude and longitude columns. To geocode addresses you can visit a Website such as geocoder.us where you can find the latitude and longitude of any US address for free.

You are going to work with one MySQL table and two files

• locations, the table that will hold the locations’ information, including the latitude and longitude.
• locations.php, the file that will fetch the data from the MySQL database and format it as XML data.
• maps.php, the file that will get the XML data from locations.php and will map the addresses using Google Maps API.

The locations table

I created a table with addresses of several shopping centers at Austin TX. My table looks like this:

WATCHOUT!
The Google Maps API will use the latitude and longitude columns to map the addresses, not the address, city, state nor zipcode columns.

locations.php

In the locations.php file do not use HTML tags like TITLE, HEAD or BODY or any other tag, since this file will work as an XML file. Copy and paste the code and edit the MySQL Database Connection section to suit your server settings.

//////////////////////////////////////////
//// MySQL Database Connection ///////////
//////////////////////////////////////////
$host = "yourhost";
$user = "yourusername";
$db_name= "yourdatabasename";
$pass= "yourpassword";

$conn = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db_name, $conn) or die(mysql_error());

function parseToXML($htmlStr) {
    $xmlStr=str_replace('<','<',$htmlStr);
    $xmlStr=str_replace('>','>',$xmlStr);
    $xmlStr=str_replace('"','"',$xmlStr);
    $xmlStr=str_replace("'",''',$xmlStr);
    $xmlStr=str_replace("&",'&',$xmlStr);
    return $xmlStr;
}

$query = "SELECT * FROM locations";
$result = mysql_query($query);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");

echo '';
while ($row = @mysql_fetch_assoc($result)) {
    if(!empty($row['lat']) or !empty($row['lng'])) {
        echo '';
    }
}
echo '';

maps.php

Now let’s work on map.php On this file you can use HTML tags. Place the following code between your HEAD tags. Enter your key where it reads YOUR-KEY-GOES-HERE.

On your BODY tag, load the map, like this:

Somewhere between the BODY tags add the following:

The previous div will be the map’s container.

For more information and customization please visit the official documentation at the following link: Using PHP/MySQL with Google Maps.

Hope I saved you the hours of hair pulling and heavy coffee drinking.

Tags: Google Maps API, MySQL, PHP

This entry was posted on Thursday, January 14th, 2010 at 9:36 am and is filed under Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.