AJAX search from MySQL database in PHP example

By informerfrk

AJAX (Asynchronous JavaScript And XML) is very popular on modern websites. On Google, Facebook, YouTube you will see a huge use of AJAX. It is not a programming language, but a new technique of using JavaScript with XMLHttpRequest.

Using AJAX, you can load content on your existing page without refreshing the web page. For example, when you chat with your friend on Facebook, the page never refresh.

In this post, I am going to provide you the example code of AJAX search from database table in PHP. Here we are also going to use jQuery. jQuery contains an easy to use a function for AJAX which we will use.

jQuery AJAX search from database – example code in PHP

Here in this tutorial, we are going to make an AJAX search system which will search from a database table. In this example, we are going to build a country search system from a database table.To do this, you need the database table of all the countries.

To build the AJAX country search system from a database table, you need the database table of all the countries. If you want to follow this tutorial then, here is the download link to download the zipped SQL file which contains all the country list – apps_countries. After you download it, unzip it and import it into your database table.

Before import the table we create a database “countries” and inside this database we are going to import the SQL file.

Step 1

Create a PHP file and name it country_search.php

Inside country_search.php file, put the below code:

<!DOCTYPE html>
<html>
<head>
  <title>jQuery autocomplete</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

  <style type="text/css">
    #search_container {text-align: center;}
    #results {text-align: left; border: solid 1px #777; display: none; margin: 0 auto; width: 180px;}
  </style>
</head>
<body>

<div id="search_container">
   <h2>Search for country</h2>
   <input type="text" name="country" id="country" autocomplete="off">
   <div id="results"></div>
</div>

<script type="text/javascript">
  $(document).ready(function(){
    $("#country").keyup(function(){
    	var query = $(this).val();
    	if (query != "") {
    		$.ajax({
                url: 'query.php',
                method: 'POST',
                data: {query:query},
                success: function(data)
                {
                	$('#results').html(data);
                	$('#results').css('display', 'block');

                    $("#country").focusout(function(){
                        $('#results').css('display', 'none');
                    });
                    $("#country").focusin(function(){
                        $('#results').css('display', 'block');
                    });

                }
    		});
    	} else {
             $('#results').css('display', 'none');
    	}
    });
  });
</script>

</body>
</html>

 

Step 2

Now create another PHP file in the same directory and name it query.php

Below is the code which will be in query.php:

<?php

  $connect = mysqli_connect("localhost", "root", "", "countries");


  if (isset($_POST['query'])) {

  	$search_query = $_POST['query'];
  	
  
    $query = "SELECT * FROM apps_countries WHERE country_name LIKE '$search_query%' LIMIT 12";
    $result = mysqli_query($connect, $query);

  if (mysqli_num_rows($result) > 0) {
   while ($country_row = mysqli_fetch_array($result)) {
    echo $country_row['country_name']."<br/>";
  }
} else {
  echo "<p style='color:red'>Country not found...</p>";
}

}
?>

Thats it. Now your search will work. Open country_search.php file through your server. Now type something to search for a country. You will see that it is working. It is searching country name from the table.

Leave a Reply

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