AJAX with PHP and MySQL
From Google Mapki
Contents |
[edit] Introduction
Static XML is all fair and well, but when you have a database of locations, it just will not do. A good solution is to have a back-end script such as PHP generate the XML file as required. The example here doesn't really need AJAX, as it only gets the points once, but it serves as a framework upon which to build. Often parameters are passed to the back-end script (ie read.php) to refine which points are returned.
A common example of this would be passing the bounds of the viewing area to read.php, eg something like:
- read.php?top=1.234&bottom=2.3456&left=3.4567&right=4.5678
Thus telling it where the viewport is and read.php returning an XML document containing only points in that area.
In this case, you would probably want to make a mapmoved event to re-call getMarkers() when the map moved, thus changing where was being looked at.
[edit] The Front End
The Javascript front end. Very basic.
Just paste it into a text file and save as index.html
Be sure to replace the API key with your own.
<!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" xmlns:v="urn:schemas-microsoft-com:vml"> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"/> <title>Google Maps API Example - overlay</title> <style type="text/css"> v\:* { behavior:url(#default#VML); } </style> <script src="http://maps.google.com/maps?file=api&v=2&key=-wqrVbaSwRQZJD0wTteSofwUqt00c9G0VVfRSg" type="text/javascript"></script> <script type="text/javascript"> //<![CDATA[ function onLoad() { map = new GMap(document.getElementById("div_map")); map.addControl(new GSmallMapControl()); map.addControl(new GMapTypeControl()); map.setCenter(new GLatLng(54, -3), 5); getMarkers(); GEvent.addListener(map, "click", function(overlay, point) { if (overlay){ // marker clicked overlay.openInfoWindowHtml(overlay.infowindow); // open InfoWindow } else if (point) { // background clicked } }); } function getMarkers(){ var urlstr="read.php"; var request = GXmlHttp.create(); request.open('GET', urlstr , true); // request XML from PHP with AJAX call request.onreadystatechange = function () { if (request.readyState == 4) { var xmlDoc = request.responseXML; locations = xmlDoc.documentElement.getElementsByTagName("location"); markers = []; if (locations.length){ for (var i = 0; i < locations.length; i++) { // cycle thru locations markers[i] = new GMarker(new GLatLng(locations[i].getAttribute("lat"),locations[i].getAttribute("lng"))); // Add attributes to the marker so we can poll them later. // When clicked, an overlay will have these properties. markers[i].infowindow = "This is "+locations[i].getAttribute("name"); // Useful things to store on a marker (Not needed for this example, could be removed) // Tells you what index in the markers[] array an overlay is markers[i].markerindex = i; // Store the location_id of the location the marker represents. // Very useful to know the true id of a marker, you could then make // AJAX calls to the database to update the information if you had it's location_id markers[i].db_id = locations[i].getAttribute("location_id"); map.addOverlay(markers[i]); } } } } request.send(null); } //]]> </script> </head> <body onload="onLoad()"> <div id="div_map" style="width: 500px; height: 300px"></div> </body> </html>
[edit] The Back End
Make a text file called read.php and put this in it.
Change the variables at the start to match the details of your SQL server
<?php
$host = 'localhost';
$user = 'root';
$pass = ;
$dbname = 'test';
if (!$db = mysql_connect($host, $user, $pass)) {
echo 'Could not connect to mysql';
exit;
}
if (!mysql_select_db($dbname, $db)) {
echo 'Could not select database';
exit;
}
// Date in the past
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
// always modified
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
// HTTP/1.1
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
// HTTP/1.0
header("Pragma: no-cache");
//XML Header
header("content-type:text/xml");
$query = "SELECT location_id, name, lat, lng FROM locations WHERE 1";
$query = mysql_query($query);
echo "<locations>";
while ($row=mysql_fetch_assoc($query)){
echo '<location id="'.$row['location_id'].'" name="'.$row['name'].'" lat="'.$row['lat'].'" lng="'.$row['lng'].'"/>';
}
echo "</locations>";
?>
[edit] The Database
Use this to create the database
CREATE TABLE `locations` ( `location_id` int(10) NOT NULL auto_increment, `name` text NOT NULL, `lat` double NOT NULL default '0', `lng` double NOT NULL default '0', PRIMARY KEY (`location_id`) ) AUTO_INCREMENT=3 ; INSERT INTO `locations` VALUES (1, 'London', 51.49945, -0.11332); INSERT INTO `locations` VALUES (2, 'Manchester', 53.481508, -2.241211);
