IDIOT DEVELOPER

Loading Locations from MySQL Database and Display Them on Google Maps
Loading Locations from MySQL Database and Display Them on Google Maps
DOWNLOAD

In previous tutorial we have learn how to display user current location on the Google Map. In this tutorial we are going to load the different locations(latitude/latitude) from the MySQL Database, and display all of them on the Google Map.

 

We need to include these two scripts:

 


<script src="https://maps.googleapis.com/maps/api/js?key=API_KEY&callback=initMap" async defer></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

 

Database

First we need to create the table for our data.

 


CREATE TABLE `locations` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `lat` FLOAT(10,6) NOT NULL,
    `lon` FLOAT(10,6) NOT NULL,
    `description` TEXT NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

 

Now we need to insert some data into the table.

 


INSERT INTO `locations` (`id`, `name`, `lat`, `lon`, `description`) VALUES (1, 'Archaeological Museum', 45.823704, 15.990757, 'The Archaeological Museum in Zagreb, Croatia is an archaeological museum with over 450,000 varied artifacts and monuments, gathered from various sources.');
 
INSERT INTO `locations` (`id`, `name`, `lat`, `lon`, `description`) VALUES (2, 'Modern Gallery', 45.809280, 15.977570, 'Modern Gallery is a museum in Zagreb, Croatia that holds the most important and comprehensive collection of paintings, sculptures and drawings by 19th and 20th century Croatian artists.');
 
INSERT INTO `locations` (`id`, `name`, `lat`, `lon`, `description`) VALUES (3, 'Technical Museum', 45.803555, 15.965023, 'The museum was founded in 1954 and it maintains the oldest preserved steam engine in the area, dating from the mid-19th century, which is still operational.');
 
INSERT INTO `locations` (`id`, `name`, `lat`, `lon`, `description`) VALUES (4, 'St. Mark\'s Church', 45.816677, 15.973806, 'The Romanesque window found in its south facade is the best evidence that the church must have been built as early as the 13th century as is also the semicircular groundplan of St. Mary\'s chapel');

 

Connecting to the database

We are going to use the PDO (PHP Data Object) to connect to the database. The get_db() helps us in connecting with the database. If it encounter some error it will print a message on the screen.

 


define('HOST', 'localhost');
define('USERNAME', 'root');
define('PASSWORD', '');
define('DBNAME', 'tut');

function get_db(){
  try{
    $db = new PDO( 'mysql:host=' . HOST  . '; dbname=' . DBNAME . ';charset=utf8', USERNAME, PASSWORD );
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $db;
  }catch(PDOExecption $e){
    echo 'Connection Failed: ' . $e->getMessage();
    return false;
  }
}

 

Loading Data from the Database Table

After connecting to the database, the next task it get the data from the database. The get_map_data() function helps us in getting the data from the database, and returns the data in the JSON format.

 


function get_map_data(){
  try{
    $db=get_db();
    $stmt = $db->query("SELECT name, lat, lon, description FROM locations");
    $data = $stmt->fetchAll();
    return json_encode($data);
  }catch(PDOExecption $e){
    return 'Error: ' . $e->getMessage();
  }
}

 

We load this data using an AJAX call from the map_data.php file which contains this function.

 


<?php
include('config.php');
echo get_map_data();
?>

 


function fetch_data(){
  var response = '';
    $.ajax({
      type: "GET",
      url: "map_data.php",
      async: false,
      success : function(text){
        response = text;
      }
    });
   return response;
 }

 

The JSON data will contains the following information:

1. Name

2. Latitude

3. Longitude

4. Description

 

Creating the Map

Now we will create a new Map.

 


function initMap(){
    map = new google.maps.Map(document.getElementById("map"), {
      center: new google.maps.LatLng(45.812897, 15.97706),
      zoom: 13,
      mapTypeId: google.maps.MapTypeId.ROADMAP
    });
  }

 

Adding the Markers and infoWindows

To create markers from JSON data we need to loop through the data and extract the information.

 

Ok, so now we’ve created a map and added some markers from JSON data. First of all We’re going to create a global InfoWindow that we will reuse for all markers. The next step is to attach a click event to each marker. In the code that executes we will fill the InfoWindow with the correct information and open it att the right location, pointing at the clicked marker.

 


  function map_function(){
    infoWindow = new google.maps.InfoWindow();
    //Adding Markers
    for(var i=0;  i<map_data.length; i++){
      var data = map_data[i];
      latLng = new google.maps.LatLng(data.lat, data.lon);

      var marker = new google.maps.Marker({
        position: latLng,
        map: map,
        title: data.name
      });

      (function (marker, data) {
        google.maps.event.addListener(marker, "click", function (e) {
          infoWindow.setContent("<div class = 'marker-desc'>" + data.description + "</div>");
          infoWindow.open(map, marker);
        });
      })(marker, data);

    }

  }

 

Leave a Reply

Your email address will not be published. Required fields are marked *