how to fetch data from database in php mysql using search button?

Today, We want to share with you search in php mysql with examples.In this post we will show you how to fetch data from database in php using search button, hear for how to search and filter data in html table using php and mysql database [ with source code ] we will give you demo and example for implement.In this post, we will learn about PHP AJAX Live Search Box Autocomplete with an example.

Ajax Live Database Search

Creating the Database Table

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

Creating the Search Form

index.php

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Live MySQL Database Search - www.pakainfo.com</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;
    }
    .response{
        position: absolute;        
        z-index: 999;
        top: 100%;
        left: 0;
    }
    .search-box input[type="text"], .response{
        width: 100%;
        box-sizing: border-box;
    }
    /* Formatting response items */
    .response p{
        margin: 0;
        padding: 7px 10px;
        border: 1px solid #CCCCCC;
        border-top: none;
        cursor: pointer;
    }
    .response 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(){
        
        var inputVal = $(this).val();
        var responseDropdown = $(this).siblings(".response");
        if(inputVal.length){
            $.get("backend-search.php", {term: inputVal}).done(function(data){
              
                responseDropdown.html(data);
            });
        } else{
            responseDropdown.empty();
        }
    });
    
    // Set search input value on click of response item
    $(document).on("click", ".response p", function(){
        $(this).parents(".search-box").find('input[type="text"]').val($(this).text());
        $(this).parent(".response").empty();
    });
});
</script>
</head>
<body>
    <div class="search-box">
        <input type="text" autocomplete="off" placeholder="Search country..." />
        <div class="response"></div>
    </div>
</body>
</html>

Processing Search Query in Backend

backend-search.php

<?php

$link = mysqli_connect("localhost", "root", "F9898Jayshd", "ap_v1_v23_v1");
 
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
if(isset($_REQUEST["term"])){

    $sql = "SELECT * FROM brands WHERE name LIKE ?";
    
    if($stmt = mysqli_prepare($link, $sql)){

        mysqli_stmt_bind_param($stmt, "s", $param_term);
        

        $param_term = $_REQUEST["term"] . '%';
        

        if(mysqli_stmt_execute($stmt)){
            $response = mysqli_stmt_get_result($stmt);
            

            if(mysqli_num_rows($response) > 0){

                while($row = mysqli_fetch_array($response, 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);
        }
    }
     

    mysqli_stmt_close($stmt);
}
 
mysqli_close($link);
?>

php mysql search database and display results

Example

<?php
    $con= new mysqli("localhost","root","","Meembers_lisat");
    $name = $_post['search'];
    //$query = "SELECT * FROM members
   // WHERE nic_nm LIKE '%{$name}%' OR profile_nm LIKE '%{$name}%'";

    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }

$response = mysqli_query($con, "SELECT * FROM members
    WHERE nic_nm LIKE '%{$name}%' OR profile_nm LIKE '%{$name}%'");

while ($row = mysqli_fetch_array($response))
{
        echo $row['nic_nm'] . " " . $row['profile_nm'];
        echo "<br>";
}
    mysqli_close($con);
    ?>

I hope you get an idea about Advanced Search using PHP.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

Read Also:  ajax live data search using jquery php mysql

About Pakainfo

I am Jaydeep Gondaliya, a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.

View all posts by Pakainfo →

Leave a Reply

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