Using PHP MySQL with Google Maps Alternative using the Leaflet library

Leaflet Google Maps Alternative

The Leaflet Database Structure

Download the Complete Leaflet PHP MySQL Map Scripts

Leaflet PHP MySQL Map

Leaflet Google Maps Alternative

Leaflet is an open-source JavaScript library for creating interactive maps. It is designed with support for flexible OOP capabilities in JavaScript.

It is a good solution to provide an alternative to Google Maps, with support to use any other Web map tile service. This is good, since Google started to charge for the use of Google Maps above a certain level of usage.

Create a Basic Map

To start our interactive map service, we need to setup a basic map. Lets start by downloading the leaflet library. Download the Leaflet 0.7.3 version, as I will use that one in this article.

Now upload these files after unzipping them to your web server. Put leaflet.css in css folder, and leaflet.js in js folder. Also put the images in images folder in the js folder, leaflet will look for them there.

Now we create an index.php file in root folder. Put this code in it:

<!DOCTYPE html>
<html>
 <head>
  <title>Leaflet basic example</title>
  <link rel="stylesheet" href="css/leaflet.css" />
  <script src="js/leaflet.js"></script>
 </head>
 <body>
  <div id="map" style="width: 600px; height: 400px"></div>
  <script>
   var map = L.map('map').setView([51.505, -0.09], 13);
   L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
   maxZoom: 18,
   attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
   '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
   'Imagery � <a href="http://mapbox.com">Mapbox</a>',
   id: 'examples.map-i875mjb7'
   }).addTo(map);
  </script>
 </body>
</html>

First we should include the CSS file in the pages this way:

<link rel="stylesheet" href="css/leaflet.css" />

Then we include Leaflet JS library this way:

<script src="js/leaflet.js"></script>

We create a DIV with ID "map" to put in it the map:

<div id="map" style="width: 600px; height: 400px"></div>

Finally we include JavaScript code at the bottom of our HTML page which will create the map.

var map = L.map('map').setView([51.505, -0.09], 13);

Create a map instance, and assign it to the 'map' DIV, define it's coordinates to London (51.505, -0.09) and zoom of 13.

L.tileLayer('https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
 maxZoom: 18,
 attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
 '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
 'Imagery � <a href="http://mapbox.com">Mapbox</a>',
 id: 'examples.map-i875mjb7'
 }).addTo(map);

We define a tile for this map. We are using Mapbox tiles. You can use OpenStreetMap also. We define the maximum zoom as 18. And add it to our map with .addTo(map).

The Leaflet Database Structure

In this tutorial I am creating an informative map. We will need some markers to put on the map, a street and an area. Lets start with markers. We will use them to mark some companies. Every company has a name, a description, geographic location and a telephone.

You will need to create a database. Then we create a table and name it companies. It will have six columns.

CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company` varchar(255) NOT NULL,
  `details` text NOT NULL,
  `latitude` varchar(255) NOT NULL,
  `longitude` varchar(255) NOT NULL,
  `telephone` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

It needs the ID field to be of type INT, auto incremental and be the primary key. Then comes the company name in company field with VARCHAR type and 255 characters.

The description can be any details about the company, so we use a TEXT field to hold a lot of text. Latitude and longitude are the geographic coordinates of the location on the map. We will get that from the map, so do not worry. Finally the telephone, or you can make it email, is another VARCHAR field to hold that data.

For streets we need three columns:

CREATE TABLE `streets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `geolocations` text NOT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

Again the ID field of INT type, auto incremental and being the primary key. Every street have a name, so we make a field with VARCHAR type and 255 characters.

Finally every street is a line, it has a start and an end, but it is not necessarily a straight line, as we may have some points where the street changes its direction. So we have at least two geographic points on the map, and we have some other points where the street changes its way. I will make a TEXT field to contain all this data.

Now the last element is an area. If you want to draw an area on the map to show a park for example, or a restricted area, you can not do it with just one marker, so we need an area for that. The table will be named AREAS and it has 3 columns as well:

