From info-window to db: save user input

Gag

Cross-post of the fourth translation of tutorials on Google Maps from my blog . It describes how the user adds information using the info window.

This third article, in which we will use a bunch of PHP and MySQL in conjunction with the Google Maps API. If you are a PHP developer, then you may be interested in two previous articles in which we loaded markers from the database and created a KML file.

Introduction

Many developers using the Google Maps API to create their own applications give users the ability to add information to the map themselves. The most logical is the following process of adding information by the user. First, he clicks on the map, thereby creating a marker (or draws a line, a polygon); in the place of the click (where the marker has been set) an info-window appears in which there are fields for entering information; the user fills in these fields and presses the “Save” button; after clicking the “Save” button, the info-window closes. This method is used on the service My Maps Google. In this tutorial, we will explain how you can use this method in your maps and how to save information entered by the user in the MySQL database using PHP. To understand the material in this lesson, you need some experience with the HTML / JavaScrip / Google Maps API. Knowledge of PHP and MySQL is desirable.
This lesson is divided into the following parts:

• Creating a database table ;
• Adding information to the database using PHP ;
• Creating a map and user interface .

Creating a database table

When creating a table, first of all, you should pay attention to the lat (latitude) and lng (longitude) card parameters. With the current resolution of Google Maps, we only need 6 digits after the decimal point. To reduce the disk space occupied by the database to a minimum, for lat (latitude) and lng (longitude) values, it is recommended to select the FLOAT data type with parameter 10.6. This type allows you to store up to 6 decimal places and up to 4 before the comma, for example -123.456789 degrees. Your table should have an id field, which will be the primary key for access to the records, as well as a type field in which the institution type will be recorded (restaurant or bar).
If you prefer to create tables in the database using phpMyAdmin, then below is a screenshot of the table creation:

If you do not have access to phpMyAdmin or give preference to pure SQL, the SQL query code is shown below:

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 information to the database using PHP

In this step, you must fill in an empty table called markers . To do this, you need to write a small code in PHP, during which the information placed in the URL will be written as a string in the database. The functions used should work in both PHP4 and PHP5. If you have never written to PHP code, during which the connection to the database occurs, then visit php.net and read about the functions mysql_connect , mysql_select_db , my_sql_query , and mysql_error .
First, we will remove all information about the connection to the database in a separate file. Below is the PHP code in which you must enter your own username, password and database name:
<?php
$username="username";
$password="password";
$database="username-databaseName";
?>
Now let's proceed to the code, when executed, a row with data will be added to the database table. The first part of this code will be responsible for selecting the necessary information from the URL, and the second - for connecting to the database and transmitting the received information to it using the INSERT INTO request. If in the process of processing or sending this data any errors occur, a message will appear on the screen that will help you find out what the problem is. To check the correctness of the script, add the following parameters to the URL ( name= , address= , lat= , lng= , type= ): http://www.yoursite.com/?name=Best%20Bar%20Ever&address=123%20Main % 20St & lat = -37.12345 & lng = 122.12345 & type = bar.
If no error messages are displayed on the screen, the script works correctly. Just do not forget to remove from the database table the rows with which you checked the working capacity of the script.
And here is the code:
<?php
require("phpsqlinfo_dbinfo.php");

// URL
$name = $_GET['name'];
$address = $_GET['address'];
$lat = $_GET['lat'];
$lng = $_GET['lng'];
$type = $_GET['type'];

// MySQL
$connection=mysql_connect ("localhost", $username, $password);
if (!$connection) {
die(' : ' . mysql_error());
}

// ,
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die (' : ' . mysql_error());
}

//
$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(' : ' . mysql_error());
}

?>

Creating a map and user interface

Now that we have the entire server code, let's proceed to the creation of the card itself. If you have never worked with the Google Maps API, then please learn the basics.

Create markers and info-windows
After the map is created and centered, you can add events that will occur when you click on the map. The function that will be executed when clicking will create a marker by click coordinates and will set the dragging property of the marker to " true ". Then, when you click on the marker itself, the info-window will open. This window will contain an HTML form with fields to fill in, a drop-down list and the Save button. Each element of our form will have an id attribute, and when we click on the “Save” button, the saveData function described below will work.

We save data
The saveData function, launched by clicking on the “Save” button, will perform the following actions:

• Save marker coordinate values ​​and values ​​of filled form fields;
• Construct a URL;
• Put the new URL as the first parameter of the GDownloadUrl function;
• Check that the status code returned by the GDowloadUrl function is 200. This means that all data was successfully transferred;
• Check the length of the resulting string with the data. If everything is normal, then a message will appear indicating that the information has been added.

Here is the HTML code:

<! 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=YOUR_KEY"
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 >

What else can you do
Now that the users of your card can add information to it, several interesting possibilities open up before you:

• Let users draw polygons on your map, thereby marking any regions. This will be discussed in other lessons;
• Users can rate the places they like on the map;
• You can display all user information on the map.