Google Maps API

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

Abdulla Kamar, Google Maps API Team
June 2009

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 v3 experience, as well as PHP/MySQL knowledge.

This article is broken up into the following steps:


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 public forums, we've occasionally seen 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 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 addListener(), create a marker at the coordinate that was clicked. 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.

    google.maps.event.addListener(map, "click", function(event) {
        marker = new google.maps.Marker({
          position: event.latLng,
          map: map
        });
        google.maps.event.addListener(marker, "click", function() {
          infowindow.open(map, marker);
        });
    });

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 values contained within 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 downloadUrl(), a simple function which wraps the XMLHTTPRequest object that lets you retrieve files (commonly in XML format) via JavaScript. The downloadUrl() callback function will provide you with the content of the URL and the status code. If you use a framework like jQuery or YUI, you may want to replace this function with their respective wrapper functions.
  • 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 indicates that the request generated no error strings. If the length is zero, you can close the info window and output a success message.

The HTML file containing this code is shown below (phpsqlinfo_add.html):

<!DOCTYPE html >
  <head>
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
    <title>Google Maps JavaScript API v3 Example: Map Simple</title>
    <script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>
    <script type="text/javascript">
    var marker;
    var infowindow;

    function initialize() {
      var latlng = new google.maps.LatLng(37.4419, -122.1419);
      var options = {
        zoom: 13,
        center: latlng,
        mapTypeId: google.maps.MapTypeId.ROADMAP
      }
      var map = new google.maps.Map(document.getElementById("map-canvas"), options);
      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>";
    infowindow = new google.maps.InfoWindow({
     content: html
    });

    google.maps.event.addListener(map, "click", function(event) {
        marker = new google.maps.Marker({
          position: event.latLng,
          map: map
        });
        google.maps.event.addListener(marker, "click", function() {
          infowindow.open(map, 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.getPosition();

      var url = "phpsqlinfo_addrow.php?name=" + name + "&address=" + address +
                "&type=" + type + "&lat=" + latlng.lat() + "&lng=" + latlng.lng();
      downloadUrl(url, function(data, responseCode) {
        if (responseCode == 200 && data.length <= 1) {
          infowindow.close();
          document.getElementById("message").innerHTML = "Location added.";
        }
      });
    }

    function downloadUrl(url, callback) {
      var request = window.ActiveXObject ?
          new ActiveXObject('Microsoft.XMLHTTP') :
          new XMLHttpRequest;

      request.onreadystatechange = function() {
        if (request.readyState == 4) {
          request.onreadystatechange = doNothing;
          callback(request.responseText, request.status);
        }
      };

      request.open('GET', url, true);
      request.send(null);
    }

    function doNothing() {}
    </script>
  </head>

  <body style="margin:0px; padding:0px;" onload="initialize()">
    <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 '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.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.