Wednesday, 9 June 2021

Ajax Live Data Search using JavaScript with PHP

In this post you can find tutorial on How to Create Ajax Live Mysql Database search using JavaScript with PHP script. With the help of this tutorial you can learn how to implement Ajax Live data search functionality in your HTML web page using javaScript without using any extra library like jQuery. So from HTML web page you can search mysql database using PHP script with the help of Ajax.

If you want to improve your web application UI then at that time we have to reqire search database data without reloading of entire web page. So for get the solution of this problem, here you can see how can we have implement ajax live data search using javaScript with PHP script. From this post you can see how can we easy way to live search with Mysql using javaScript with Ajax.

Ajax Live Database Search using javaScript

If you are looking for Live Data search functionality using pure vanilla javaScript, then you can come on right place because in this tutorial, we have covered topic simple live database search functionality using javaScript with Ajax and PHP, in which search results will be start displaying, when user has start write in search textbox.

In this tutorial we have are going to make live search box which will search data in mysql table and display result on web page without refresh of web page, because here we have use Ajax in javaScript.

Ajax Live Data Search using javaScript with PHP

View Demo

Download Source Code

Step 1: Create Database Table

For create table in mysql database, we need to execute following SQL query which will create post table in your MySQL database.


--
-- Table structure for table `post`
--

CREATE TABLE `post` (
  `id` mediumint(8) UNSIGNED NOT NULL,
  `post_title` text,
  `post_description` text,
  `author` varchar(255) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  `post_image` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `post`
--
ALTER TABLE `post`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `post`
--
ALTER TABLE `post`
  MODIFY `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT;

Once table has been created then you have to insert some data using the SQL INSERT statement. After inserting data into MySQL table then able you can perform live database search operation using pure vanilla javaScript.

Step2: Create Search Form & Table for Load Data

After your MySQL database is ready, then we have to proceed to write HTML code and javaScript code. First we have to create web interface which will allows user to perform live search functionality.

Under this tutorial, we will write HTML and javaScript code under index.html file, which source code you can find below.

index.html


<!DOCTYPE HTML>
<html>
<head>
	<meta charset="utf-8" />
	<title>Live Mysql Data Search using javaScript with PHP</title>
	<meta name="viewport" content="width=device-width, initial-scale=1" />
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
</head>
<body>
    <div class="container">
    	<h2 class="text-center mt-4 mb-4">Live Mysql Data Search using javaScript with PHP</h2>
    	
    	<div class="card">
    		<div class="card-header">
    			<div class="row">
    				<div class="col-md-6">Sample Data</div>
    				<div class="col-md-3 text-right"><b>Total Data - <span id="total_data"></span></b></div>
    				<div class="col-md-3">
    					<input type="text" name="search" class="form-control" id="search" placeholder="Search Here" onkeyup="load_data(this.value);" />
    				</div>
    			</div>
    		</div>
    		<div class="card-body">
    			<table class="table table-bordered">
    				<thead>
    					<tr>
    						<th width="5%">#</th>
    						<th width="35%">Post Title</th>
    						<th width="60%">Description</th>
    					</tr>
    				</thead>
    				<tbody id="post_data"></tbody>
    			</table>
    		</div>
    	</div>
    	
    </div>
</body>
</html>

<script>

load_data();

function load_data(query = '')
{
	var form_data = new FormData();

	form_data.append('query', query);

	var ajax_request = new XMLHttpRequest();

	ajax_request.open('POST', 'process_data.php');

	ajax_request.send(form_data);

	ajax_request.onreadystatechange = function()
	{
		if(ajax_request.readyState == 4 && ajax_request.status == 200)
		{
			var response = JSON.parse(ajax_request.responseText);

			var html = '';

			var serial_no = 1;
			if(response.length > 0)
			{
				for(var count = 0; count < response.length; count++)
				{
					html += '<tr>';
					html += '<td>'+serial_no+'</td>';
					html += '<td>'+response[count].post_title+'</td>';
					html += '<td>'+response[count].post_description+'</td>';
					html += '</tr>';
					serial_no++;
				}
			}
			else
			{
				html += '<tr><td colspan="3" class="text-center">No Data Found</td></tr>';
			}
			document.getElementById('post_data').innerHTML = html;
			document.getElementById('total_data').innerHTML = response.length;
		}
	}
}
	

</script>

In above code, first we have create Search form in which you can type their search query and below this form we have create one table under this table we will load post table data when page has been load by using javaScript.

Below HTML code we have write javaScript code and under this we have create one load_data(query = '') function. This function will be called when page has been load then at that time then it will received all post table data in JSON format and convert that data into HTML format and display on this page. And when user has type something in search textbox, then also this function will be called and it will display only filter data on web page without refresh of web page. This is because, we have called this function under search textbox onkeyup="load_data(this.value);" attribute, so when user type something then this javascript function will called and it will display filter data on web page.

Step 3: Processing Search Query at PHP Script

Now we have come on backend PHP script code which we will write under process_data.php file. Under this file we will write PHP script which will search MySQL database data based on query string which user has send by the Ajax request and this PHP script send data back to ajax request in JSON string fromat which has been display in browser to user side.

process_data.php


<?php

//process_data.php

if(isset($_POST["query"]))
{
	$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");

	$data = array();

	if($_POST["query"] != '')
	{
		$condition = preg_replace('/[^A-Za-z0-9\- ]/', '', $_POST["query"]);
		$condition = trim($condition);
		$condition = str_replace(" ", "%", $condition);

		$sample_data = array(
			':post_title'		=>	'%' . $condition . '%',
			':post_description'	=>	'%' . $condition . '%'
		);

		$query = "
		SELECT post_title, post_description 
		FROM post 
		WHERE post_title LIKE :post_title 
		OR post_description LIKE :post_description 
		ORDER BY id DESC
		";

		$statement = $connect->prepare($query);

		$statement->execute($sample_data);

		$result = $statement->fetchAll();

		$replace_array_1 = explode("%", $condition);

		foreach($replace_array_1 as $row_data)
		{
			$replace_array_2[] = '<span style="background-color:#'.rand(100000, 999999).'; color:#fff">'.$row_data.'</span>';
		}

		foreach($result as $row)
		{
			$data[] = array(
				'post_title'			=>	str_ireplace($replace_array_1, $replace_array_2, $row["post_title"]),
				'post_description'		=>	str_ireplace($replace_array_1, $replace_array_2, $row["post_description"])
			);
		}
	}
	else
	{
		$query = "
		SELECT post_title, post_description 
		FROM post 
		ORDER BY id DESC
		";

		$result = $connect->query($query);

		foreach($result as $row)
		{
			$data[] = array(
				'post_title'			=>	$row["post_title"],
				'post_description'		=>	$row["post_description"]
			);
		}
	}

	echo json_encode($data);
}

?>

Under this PHP script first we have make database connection and after making database connection this script check ajax request has been received from fetch all data from post table or search query has been send for fetch filter data from database.

Suppose Ajax request has been received for fetch all data from Mysql database then here we have write SELECT query for fetch all data from post and send back data to Ajax request in JSON format.

But suppose Ajax request has been receive for get filter data from MySQL database. So in this script first it has clean search query by using preg_replace() function for prevent SQL Injection and then after write SELECT query with LIKE statement for search data from post MySQL table and return back data to Ajax request in JSON format.

Conclusion

In this post, you have learned how to create live search in PHP with MySQL database table using javaScript and Ajax.

If you want to get complete source with .sql file, so please write your email address in comment box. We will send you complete source code file at your define email address.

View Demo

Download Source Code