Category Archives: JQuery

[Demo] Use a PHP & MySQL database to load markers on a Google Map

Setting up a Google Maps to use PHP and a MySQL back-end isn’t the most straight forward thing to do, especially in comparison to using Google Map’s JavaScript API V3.  I wanted to load a map from a MySQL database with lat, longs, the name of the marker, and a description to put in an information window (it’s the little text bubble that pops up when you click on a marker).  I didn’t want to learn how to use the Maps API through PHP, so what did I do?

  1. Loaded the name, description, latitude, and longitude into a MySQL table
  2. Had a PHP query output a hidden input with the name, description, latitude and longitude in a single string with each field and marker separated by some sort of delimiter that you split the string with.
  3. When the page loads, jQuery will take that hidden input and split the string by the delimiter
  4. Use jQuery and Google Maps JavaScript API V3 to loop through the arrays of markers and plop them to the map and also add the info windows.

Here’s a link to the finished version demo, it should work in Chrome, Firefox and IE: http://www.macrostash.com/wp-content/plugins/big-ten-demo.php

There’s a lot going on behind the scenes, so this is a pretty long demo. Features this demo shows:

  1. Multiple Info-windows
  2. Multiple Markers
  3. Auto-sizing the map
  4. jQuery
  5. MySQL
  6. PHP
  7. Javascript, especially string splitting and arrays

The MySQL Database

I’m using the locations of the 12 colleges/universities in the Big Ten Conference. The XLS of the data that I used is available: Big Ten Schools Demo Data

Basically, what I’ve got there is the latitude, longitude, name of the location, address of the location, and a random fact. In this case the random fact is which division the school is in. Legends or Leaders. Here’s the first school, so that you get an idea of what the data looks like.

Id Lat Long Name Returned Address Division
1 40.01264 -83.0308 Ohio State University – Columbus Campus The Ohio State University, 1739 N High St, Columbus, OH 43210, USA Leaders

The PHP

//Connect to the MySQL database that is holding your data, replace the x's with your data
mysql_connect("localhost", "xxxxx_xxx", "xxxx") or
 die("Could not connect: " . mysql_error());
mysql_select_db("xxxxx_xxxx");
 
//Initialize your first couple variables
$encodedString = ""; //This is the string that will hold all your location data
$x = 0; //This is a trigger to keep the string tidy
 
//Now we do a simple query to the database
$result = mysql_query("SELECT * FROM `big-ten`");
 
//Multiple rows are returned
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
    //This is to keep an empty first or last line from forming, when the string is split
    if ( $x == 0 )
    {
         $separator = "";
    }
    else
    {
         //Each row in the database is separated in the string by four *'s
         $separator = "****";
    }
    //Saving to the String, each variable is separated by three &'s
    $encodedString = $encodedString.$separator.
    "<p class='content'><b>Lat:</b> ".$row[1].
    "<br><b>Long:</b> ".$row[2].
    "<br><b>Name: </b>".$row[3].
    "<br><b>Address: </b>".$row[4].
    "<br><b>Division: </b>".$row[5].
    "</p>&&&".$row[1]."&&&".$row[2];
    $x = $x + 1;
}

The HTML (Body)

We have 2 parts to the body.

The input part has the encoded string, and it’s hidden.

The map_canvas is where the Google map will be loaded into.

<body>
    <div id='input'>
        <input type="hidden" id="encodedString" name="encodedString" value="<?php echo $encodedString; ?>" />
    </div>
    <div id="map_canvas"></div>
</body>

 

The JavaScript

//This javascript will load when the page loads.
jQuery(document).ready( function($){
 
        //Initialize the Google Maps
        var geocoder;
        var map;
        var markersArray = [];
        var infos = [];
 
        geocoder = new google.maps.Geocoder();
        var myOptions = {
              zoom: 9,
              mapTypeId: google.maps.MapTypeId.ROADMAP
            }
        //Load the Map into the map_canvas div
        var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
        map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
 
        //Initialize a variable that the auto-size the map to whatever you are plotting
        var bounds = new google.maps.LatLngBounds();
        //Initialize the encoded string
        var encodedString;
        //Initialize the array that will hold the contents of the split string
        var stringArray = [];
        //Get the value of the encoded string from the hidden input
        encodedString = document.getElementById("encodedString").value;
        //Split the encoded string into an array the separates each location
        stringArray = encodedString.split("****");
 
        var x;
        for (x = 0; x < stringArray.length; x = x + 1)
        {
            var addressDetails = [];
            var marker;
            //Separate each field
            addressDetails = stringArray[x].split("&&&");
            //Load the lat, long data
            var lat = new google.maps.LatLng(addressDetails[1], addressDetails[2]);
            //Create a new marker and info window
            marker = new google.maps.Marker({
                map: map,
                position: lat,
                //Content is what will show up in the info window
                content: addressDetails[0]
            });
            //Pushing the markers into an array so that it's easier to manage them
            markersArray.push(marker);
            google.maps.event.addListener( marker, 'click', function () {
                closeInfos();
                var info = new google.maps.InfoWindow({content: this.content});
                //On click the map will load the info window
                info.open(map,this);
                infos[0]=info;
            });
           //Extends the boundaries of the map to include this new location
           bounds.extend(lat);
        }
        //Takes all the lat, longs in the bounds variable and autosizes the map
        map.fitBounds(bounds);
 
        //Manages the info windows
        function closeInfos(){
       if(infos.length > 0){
          infos[0].set("marker",null);
          infos[0].close();
          infos.length = 0;
       }
        }
 
});

 