CREATE TABLE `areas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `geolocations` text NOT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

Its structure is the same as streets, but we did not put them together in the same table, as areas are a little bit different than streets. In the future you may want to connect streets with companies, as every company is on a street address. And there may be other reasons why you do not want to do that.

One note, an area does not have a start and end point, but it is at least three points, with two points it is a line. When we talk about areas we need three points at least.

Administration Interface

To administrate the map elements we need 9 admin pages:

Add a new company
Edit companies
Delete a company
Add a new street
Edit streets
Delete a street
Add a new area
Edit areas
Delete an area

I am separating every admin operation in a page so you do not get confused with the complexity. Naturally I would make a dashboard and a very complex administration system, but for this article I am doing it in a simple way.

Add a new company

Create a file named as addcompany.php in root, add this code in it:

<!DOCTYPE html>
<html>
 <head>
  <title>Add a company</title>
  <script src="js/jquery.min.js"></script>
  <link rel="stylesheet" href="css/leaflet.css" />
  <script src="js/leaflet.js"></script>
 </head>
 <body>
  <div id="map" style="width: 600px; height: 400px"></div>
  <form action="addcompanydb.php" method="post">
   <h1>Add a new company</h1>
   <table cellpadding="5" cellspacing="0" border="0">
    <tbody>
     <tr align="left" valign="top">
      <td align="left" valign="top">Company name</td>
      <td align="left" valign="top"><input type="text" name="company" /></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Description</td>
      <td align="left" valign="top"><textarea name="details"></textarea></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Latitude</td>
      <td align="left" valign="top"><input id="lat" type="text" name="latitude" /></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Longitude</td>
      <td align="left" valign="top"><input id="lng" type="text" name="longitude" /></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Telephone</td>
      <td align="left" valign="top"><input type="text" name="telephone" /></td>
    </tr>
    <tr align="left" valign="top">
     <td align="left" valign="top"></td>
     <td align="left" valign="top"><input type="submit" value="Save"></td>
    </tr>
   </tbody>
  </table>
 </form>
 <script>
  var map = L.map('map').setView([51.505, -0.09], 13);

  L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
   maxZoom: 18,
   attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
    'Imagery � <a href="http://mapbox.com">Mapbox</a>',
   id: 'examples.map-i875mjb7'
  }).addTo(map);
   
  function putDraggable() {
   /* create a draggable marker in the center of the map */
   draggableMarker = L.marker([ map.getCenter().lat, map.getCenter().lng], {draggable:true, zIndexOffset:900}).addTo(map);
   
   /* collect Lat,Lng values */
   draggableMarker.on('dragend', function(e) {
    $("#lat").val(this.getLatLng().lat);
    $("#lng").val(this.getLatLng().lng);
   });
  }
   
  $( document ).ready(function() {
   putDraggable();
  });
 </script>
 </body>
</html>

For cleaner JS code I will use Jquery. Includes Jquery library.

<script src="js/jquery.min.js"></script>

This is our map container.

<div id="map" style="width: 600px; height: 400px"></div>

We create a form to send our data via POST method to PHP.

<form action="addcompanydb.php" method="post">
<input id="lng" type="text" name="longitude" />
and
<input id="lng" type="text" name="longitude" />

Notice the ID values, we will need them later in JavaScript.

function putDraggable() {
 /* create a draggable marker in the center of the map */
 draggableMarker = L.marker([ map.getCenter().lat, map.getCenter().lng], {draggable:true, zIndexOffset:900}).addTo(map);
 
 /* collect Lat,Lng values */
 draggableMarker.on('dragend', function(e) {
  $("#lat").val(this.getLatLng().lat);
  $("#lng").val(this.getLatLng().lng);
 });
}

This function will create a draggable marker and add it to the map.

draggableMarker = L.marker([ map.getCenter().lat, map.getCenter().lng], {draggable:true, zIndexOffset:900}).addTo(map);

We instantiate a marker object given a geographical point and some options. map.getCenter() returns the geographical center of the map view. For the syntax details check the Leaflet documentation.

draggableMarker.on('dragend', function(e) {
 $("#lat").val(this.getLatLng().lat);
 $("#lng").val(this.getLatLng().lng);
});

We create an event listener on "dragend", collect the latitude and longitude values, assign them back in our HTML.

$( document ).ready(function() {
 putDraggable();
});

To make sure the map and the document is fully loaded we use Jquery's $( document ).ready. and then we call our function putDraggable();

You can grab and drop the marker, it will collect the point in the form. Add a company name, a description and a telephone number.

After submitting our form we are redirected to addcompanydb.php, create a file with that name on root:

<?php

require_once("db.php");

$company = strip_tags($_POST['company']);
$details = strip_tags($_POST['details']);
$latitude = strip_tags($_POST['latitude']);
$longitude = strip_tags($_POST['longitude']);
$telephone = strip_tags($_POST['telephone']);

$conn->addCompany($company, $details, $latitude, $longitude, $telephone);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Company added</title>
 </head>
 <body>
  <h1>Company has been added</h1>
 </body>
</html>

Now lets explain this code. We are including db.php, there we have a DB class for all PHP operations with the database.

require_once("db.php");

Now we collect all the data we got from the form with some filtering.

$company = strip_tags($_POST['company']);
$details = strip_tags($_POST['details']);
$latitude = strip_tags($_POST['latitude']);
$longitude = strip_tags($_POST['longitude']);
$telephone = strip_tags($_POST['telephone']);

We do not need any tags in our data.

$conn->addCompany($company, $details, $latitude, $longitude, $telephone);

Do not panic, we are calling a method addCompany from our class connectToDB and passing the variables we have collected. The rest is a regular HTML.

To complete the operation we need to create db.php which holds the DB manipulation class, we will add our first method addCompany in it. But first we will create config.php which will hold the DB config variables.

Create config.php with this code:

<?php
return [
  "db" => [
      "server" => "localhost",
      "dbname" => "leaflet",
      "user" => "root",
      "pass" => "",
   ]
];

Let me explain this line by line:

return [
  "db" => [
      "server" => "localhost",
      "dbname" => "leaflet",
      "user" => "root",
      "pass" => "",
   ]
];

I am defining DB constants. Four constants we are using with every connection to MySQL server. "server" is you MySQL server, in my case it is localhost. "dbname" is the DB name of your MySQL Db, "user" is the mysql username and "pass" is mysql password.

Create db.php with this code. Let me explain this line by line:

class connectToDB {

We are creating a class named as "connectToDB", here we will put all the methods related to the DB.

  private $conn;

Here we are defining a property which will be used to hold the instance of the mysqli class.

  public function __construct()
  {
    $config = include 'config.php';
    $this->conn = new mysqli( $config['db']['server'], $config['db']['user'], $config['db']['pass'], $config['db']['dbname']);
  }

This is the constructor method, this will get the config values from "config.php" and initialize new connection to the MySQL DB.


  function __destruct()
  {
    $this->conn->close();
  }

This is the destructor method thise will close the mysql connection after all the operations are performed.

public function addCompany( $company, $details, $latitude, $longitude, $telephone)

Make an instance to mysqli using MySQL Improved Extension which allows us to access the functionality provided by MySQL 4.1 and above. We pass our DB constants. Now we have a connection established with the DB.

$statement = $this->conn->prepare("Insert INTO companies( company, details, latitude, longitude, telephone) VALUES(?, ?, ?, ?, ?)");

We create a prepared statement to insert our data to the companies table. The question marks are placeholders where we it will bind our variables. Prepared statements secures us from SQL injections. Never use classic statements if you want to write secure Web applications.

$statement->bind_param('sssss', $company, $details, $latitude, $longitude, $telephone);

We bind our variables with bind_param method, they are all strings so for five placeholders we specify five strings as 's', and then pass our variables.

$statement->execute();

Finally we execute our statement. Without this method the query would not be executed.

$statement->close();

Do not forget to close your statement. Sometimes I forget to do that. It does not cause noticeable problem, but it is always wiser to write clean code.

With this we finished adding the company.

Edit companies

Create a file named as editcompany.php in root, add this code in it:

<?php
 require_once("db.php");
 $arr = $conn->getCompaniesList();
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Edit a company</title>
  <script src="js/jquery.min.js"></script>
  <link rel="stylesheet" href="css/leaflet.css" />
  <script src="js/leaflet.js"></script>
 </head>
 <body>
  <div id="map" style="width: 600px; height: 400px"></div>
  <form action="updatecompany.php" method="POST">
   <h1>Edit a company</h1>
   <table cellpadding="5" cellspacing="0" border="0">
    <tbody>
     <tr align="left" valign="top">
      <td align="left" valign="top">Company name</td>
      <td align="left" valign="top"><select id="company" name="company"><option value="0">Please choose a company</option><?php for($i=0; $i < count($arr); $i++) { print '<option value="'.$arr[$i]['id'].'">'.$arr[$i]['company'].'</option>'; } ?></select></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Description</td>
      <td align="left" valign="top"><textarea id="details" name="details"></textarea></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Latitude</td>
      <td align="left" valign="top"><input id="latitude" type="text" name="latitude" /></td>
     </tr>
      <tr align="left" valign="top">
      <td align="left" valign="top">Longitude</td>
     <td align="left" valign="top"><input id="longitude" type="text" name="longitude" /></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Telephone</td>
      <td align="left" valign="top"><input id="telephone" type="text" name="telephone" /></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top"></td>
      <td align="left" valign="top"><input type="submit" value="Update"></td>
     </tr>
    </tbody>
   </table>
  </form>
  <script>
   var map = L.map('map').setView([51.505, -0.09], 13);

   L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
    maxZoom: 18,
    attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA</a>, ' +
    'Imagery � <a href="http://mapbox.com">Mapbox</a>',
    id: 'examples.map-i875mjb7'
   }).addTo(map);
  
   function putDraggable() {
    /* create a draggable marker in the center of the map */
    draggableMarker = L.marker([ map.getCenter().lat, map.getCenter().lng], {draggable:true, zIndexOffset:900}).addTo(map);
    
    /* collect Lat,Lng values */
    draggableMarker.on('dragend', function(e) {
     $("#lat").val(this.getLatLng().lat);
     $("#lng").val(this.getLatLng().lng);
    });
   }
   
   $( document ).ready(function() {
    putDraggable();
    
    $("#company").change(function() {
     for(var i=0;i<arr.length;i++) {
      if(arr[i]['id'] == $('#company').val()) {
       $('#details').val(arr[i]['details']);
       $('#latitude').val(arr[i]['latitude']);
       $('#longitude').val(arr[i]['longitude']);
       $('#telephone').val(arr[i]['telephone']);
       
       map.panTo([arr[i]['latitude'], arr[i]['longitude']]);
       draggableMarker.setLatLng([arr[i]['latitude'], arr[i]['longitude']]);
       break;
      }
     }
    });
    
   });
   
   var arr = JSON.parse( '<?php echo json_encode($arr) ?>' );
  </script>
 </body>
</html>

Now add this method in db.php in connectToDB class:

public function getCompaniesList() {
 $arr = array();
 $statement = $this->conn->prepare("Select id, company, details, latitude, longitude, telephone from companies order by company ASC");
 $statement->bind_result( $id, $company, $details, $latitude, $longitude, $telephone);
 $statement->execute();
 while ($statement->fetch()) {
  $arr[] = [ "id" => $id, "company" => $company, "details" => $details, "latitude" => $latitude, "longitude" => $longitude, "telephone" => $telephone];
 }
 $statement->close();
 return $arr;
}

So let me explain it now for you. When you edit a company you need to get it from the DB first, so we will need to create a method which will get us all the companies from the DB. This is where our method getCompaniesList gets handy. But to use this company we need to include the db.php file in our editcompany.php file, and then call that method.

<?php
 require_once("db.php");
 $arr = $conn->getCompaniesList();
?>

First we include the db.php file, and now we call getCompaniesList() method which returns an array to our $arr array. We will use the array here:

<tr align="left" valign="top">
 <td align="left" valign="top">Company name</td>
 <td align="left" valign="top"><select id="company" name="company><option value="0">Please choose a company</option><?php for( $i=0; $i < count($arr); $i++ ) { print '<option value="'.$arr[$i]['id'].'">'.$ arr[$i]['company'].'</option>'; } ?></select></td>
</tr>

As you can see now we do not write the company name, it is selected by the user from a drop-down list. The data from our DB are printed as options to select from. Another place we will need our array in PHP is in the end of the file, look for:

var arr = JSON.parse( '<?php echo json_encode($arr) ?>' );

We are converting our PHP array to JavaScript array. JSON is a great format to send data from one language to another. We encode our array in JSON, and decode it in JavaScript. This JavaScript array will be used when we choose an option from the drop-down list to fill out all the fields in our HTML form.

To fill the form we use this:

$("#company").change(function() {
    for(var i=0; i < arr.length; i++) {
     if(arr[i]['id'] == $('#company').val()) {
      $('#details').val(arr[i]['details']);
      $('#latitude').val(arr[i]['latitude']);
      $('#longitude').val(arr[i]['longitude']);
      $('#telephone').val(arr[i]['telephone']);
   
   map.panTo( [arr[i]['latitude'], arr[i]['longitude']]);
   draggableMarker.setLatLng( [arr[i]['latitude'], arr[i]['longitude']]);
   break;
  }
 }
});

I have created an event listener with jQuery on our drop-down list, so when it is changed we invoke a for loop to go through our JavaScript array and look for the selected company from the list. When it finds the company, it fills out all fields with company data like this:

$('#details').val(arr[i]['details']);
$('#latitude').val(arr[i]['latitude']);
$('#longitude').val(arr[i]['longitude']);
$('#telephone').val(arr[i]['telephone']);

We also need to change the marker position and map location, to see where this company is located:

map.panTo([arr[i]['latitude'], arr[i]['longitude']]);
draggableMarker.setLatLng( [arr[i]['latitude'], arr[i]['longitude']]);

With panTo(), we tell the map to change its center, and we set the latitude and longitude of the marker with setLatLng().

Nothing complicated with the rest of our HTML file, so lets get back to our PHP method getCompaniesList() :

public function getCompaniesList() {
 $arr = array();
 $statement = $this->conn->prepare("Select id, company, details, latitude, longitude, telephone from companies order by company ASC");
 $statement->bind_result( $id, $company, $details, $latitude, $longitude, $telephone);
 $statement->execute();
 while ($statement->fetch()) {
  $arr[] = [ "id" => $id, "company" => $company, "details" => $details, "latitude" => $latitude, "longitude" => $longitude, "telephone" => $telephone];
 }
 $statement->close();
 return $arr;
}

We connect to the DB and query for a list of all companies:

$statement = $this->conn->prepare("SELECT id, company, details, latitude, longitude, telephone from companies order by company ASC");

Now we bind the results to PHP variables:

$statement->bind_result( $id, $company, $details, $latitude, $longitude, $telephone);

Take care to match variables locations with columns locations in the query.

Next we execute our statement and fetch the results. Of course we may have more than one company, so we need a while loop to loop through the results until there is no more fields:

$statement->execute();
while ($statement->fetch()) {
 $arr[] = [ "id" => $id, "company" => $company, "details" => $details, "latitude" => $latitude, "longitude" => $longitude, "telephone" => $telephone];
}

The results are assigned to an array which we finally return it.

return $arr;

Now we will make a file named updatecompany.php to update our DB with the new changes in a company:

<?php

 require_once("db.php");

 $id = intval($_POST['company']);
 $details = strip_tags($_POST['details']);
 $latitude = strip_tags($_POST['latitude']);
 $longitude = strip_tags($_POST['longitude']);
 $telephone = strip_tags($_POST['telephone']);

 $conn->updateCompany( $id, $details, $latitude, $longitude, $telephone);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Company updated</title>
 </head>
 <body>
  <h1>Company has been updated</h1>
 </body>
</html>

There is no need to explain this file as it is very simple. Just take a look at updateCompany method. We need to create that. Again in our connectToDB class we add this new method:

public function updateCompany( $id, $details, $latitude, $longitude, $telephone) {
 $statement = $this->conn->prepare("Update companies SET details = ?,latitude = ?,longitude = ?,telephone = ? where id = ?");
 $statement->bind_param('ssssi', $details, $latitude, $longitude, $telephone, $id);
 $statement->execute();
 $statement->close();
}

This function will make a query and update our company we selected in the drop-down list.

Delete a company

We can now add a new company and edit that company. Now we will make it possible to delete a company from the DB. To do that create a file named deletecompany.php on root, and add this code in it:

<?php
 require_once("db.php");
 $arr = $conn->getCompaniesList();
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Delete a company</title>
  <script src="js/jquery.min.js"></script>
  <link rel="stylesheet" href="css/leaflet.css" />
  <script src="js/leaflet.js"></script>
 </head>
 <body>
  <div id="map" style="width: 600px; height: 400px"></div>
  <form action="deletecompanydb.php" method="POST">
   <h1>Delete a company</h1>
   <table cellpadding="5" cellspacing="0" border="0">
    <tbody>
     <tr align="left" valign="top">
      <td align="left" valign="top">Company name</td>
      <td align="left" valign="top"><select id="company" name="company"><option value="0">Please choose a company</option><?php for( $i=0; $i < count($arr); $i++) { print '<option value="'.$arr[$i]['id'].'">'.$arr[$i]['company'].'</option>'; } ?></select></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top"></td>
      <td align="left" valign="top"><input type="submit" value="Delete"></td>
     </tr>
    </tbody>
   </table>
  </form>
  <script>
   var map = L.map('map').setView([51.505, -0.09], 13);

   L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
    maxZoom: 18,
    attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
    'Imagery � <a href="http://mapbox.com"> Mapbox </a>',
    id: 'examples.map-i875mjb7'
   }).addTo(map);
   
   function putDraggable() {
    /* create a draggable marker in the center of the map */
    draggableMarker = L.marker([ map.getCenter().lat, map.getCenter().lng], {draggable: true, zIndexOffset: 900}).addTo(map);
    
    /* collect Lat,Lng values */
    draggableMarker.on('dragend', function(e) {
     $("#lat").val(this.getLatLng().lat);
     $("#lng").val(this.getLatLng().lng);
    });
   }
   
   $( document ).ready(function() {
    putDraggable();
    
    $("#company").change(function() {
     for(var i=0; i <arr.length; i++) {
      if(arr[i]['id'] == $('#company').val()) {
       map.panTo([arr[i]['latitude'], arr[i]['longitude']]);
       draggableMarker.setLatLng([arr[i]['latitude'], arr[i]['longitude']]);
       break;
      }
     }
    });
  
   });
   
   var arr = JSON.parse( '<?php echo json_encode($arr) ?>' );
  </script>
 </body>
</html>

As you can see it is nearly identical to editcompany.php. We include db.php, get the list of companies in an array, list it in a drop-down list. The difference is we do not need any other fields, just the name of the company to be choosen. Now we kept the map to show the user where the company he wants to delete is located. Also in JavaScript take a look at:

$("#company").change(function() {
 for(var i=0; i < arr.length; i++) {
  if(arr[i]['id'] == $('#company').val()) {
   map.panTo([arr[i]['latitude'], arr[i]['longitude']]);
   draggableMarker.setLatLng( [arr[i]['latitude'], arr[i]['longitude']]);
   break;
  }
 }
});

We are not assigning data from the array to any HTML field, as we do not have them here any way.

Now we need to create deletecompanydb.php to send a request when someone clicks Delete button. Put this code in it:

<?php
 require_once("db.php");
 $id = intval($_POST['company']);
 $conn->deleteCompany($id);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Company deleted</title>
 </head>
 <body>
  <h1>Company has been deleted</h1>
 </body>
</html>

Very simple. We are getting the selected company's ID, and calling a method we will make named deleteCompany, it's code is:

public function deleteCompany($id) {
 $statement = $this->conn->prepare("Delete from companies where id = ?");
 $statement->bind_param('i', $id);
 $statement->execute();
 $statement->close();
}

Put this method in our connectToDB class.

Add a new street

We have finished company's three administration pages. Now we will make the same thing for streets. We need to make a page to add a street, edit streets and delete a street. Lets begin with creating addstreet.php file. Add this code in it:

<!DOCTYPE html>
<html>
 <head>
  <title>Add a street</title>
  <script src="js/jquery.min.js"></script>
  <link rel="stylesheet" href="css/leaflet.css" />
  <script src="js/leaflet.js"></script>
 </head>
 <body>
  <div id="map" style="width: 600px; height: 400px"></div><br />
  <input type="button" onclick="drawStreet();" value="Draw a street" /> <input type="button" onclick="resetStreet();" value="Clear map" /><br />
  <p>To add a street point click on the map. To remove a street point click on it again.</p>
  <form action="addstreetdb.php" method="post">
   <h1>Add a new street</h1>
   <table cellpadding="5" cellspacing="0" border="0">
    <tbody>
     <tr align="left" valign="top">
      <td align="left" valign="top">Street name</td>
      <td align="left" valign="top"><input type="text" name="street" /></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Geographic locations</td>
      <td align="left" valign="top">
       <textarea id="geo" name="geo"></textarea>
       <br /><input type="button" onclick="getGeoPoints();" value="Collect points" />
      </td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top"></td>
      <td align="left" valign="top"><input type="submit" value="Save"></td>
     </tr>
    </tbody>
   </table>
  </form>
  <script>
   var map = L.map( 'map' ).setView( [51.505, -0.09], 13 );
   var polyLine;
   var draggableStreetMarkers = new Array();

   L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
    maxZoom: 18,
    attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
    'Imagery � <a href="http://mapbox.com"> Mapbox </a>',
    id: 'examples.map-i875mjb7'
   }).addTo(map);
   
   function resetStreet() {
    if(polyLine != null) {
     map.removeLayer( polyLine );
    }
    for(i=0; i< draggableStreetMarkers.length; i++) {
     map.removeLayer( draggableStreetMarkers[i] );
    }
    draggableStreetMarkers = new Array();
   }
   
   function addMarkerStreetPoint( latLng ) {
    var streetMarker = L.marker( [latLng.lat, latLng.lng], { draggable: true, zIndexOffset: 900}).addTo(map);

    streetMarker.arrayId = draggableStreetMarkers.length;

    streetMarker.on('click', function() {
     map.removeLayer( draggableStreetMarkers[ this.arrayId ]);
     draggableStreetMarkers[ this.arrayId ] = "";
    });

    draggableStreetMarkers.push( streetMarker );
   }
   
   function drawStreet() {
    if(polyLine != null) {
     map.removeLayer( polyLine );
    }

    var latLngStreets = new Array();

    for(i=0; i < draggableStreetMarkers.length; i++) {
     if(draggableStreetMarkers[i]!="") {
      latLngStreets.push( L.latLng( draggableStreetMarkers[ i ].getLatLng().lat, draggableStreetMarkers[ i ].getLatLng().lng));
     }
    }

    if(latLngStreets.length > 1) {
     // create a red polyline from an array of LatLng points
     polyLine = L.polyline( latLngStreets, {color: 'red'} ).addTo(map);
    }

    if(polyLine != null) {
     // zoom the map to the polyline
     map.fitBounds( polyLine.getBounds() );
    }
   }
   
   function getGeoPoints() {
    var points = new Array();
    for(var i=0; i < draggableStreetMarkers.length; i++) {
     if(draggableStreetMarkers[i] != "") {
      points[i] =  draggableStreetMarkers[ i ].getLatLng().lng + "," + draggableStreetMarkers[ i ].getLatLng().lat;
     }
    }
    $('#geo').val(points.join(','));
   }
   
   $( document ).ready(function() {
    map.on('click', function(e) {
     addMarkerStreetPoint( e.latlng );
    });
   });
  </script>
 </body>
</html>

In this file we have a lot of new functions, but before getting into them we need to talk about what we need here.

When we create a street, we need some way to mark points on the map. We will need a way to draw the street line through the points we marked. We will need to clear the points we drew. We need to be able to delete one point a time if we want and finally we need to be able to collect the geographic locations of all these points.

Lets analyze the code. First new line is:

<input type="button" onclick="drawStreet();" value="Draw a street" /> <input type="button" onclick="resetStreet();" value="Clear map" /><br />

I have created two buttons under the map, one is to draw a street and the other is to clear the map. Another button is in the form under the textarea for collecting points from the map to the textarea.

<input type="button" onclick="getGeoPoints();" value="Collect points" />

You will notice that every button has an onclick event with a function. Let me go through these functions. But before that let me explain two global variables I will use in these functions, "polyLine" and "draggableStreetMarkers".

"polyLine" will hold the poly-line we draw on the map. We need to have access to it from several functions.

Next "draggableStreetMarkers" is an array which will hold all our points geographical locations. Every location has two values, latitude and longitude, and a line has several points, so we need an array to hold all these data values, and we need to have access to it from several functions.

function resetStreet() {
 if(polyLine != null) {
  map.removeLayer( polyLine );
 }
 for(i=0; i < draggableStreetMarkers.length; i++) {
  map.removeLayer( draggableStreetMarkers[i] );
 }
 draggableStreetMarkers = new Array();
}

The first function I'll talk about is the one we call in "Clear map" button. Obviously we use it to clear the map from all the points and lines we put on it. It is named as resetStreet(), and we check if there is a polyLine available and remove it from the map.

Then we loop through the draggableStreetMarkers array and check every element which is a marker in this situation. We also remove it from the map and reset draggableStreetMarkers by assigning to it an empty new array.

You will notice a method in leaflet called removeLayer. We use it twice here. All elements we put on the map are layers, as like you take a bunch of transparent papers and start lining them one on another, the map works in the same way.

function drawStreet() {
 if(polyLine != null) {
  map.removeLayer( polyLine );
 }

 var latLngStreets = new Array();

 for(i=0; i < draggableStreetMarkers.length; i++) {
  if(draggableStreetMarkers[i] !="") {
   latLngStreets.push( L.latLng( draggableStreetMarkers[ i ].getLatLng().lat, draggableStreetMarkers[ i ].getLatLng().lng));
  }
 }

 if(latLngStreets.length > 1) {
  // create a red polyline from an array of LatLng points
  polyLine = L.polyline( latLngStreets, {color: 'red'} ).addTo(map);
 }

 if(polyLine != null) {
  // zoom the map to the polyline
  map.fitBounds( polyLine.getBounds() );
 }
}

In this function we draw a line through the points of a street we want to create.

if(polyLine != null) {
 map.removeLayer( polyLine );
}

Remove any poly-line we drew before, so we do not have overlapping on the map.

var latLngStreets = new Array();

Define an array for holding latitude and longitude of every point on the map.

for(i=0; i< draggableStreetMarkers.length; i++) {
 if(draggableStreetMarkers[i] != "") {
  latLngStreets.push( L.latLng( draggableStreetMarkers[ i ].getLatLng().lat, draggableStreetMarkers[ i ].getLatLng().lng));
 }
}

Loop through the points in draggableStreetMarkers array. If the element is not empty them push the latitude and longitude values to latLngStreets array as a LatLng pair "a type of data in leaflet". In draggableStreetMarkers we have the elements as markers. We need only the geographical points from them so we use getLatLng() mthod to get it.

if(latLngStreets.length > 1) {
 // create a red polyline from an array of LatLng points
 polyLine = L.polyline( latLngStreets, {color: 'red'} ).addTo(map);
}

If we have more than one point on the map we can draw a line. So we create a red line and add it to the map.

if(polyLine != null) {
 // zoom the map to the polyline
 map.fitBounds( polyLine.getBounds() );
}

If the line is created successfully we need to zoom the map so it fits the line we have drawn. This function seems complicated at the first sight, but when you analyze it step by step, you can see it is very logical and simple.

function getGeoPoints() {
 var points = new Array();
 for(var i=0; i < draggableStreetMarkers.length; i++) {
  if(draggableStreetMarkers[i] != "") {
   points[i] =  draggableStreetMarkers[ i ].getLatLng().lng + "," + draggableStreetMarkers[ i ].getLatLng().lat;
  }
 }
 $('#geo').val(points.join(','));
}

In the third button we call getGeoPoints(), with it we get the points from the map and assign them in the text-area box in our HTML.

In this function we first create a local array named points. Then we loop through the draggableStreetMarkers array and for each element that is not empty, we extract from it the latitude and longitude, and pair them with a comma between putting the longitude first.

For some reason the standard in leaflet is to keep pairs of latitude and longitude values in that order when it is in the string format.

Finally we join all the pairs again using commas as separators and assign the big resulting string to our text-area.

We saw how to draw a line, how to clear the map and how to get the points data from the map. But do we create points on the map?

It is not one draggable marker as in companies. This time we can use an unlimited number of these. We create a mouse listener function on the map. On each right click it gets, it will create a new draggable marker with the geographical point location we clicked on. If we want to remove a marker we have created, we click on the marker and it will be deleted.

$( document ).ready(function() {
 map.on('click', function(e) {
  addMarkerStreetPoint( e.latlng );
 });
});

So when the document is fully loaded and ready, we add a listener "on click" to the map to invoke addMarkerStreetPoint() function with passing e.latlng which represents the points latitude and longitude.

function addMarkerStreetPoint( latLng ) {
 var streetMarker = L.marker( [latLng.lat, latLng.lng], { draggable: true, zIndexOffset: 900}).addTo(map);

 streetMarker.arrayId = draggableStreetMarkers.length;

 streetMarker.on('click', function() {
  map.removeLayer( draggableStreetMarkers[ this.arrayId ]);
  draggableStreetMarkers[ this.arrayId ] = "";
 });

 draggableStreetMarkers.push( streetMarker );
}

In addMarkerStreetPoint function we create a marker with latitude and longitude we have provided, make it draggable and add it to the map.

var streetMarker = L.marker( [latLng.lat, latLng.lng], {draggable: true, zIndexOffset: 900 }).addTo(map);

For every marker we assign an ID, so we can find it later in draggableStreetMarkers array. The ID value will be next available number in the array.

streetMarker.arrayId = draggableStreetMarkers.length;

For deleting the marker by a click on it we add a listener on the marker itself, when triggered we remove the marker from the map and the draggableStreetMarkers array.

streetMarker.on('click', function() {
 map.removeLayer( draggableStreetMarkers[ this.arrayId ]);
 draggableStreetMarkers[ this.arrayId ] = "";
});

Finally we add the marker in the draggableStreetMarkers array which holds all the markers we have created.

When we click on submit we will go to addstreetdb.php file, which will save our poly-line and street data in the DB. So lets create a new file named like that with this code in it:

<?php
require_once("db.php");
$street = strip_tags( $_POST['street'] );
$geo = strip_tags( $_POST['geo'] );

$conn->addStreet( $street, $geo);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Street added</title>
 </head>
 <body>
  <h1>Street has been added</h1>
 </body>
</html>

Very similar to addcompanydb.php file, we include our database class, get the data of the street (name and bunch of points) and pass them to addStreet() method in our class. Now lets add that method in db.php:

public function addStreet($street,$geo) {
 $statement = $this->conn->prepare("Insert INTO streets( name, geolocations ) VALUES(?,?)");
 $statement->bind_param('ss', $street, $geo);
 $statement->execute();
 $statement->close();
}

Edit streets

After adding a street to the DB we need to be able to edit them, so I am making a file editstreet.php with this code:

<?php
 require_once("db.php");
 $arr = $conn->getStreetsList();
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Edit a street</title>
  <script src="js/jquery.min.js"></script>
  <link rel="stylesheet" href="css/leaflet.css" />
  <script src="js/leaflet.js"></script>
 </head>
 <body>
  <div id="map" style="width: 600px; height: 400px"></div><br />
  <input type="button" onclick="drawStreet();" value="Draw a street" /> <input type="button" onclick="resetStreet();" value="Clear map" /><br />
  <p>To add a street point click on the map. To remove a street point click on it again.</p>
  <form action="updatestreet.php" method="post">
  <h1>Edit a street</h1>
   <table cellpadding="5" cellspacing="0" border="0">
    <tbody>
     <tr align="left" valign="top">
      <td align="left" valign="top">Street name</td>
      <td align="left" valign="top"><select id="street" name="street"><option value="0">Please choose a street</option><?php for($i=0; $i < count($arr); $i++) { print '<option value="'.$arr[$i]['id'].'">'.$arr[$i]['name'].'</option>'; } ?></select></td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top">Geographic locations</td>
      <td align="left" valign="top">
       <textarea id="geo" name="geo"></textarea>
       <br /><input type="button" onclick="getGeoPoints();" value="Collect points" />
      </td>
     </tr>
     <tr align="left" valign="top">
      <td align="left" valign="top"></td>
      <td align="left" valign="top"><input type="submit" value="Update"></td>
     </tr>
    </tbody>
   </table>
  </form>
  <script>
   var map = L.map( 'map' ).setView( [51.505, -0.09], 13);
   var polyLine;
   var draggableStreetMarkers = new Array();

   L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
    maxZoom: 18,
    attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
     '<a href="http://creativecommons.org/"> CC-BY-SA</a>, ' +
     'Imagery � <a href="http://mapbox.com"> Mapbox </a>',
    id: 'examples.map-i875mjb7'
   }).addTo(map);
   
   function resetStreet() {
    if(polyLine != null) {
     map.removeLayer( polyLine );
    }
    for(i=0; i < draggableStreetMarkers.length; i++) {
     map.removeLayer( draggableStreetMarkers[ i ]);
    }
    draggableStreetMarkers = new Array();
   }
   
   function addMarkerStreetPoint( latLng ) {
    var streetMarker = L.marker( [latLng.lat, latLng.lng], {draggable: true, zIndexOffset: 900}).addTo(map);
    
    streetMarker.arrayId = draggableStreetMarkers.length;

    streetMarker.on('click', function() {
     map.removeLayer( draggableStreetMarkers[ this.arrayId ]);
     draggableStreetMarkers[ this.arrayId ] = "";
    });

    draggableStreetMarkers.push( streetMarker );
   }
   
   function drawStreet() {
    if(polyLine != null) {
     map.removeLayer( polyLine );
    }

    var latLngStreets = new Array();

    for(i=0; i< draggableStreetMarkers.length; i++) {
     if(draggableStreetMarkers[i] != "") {
      latLngStreets.push( L.latLng( draggableStreetMarkers[ i ].getLatLng().lat, draggableStreetMarkers[ i ].getLatLng().lng));
     }
    }

    if(latLngStreets.length > 1) {
     // create a red polyline from an array of LatLng points
     polyLine = L.polyline( latLngStreets, {color: 'red'}).addTo(map);
    }

    if(polyLine != null) {
     // zoom the map to the polyline
     map.fitBounds( polyLine.getBounds());
    }
   }
   
   function getGeoPoints() {
    var points = new Array();
    for(var i=0; i <draggableStreetMarkers.length; i++) {
     if(draggableStreetMarkers[i] != "") {
      points[i] =  draggableStreetMarkers[ i ].getLatLng().lng + "," + draggableStreetMarkers[ i ].getLatLng().lat;
     }
    }
    $('#geo').val(points.join(','));
   }
   
   $( document ).ready(function() {
    map.on('click', function(e) {
     addMarkerStreetPoint( e.latlng );
    });
    
    $("#street").change(function() {
     resetStreet();
     for(var i=0; i <arr.length; i++) {
      if(arr[i]['id'] == $('#street').val()) {
       $('#geo').val(arr[ i ]['geolocations']);
       arrangePoints(arr[ i ]['geolocations']);
       drawStreet();
       break;
      }
     }
    });
   });
   
   function arrangePoints(geo) {
    var linesPin = geo.split(",");

    var linesLat = new Array();
    var linesLng = new Array();

    for(i=0; i < linesPin.length; i++) {
     if(i % 2) {
      linesLat.push( linesPin[i] );
     } else {
      linesLng.push( linesPin[i] );
     }
    }

    var latLngLine = new Array();

    for(i=0; i<linesLng.length; i++) {
     latLngLine.push( L.latLng( linesLat[i], linesLng[i]));
    }

    for(i=0; i<latLngLine.length; i++) {
     addMarkerStreetPoint( latLngLine[i]);
    }
   }
   
   var arr = JSON.parse( '<?php echo json_encode($arr) ?>' );
  </script>
 </body>
</html>

This file is very similar to the previous one, let me explain the new code in it:

<?php
 require_once("db.php");
 $arr = $conn->getStreetsList();
?>

We include our database class because we need to connect to the database and get all the streets by getStreetsList() method.

<tr align="left" valign="top">
 <td align="left" valign="top">Street name</td>
 <td align="left" valign="top">
  <select id="street" name="street"><option value="0">Please choose a street</option><?php for($i=0; $i < count($arr); $i++) { print '<option value="'.$arr[$i]['id'].'">'.$arr[$i]['name'].'</option>'; } ?></select>
 </td>
</tr>

In our HTML form we are now choosing from several streets by a drop-down list. When we select an option from the select we will invoke a trigger which draws that street and assigns its points.

$("#street").change(function() {
 resetStreet();
 for(var i=0;i<arr.length;i++) {
  if(arr[i]['id'] == $('#street').val()) {
   $('#geo').val(arr[ i ]['geolocations']);
   arrangePoints(arr[ i ]['geolocations']);
   drawStreet();
   break;
  }
 }
});

When this trigger is invoked first we clean the map with resetStreet();, then loop through our array we got from getStreetsList() method and if there is a match, we assign the geographic points to the text-area and then draw the street.

Before drawing we need to convert the raw data from the database to more suitable representation of data to leaflet, so we use arrangePoints() function to which we pass the raw data.

function arrangePoints(geo) {
 var linesPin = geo.split(",");

 var linesLat = new Array();
 var linesLng = new Array();

 for(i=0; i<linesPin.length;i++) {
  if(i % 2) {
   linesLat.push(linesPin[i]);
  } else {
   linesLng.push(linesPin[i]);
  }
 }

 var latLngLine = new Array();

 for(i=0; i<linesLng.length; i++) {
  latLngLine.push( L.latLng( linesLat[i], linesLng[i] ));
 }

 for(i=0; i<latLngLine.length; i++) {
  addMarkerStreetPoint( latLngLine[i] );
 }
}

arrangePoints() function splits the raw data (comma-sperated points) to array of points.

var linesPin = geo.split(",");

Now we create two local arrays to divide latitudes and longitudes from each other, and loop through then assign them.

var linesLat = new Array();
var linesLng = new Array();

for(i=0; i<linesPin.length;i++) {
 if(i % 2) {
  linesLat.push(linesPin[i]);
 }else{
  linesLng.push(linesPin[i]);
 }
}

Now we create a new array and fill it with the points in leaflet "latLng" data representation.

var latLngLine = new Array();

for(i=0; i<linesLng.length; i++) {
 latLngLine.push( L.latLng( linesLat[i], linesLng[i]));
}

Finally we add the points on the map and to draggableStreetMarkers array.

for(i=0; i<latLngLine.length;i++) {
 addMarkerStreetPoint( latLngLine[i]);
}

To get streets from the database, we use getStreetsList() method. We need to create that one also. Just add this code to db.php file:

public function getStreetsList() {
 $arr = array();
 $statement = $this->conn->prepare("Select id, name, geolocations from streets order by name ASC");
 $statement->bind_result( $id, $name, $geolocations);
 $statement->execute();
 while ($statement->fetch()) {
  $arr[] = [ "id" => $id, "name" => $name, "geolocations" => $geolocations];
 }
 $statement->close();
 
 return $arr;
}

Nothing special about this function I already explained a similar one before with companies.

Now after finishing with editing the street we need to be able to update it with, so I am creating updatestreet.php file with this code:

<?php
 require_once("db.php");

 $id = intval($_POST['street']);
 $geo = strip_tags($_POST['geo']);

 $conn->updateStreet( $id, $geo);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Street updated</title>
 </head>
 <body>
  <h1>Street has been updated</h1>
 </body>
</html>

Very simple code. We get the data in variables and call updateStreet method in our class to update the chosen street. I added updateStreet method to the class:

public function updateStreet($id, $geo) {
 $statement = $this->conn->prepare("Update streets SET geolocations = ? where id = ?");
 $statement->bind_param('si', $geo, $id);
 $statement->execute();
 $statement->close();
}

Delete a street

We can create a street and edit it, now lets delete it, we create a file named deletestreet.php, and add this code:

<?php
 require_once</pre><pre class="code">("db.php");
 $arr = $conn->getStreetsList();
?>
<!DOCTYPE html>
<html>
<head>
 <title>Delete a street</title>
 <script src="js/jquery.min.js"></script>
 <link rel="stylesheet" href="css/leaflet.css" />
 <script src="js/leaflet.js"></script>
</head>
<body>
 <div id="map" style="width: 600px; height: 400px"></div><br />
 <form action="deletestreetdb.php" method="post">
  <h1>Delete a street</h1>
  <table cellpadding="5" cellspacing="0" border="0">
   <tbody>
    <tr align="left" valign="top">
     <td align="left" valign="top">Street name</td>
     <td align="left" valign="top"><select id="street" name="street"><option value="0">Please choose a street</option><?php for($i=0;$i<count($arr);$i++) { print '<option value="'.$arr[$i]['id'].'">'.$arr[$i]['name'].'</option>'; } ?></select></td>
    </tr>
    <tr align="left" valign="top">
     <td align="left" valign="top"></td>
     <td align="left" valign="top"><input type="submit" value="Delete"></td>
    </tr>
   </tbody>
  </table>
 </form>
 <script>
  var map = L.map( 'map' ).setView( [51.505, -0.09], 13);
  var polyLine;
  var draggableStreetMarkers = new Array();

  L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
   maxZoom: 18,
   attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA</a>, ' +
    'Imagery � <a href="http://mapbox.com"> Mapbox </a>',
   id: 'examples.map-i875mjb7'
  }).addTo(map);
  
  function resetStreet() {
   if(polyLine != null) {
    map.removeLayer( polyLine );
   }
   for(i=0; i<draggableStreetMarkers.length; i++) {
    map.removeLayer( draggableStreetMarkers[ i ]);
   }
   draggableStreetMarkers = new Array();
  }
  
  function addMarkerStreetPoint( latLng ) {
   var streetMarker = L.marker( [latLng.lat, latLng.lng], { draggable: true, zIndexOffset: 900 }).addTo(map);
   
   streetMarker.arrayId = draggableStreetMarkers.length;

   streetMarker.on('click', function() {
    map.removeLayer( draggableStreetMarkers[ this.arrayId ]);
    draggableStreetMarkers[ this.arrayId ] = "";
   });

   draggableStreetMarkers.push( streetMarker );
  }
  
  function drawStreet() {
   if(polyLine != null) {
    map.removeLayer( polyLine );
   }

   var latLngStreets = new Array();

   for(i=0; i < draggableStreetMarkers.length; i++) {
    if(draggableStreetMarkers[ i ] != "") {
     latLngStreets.push( L.latLng( draggableStreetMarkers[ i ].getLatLng().lat, draggableStreetMarkers[ i ].getLatLng().lng));
    }
   }

   if(latLngStreets.length > 1) {
    // create a red polyline from an array of LatLng points
    polyLine = L.polyline(latLngStreets, {color: 'red'}).addTo(map);
   }

   if(polyLine != null) {
    // zoom the map to the polyline
    map.fitBounds( polyLine.getBounds() );
   }
  }
  
  function getGeoPoints() {
   var points = new Array();
   for(var i=0; i < draggableStreetMarkers.length; i++) {
    if(draggableStreetMarkers[ i ] != "") {
     points[i] =  draggableStreetMarkers[ i ].getLatLng().lng + "," + draggableStreetMarkers[ i ].getLatLng().lat;
    }
   }
   $('#geo').val(points.join(','));
  }
  
  $( document ).ready(function() {   
   $("#street").change(function() {
    resetStreet();
    for(var i=0; i <arr.length; i++) {
     if(arr[i]['id'] == $('#street').val()) {
      arrangePoints( arr[ i ]['geolocations']);
      drawStreet();
      break;
     }
    }
   });
  });
  
  function arrangePoints(geo) {
   var linesPin = geo.split(",");

   var linesLat = new Array();
   var linesLng = new Array();

   for(i=0; i<linesPin.length;i++) {
    if(i % 2) {
     linesLat.push(linesPin[i]);
    }else{
     linesLng.push(linesPin[i]);
    }
   }

   var latLngLine = new Array();

   for(i=0; i<linesLng.length;i++) {
    latLngLine.push( L.latLng( linesLat[i], linesLng[i]));
   }

   for(i=0; i < latLngLine.length; i++) {
    addMarkerStreetPoint( latLngLine[i]);
   }
  }
  
  var arr = JSON.parse( '<?php echo json_encode($arr) ?>' );
 </script>
</body>
</html>

It is the same as editstreet.php file, but here we can not edit the street. We just choose it from a drop-down list and can click on button to delete it.

For deletion we make a file named deletestreetdb.php and add to it this code:

<?php
 require_once("db.php");

 $id = intval($_POST['street']);

 $conn->deleteStreet($id);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Street deleted</title>
 </head>
 <body>
  <h1>Street has been deleted</h1>
 </body>
</html>

And we add a new method to the database class "deleteStreet()":

public function deleteStreet($id) {
 $statement = $this->conn->prepare("Delete from streets where id = ?");
 $statement->bind_param('i', $id);
 $statement->execute();
 $statement->close();
}

Add a new area

Areas are very similar to streets, in fact identical, but instead drawing just a line we draw a polygon, which is at least three connected lines. The code are very similar. Lets make a new file named addarea.php with this code:

<!DOCTYPE html>
<html>
<head>
 <title>Add an area</title>
 <script src="js/jquery.min.js"></script>
 <link rel="stylesheet" href="css/leaflet.css" />
 <script src="js/leaflet.js"></script>
</head>
<body>
 <div id="map" style="width: 600px; height: 400px"></div><br />
 <input type="button" onclick="drawArea();" value="Draw an area" /> <input type="button" onclick="resetArea();" value="Clear map" /><br />
 <p>To add an area point click on the map. To remove an area point click on it again.</p>
 <form action="addareadb.php" method="post">
  <h1>Add a new area</h1>
  <table cellpadding="5" cellspacing="0" border="0">
   <tbody>
    <tr align="left" valign="top">
     <td align="left" valign="top">Area name</td>
     <td align="left" valign="top"><input type="text" name="area" /></td>
    </tr>
    <tr align="left" valign="top">
     <td align="left" valign="top">Geographic locations</td>
     <td align="left" valign="top"><textarea id="geo" name="geo"></textarea>
            <br /><input type="button" onclick="getGeoPoints();" value="Collect points" /></td>
    </tr>
    <tr align="left" valign="top">
     <td align="left" valign="top"></td>
     <td align="left" valign="top"><input type="submit" value="Save"></td>
    </tr>
   </tbody>
  </table>
 </form>
 <script>
  var map = L.map('map').setView([51.505, -0.09], 13);
  var polygon;
  var draggableAreaMarkers = new Array();

  L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
   maxZoom: 18,
   attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
    'Imagery � <a href="http://mapbox.com"> Mapbox </a>',
   id: 'examples.map-i875mjb7'
  }).addTo(map);
  
  function resetArea() {
   if(polygon != null) {
    map.removeLayer( polygon );
   }
   for(i=0; i < draggableAreaMarkers.length; i++) {
    map.removeLayer( draggableAreaMarkers[i] );
   }
   draggableAreaMarkers = new Array();
  }
  
  function addMarkerAreaPoint(latLng) {
   var areaMarker = L.marker( [latLng.lat, latLng.lng], { draggable: true, zIndexOffset: 900 }).addTo(map);
   
   areaMarker.arrayId = draggableAreaMarkers.length;

   areaMarker.on('click', function() {
    map.removeLayer( draggableAreaMarkers[ this.arrayId ]);
    draggableAreaMarkers[ this.arrayId ] = "";
   });

   draggableAreaMarkers.push( areaMarker );
  }
  
  function drawArea() {
   if(polygon != null) {
    map.removeLayer( polygon );
   }

   var latLngAreas = new Array();

   for(i=0; i < draggableAreaMarkers.length; i++) {
    if(draggableAreaMarkers[ i ]!="") {
     latLngAreas.push( L.latLng( draggableAreaMarkers[ i ].getLatLng().lat, draggableAreaMarkers[ i ].getLatLng().lng));
    }
   }

   if(latLngAreas.length > 1) {
    // create a blue polygon from an array of LatLng points
    polygon = L.polygon( latLngAreas, {color: 'blue'}).addTo(map);
   }

   if(polygon != null) {
    // zoom the map to the polygon
    map.fitBounds( polygon.getBounds() );
   }
  }
  
  function getGeoPoints() {
   var points = new Array();
   for(var i=0; i < draggableAreaMarkers.length; i++) {
    if(draggableAreaMarkers[i] != "") {
     points[i] =  draggableAreaMarkers[ i ].getLatLng().lng + "," + draggableAreaMarkers[ i ].getLatLng().lat;
    }
   }
   $('#geo').val(points.join(','));
  }
  
  $( document ).ready(function() {
   map.on('click', function(e) {
    addMarkerAreaPoint( e.latlng);
   });
  });
 </script>
</body>
</html>

It is the same code as in addstreet.php. I have just changed the names of the functions and some variables. The only difference we need to notice is this code.

polygon = L.polygon( latLngAreas, {color: 'blue'}).addTo(map);

This is the way we create a polygon in leaflet. Like with lines, it receives an array of points and options like color.

Now we create addareadb.php and put this code in it:

<?php
 require_once("db.php");

 $area = strip_tags($_POST['area']);
 $geo = strip_tags($_POST['geo']);

 $conn->addArea( $area, $geo);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Area added</title>
 </head>
 <body>
  <h1>Area has been added</h1>
 </body>
</html>

We call addArea() mehod from database class, and pass to it the name of that area and an array of points. In the class we add addArea() method:

public function addArea($area,$geo) {
 $statement = $this->conn->prepare("Insert INTO areas( name, geolocations ) VALUES(?,?)");
 $statement->bind_param('ss', $area, $geo);
 $statement->execute();
 $statement->close();
}

Edit areas

Lets create a file named editarea.php with this code in it:

<?php
 require_once("db.php");
 $arr = $conn->getAreasList();
?>
<!DOCTYPE html>
<html>
<head>
 <title>Edit an area</title>
 <script src="js/jquery.min.js"></script>
 <link rel="stylesheet" href="css/leaflet.css" />
 <script src="js/leaflet.js"></script>
</head>
<body>
 <div id="map" style="width: 600px; height: 400px"></div><br />
 <input type="button" onclick="drawArea();" value="Draw an area" /> <input type="button" onclick="resetArea();" value="Clear map" /><br />
 <p>To add an area point click on the map. To remove an area point click on it again.</p>
 <form action="updatearea.php" method="post">
  <h1>Edit an area</h1>
  <table cellpadding="5" cellspacing="0" border="0">
   <tbody>
    <tr align="left" valign="top">
     <td align="left" valign="top">Area name</td>
     <td align="left" valign="top"><select id="area" name="area"><option value="0">Please choose an area</option><?php for($i=0;$i<count($arr);$i++) { print '<option value="'.$arr[$i]['id'].'">'.$arr[$i]['name'].'</option>'; } ?></select></td>
    </tr>
    <tr align="left" valign="top">
     <td align="left" valign="top">Geographic locations</td>
     <td align="left" valign="top"><textarea id="geo" name="geo"></textarea>
      <br /><input type="button" onclick="getGeoPoints();" value="Collect points" /></td>
    </tr>
    <tr align="left" valign="top">
     <td align="left" valign="top"></td>
     <td align="left" valign="top"><input type="submit" value="Update"></td>
    </tr>
   </tbody>
  </table>
 </form>
 <script>
  var map = L.map( 'map' ).setView( [51.505, -0.09], 13);
  var polygon;
  var draggableAreaMarkers = new Array();

  L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
   maxZoom: 18,
   attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
    'Imagery � <a href="http://mapbox.com"> Mapbox </a>',
   id: 'examples.map-i875mjb7'
  }).addTo(map);
  
  function resetArea() {
   if(polygon != null) {
    map.removeLayer( polygon );
   }
   for(i=0; i <draggableAreaMarkers.length; i++) {
    map.removeLayer( draggableAreaMarkers[i] );
   }
   draggableAreaMarkers = new Array();
  }
  
  function addMarkerAreaPoint(latLng) {
   var areaMarker = L.marker( [latLng.lat, latLng.lng], { draggable: true, zIndexOffset: 900}).addTo(map);
   
   areaMarker.arrayId = draggableAreaMarkers.length;

   areaMarker.on('click', function() {
    map.removeLayer( draggableAreaMarkers[ this.arrayId ]);
    draggableAreaMarkers[ this.arrayId ] = "";
   });

   draggableAreaMarkers.push( areaMarker );
  }
  
  function drawArea() {
   if(polygon != null) {
    map.removeLayer( polygon );
   }

   var latLngAreas = new Array();

   for(i=0; i<draggableAreaMarkers.length; i++) {
    if(draggableAreaMarkers[i] != "") {
     latLngAreas.push( L.latLng( draggableAreaMarkers[ i ].getLatLng().lat, draggableAreaMarkers[ i ].getLatLng().lng));
    }
   }

   if(latLngAreas.length > 1) {
    // create a blue polygon from an array of LatLng points
    polygon = L.polygon( latLngAreas, { color: 'blue' }).addTo(map);
   }

   if(polygon != null) {
    // zoom the map to the polygon
    map.fitBounds( polygon.getBounds() );
   }
  }
  
  function getGeoPoints() {
   var points = new Array();
   for(var i=0; i<draggableAreaMarkers.length; i++) {
    if(draggableAreaMarkers[ i ] != "") {
     points[i] =  draggableAreaMarkers[ i ].getLatLng().lng + "," + draggableAreaMarkers[ i ].getLatLng().lat;
    }
   }
   $('#geo').val(points.join(','));
  }
  
  $( document ).ready(function() {
   map.on('click', function(e) {
    addMarkerAreaPoint( e.latlng );
   });
   
   $("#area").change(function() {
    resetStreet();
    for(var i=0; i < arr.length; i++) {
     if(arr[i]['id'] == $('#area').val()) {
      $('#geo').val( arr[i]['geolocations'] );
      arrangePoints( arr[i]['geolocations'] );
      drawArea();
      break;
     }
    }
   });
  });
  
  function arrangePoints(geo) {
   var linesPin = geo.split(",");

   var linesLat = new Array();
   var linesLng = new Array();

   for(i=0; i < linesPin.length; i++) {
    if(i % 2) {
     linesLat.push(linesPin[i]);
    }else{
     linesLng.push(linesPin[i]);
    }
   }

   var latLngLine = new Array();

   for(i=0; i<linesLng.length;i++) {
    latLngLine.push( L.latLng( linesLat[i], linesLng[i]));
   }

   for(i=0; i < latLngLine.length; i++) {
    addMarkerAreaPoint( latLngLine[i] );
   }
  }
  
  var arr = JSON.parse( '<?php echo json_encode($arr) ?>' );
 </script>
</body>
</html>

Again it is the same as editstreet.php but with functions and variables names changed. Instead of polyline, we create a polygon. Also we call a list of polygons from the database using getAreasList() method, which we need to add in db.php:

public function getAreasList() {
 $arr = array();
 $statement = $this->conn->prepare("Select id, name, geolocations from areas order by name ASC");
 $statement->bind_result( $id, $name, $geolocations);
 $statement->execute();
 while ($statement->fetch()) {
  $arr[] = [ "id" => $id, "name" => $name, "geolocations" => $geolocations];
 }
 $statement->close();
 
 return $arr;
}

Now lets create updatearea.php to save the updates to the database:

<?php
 require_once("db.php");

 $id = intval($_POST['area']);
 $geo = strip_tags($_POST['geo']);

 $conn->updateArea( $id, $geo);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Area updated</title>
 </head>
 <body>
  <h1>Area has been updated</h1>
 </body>
</html>

And add updateArea() method to connectToDB class in db.php:

public function updateArea($id,$geo) {
 $statement = $this->conn->prepare("Update areas SET geolocations = ? where id = ?");
 $statement->bind_param('si', $geo, $id);
 $statement->execute();
 $statement->close();
}

With this we finished with the updating.

Delete an area

To delete an area we create the file deletearea.php with this code:

<?php
 require_once("db.php");
 $arr = $conn->getAreasList();
?>
<!DOCTYPE html>
<html>
<head>
 <title>Delete an area</title>
 <script src="js/jquery.min.js"></script>
 <link rel="stylesheet" href="css/leaflet.css" />
 <script src="js/leaflet.js"></script>
</head>
<body>
 <div id="map" style="width: 600px; height: 400px"></div><br />
 <form action="deleteareadb.php" method="post">
  <h1>Delete an area</h1>
  <table cellpadding="5" cellspacing="0" border="0">
   <tbody>
    <tr align="left" valign="top">
     <td align="left" valign="top">Area name</td>
     <td align="left" valign="top"><select id="area" name="area"><option value="0">Please choose an area</option><?php for( $i=0; $i < count($arr); $i++) { print '<option value="'.$arr[$i]['id'].'">'.$arr[$i]['name'].'</option>'; } ?></select></td>
    </tr>
    <tr align="left" valign="top">
     <td align="left" valign="top"></td>
     <td align="left" valign="top"><input type="submit" value="Delete"></td>
    </tr>
   </tbody>
  </table>
 </form>
 <script>
  var map = L.map( 'map' ).setView( [51.505, -0.09], 13);
  var polygon;
  var draggableAreaMarkers = new Array();

  L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
   maxZoom: 18,
   attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
    'Imagery � <a href="http://mapbox.com"> Mapbox </a>',
   id: 'examples.map-i875mjb7'
  }).addTo(map);
  
  function resetArea() {
   if(polygon != null) {
    map.removeLayer( polygon );
   }
   for(i=0; i < draggableAreaMarkers.length; i++) {
    map.removeLayer( draggableAreaMarkers[i] );
   }
   draggableAreaMarkers = new Array();
  }
  
  function addMarkerAreaPoint(latLng) {
   var areaMarker = L.marker( [latLng.lat, latLng.lng], { draggable: true, zIndexOffset: 900}).addTo(map);
   
   areaMarker.arrayId = draggableAreaMarkers.length;

   areaMarker.on('click', function() {
    map.removeLayer( draggableAreaMarkers[ this.arrayId ]);
    draggableAreaMarkers[ this.arrayId ] = "";
   });

   draggableAreaMarkers.push( areaMarker );
  }
  
  function drawArea() {
   if(polygon != null) {
    map.removeLayer( polygon );
   }

   var latLngAreas = new Array();

   for(i=0; i < draggableAreaMarkers.length; i++) {
    if(draggableAreaMarkers[i] != "") {
     latLngAreas.push( L.latLng( draggableAreaMarkers[ i ].getLatLng().lat, draggableAreaMarkers[ i ].getLatLng().lng));
    }
   }

   if(latLngAreas.length > 1) {
    // create a blue polygon from an array of LatLng points
    polygon = L.polygon( latLngAreas, { color: 'blue' }).addTo(map);
   }

   if(polygon != null) {
    // zoom the map to the polygon
    map.fitBounds( polygon.getBounds() );
   }
  }
  
  function getGeoPoints() {
   var points = new Array();
   for(var i=0; i <draggableAreaMarkers.length; i++) {
    if(draggableAreaMarkers[ i ] != "") {
     points[i] =  draggableAreaMarkers[ i ].getLatLng().lng + "," + draggableAreaMarkers[ i ].getLatLng().lat;
    }
   }
   $('#geo').val(points.join(','));
  }
  
  $( document ).ready(function() {   
   $("#area").change(function() {
    resetArea();
    for(var i=0;i<arr.length;i++) {
     if(arr[i]['id'] == $('#area').val()) {
      arrangePoints(arr[i]['geolocations']);
      drawArea();
      break;
     }
    }
   });
  });
  
  function arrangePoints( geo ) {
   var linesPin = geo.split(",");

   var linesLat = new Array();
   var linesLng = new Array();

   for(i=0; i < linesPin.length;i++) {
    if(i % 2) {
     linesLat.push(linesPin[i]);
    }else{
     linesLng.push(linesPin[i]);
    }
   }

   var latLngLine = new Array();

   for(i=0; i < linesLng.length; i++) {
    latLngLine.push( L.latLng( linesLat[i], linesLng[i]));
   }

   for(i=0; i < latLngLine.length;i++) {
    addMarkerAreaPoint( latLngLine[i]);
   }
  }
  
  var arr = JSON.parse( '<?php echo json_encode($arr) ?>' );
 </script>
</body>
</html>

For deleting the area from the database we create deleteareadb.php with this code:

<?php
 require_once("db.php");

 $id = intval($_POST['area']);

 $conn->deleteArea($id);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Area deleted</title>
 </head>
 <body>
  <h1>Area has been deleted</h1>
 </body>
</html>

In db.php add deleteArea() method:

public function deleteArea($id) {
 $statement = $this->conn->prepare("Delete from areas where id = ?");
 $statement->bind_param('i', $id);
 $statement->execute();
 $statement->close();
}

By this we have finished the administration.

Frontend Interface

We are back to the front end interface. It is our index.php file. We have finished the administration interface, after filling up the database with markers, streets and areas. Now we want to show them all on the map. Of course this is an interactive map, so when a marker is clicked it must trigger a popup with some information, the same for the street and area.

Lets start by getting all markers, streets and areas from the database by adding these lines of code in the top of index.php file:

<?php
 require_once("db.php");
 $companies = $conn->getCompaniesList();
 $streets = $conn->getStreetsList();
 $areas = $conn->getAreasList();
?>

At the bottom of index.php in the script tag we will convert these PHP arrays to JavaScript arrays so we can use them:

var companies = JSON.parse( '<?php echo json_encode($companies) ?>' );
var streets = JSON.parse( '<?php echo json_encode($streets) ?>' );
var areas = JSON.parse( '<?php echo json_encode($areas) ?>' );

Do not forget to include the jQuery library to the head section:

<script src="js/jquery.min.js"></script>

Now when the document is fully loaded, we call three functions: one to add all companies and markers, the second to draw all streets and the third is to draw all the areas on the map. This is what you need to put between script tag in the bottom of the file.

$( document ).ready(function() {
 addCompanies();
 addStreets();
 addAreas();
});

Now we create these three functions as below:

function addCompanies() {
 for(var i=0; i < companies.length; i++) {
  var marker = L.marker( [companies[i]['latitude'], companies[i]['longitude']] ).addTo(map);
  marker.bindPopup( "<b>" + companies[i]['company'] + "</b><br>Details:" + companies[i]['details'] + "<br />Telephone: " + companies[i]['telephone']);   
 }
}

In this function we loop through companies array and create a marker for every company. Then bind a popup to every marker with the details of that company. If you click on the marker later a popup with these information will appear.

function addAreas() {
 for(var i=0; i <areas.length; i++) {
  var polygon = L.polygon( stringToGeoPoints( polygon[i]['geolocations']), { color: 'blue'}).addTo(map);
  polygon.bindPopup( "<b>" + polygon[i]['name'] );   
 }
}

This function will also loop but through areas array. It creates a polygon for every area and bind a popup to it. Do not miss stringToGeoPoints, we will get back to that later.

function addStreets() {
 for(var i=0; i <streets.length; i++) {
  var polyline = L.polyline( stringToGeoPoints( streets[i]['geolocations']), { color: 'red'}).addTo(map);
  polyline.bindPopup( "<b>" + streets[i]['name']);   
 }
}

In this function, like with the two previous functions, we loop through streets array and draw a line for every street. Again we are binding a popup with the street also.

function stringToGeoPoints(geo) {
 var linesPin = geo.split(",");

 var linesLat = new Array();
 var linesLng = new Array();

 for(i=0; i<linesPin.length;i++) {
  if(i % 2) {
   linesLat.push(linesPin[i]);
  }else{
   linesLng.push(linesPin[i]);
  }
 }

 var latLngLine = new Array();

 for(i=0; i < linesLng.length; i++) {
  latLngLine.push( L.latLng( linesLat[i], linesLng[i]));
 }
 
 return latLngLine;
}

Finally stringToGeoPoints() function is a helper function. It takes a string with comma separated list of points and convert it to an array of geographic points, which is returned back to the caller. This function is used in addAreas() and addStreets() functions.

Take a look on index.php now:

<?php
 require_once("db.php");
 $companies = $conn->getCompaniesList();
 $streets = $conn->getStreetsList();
 $areas = $conn->getAreasList();
?>
<!DOCTYPE html>
<html>
<head>
 <title>Leaflet basic example</title>
 <script src="js/jquery.min.js"></script>
 <link rel="stylesheet" href="css/leaflet.css" />
 <script src="js/leaflet.js"></script>
</head>
<body>
 <div id="map" style="width: 600px; height: 400px"></div>
 <script>

  var map = L.map('map').setView([51.505, -0.09], 13);

  L.tileLayer( 'https://api.mapbox.com/styles/v1/mapbox/streets-v10/tiles/{z}/{x}/{y}?access_token=pk.eyJ1IjoibWVnYTYzODIiLCJhIjoiY2ozbXpsZHgxMDAzNjJxbndweDQ4am5mZyJ9.uHEjtQhnIuva7f6pAfrdTw', {
   maxZoom: 18,
   attribution: 'Map data &copy; <a href="http://openstreetmap.org/"> OpenStreetMap </a> contributors, ' +
    '<a href="http://creativecommons.org/"> CC-BY-SA </a>, ' +
    'Imagery � <a href="http://mapbox.com">Mapbox</a>',
   id: 'examples.map-i875mjb7'
  }).addTo(map);
  
  $( document ).ready(function() {
   addCompanies();   
   addStreets();   
   addAreas();   
  });
  
  function addCompanies() {
   for(var i=0; i<companies.length; i++) {
    var marker = L.marker( [companies[i]['latitude'], companies[i]['longitude']]).addTo(map);
    marker.bindPopup( "<b>" + companies[i]['company']+"</b><br>Details:" + companies[i]['details'] + "<br />Telephone: " + companies[i]['telephone']);
   }
  }
  
  function stringToGeoPoints( geo ) {
   var linesPin = geo.split(",");

   var linesLat = new Array();
   var linesLng = new Array();

   for(i=0; i < linesPin.length; i++) {
    if(i % 2) {
     linesLat.push(linesPin[i]);
    }else{
     linesLng.push(linesPin[i]);
    }
   }

   var latLngLine = new Array();

   for(i=0; i<linesLng.length;i++) {
    latLngLine.push( L.latLng( linesLat[i], linesLng[i]));
   }
   
   return latLngLine;
  }
  
  function addAreas() {
   for(var i=0; i < areas.length; i++) {
    var polygon = L.polygon( stringToGeoPoints(areas[i]['geolocations']), { color: 'blue'}).addTo(map);
    polygon.bindPopup( "<b>" + areas[i]['name']);   
   }
  }
  
  function addStreets() {
   for(var i=0; i < streets.length; i++) {
    var polyline = L.polyline( stringToGeoPoints(streets[i]['geolocations']), { color: 'red'}).addTo(map);
    polyline.bindPopup( "<b>" + streets[i]['name']);   
   }
  }
  
  var companies = JSON.parse( '<?php echo json_encode($companies) ?>' );
  var streets = JSON.parse( '<?php echo json_encode($streets) ?>' );
  var areas = JSON.parse( '<?php echo json_encode($areas) ?>' );
 </script>
</body>
</html>

Download the Complete Leaflet PHP MySQL Map Scripts

With this tutorial you have learned how easy it is to create your maps based application using leaflet library, thus without relying on Google Maps.

The code was presented in the article in a way to help you understand it without having to go back and forth in the article.

In the next article article I cover how to search for locations in a LeafLet map using AJAX requests.

You can download the complete Leaflet PHP MySQL Map package in the ZIP format, or install it using the PHP composer tool with instructions in the same page.

If you liked this article, share it with your colleague developers. If you have a question post a comment below.

Using PHP MySQL with Google Maps Alternative using the Leaflet library

Contents

Leaflet Google Maps Alternative

Create a Basic Map

The Leaflet Database Structure

Administration Interface

Frontend Interface

Download the Complete Leaflet PHP MySQL Map Scripts

Leaflet Google Maps Alternative

Create a Basic Map

The Leaflet Database Structure

Administration Interface

Add a new company

Edit companies

Delete a company

Add a new street

Edit streets

Delete a street

Add a new area

Edit areas

Delete an area

Frontend Interface

Download the Complete Leaflet PHP MySQL Map Scripts

You need to be a registered user or login to post a comment

1,616,665 PHP developers registered to the PHP Classes site.
Be One of Us!

Login Immediately with your account on:

Using PHP MySQL with Google Maps Alternative using the Leaflet library

Contents

Add a new company

Edit companies

Delete a company

Add a new street

Edit streets

Delete a street

Add a new area

Edit areas

Delete an area

You need to be a registered user or login to post a comment

1,616,665 PHP developers registered to the PHP Classes site. Be One of Us!

Login Immediately with your account on:

1,616,665 PHP developers registered to the PHP Classes site.
Be One of Us!