PHP MySQL Ajax Live Search

The SELECT statement can be used in combination with the LIKE operator and regular expressions to find the matching records in MySQL database table.

Ajax Live Database Search

You can create a simple live database search feature utilizing the Ajax and PHP, where the search results will displayed as you start typing some character in search input box.

Suppose, we've a table countries in our database that has two fields id and name. Now, let's create a simple web interface that allows user to live search the names of countries available in our countries table, just like an autocomplete or typeahead.

Step 1: Creating the Search Form

Create a PHP file named "search-form.php" and put the following code inside of it.

Example

Download
  • <!DOCTYPE html>
  • <html lang="en">
  • <head>
  • <meta charset="UTF-8">
  • <title>PHP Live MySQL Database Search</title>
  • <style type="text/css">
  •     body{
  •         font-family: Arail, sans-serif;
  •     }
  •     /* Formatting search box */
  •     .search-box{
  •         width: 300px;
  •         position: relative;
  •         display: inline-block;
  •         font-size: 14px;
  •     }
  •     .search-box input[type="text"]{
  •         height: 32px;
  •         padding: 5px 10px;
  •         border: 1px solid #CCCCCC;
  •         font-size: 14px;
  •     }
  •     .result{
  •         position: absolute;        
  •         z-index: 999;
  •         top: 100%;
  •         left: 0;
  •     }
  •     .search-box input[type="text"], .result{
  •         width: 100%;
  •         box-sizing: border-box;
  •     }
  •     /* Formatting result items */
  •     .result p{
  •         margin: 0;
  •         padding: 7px 10px;
  •         border: 1px solid #CCCCCC;
  •         border-top: none;
  •         cursor: pointer;
  •     }
  •     .result p:hover{
  •         background: #f2f2f2;
  •     }
  • </style>
  • <script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
  • <script type="text/javascript">
  • $(document).ready(function(){
  •     $('.search-box input[type="text"]').on("keyup input", function(){
  •         /* Get input value on change */
  •         var term = $(this).val();
  •         var resultDropdown = $(this).siblings(".result");
  •         if(term.length){
  •             $.get("backend-search.php", {query: term}).done(function(data){
  •                 // Display the returned data in browser
  •                 resultDropdown.html(data);
  •             });
  •         } else{
  •             resultDropdown.empty();
  •         }
  •     });
  •     
  •     // Set search input value on click of result item
  •     $(document).on("click", ".result p", function(){
  •         $(this).parents(".search-box").find('input[type="text"]').val($(this).text());
  •         $(this).parent(".result").empty();
  •     });
  • });
  • </script>
  • </head>
  • <body>
  •     <div class="search-box">
  •         <input type="text" autocomplete="off" placeholder="Search country..." />
  •         <div class="result"></div>
  •     </div>
  • </body>
  • </html>

Every time the content of search input is changed or keyup event occur on search input the jQuery code (line no-47 to 67) sent an Ajax request to the "backend-search.php" file which retrieves the records from countries table related to the searched term. Those records later will be inserted inside a <div> by the jQuery and displayed on the browser.

Step 2: Processing Search Query in Backend

And here's the source code of our "backend-search.php" file which searches the database based on query string sent by the Ajax request and send the results back to browser.

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Escape user inputs for security
  • $query = mysqli_real_escape_string($link, $_REQUEST['query']);
  •  
  • if(isset($query)){
  •     // Attempt select query execution
  •     $sql = "SELECT * FROM countries WHERE name LIKE '" . $query . "%'";
  •     if($result = mysqli_query($link, $sql)){
  •         if(mysqli_num_rows($result) > 0){
  •             while($row = mysqli_fetch_array($result)){
  •                 echo "<p>" . $row['name'] . "</p>";
  •             }
  •             // Close result set
  •             mysqli_free_result($result);
  •         } else{
  •             echo "<p>No matches found for <b>$query</b></p>";
  •         }
  •     } else{
  •         echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  •     }
  • }
  •  
  • // close connection
  • mysqli_close($link);
  • ?>
 

Note:Always filter and validate user input before using it in a SQL statement. You can use the PHP mysqli_real_escape_string() function to escape special characters in a user input and create a legal SQL string to protect against SQL injection.

 
Close

Your Feedback:

 

We would love to hear from you! Please say something.