Ajax Search Data in PHP & MySQL using jQuery

Originally posted @ https://codeanddeploy.com visit and download the sample code: https://codeanddeploy.com/blog/php/ajax-search-data-in-php-mysql-using-jquery

Advanced Laravel SAAS Starter Kit with CRUD Generator - GET YOUR COPY NOW!

In this post, I will show you how to code the ajax search data in PHP & MySQL using jQuery. It is the most important functionality after adding a record. You need to search a specific record by keyword to retrieve the data. We are using my previous code about Simple Employee Management and we will add this functionality. I will show you first the outcome of this function.

Now let's start to code this function just follow my step by step. Don't worry I will provide below the sample source code for you to see it in action.

1. Create Database

You will need to create your database first in any name you want. Just open your command prompt and do the process using MySQL command for easier just use PHPMyAdmin if you have installed it already at your localhost.

2. Create Table

Then run this SQL named "employees" table.

CREATE TABLE `employees` (
  `id` int(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `address` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `employees`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

3. Setup Database Connection

Following code below is our config.php file at available download source code we define our database credentials here.

<?php
    //set the servername
    define("SERVER_NAME", "localhost");
    //set the server username
    define("SERVER_UNAME", "root");
    // set the server password (you must put password here if your using live server)
    define("SERVER_UPASS", "");
    // set the database name
    define("SERVER_DB", "demos");

    // Include functions file
    require_once 'functions.php';

    // Set a variable $db and store db connection
    $db = connectDB();
?>

4. Setting Up Javascript Function

Now we are using our previous javascript function called all() function because that function is an ajax that request all employees record to the server. So here check the original code for you to understand the process.

Old all() function code

function all() 
{
    // Ajax config
    $.ajax({
        type: "GET", //we are using GET method to get all record from the server
        url: 'all.php', // get the route value
        beforeSend: function () {//We add this before send to disable the button once we submit it so that we prevent the multiple click
            ajaxLoader("#employees-list", "show");
        },
        success: function (response) {//once the request successfully process to the server side it will return result here

            // Parse the json result
            response = JSON.parse(response);

            var html = "";
            // Check if there is available records
            if(response.length) {
                html += '<div class="list-group">';
                // Loop the parsed JSON
                $.each(response, function(key,value) {
                    // Our employee list template
                    html += '<a href="#" class="list-group-item list-group-item-action">';
                    html += "<p>" + value.first_name +' '+ value.last_name + " <span class='list-email'>(" + value.email + ")</span>" + "</p>";
                    html += "<p class='list-address'>" + value.address + "</p>";
                    html += "<button class='btn btn-sm btn-primary mt-2' data-toggle='modal' data-target='#edit-employee-modal' data-id='"+value.id+"'>Edit</button>";
                    html += "<button class='btn btn-sm btn-danger mt-2 ml-2 btn-delete-employee' data-id='"+value.id+"' typle='button'>Delete</button>";
                    html += '</a>';
                });
                html += '</div>';
            } else {
                html += '<div class="alert alert-warning">';
                  html += 'No records found!';
                html += '</div>';
            }

            // Insert the HTML Template and display all employee records
            $("#employees-list").html(html);
        },
        complete: function() {
            ajaxLoader("#employees-list", "hide");
        }
    });
}

Now we will modify the code above to support the search function. Kindly see below the code of our updated function.

/**
 * Get all employees with search keyword
 *
 * @param {string} keyword - The form selector
 * @return {any}
 */
function all(keyword) 
{   
    // Ajax config
    $.ajax({
        type: "GET", //we are using GET method to get all record from the server
        url: 'all.php', // get the route value
        data: {keyword : keyword},
        beforeSend: function () {//We add this before send to disable the button once we submit it so that we prevent the multiple click
            ajaxLoader("#employees-list", "show");

            // prevent multiple click for search button
            if(keyword != "" && keyword !== undefined) {
                $("#btnSearchEmployees").attr('disabled', true).html("Processing...");
            }
        },
        success: function (response) {//once the request successfully process to the server side it will return result here

            // Parse the json result
            response = JSON.parse(response);

            var html = "";
            // Check if there is available records
            if(response.length) {
                html += '<div class="list-group">';
                // Loop the parsed JSON
                $.each(response, function(key,value) {
                    // Our employee list template
                    html += '<a href="javascript:void(0)" class="list-group-item list-group-item-action">';
                    html += "<p><input type='checkbox' value='"+value.id+"' class='multi-options-action'>&nbsp;" + value.first_name +' '+ value.last_name + " <span class='list-email'>(" + value.email + ")</span>" + "</p>";
                    html += "<p class='list-address'>" + value.address + "</p>";
                    html += "<button class='btn btn-sm btn-primary mt-2' data-toggle='modal' data-target='#edit-employee-modal' data-id='"+value.id+"'>Edit</button>";
                    html += "<button class='btn btn-sm btn-danger mt-2 ml-2 btn-delete-employee' data-id='"+value.id+"' typle='button'>Delete</button>";
                    html += '</a>';
                });
                html += '</div>';
            } else {
                html += '<div class="alert alert-warning">';
                  html += 'No records found!';
                html += '</div>';
            }

            // Insert the HTML Template and display all employee records
            $("#employees-list").html(html);
        },
        complete: function() {
            ajaxLoader("#employees-list", "hide");

            // prevent multiple click for search button
            if(keyword != "" && keyword !== undefined) {
                $("#btnSearchEmployees").attr('disabled', false).html("Search");
            }
        }
    });
}

As you can see we added a parameter called keyword we will use it to pass to the server and trigger the search function and display what we query. Next, I added this line of code data: {keyword: keyword}, so that if you submit it with search function the keyword or query will also be submitted via ajax. Then I added the following code below also for preventing multiple clicks of the search button.

// prevent multiple click for search button
if(keyword != "" && keyword !== undefined) {
   $("#btnSearchEmployees").attr('disabled', true).html("Processing...");
}

You can see it under beforeSend() function of ajax. And also in complete() function, I added another line of code to remove the search button disabled attribute.

// prevent multiple click for search button
if(keyword != "" && keyword !== undefined) {
   $("#btnSearchEmployees").attr('disabled', false).html("Search");
}

Now you understand already what I change of all() **function. Then let's move on and create another function called **search().

function search() {
    $("#btnSearchEmployees").on("click", function() {
        var keyword = $("#search-keyword").val();

        all(keyword);

    });
}

Then next, we will call the search() function to and load to jQuery.

$(document).ready(function() {
    .
    .
    .
    // Search employee
    search();
    .
    .
    .
});

Now let's modify also our all.php file a function that previously only call all employee's records. Now we will add another function to search if there is a query.

Here is the previous code:

<?php
    // include config file
    require_once 'config.php';

    // SQL Statement
    $sql = "SELECT * FROM employees";

    // Process the query
    $results = $db->query($sql);

    // Fetch Associative array
    $row = $results->fetch_all(MYSQLI_ASSOC);

    // Free result set
    $results->free_result();

    // Close the connection after using it
    $db->close();

    // Encode array into json format
    echo json_encode($row);
?>

Now let's modify it to support search queries. See the modified code below:

<?php
    // include config file
    require_once 'config.php';

    // Check if keyword is existing
    if(isset($_GET['keyword']) && $_GET['keyword'] != ""):

        // Sanitize data
        $request = sanitize($_GET);

        // SQL Statement
        $sql = "SELECT * FROM employees WHERE first_name LIKE '%".$request['keyword']."%' OR last_name LIKE '%".$request['keyword']."%'";
    else:
        // SQL Statement
        $sql = "SELECT * FROM employees";
    endif;


    // Process the query
    $results = $db->query($sql);

    // Fetch Associative array
    $row = $results->fetch_all(MYSQLI_ASSOC);

    // Free result set
    $results->free_result();

    // Close the connection after using it
    $db->close();

    // Encode array into json format
    echo json_encode($row);
?>

As you can see above I added the following line of codes.

// Check if keyword is existing
    if(isset($_GET['keyword']) && $_GET['keyword'] != ""):

        // Sanitize data
        $request = sanitize($_GET);

        // SQL Statement
        $sql = "SELECT * FROM employees WHERE first_name LIKE '%".$request['keyword']."%' OR last_name LIKE '%".$request['keyword']."%'";
    else:
        // SQL Statement
        $sql = "SELECT * FROM employees";
    endif;

This condition will check if the request has a query or a keyword key from $_GET Super Global variable then if existing I sanitize first the data then I perform the query statement to search the record so I'm using MySQL LIKE condition with wildcard '%' from start and end of the keyword so that any keyword query will match.

For more info about SQL LIKE condition kindly visit (https://www.w3schools.com/sql/sql_like.asp)

Advanced Laravel SAAS Starter Kit with CRUD Generator - GET YOUR COPY NOW!

Now we have the full functionality to work our Ajax Search Data in PHP. I hope this tutorial can help you. Kindly visit here https://codeanddeploy.com/blog/php/ajax-search-data-in-php-mysql-using-jquery if you want to download this code.

Happy coding :)