Includes enterprise licensing and support
This is the third article in our series on using PHP/MySQL with Google Geo APIs. If you're a PHP/MySQL developer, you may also be interested in our articles on loading markers from a database and creating KML from a database.
Many developers use the Google Maps API to create mashups that enable users to add and annotate geographically located information. The most intuitive user interface for accomplishing that goal is to have the user first create a marker or poly, pop up a window with a form, then let the user fill out the form and press a 'save' button to close the info window. This is the method used, for instance, by Google's My Maps service. This tutorial will explain the concepts behind the HTML and JavaScript that make up the info-window editing user interface, as well as the PHP code that can be used to add the saved info window data into a MySQL database. Developers following this tutorial should have some HTML/JavaScript/Google Maps API experience, as well as PHP/MySQL knowledge.
This article is broken up into the following steps:
When you create the MySQL table, you want to pay particular attention to the lat and lng attributes. With the current zoom capabilities of Google Maps, you should only need 6 digits of precision after the decimal. To keep the storage space required for our table at a minimum, you can specify that the lat and lng attributes are floats of size (10,6). That will let the fields store 6 digits after the decimal, plus up to 4 digits before the decimal, e.g. -123.456789 degrees. Your table should also have an id attribute to serve as the primary key, and a type attribute to distinguish between restaurants and bars, as we'll let users select one of those options from a drop-down select in the form.
If you prefer interacting with your database through the phpMyAdmin interface, here's a screenshot of the table creation.
If you don't have access to phpMyAdmin or prefer using SQL commands instead, here's the SQL statement that creates the table. phpsqlinfo_createtable.sql:
CREATE TABLE `markers` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 60 ) NOT NULL , `address` VARCHAR( 80 ) NOT NULL , `lat` FLOAT( 10, 6 ) NOT NULL , `lng` FLOAT( 10, 6 ) NOT NULL , `type` VARCHAR( 30 ) NOT NULL ) ENGINE = MYISAM ;
At this point, you should have an empty table named markers. You now need to write some PHP statements that can add a row to the table with data passed into the URL. The functions used here should work in both PHP 4 and PHP 5. If you've never written PHP to connect to a MySQL database, you should visit php.net and read up on mysql_connect, mysql_select_db, my_sql_query, and mysql_error.
First, you should put your database connection information in a separate file. This is generally a good idea whenever you're using PHP to access a database, as it keeps your confidential information in a file that you won't be tempted to share. In the Maps API forum, we've occasionally had people accidentally publish their database connection information when they were just trying to debug their XML-outputting code. The file should look like this, but with your own database information filled in (phpsqlinfo_dbinfo.php):
<? $username="username"; $password="password"; $database="username-databaseName"; ?>
Now, you can write the code that will do the fun stuff — inserting the row. In the PHP, first retrieve the user data passed in through the URL, then connect to your database and execute an "INSERT INTO" query on your table, passing in the user data.
If there's an error along the way, output a message to the screen to help you debug what's going on. To test that this PHP script works correctly, visit the URL in the browser and append the necessary parameters, e.g.:
If no error message is output to the screen, then the PHP worked. Just make sure to delete later the rows you added while debugging.
The PHP file that does all that is shown below (phpsqlinfo_addrow.php):
|
<?php
require("phpsqlinfo_dbinfo.php");
// Gets data from URL parameters
$name = $_GET['name'];
$address = $_GET['address'];
$lat = $_GET['lat'];
$lng = $_GET['lng'];
$type = $_GET['type'];
// Opens a connection to a MySQL server
$connection=mysql_connect ("localhost", $username, $password);
if (!$connection) {
die('Not connected : ' . mysql_error());
}
// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die ('Can\'t use db : ' . mysql_error());
}
// Insert new row with user data
$query = sprintf("INSERT INTO markers " .
" (id, name, address, lat, lng, type ) " .
" VALUES (NULL, '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($name),
mysql_real_escape_string($address),
mysql_real_escape_string($lat),
mysql_real_escape_string($lng),
mysql_real_escape_string($type));
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
?>
Now that the backend functionality is setup, it's time to move onto creating the map and user interface functionality. If you have never created a Google Map, please try some of the basic examples in the documentation to make sure you understand the basics of creating a Google Map.
After creating the map and centering it, you can assign a click listener to the map. In the callback function to the listener, create a marker at the coordinate that was clicked and set the draggable property to "true" in the GMarkerOptions object you send in to the constructor. Then, assign a click listener to the marker that pops open an info window over the marker. The info window has the HTML for a form with several text fields, a drop down, and a save button. Each form input has an ID, and the button has an onclick listener to call the saveData function, described below.
In the saveData function that's called by the info window button, you do the following:
GDownloadUrl, an API-provided function which wraps the XMLHTTPRequest object that lets you retrieve files (commonly in XML format) via JavaScript. The GDownloadUrl callback function will provide you with the content of the URL and the status code.
The HTML file that does this is shown below (phpsqlinfo_add.html):
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title>Google Maps JavaScript API Example: Simple Map</title>
<script src="http://maps.google.com/maps?file=api&v=2.x&key=ABQIAAAAjU0EJWnWPMv7oQ-jjS7dYxTPZYElJSBeBUeMSX5xXgq6lLjHthSAk20WnZ_iuuzhMt60X_ukms-AUg"
type="text/javascript"></script>
<script type="text/javascript">
var marker;
function initialize() {
if (GBrowserIsCompatible()) {
var map = new GMap2(document.getElementById("map_canvas"));
map.setCenter(new GLatLng(37.4419, -122.1419), 13);
GEvent.addListener(map, "click", function(overlay, latlng) {
if (latlng) {
marker = new GMarker(latlng, {draggable:true});
GEvent.addListener(marker, "click", function() {
var html = "<table>" +
"<tr><td>Name:</td> <td><input type='text' id='name'/> </td> </tr>" +
"<tr><td>Address:</td> <td><input type='text' id='address'/></td> </tr>" +
"<tr><td>Type:</td> <td><select id='type'>" +
"<option value='bar' SELECTED>bar</option>" +
"<option value='restaurant'>restaurant</option>" +
"</select> </td></tr>" +
"<tr><td></td><td><input type='button' value='Save & Close' onclick='saveData()'/></td></tr>";
marker.openInfoWindow(html);
});
map.addOverlay(marker);
}
});
}
}
function saveData() {
var name = escape(document.getElementById("name").value);
var address = escape(document.getElementById("address").value);
var type = document.getElementById("type").value;
var latlng = marker.getLatLng();
var lat = latlng.lat();
var lng = latlng.lng();
var url = "phpsqlinfo_addrow.php?name=" + name + "&address=" + address +
"&type=" + type + "&lat=" + lat + "&lng=" + lng;
GDownloadUrl(url, function(data, responseCode) {
if (responseCode == 200 && data.length <= 1) {
marker.closeInfoWindow();
document.getElementById("message").innerHTML = "Location added.";
}
});
}
</script>
</head>
<body onload="initialize()" onunload="GUnload()">
<div id="map_canvas" style="width: 500px; height: 300px"></div>
<div id="message"></div>
</body>
</html>
The map should look like this, when the info window is opened:
saveData function to retrieve the points of the poly with the getNumVertices and getVertex function. You'll probably want to create a separate table in your database just for storing the poly points data.