All Together With the HTML

*The comments are getting highlighted a little bit weird (I set it to HTML syntax), but the code should copy paste properly into a new file.

<html>
    <head>
    <script type='text/javascript' src='jquery-1.6.2.min.js'></script>
    <script type='text/javascript' src='jquery-ui-1.8.14.custom.min.js'></script>
    <style>
 
        BODY {font-family : Verdana,Arial,Helvetica,sans-serif; color: #000000; font-size : 13px ; }
 
        #map_canvas { width:100%; height: 100%; z-index: 0; }
    </style>
    <script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false" /></script>
    <script type='text/javascript'>
 
    //This javascript will load when the page loads.
    jQuery(document).ready( function($){
 
            //Initialize the Google Maps
            var geocoder;
            var map;
            var markersArray = [];
            var infos = [];
 
            geocoder = new google.maps.Geocoder();
            var myOptions = {
                  zoom: 9,
                  mapTypeId: google.maps.MapTypeId.ROADMAP
                }
            //Load the Map into the map_canvas div
            var map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
            map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
 
            //Initialize a variable that the auto-size the map to whatever you are plotting
            var bounds = new google.maps.LatLngBounds();
            //Initialize the encoded string       
            var encodedString;
            //Initialize the array that will hold the contents of the split string
            var stringArray = [];
            //Get the value of the encoded string from the hidden input
            encodedString = document.getElementById("encodedString").value;
            //Split the encoded string into an array the separates each location
            stringArray = encodedString.split("****");
 
            var x;
            for (x = 0; x < stringArray.length; x = x + 1)
            {
                var addressDetails = [];
                var marker;
                //Separate each field
                addressDetails = stringArray[x].split("&&&");
                //Load the lat, long data
                var lat = new google.maps.LatLng(addressDetails[1], addressDetails[2]);
                //Create a new marker and info window
                marker = new google.maps.Marker({
                    map: map,
                    position: lat,
                    //Content is what will show up in the info window
                    content: addressDetails[0]
                });
                //Pushing the markers into an array so that it's easier to manage them
                markersArray.push(marker);
                google.maps.event.addListener( marker, 'click', function () {
                    closeInfos();
                    var info = new google.maps.InfoWindow({content: this.content});
                    //On click the map will load the info window
                    info.open(map,this);
                    infos[0]=info;
                });
               //Extends the boundaries of the map to include this new location
               bounds.extend(lat);
            }
            //Takes all the lat, longs in the bounds variable and autosizes the map
            map.fitBounds(bounds);
 
            //Manages the info windows
            function closeInfos(){
           if(infos.length > 0){
              infos[0].set("marker",null);
              infos[0].close();
              infos.length = 0;
           }
            }
 
    });
    </script>
 
    </head>
    <body>
    <div id='input'>
 
        <?php
 
        //Connect to the MySQL database that is holding your data, replace the x's with your data
        mysql_connect("localhost", "xxxxx_xxx", "xxxx") or
         die("Could not connect: " . mysql_error());
        mysql_select_db("xxxxx_xxxx");
 
        //Initialize your first couple variables
        $encodedString = ""; //This is the string that will hold all your location data
        $x = 0; //This is a trigger to keep the string tidy
 
        //Now we do a simple query to the database
        $result = mysql_query("SELECT * FROM `big-ten`");
 
        //Multiple rows are returned
        while ($row = mysql_fetch_array($result, MYSQL_NUM))
        {
            //This is to keep an empty first or last line from forming, when the string is split
            if ( $x == 0 )
            {
                 $separator = "";
            }
            else
            {
                 //Each row in the database is separated in the string by four *'s
                 $separator = "****";
            }
            //Saving to the String, each variable is separated by three &'s
            $encodedString = $encodedString.$separator.
            "<p class='content'><b>Lat:</b> ".$row[1].
            "<br><b>Long:</b> ".$row[2].
            "<br><b>Name: </b>".$row[3].
            "<br><b>Address: </b>".$row[4].
            "<br><b>Division: </b>".$row[5].
            "</p>&&&".$row[1]."&&&".$row[2];
            $x = $x + 1;
        }        
        ?>
 
        <input type="hidden" id="encodedString" name="encodedString" value="<?php echo $encodedString; ?>" />
    </div>
    <div id="map_canvas"></div>
    </body>
</html>