From Info Windows to a Database: Saving User-Added Form Data

From Info Windows to a Database: Saving User-Added Form Data
Pamela Fox, Google Maps API Team
September 2007

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.
Objective

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:

    * Creating the table
    * Adding row data with PHP
    * Creating the map & UI


Creating the table

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 ;


Adding row data with PHP

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.:
http://yourdomain.com/phpsqlinfo_addrow.php?name=Best%20Bar%20Ever&address=123%20Main%20St&lat=-37.12345&lng=122.12345&type=bar

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());
}

?>

Creating the map & UI

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.
Creating the Marker + Info Window

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.
Saving the Data

In the saveData function that's called by the info window button, you do the following:

    * Save the value of the marker coordinates and all the form elements (escaping when needed, for URL-friendliness).
    * Construct a URL by concatenating the name of the PHP file with the parameters and their values.
    * Pass the URL as the first parameter of 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.
    * Check that the returned status code is 200. This means that the file was retrieved successfully and we can continue processing.
    * Check the length of the data string returned — an empty data file signifies no error strings were outputted. If the length is near zero, you can close the info window and output a success message.

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:
Screenshot of map
Where to go from here
Now that you have a basic running example of adding user-annotated data from a Google Map into your database, there are many more fun features you can add. Some ideas:

    * Let users create polygons, and annotate them with info: Check out the E-Z digitizer tutorial for the polygon creation, add a poly click listener with our info window, and modify the 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.
    * Let users 'rate' each location: Modify a star rating system, add it to your info window, and save the rating to the table.
    * Output all of the user-added data on one map: Check out our PHP/MySQL -> Google Maps tutorial. It uses the exact same table structure, so you'll be able to skip right down to the "Creating the Map" section.

If you have any issues or questions about this tutorial, please post them in the Maps API forum. Happy mashing!