Thursday, October 3, 2013

Mark Google Map with the addresses from Mysql database with PHP, jQuery, ajax and Google Map API 3.

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,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)