Leaflet Search Map using PHP MySQL AJAX requests

By Ashraf Gheith

upnt.org

<email contact>

Contents

Search AJAX PHP MySQL Solution

Leaflet Map Database Modifications

The Administration Panel

Create an AJAX MySQL Search Tool

Download the Complete Leaflet PHP MySQL Map Scripts

 

Search AJAX PHP MySQL Solution

In the previous article Create a Google Maps alternative with PHP and MySQL using the Leaflet library I explained how to create a small Web application with an interactive map created using Leaflet.

In this article I will continue where I have stopped and explain how to add more functionality. I cover how to add a text input to let the users search the map for previously added companies, areas and streets.

I will use AJAX requests sent by jQuery to retrieve the results and then I will show it in a clickable list to let the user see the locations found on the map.

What we will do in this article:

• Modify the database to add keywords for better search functionality
• Modify the administration pages to enable keywords addition.
• Create a search tool to locate elements on the map

Leaflet Map Database Modifications

Our search engine will be very simple. It will search for elements in the database. Basically it will look for keywords the users enter.

In our current application we can search for the company's name, company's description, street's name and area's name. For better search capabilities we will add a new field to each of the three tables (companies, streets, areas) and name it "keywords".

This new field will be a simple text field with comma-separated values in it. The search engine will match the provided keywords against this field. This field will not be public. We are using it for the search tool only.

We will now alter all the three tables we mentioned:

ALTER TABLE companies ADD keywords VARCHAR(255);

ALTER TABLE streets ADD keywords VARCHAR(255);

ALTER TABLE areas ADD keywords VARCHAR(255);

The Administration Panel

Now we will open our administration files one by one and modify them. We will add one textarea in the HTML, and modify the PHP to interact with it.

Open your "addcompany.php" file and add this code after the telephone row:

<tr align="left" valign="top">
  <td align="left" valign="top">Keywords</td>
  <td align="left" valign="top"><textarea name="keywords"></textarea></td>
</tr>

Now open your "addcompanydb.php" file and look for

$telephone = strip_tags($_POST['telephone']);

Add this line after it:

$keywords = strip_tags($_POST['keywords']);

Modify the next line to become like this:

connectToDB::addCompany($company, $details, $latitude, $longitude, $telephone, $keywords);

We have added a new textarea to our HTML part, retrieved the keywords field in PHP and passed it to the addCompany method.

Now lets modify the connectToDB class. Open db.php and look for the addCompany method. Replace it with this code:

public function addCompany( $company, $details, $latitude, $longitude, $telephone, $keywords) {
  $statement = $this->conn->prepare("INSERT INTO companies( company, details, latitude, longitude, telephone, keywords) VALUES( ?, ?, ?, ?, ?, ?)");
  $statement->bind_param('ssssss', $company, $details, $latitude, $longitude, $telephone, $keywords);
  $statement->execute();
  $statement->close();
}

You mayl notice that we added the $keywords parameter to this method.

Now open editcompany.php. Look for the telephone row again and add this after it:

<tr align="left" valign="top">
  <td align="left" valign="top">Keywords</td>
  <td align="left" valign="top"><textarea name="keywords" id="keywords"></textarea></td>
</tr>

Now look for

