How to Live Search using PHP MySQL and Ajax

Today, We will learn the program to live search using PHP MySQL and Ajax. You can also download the source code of Live Search written in PHP MySQL and Ajax.

Ajax Live Database Search

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

We will create a live search box that will search the countries table and show the results asynchronously. But, first of all, we need to create this table.

Step 1: Creating the Database Table

Execute the following SQL query to create the countries table in your MySQL database.

CREATE TABLE countries (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

After creating the table, you need to populate it with some data using the SQL INSERT statement. Alternatively, you can download the prepopulated countries table by clicking the download button and import it into your MySQL database.

Please check out the tutorial on SQL CREATE TABLE statements for detailed information about the syntax for creating tables in the MySQL database system.

Step 2: Creating the Search Form

Now, let’s create a simple web interface that allows users to live to search the names of countries available in our countries table, just like an autocomplete or typeahead.

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

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Live MySQL Database Search</title>
<style>
    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-3.5.1.min.js"></script>
<script>
$(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 key-up event occurs 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 the 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 3: Processing Search Query in Backend

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

<?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());
}
 
if(isset($_REQUEST["term"])){
    // Prepare a select statement
    $sql = "SELECT * FROM countries WHERE name LIKE ?";
    
    if($stmt = mysqli_prepare($link, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "s", $param_term);
        
        // Set parameters
        $param_term = $_REQUEST["term"] . '%';
        
        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            $result = mysqli_stmt_get_result($stmt);
            
            // Check number of rows in the result set
            if(mysqli_num_rows($result) > 0){
                // Fetch result rows as an associative array
                while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                    echo "<p>" . $row["name"] . "</p>";
                }
            } else{
                echo "<p>No matches found</p>";
            }
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        }
    }
     
    // Close statement
    mysqli_stmt_close($stmt);
}
 
// close connection
mysqli_close($link);
?>

The SQL SELECT the statement is used in combination with the LIKE operator (line no-16) to find the matching records in a countries database table. We’ve implemented the prepared statement for better search performance as well as to prevent SQL injection attacks.

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

Eduguru

3 thoughts on “How to Live Search using PHP MySQL and Ajax”

Leave a Reply Cancel reply