PHP Ajax Live Search From MySQL Database

Hello PHP developers, ajax live data search using jquery php mysql. In this post, i will teach you how to to build ajax live data search using jquery php mysql without page refresh using ajax jquery.

Also, you can use this code personally and professionally.

Ajax Live Data Search using jQuery PHP MySQL

The following PHP MySQL live data search javascript/jQuery and ajax code will search data from database and display without refresh/reloading whole web page:

  • Step 1 – Create PHP Project Directory
  • Step 2 – Create phpmyadmin MySQL Database and Connecting File
  • Step 3 – Create Ajax Live Search HTML Form
  • Step 4 – Create ajax-search-db.php

Step 1 – Create PHP Project Directory

In step 1, Navigate to your local web server directory. And inside this directory, create one directory. And you can name this folder anything.

Here, I will “demo” the name of this folder. Then open this folder in any text editor (i will use sublime text editor).

Step 2 – Create phpmyadmin MySQL Database and Connecting File

In step 2, you need to create database and table. So run the following sql query to create database and table:

Then create a php file that named mydbCon. Which is used to connect phpmyadmin mysql database to project (demo).

So, now create mydbCon file and add the below given code into your file:

CREATE DATABASE my_db;


CREATE TABLE `customers` (
  `id` int(10) UNSIGNED NOT NULL,
  `fname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Step 3 – Create Ajax Live Search HTML Form

In step 3, create a php file that named ajax-live-search.php. This file will display PHP MySQL live search box.

Here, i will use jQuery ajax to search from database without realoding the whole web page. Now, you need to add the following ajax live search html form code into ajax-live-search.php file:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>php mysql ajax live data search</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">

    <script src="https://code.jquery.com/jquery-3.4.1.js"></script> 

</head>
<body>
<div class="container">
        <div class="row">
            <div class="col-md-12">
                <center><h2>PHP MySQL ajax live data search</h2></center>
            </div>
        </div>
        <br>
        <div class="row">
            <div class="col-md-6 col-md-offset-3">
                <!-- Form data start -->
                <form action="" method="POST" class="form-inline" role="form">
                    <div class="form-group">
                        <label class="" for="">Search a Query : </label>
                        <input type="text" name="search_term" id="search_term" autocomplete="on" placeholder="search user name here...." class="form-control search_term">
                    </div>
                </form>
                <div id="data-container"></div>
                <!-- Form data end -->
                <div class="selected_data_containter" id="selected_data_containter">
                    
                </div>
            </div>
        </div>
    </div>
</body>

<script type="text/javascript">
$('#search_term').keyup(function() {
    var search_term = $(this).val();
    var key = {
        search_term : search_term
    }
    var resp_data_format="";
    $.ajax({
        url:"ajax-search-db.php",
        data : key,
        method : "post",
        dataType : "json",
        success : function(response) {
            for (var i = 0; i < response.length; i++) {
                resp_data_format=resp_data_format+"<li class='select_customer'>"+response[i]+"</li>";
            };
            $("#data-container").html(resp_data_format);
        }
    });
});

$(document).on( "click", ".select_customer", function(){
    var selected_customer = $(this).html();
    $('#search_term').val(selected_customer);
    $('#data-container').html('');
});

</script>
</body>
</html>

Step 4 – Create ajax-search-db.php

In step 4, create new file that named ajax-search-db.php file. Because this is used to search form data into mysql database without realod the web page.

So, add the below code into your ajax-search-db.php file:

<?php
include 'mydbCon.php';

$search_term = mysqli_real_escape_string($dbCon,$_POST['search_term']);

$sql = "SELECT * FROM customers WHERE fname LIKE '$search_term%'";

$query = mysqli_query($dbCon, $sql);

$result = [];

while($data = mysqli_fetch_array($query))
{
	$result[] = $data['fname'];
}
echo json_encode($result);
?>

Recommended PHP Tutorials

Leave a Comment