$("#company").change(function() {

and add this code inside it:

$('#keywords').val(arr[i]['keywords']);

This will assign the data to the text area when you edit a company. To retrieve our keywords from the DB we need to modify connectToDB class. Go to getCompaniesList() method and replace it with this code:

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

Great! Now we are retrieving the keywords in editcompany.php file. Lets open updatecompany.php to reflect the changes there. Look for the telephone variable again and add this beneath it:

$keywords = strip_tags($_POST['keywords']);

And replace the call of updateCompany() method to become like this:

$conn->updateCompany( $id, $details, $latitude, $longitude, $telephone, $keywords);

Now of course you need to change the class, so replace updateCompany() method with this code:

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

We can now add and edit companies with our new field keywords. Lets do the same for streets. In addstreet.php add the code below under the street name row in the table:

<tr align="left" valign="top">
  <td align="left" valign="top">Keywords</td>
  <td align="left" valign="top"><textarea name="keywords"></textarea></td>
</tr>

Now open addstreetdb.php file and add this code under "$geo" line:

$keywords = strip_tags( $_POST['keywords'] );

And modify the line after that to become like this:

$conn->addStreet( $street, $geo, $keywords);

Open connectToDB class and modify addStreet() method to reflect the new changes:

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

That was street adding. Now open editstreet.php file and add the keywords row there also in the same place as the file before:

<tr align="left" valign="top">
  <td align="left" valign="top">Keywords</td>
  <td align="left" valign="top"><textarea name="keywords" id="keywords"></textarea></td>
</tr>

Now look for "$("#street").change(function() {" line and replace it with this one:

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

We need to change getStreetsList() method so lets open the class and replace the method with this one:

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

Now open updatestreet.php and add the post retrieving line:

$keywords = strip_tags($_POST['keywords']);

And replace the method calling with this one:

$conn->updateStreet( $id, $geo, $keywords);

And now we will look for updateStreet() method in the class and change it with this code:

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

By this we have changed companies and streets. We need to change areas now. Open addarea.php file and add the row code after area name row:

<tr align="left" valign="top">
  <td align="left" valign="top">Keywords</td>
  <td align="left" valign="top"><textarea name="keywords"></textarea></td>
</tr>

Now open addareadb.php file and add this code under "$geo" line:

$keywords = strip_tags($_POST['keywords']);

And change the line beneath:

$conn->addArea( $area, $geo, $keywords);

Change addArea() method in the class with this code:

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

Now lets change editarea.php file, add the row code:

<tr align="left" valign="top">
  <td align="left" valign="top">Keywords</td>
  <td align="left" valign="top"><textarea name="keywords" id="keywords"></textarea></td>
</tr>

Add the assign code in "$("#area").change(function()" code:

$('#keywords').val( arr[i]['keywords'] );

In the class change getAreasList() method:

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

Finally update updatearea.php file by adding the code below:

$keywords = strip_tags($_POST['keywords']);

And changing the method call:

$conn->updateArea( $id, $geo, $keywords);

Now replace updateArea() in the class to reflect the new changes:

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

We finished the administration panel changes. Next we will implement front-end changes.

Create an AJAX MySQL Search Tool

In this section we will put a form text input in our HTML with a search button. When the user clicks the search button, it will call an AJAX function which will pass the keyword to a PHP script which will connect to our class and search for the keyword in three different tables.

The resulting queries would be returned combined in one array. Then with jQuery we will fill a list of results which can be clicked to move the respective locations to the center of the map.

Lets start with index.php, after the opening "body" tag add this HTML code:

<input type="text" name="search" id="search" /> <input type="button" id="searchBtn" value="Search" />

Now under:

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

Add this code to insert the search result in there:

<div id="searchresult"></div>

Now look for "$( document ).ready()" and add this code:

$('#searchBtn').click(function() {
  $.ajax({
    type: "GET",
    url: "/ajax.php?keyword="+$("#search").val()
  }).done(function( data )
  {
    var jsonData = JSON.parse(data);
    var jsonLength = jsonData.results.length;
    var html = "<ul>";
    for (var i = 0; i < jsonLength; i++) {
      var result = jsonData.results[i];
      html += '<li data-lat="' + result.latitude + '" data-lng="' + result.longitude + '" class="searchResultElement">' + result.name + '</li>';
    }
    html += '</ul>';
    $('#searchresult').html(html);  
  });
});

We have added an listener to button click event, so we can send an AJAX request when the search button is clicked. Then we send the AJAX request with jQuery, sending data using the GET method to the file ajax.php which we will pass the search keywords the user entered in the form text input.

After processing the AJAX request it will return JSON encoded response data which we need to parse. Then we need to loop through our results array we got from the JSON response and fill out "html" variable.

We are constructing our list of results in HTML. We will need the name of the object that will contain the presentation of the results, as well the latitude and longitude to change the map center. Of course generated code is inserted in the DIV with ID "searchresult" we created.

One thing left in index.php file to be done, it is a handler to catch mouse click on the result list.

In "$( document ).ready()" add another code:

$( 'li.searchResultElement' ).click( function() {
  var lat = $(this).attr( "data-lat" );
  var lng = $(this).attr( "data-lng" );
  map.panTo( [lat,lng] );
}

This code will catch any click on any LI element in our list, then retrieves the latitude and longitude values and pan the map to that location point.

After finishing index.php file, we need to create ajax.php which will get the submitted search keyword and returns a JSON object with the requested search result.

Create a new file named ajax.php and paste this code in it:

<?php
  require_once("db.php");
  $keyword = strip_tags( $_GET['keyword'] );
  $jsonData = $conn->getSearchResults( $keyword );
  print $jsonData;
?>

This file is very simple. We include our class file. Call the method getSearchResults() and pass the keyword variable. Print the results out to be returned back as the AJAX request response.

The JSON data must include three values, the name of the element, and its latitude and longitude. So we will create a new method in the class called getSearchResults():

public function getSearchResults($keyword)
{
  $arr = array();
  $jsonData = '{"results":[';
  $this->conn->query( "SET NAMES 'UTF8'" );
  $statement = $this->conn->prepare("SELECT company, latitude, longitude FROM `companies` where keywords REGEXP ? or company REGEXP ?");
  $statement->bind_param( 'ss', $keyword, $keyword);
  $statement->execute();
  $statement->bind_result( $name, $lat, $lng);
  while ($statement->fetch()) {
    $arr[] = '{"name":"' . $name. '","latitude":"' . $lat. '","longitude":"' . $lng. '"}';
  }
  $statement->close();

  $statement = $this->conn->prepare( "SELECT name, geolocations FROM `streets` where keywords REGEXP ? or name REGEXP ?");
  $statement->bind_param( 'ss', $keyword, 4keyword);
  $statement->execute();
  $statement->bind_result( $name, $geolocations);
  while ($statement->fetch()) {
    $temp = explode(",",$geolocations);
    $arr[] = '{"name":"' . $name. '","latitude":"' . $temp[1]. '","longitude":"' . $temp[0]. '"}';
  }
  $statement->close();

  $statement = $this->conn->prepare( "SELECT name, geolocations FROM `areas` where keywords REGEXP ? or name REGEXP ?");
  $statement->bind_param( 'ss', $keyword, $keyword);
  $statement->execute();
  $statement->bind_result( $name, $geolocations);
  while ($statement->fetch()) {
    $temp = explode(",",$geolocations);
    $arr[] = '{"name": "' . $name. '", "latitude": "' . $temp[1]. '","longitude":"' . $temp[0]. '"}';
  }
  $statement->close();

  $jsonData .= implode(",", $arr);
  $jsonData .= ']}';
  return $jsonData;
}

This method will receive a keyword and perform three queries: one on companies, one on streets and one on areas. It will search for occurrences in two fields: one the name field and the other the keywords field.

The results will return three values: name, latitude and longitude. In companies case it is straight forward. The next two tables do not have latitude and longitude, so we extract the first point from the geo-locations.

Download the Complete Leaflet PHP MySQL Map Scripts

With this tutorial you have learned how easy it is to create an AJAX driven search tool for your Leaflet map application.

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.