Ajax Search Data in PHP & MySQL using jQuery
Tags: javascript, mysql, php, jquery
Published: 2022-03-23 06:15:01

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wyjyev8tz3r8vi2w3on1.png)](https://1.envato.market/5g5QAn)

[Advanced Laravel SAAS Starter Kit with CRUD Generator - GET YOUR COPY NOW!](https://1.envato.market/5g5QAn)

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.

![ajax-search-data-in-php-mysql-using-jquery](https://ecn-storage.s3.us-west-2.amazonaws.com/articles/ajax-search-data-in-php-mysql-using-jquery-QqUGVbPSwJx980.webp?ctm=1628250966)

![ajax-search-data-in-php-mysql-using-jquery](https://ecn-storage.s3.us-west-2.amazonaws.com/articles/ajax-search-data-in-php-mysql-using-jquery-RYHlqhA7uRx980.webp?ctm=1628251027)

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.

```php
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
<?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**


```php
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.

```php
/**
 * 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.

```php
// 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.

```php
// 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()**.

```php
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.


```php
$(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
<?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
<?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.

```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;
```


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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wyjyev8tz3r8vi2w3on1.png)](https://1.envato.market/5g5QAn)

[Advanced Laravel SAAS Starter Kit with CRUD Generator - GET YOUR COPY NOW!](https://1.envato.market/5g5QAn)

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 :)