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:

  1. An Apache web server running PHP and MySQL. (Other web server software with PHP and MySQL will probably do as well.)
  2. A table in your database with lat, long, and description columns.
  3. 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.

Figure 7-2. A map for Who fans


7.3.1. Structuring Your Database

The database can be recreated with this snippet of SQL:

	CREATE TABLE subfinder (
	 id int(11) NOT NULL auto_increment,
	 lat decimal(10,6) NOT NULL default '0.000000',
	 lon decimal(10,6) NOT NULL default '0.000000',
	 desc varchar(255) NOT NULL default '',
	 url varchar(255) NOT NULL default '',
	 marker char(1) NOT NULL default '',

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:


"; echo "

"; echo "Lat: " . $new_lat . "
"; echo "Lon: " . $new_lon . "
"; echo "Text: " . $new_desc . "
"; echo "URL: " . $new_url . "
"; echo "Marker: " . $new_marker . "
"; echo "Will now be added to the database…"; $new_desc = addslashes($new_desc); $link = mysql_connect($dbserver, $username, $password) or die("Could not connect: " . mysql_error()); mysql_select_db("dbmapserver",$link) or die ("Can't use dbmapserver : " . mysql_error()); $sql = "INSERT INTO subfinder "; $sql = $sql . "VALUES('','" .$new_lat."','".$new_lon."','".$new_desc."','".$new_url."','" .$new_marker ."')"; $result = mysql_query($sql ,$link); if (!$result) { echo "

Due to an error (" . mysql_error() . ")
, your entry could " . "not be loaded into the database. Please return to " . "<a href="">Subfinder</a>."; } else { echo "

Your entry has been loaded into the database. " . "Please return to <a href="">SubFinder</a>."; } echo ""; ?>


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.

7.3.3. See Also

  • Red Geographics Map-Server page: http://www.map-server.com/

Hans van der Maarel

Google Maps Hacks
Google Maps Hacks: Tips & Tools for Geographic Searching and Remixing
ISBN: 0596101619
EAN: 2147483647
Year: N/A
Pages: 131
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net