ajax live data search using jquery php mysql

ajax live data search using jquery php mysql

November 12, 2020 Pakainfo php, Ajax, JavaScript, jQuery, Mysql, Mysqli Leave a comment

Today, We want to share with you ajax live data search using jquery php mysql.In this post we will show you php mysql ajax search autocomplete, hear for ajax search box php mysql like google.

ajax live search jquery

Contents

we will give you demo and example for implement.In this post, we will learn about PHP AJAX Live Search Box Autocomplete Using MySQL Database with an example.

Step 1: Create the members’ table in MySQL:

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `mail_address` varchar(80) NOT NULL,
  `membername` varchar(45) DEFAULT NULL,
  `cell_phone` varchar(50) DEFAULT NULL,
  `bio` text,
  `profile_pic` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`name`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2: Create Connection(config.php)

<?php
//Database details
$db_host = 'localhost';
$db_username = 'username';
$db_password = 'password';
$db_name = 'database_name';

//Create connection and select DB
$conn = mysqli_connect($db_host, $db_username, $db_password, $db_name);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

Step : 3 index.php

<!--
Used basic html and bootstrap skeleton from the below link for style purpose which contains bootstrap.css,bootstrap.js,jquery.js
https://www.w3schools.com/bootstrap/bootstrap_get_started.asp
Used bootstrap form elements from below link.
https://www.w3schools.com/bootstrap/bootstrap_forms.asp-->
<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Live Demo: Ajax live data search using PHP and MySQL</title>
        <meta content='Live Demo: Ajax live data search using PHP and MySQL' name='description' />
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">      
        <style>
            .img-box{
                max-height: 300px;
                margin-bottom: 20px;
            }
        </style>
    </head>
    <body>
        <div class="container" style="max-width:800px;margin:0 auto;margin-top:50px;">  
            <div>
                <h2 style="margin-bottom:50px;">Live Demo: Ajax live data search using PHP and MySQL</h2>
                <div>
                    <div style="margin-bottom:30px;"><input type="text" class="form-control" id="query_find" placeholder="Search"/></div>
                    <table class="table table-hover">
                        <thead>
                            <tr>
                                <th>Name</th>
                                <th>Email</th>
                                <th>Membername</th>
                                <th>Mobile No.</th>
                            </tr>
                        </thead>
                        <tbody id="tbl_body">
                            <?php
                            $sql = mysqli_query($conn, "SELECT * FROM members ORDER BY id LIMIT 20");
                            while ($row = mysqli_fetch_array($sql)) {
                                ?>
                                <tr>
                                    <td><?php echo $row['name']; ?></td>
                                    <td><?php echo $row['mail_address']; ?></td>
                                    <td><?php echo $row['membername']; ?></td>
                                    <td><?php echo $row['cell_phone']; ?></td>
                                </tr>
                            <?php } ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
        <script>
            $(document).on("keyup", "#query_find", function () {
                var query_find = $("#query_find").val();
                $.ajax({
                    url: 'getData.php',
                    type: 'POST',
                    data: {query_find: query_find},
                    success: function (data) {
                        $("#tbl_body").html(data);
                    }
                });
            });
        </script>      
    </body>
</html>

Step 4: getData.php

<?php

require_once 'inc/config.php';
if (isset($_POST['query_find'])) {
    $query_find = mysqli_real_escape_string($conn, $_POST['query_find']);
    $query = mysqli_query($conn, "SELECT * FROM members where name like '%$query_find%' or mail_address like '%$query_find%' or membername like '%$query_find%' or cell_phone like '%$query_find%' order by id limit 20");
    $output = '';
    if ($query->num_rows > 0) {
        while ($row = mysqli_fetch_array($query)) {
            $output .= '<tr>
    <td>' . $row['name'] . '</td>
    <td>' . $row['mail_address'] . '</td>
    <td>' . $row['membername'] . '</td>
    <td>' . $row['cell_phone'] . '</td>
  </tr>';
        }
    } else {
        $output = '
  <tr>
    <td colspan="4"> No result found. </td>   
  </tr>';
    }
    echo $output;
}
?>

I hope you get an idea about ajax live data search using jquery php mysql.
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.