Creating Maps with MySQL, PHP and Google Maps API v3

It has been awhile since I started this and never really followed up. I created this blog to be a space where I can share data, news and concepts I am working with.

I have been feverishly working on a project that will be hosted here that just doesn’t seem to want to end. In the meantime, I decided I needed to catch up with a few projects that I have actually completed.

Over the past week, Google has been sending out reminders that their support for Google Maps API v2 was ending. They were going to provide a wrapper to try and support the old API. With the six-man football season in the middle of the playoffs, I was trying to put this off for at least a few more weeks. Unfortunately that all changed when my maps went blank a few days ago.

As usual, I hit the place where I tried to find a blog or post where combining MySQL, PHP and GM v3 all went together. I also just starred at the screen for what seemed to be an eternity.

I am here to tell you, Google Maps v3 is great. Converting the data points to XML, then plotting them is really quite simple.

The example I am going to post might seem a bit long, but it is a modified version of the explanation on the google developer site, retold in terms humans without too, too much experience can decipher.

First off, you need to create the XML file.

The example I am using is taken from my own website, sixmanfootball.com. This file is used to populate two maps, including the one on the index page and the weekly maps page. It is WAY more complex than many uses, but I thought it would be good to show the details.

Let’s call this file, makeXML.php

include('your_db_connection_file.inc');
// I like to use weird names like .inc files for hidden files
// this is something I was taught by someone somewhere
//who was wiser than me

header("Content-type: text/xml");

//function to help parse everything to XML
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;
}

//your actual db connection
$dblink = mysql_connect('localhost', $dbuser, $dbpass);
$dblinked = mysql_select_db($dbname, $dblink);

$key=13;

// Start XML file, echo parent node
echo '';

//my SQL command to get the game info
$q2 = "SELECT team_a, team_b, game_location, game_date, game_time FROM games WHERE game_time>0 AND game_week=$key ORDER BY game_location, game_time DESC";
$r2 = mysql_query($q2);

while($a2 = mysql_fetch_array($r2))
{
$team_a = $a2[team_a];
$team_b = $a2[team_b];
$game_site = $a2[game_location];
$game_date = $a2[game_date];
$game_time = $a2[game_time];
$game_date=date('D',$game_date);
$game_time=$game_time = date('g:i A', $game_time);

$site_search = mysql_real_escape_string($game_site);
If ($site_search==$last_site)
{$previous=$game_name."
";} else
{$previous="";}

//sub SQL command to get the longitude and latitude
$q3 = "SELECT * FROM teams WHERE team_name='$site_search' AND team_lat<>0";
$r3 = mysql_query($q3);
while($a3 = mysql_fetch_array($r3))
{
$game_lat = $a3[team_lat];
$game_long = $a3[team_long];
$game_name = $previous.$team_a." vs ".$team_b." in ".$game_site." (".$game_date.", ".$game_time.")";

echo '<marker ';
echo 'name="' . parseToXML($game_name) . '" ';
echo 'lat="' . $game_lat . '" ';
echo 'lng="' . $game_long . '" ';
echo 'type="stadium"';
echo '/>';
}
//this is used to see if there is more than one game at a particular site
//that way the second node for that site is a combo node including both games
$last_site=$game_site;
}
// End XML file
echo '';

OK, now that we have XML, you can check it by calling the page via a browser and make sure it is all working.

The next step is creating the page where you make the actual map. Honestly, I was scared. I hate trying to figure out what I really did 2-3 years ago. I will share some generic code that ties in with the weekly_maps page.

In a later post, I will share how to pass a variable to the XML page for a specific query that is not predetermined.

This is all javascript, but I wrap it within a PHP template page.

So the end result looks something like this.
scrnshot
Here’s a photo when someone clicks a map bubble.

scrnshot2