Hack 63. Read and Write Markers from a MySQL Database

Keep track of almost anything with Google Maps and a relational database.

Adding a map of something new to Google Maps is good fun, but there is a lot of data already in SQL databases that is just begging to be mapped. This tutorial describes the way the Subfinder application, at http://www.map-server.com/googlemaps/subfinder.php, is integrated with a MySQL database using PHP. The Subfinder is itself an extension of the Who Locations site at http://www.map-server.com/googlemaps/wholocations2.php, which is described in a tutorial at http://www.map-server.com/googlemaps/tutorial.html. Figure 7-2 shows a map of the sites that any fan of The Who must know about.

There are easier ways to get points on a Google Map, but to integrate with a MySQL database you will need:

An Apache web server running PHP and MySQL. (Other web server software with PHP and MySQL will probably do as well.)

A table in your database with lat, long, and description columns.

Some basic HTML and PHP knowledge.

We are going to use PHP to dynamically create an HTML document with the appropriate Google Maps JavaScript code. One of the functions shown in this sample is the option for users to add their own locations, with additional attributes, into the database. The value of one of the attribute determines the type of marker placed.

If you save this into the file create_subfinder.sql, you can create a new database and this table with the following commands:

$ mysqladmin create sub_db
$ mysql sub_db < create_subfinder.sql

As you can see, there are fields for the latitude and longitude values, a description, URL (which will of course be displayed as a link), and a marker field. The value of the marker field will determine the type of marker placed. This application, which lists submarines visible on the Google Maps photos, makes a distinction between submarines that are in active duty (A), museum ships (M), places where submarines have sunk (S), and places where important events have taken place (E). The appropriate letter is displayed in the marker.

7.3.2. The Code

This system uses two PHP files to do its work. The main file, subfinder.php, is used to read the data from the database and display it on the map. The second file, subfinder_load_db.php, is called by the first to write data into the database. A standard HTML form is used for this.

The subfinder.php file imports the Google Maps API, and sets up the map in the usual way. The following bit of JavaScript creates the custom markers:

subfinder.php: setting up the custom markers

The block that contains all the HTML is generated when the user clicks somewhere on the map to add a location, which sets up a form to allow the user to add more information. This form is stored in output.innerHTML. The point.x and point.y variables are being supplied by the listener.

The PHP file that gets called by the form in subfinder.php is a lot simpler. The code, stored in subfinder_load_db.php, is just your basic "take the parameters and stuff them in the database" code:

Once again, the conf.php with the access variables is read. One important thing to note here is the addslashes() function. As we mentioned earlier, JavaScript can be quite fussy about quotes and special characters. If there are any of those characters in the texts you want to display in the info windows, it may result in either the map not being drawn at all, or just your markers not being drawn. The addslashes() function properly escapes those characters before putting the text into the database so that your users don't have to worry about that themselves.