Scenario: Mark Google Map with the addresses from Mysql database with PHP, jQuery, and ajax.
Files: map.html, gsearch.php
Database: test
Table: office
map.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" xmlns:v="urn:schemas-microsoft-com:vml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title>Children Service Field Workers</title>
<script type="text/javascript" src="//maps.google.com/maps/api/js?sensor=false"></script>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.9.1.js"></script>
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>
<script type="text/javascript">
var infowindow, map;
function initialize() {
var mapOptions = {
zoom: 8,
center: new google.maps.LatLng(43.8561002, -79.33701880000001),
mapTypeId: google.maps.MapTypeId.ROADMAP
};
var map = new google.maps.Map(document.getElementById('map-canvas'),
mapOptions);
markLocations(map);
}
function addMarkers(latLng,map) {
var marker = new google.maps.Marker({
position: latLng,
map: map
});
google.maps.event.addListener(marker, 'click', function() {
infowindow.open(map, this);
});
}
function markLocations(map) {
var geocoder = new google.maps.Geocoder();
//url = 'gsearch.php?q=' + $('#search').val();
url = 'gsearch.php';
$.getJSON(url, function(data) {
jQuery.each(data, function(key,value) {
geocoder.geocode( { 'address': value}, function(results, status) {
if (status == google.maps.GeocoderStatus.OK) {
latlng=results[0].geometry.location;
addMarkers(latlng,map);
} else {
alert('Geocode was not successful for the following reason: ' + status);
}
});
});
});
};
google.maps.event.addDomListener(window, 'load', initialize);
</script>
</head>
<body style="font-family: Arial; border: 0 none;">
<div id="map-canvas" style="width: 500px; height: 400px"></div>
</body>
</html>
gsearch.php
<?php
include_once ('database_connection.php');
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
//$query = "select office_name,postcode from office where office_name like '%$q%' or postcode like '%$q%'";
$query = "select address,city,province,country from office";
$result = mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$response = array();
$addressitems = array("address","city","province","country");
if($result){
while($row = mysql_fetch_array($result,MYSQLI_ASSOC)){
$address=$row['address'];
$city=$row['city'];
$province=$row['province'];
$country=$row['country'];
$combine="";
foreach ($addressitems as $item) {
$temp = ${$item};
if ($temp!="") {
if ($combine==""){
$combine = ${$item};
} else {
$combine .= ", $temp";
}
}
}
#echo $combine;
//array_push($response,array('label'=>$row['office_name'],'value'=>$row['office_name']));
array_push($response,$combine);
}
echo(json_encode($response));
}else {
echo 'No Results for :"'.$_POST['q'].'"';
}
?>
MySQL SQL Script
office.sql
CREATE TABLE IF NOT EXISTS `office` (
`office_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Event ID',
`office_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Event Name',
`country` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Province',
`province` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Province',
`city` varchar(30) COLLATE utf8_unicode_ci NOT NULL COMMENT 'City',
`address` varchar(88) COLLATE utf8_unicode_ci NOT NULL COMMENT 'address',
`postcode` char(10) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Postcode',
`gpslatitude` char(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'GPS Latitude',
`gpslongitude` char(15) COLLATE utf8_unicode_ci NOT NULL COMMENT 'GPS Longitude',
`catagory` varchar(16) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Catagory ID',
`start_date` date DEFAULT NULL COMMENT 'Starting Date',
`close_date` date DEFAULT NULL COMMENT 'Ending Date',
`Schedule` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Schedule',
`admission` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Admission',
`Contact` char(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Contact',
`Phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Phone',
`Fax` char(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Fax',
`Email` varchar(50) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Email',
`Website` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'website',
`image1` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Image1',
`Image2` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Image2',
`Description` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'Description',
`tags` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'tags',
`Searchable` tinyint NOT NULL DEFAULT '1' COMMENT 'Searchable',
`Submitter` varchar(80) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Submitter',
PRIMARY KEY (`office_id`),
KEY `Event_Name` (`office_name`),
FULLTEXT KEY `Description` (`Description`)
);
Data file
To load the data file into the table, the file must be have 755
permission, and its parent folder must have 755 permission too. So I
would suggest to put it in /tmp if you are using Unix/Linux.
Mysql>
load data infile '/tmp/office.txt' into table office fields terminated by ',';
office.txt
, Downtown Toronto, Canada, Ontario, Toronto, 5 John st., M5V 5C6,,,Head
Quarter,,,6am-6pm,N/A,Phy Cris,
416-333-8888,,aaa@bloger.ca,www.bloger.ca,,,Internet Marketing,IM,1,
, Downtown New York, USA, NY, New York, 8 Wall st., 10888,,,NA
HQ,,,6am-6pm,N/A,Sam US,
646-333-8888,,bbb@bloger.ca,www.bloger.ca,,,Internet Marketing,IM,1,
