PHP MySQL Ajax Live Search

In this tutorial you will learn how create a simple live MySQL database search feature using the PHP and Ajax.

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 inputVal = $(this).val();
  •         var resultDropdown = $(this).siblings(".result");
  •         if(inputVal.length){
  •             $.get("backend-search.php", {term: inputVal}).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

Procedural Object Oriented PDO
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
  • $term = mysqli_real_escape_string($link, $_REQUEST['term']);
  •  
  • if(isset($term)){
  •     // Attempt select query execution
  •     $sql = "SELECT * FROM countries WHERE name LIKE '" . $term . "%'";
  •     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</p>";
  •         }
  •     } else{
  •         echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  •     }
  • }
  •  
  • // close connection
  • mysqli_close($link);
  • ?>
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $mysqli = new mysqli("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($mysqli === false){
  •     die("ERROR: Could not connect. " . $mysqli->connect_error);
  • }
  •  
  • // Escape user inputs for security
  • $term = $mysqli->real_escape_string($_REQUEST['term']);
  •  
  • if(isset($term)){
  •     // Attempt select query execution
  •     $sql = "SELECT * FROM countries WHERE name LIKE '" . $term . "%'";
  •     if($result = $mysqli->query($sql)){
  •         if($result->num_rows > 0){            
  •             while($row = $result->fetch_array()){
  •                 echo "<p>" . $row['name'] . "</p>";
  •             }
  •             // Free result set
  •             $result->free();
  •         } else{
  •             echo "<p>No matches found</p>";
  •         }
  •     } else{
  •         echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  •     }
  • }
  •  
  • // Close connection
  • $mysqli->close();
  • ?>
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • try{
  •     $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
  •     // Set the PDO error mode to exception
  •     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  • } catch(PDOException $error){
  •     die("ERROR: Could not connect. " . $error->getMessage());
  • }
  •  
  • // Attempt search query execution
  • try{
  •     if(isset($_REQUEST['term'])){
  •         // create prepared statement
  •         $sql = "SELECT * FROM countries WHERE name LIKE :term";
  •         $stmt = $pdo->prepare($sql);
  •         $term = $_REQUEST['term'] . '%';
  •         // bind parameters to statement
  •         $stmt->bindParam(':term', $term);
  •         // execute the prepared statement
  •         $stmt->execute();
  •         if($stmt->rowCount() > 0){
  •             while($row = $stmt->fetch()){
  •                 echo "<p>" . $row['name'] . "</p>";
  •             }
  •         } else{
  •             echo "<p>No matches found";
  •         }
  •     }  
  • } catch(PDOException $error){
  •     die("ERROR: Could not able to execute $sql. " . $error->getMessage());
  • }
  •  
  • // Close connection
  • unset($pdo);
  • ?>

The SQL SELECT statement is used in combination with the LIKE operator (line no-16) to find the matching records in countries database table.

 

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.