Ajax live data search using jQuery PHP MySQL Codes With Pankaj

Step 1: Creating the Database Table
Step 2: Creating the Search Form
Step 3: Processing Search Query in Backend

Download Complete Code —

Step 1: Creating the Database Table

create database p4n;
use p4n;

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

INSERT INTO countries (name) VALUES
    ("India"),
    ("United States"),
    ("China"),
    ("Brazil"),
    ("Russia"),
    ("France"),
    ("Germany"),
    ("Japan"),
    ("United Kingdom"),
    ("Canada");

select * from countries;

Get Codes With Pankaj’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Step 2: Creating the Search Form

Create a PHP file named “search-form.php”

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>MySQL Database Search @p4n.in</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-4bw+/aepP/YC94hEpVNVgiZdgIC5+VKNBQNGCHeKRQN+PtmoHDEXuppvnDJzQIu9" crossorigin="anonymous">
<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="px-4 py-5 my-5 text-center">
    <a href="https://www.codeswithpankaj.com/"> <h1 class="display-5 fw-bold text-body-emphasis">Codes With Pankaj</h1><a>
    <div class="col-lg-6 mx-auto">
      <p class="lead mb-4">PHP Live MySQL Database Search Country</p>
      <div class="d-grid gap-2 d-sm-flex justify-content-sm-center">
      <div class="search-box">
        <input type="text" autocomplete="off" placeholder="Search country..." />
        <div class="result"></div>
        </div>
     </div>
    </div>
  </div>

    
</body>
</html>

Step 3: Processing Search Query in Backend

the source code of our “backend-search.php”

<?php
/* MySQL server connection.
MySQL server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "admin", "p4n");
 
// 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);
?>

View Search Form