Live Search with AJAX, PHP, and MYSQL

Have you ever seen a live search? Type something in the top bar of your browser. It shows suggestions when you are typing. This technique is called Live Search. To understand the concept first, be a live search.

Now think you are a live search...

The user is typing in your text box. You have to get the text box's value and search for suggestions.
Therefore, you have to send a request to the server to get suggestions.
Then, you will get a response back from the server.
You have to process the response data and show it to the user.
User says, "that's not enough".
Then, you have to load more result to satisfy the user.
You do this each and every time user types something in your text box.

That's the real way to do it. Always keep the concept in your mind until the tutorial is finished. Let's discuss how to do it programmatically with AJAX and PHP.

Simply, we first send an AJAX request to a specific PHP AJAX request handler which is the live searcher. The handler connects with MYSQL database and searches for matches. Then, the live searcher sends results back.

I have divided this tutorial into following subtopics.

Creating the AJAX Part
Connecting with PHP and MYSQL
Adding some CSS

Before you start, you can download the source code or see the demo.

Download Source Code
Live Demo

Creating the AJAX Part

index.html will have the search box and result container.


<html>
<body>

	<input type="text" name="username" id="textbox" />
	<div id="result"></div>

	<script type="text/javascript">
		// Javascript code goes here
	</script>

</body>
</html>

Declaring the Javascript variables


var textBox = document.getElementById('textbox'),
	resultContainer = document.getElementById('result')

var ajax = null;
var loadedUsers = 0;

textBox and resultContainer save DOM objects selected by the id attribute.
The instance of XMLHttpRequest() will be saved in ajax.
loadedUsers saves the number of users loaded and showed. This variable is useful when loading more suggestions.

Adding onkeyup event to the search box


textBox.onkeyup = function() {
	var val = this.value;
	val = val.replace(/^\s|\s $/, "");

	if (val !== "") {	
		searchForData(val);
	} else {
		clearResult();
	}
}

This anonymous function is executed when the user releases the key after pressing it. Note that onkeyup is better to use than onkeydown.
Inside the function, this refers to textBox.
First, it takes the value of the text box. This is the string that user has typed in.
Then, it removes extra space in the beginning and end of the string.
If the value is not empty, it searches for data, otherwise cleans the results container.
searchForData() and clearResult() functions will be created in the next step.

Sending the AJAX request

Here we create the searchForData() function.


function searchForData(value, isLoadMoreMode) {
	if (ajax && typeof ajax.abort === 'function') {
		ajax.abort(); // abort previous requests
	}

	if (isLoadMoreMode !== true) {
		clearResult();
	}

	ajax = new XMLHttpRequest();
	ajax.onreadystatechange = function() {
		if (this.readyState === 4 && this.status === 200) {
			try {
				var json = JSON.parse(this.responseText)
			} catch (e) {
				noUsers();
				return;
			}

			if (json.length === 0) {
				if (isLoadMoreMode) {
					alert('No more to load');
				} else {
					noUsers();
				}
			} else {
				showUsers(json);
			}
		}
	}
	ajax.open('GET', 'search.php?username='   value   '&startFrom='   loadedUsers , true);
	ajax.send();
}

This function has two parameters, value and isLoadMoreMode. value is the string that user typed in. If isLoadMoreMode is set to true, the function won't clean the result.
If an Ajax request is previously sent, it will be aborted because it isn't useful anymore.
Then, result container is cleaned if the function is not in load more mode.
Then, an Ajax request is sent to search.php with the get values, username which contains the string and startFrom which tells the server to where to start matching from. (As an example, think this function has loaded 6 user suggestions. Then, the user hits load more button. In that case, the server has to skip first 6 results and send other)
When the request was successful, it tries to parse the response as JSON string. "No Users" message will be shown on a failure.
Now, json variable holds an array of data.
If the length of the array is 0 (No users found), it shows "No user" message. ("No more to load" in load more mode)
Else, showUsers() function is called passing the JSON data in.

Other helping functions


function showUsers(data) {
	function createRow(rowData) {
		var wrap = document.createElement("div");
		wrap.className = 'row'

		var name = document.createElement("span");
		name.innerHTML = rowData.name;

		var picture = document.createElement("img");
		picture.src = rowData.picture;

		wrap.onclick = function() {
			alert(rowData.description);
		}

		wrap.appendChild(picture);
		wrap.appendChild(name);

		resultContainer.appendChild(wrap);
	}	
	for (var i = 0, len = data.length; i < len; i  ) {
		var userData = data[i];
		createRow(userData);
	}

	var loadMoreButton = document.createElement("span");
	loadMoreButton.innerHTML = "Load More";
	loadMoreButton.onclick = function() {
		searchForData(textBox.value, true);
		this.parentElement.removeChild(this);
	}
	resultContainer.appendChild(loadMoreButton);

	loadedUsers  = len;
}

function clearResult() {
	resultContainer.innerHTML = "";
	loadedUsers = 0;
}

function noUsers() {
	resultContainer.innerHTML = "No Users";
}

The showUsers() function converts the array of data to DOM. A row is created for each user.
In a row, the name and picture of the user will be shown. The description will be shown when we click on a row.
Then, load more button is appended. The searchForData() function is called in load more mode by clicking on this button.
Finally, increase the number of loaded users with the length of the array.
clearResult() function clears the result container.
noUsers() function shows "No Users" message in the result container.

Connecting with PHP and MYSQL

Creating the MYSQL table

Before performing a search we should have a MYSQL table with user details. Execute the following code in your MYSQL database to create a new table with four columns: id, name, picture, and description.


CREATE TABLE users (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(60) NOT NULL,
	picture VARCHAR(1000) NOT NULL,
	description VARCHAR(1000)  NOT NULL
)

Then, insert some random values to the table in the following way. Here you don't need to add a value to id as it is auto increment.


INSERT INTO 
users (name, picture, description) 
VALUES 
('name', 'picture url', 'description')

Finally, you will have a MYSQL table like this.

Creating the live search handler

In this step, I will show you how to create search.php to perform search and send suggestions in JSON format.

1. Connecting to MYSQL

First, we have to make a connection between PHP and MYSQL. Following code does it easily. Mind to replace $host, $user, $password and $database with your database credentials.


<?php

$host = 'localhost';
$user = 'root';
$password = '';
$database = 'dev_testing';

$mysqli = new mysqli($host, $user, $password, $database);

2. Validating the AJAX variables

Never trust user inputs! Do a proper validation for every $_POST and $_GET variable before using them.


$username = $_GET['username'];
$startFrom = $_GET['startFrom'];

$username = trim(htmlspecialchars($username));
$startFrom = filter_var($startFrom, FILTER_VALIDATE_INT);

3. Running the MYSQL query with a prepared statement

Always use prepared statement when you have values to input into the query, as it prevents SQL injection.


// make username search friendly
$like = '%' . strtolower($username) . '%';

// open new mysqli prepared statement
$statement = $mysqli -> prepare('
	SELECT name, picture, description 
	FROM users 
	WHERE lower(name) LIKE ?  
	ORDER BY INSTR(title, ?), title
	LIMIT 6 OFFSET ?'
);

if (
	$statement &&
	$statement -> bind_param('ssi', $like, $username, $startFrom) &&
	$statement -> execute() &&
	$statement -> store_result() &&
	$statement -> bind_result($name, $picture, $description)
) {
	$array = [];
	while ($statement -> fetch()) {
		$array[] = [
			'name' => $name,
			'picture' => $picture,
			'description' => $description
		];
	}
	echo json_encode($array);
	exit();
}

First, $username is changed to be search-friendly. % matches any character in MYSQL.
Then, $mysqli -> prepare() opens new prepared statement. In the query, name, picture and description are selected where the name is like %username%. Here, the query is case insensitive. The strtolower() function turns $username into lowercase and lower() function in MYSQL turns name of the row into lowercase. This is a very simple way among many ways to perform a case-insensitive search. However, If you need a case-sensitive search remove both of above functions.
ORDER BY statement runs a major roll in this query. It orders the result according to the best match. For example, if you search for wa, water will be before coward.
$statement stores the statement instance. Then, we check whether it's false. If not, bind_param() function is called with 3 parameters: Data types, username and startFrom (OFFSET) value. Then, the statement is executed and stored. Results are bound to variables.
Then, a new array is created ($array).
Another new associative array which has three keys (name, picture, description) is created while looping through the result of the statement.

So, full code of search.php will be like following.


<?php

$host = 'localhost';
$user = 'root';
$password = '';
$database = 'dev_testing';

$mysqli = new mysqli($host, $user, $password, $database);

$username = $_GET['username'];
$startFrom = $_GET['startFrom'];

$username = trim(htmlspecialchars($username));
$startFrom = filter_var($startFrom, FILTER_VALIDATE_INT);

$like = '%' . strtolower($username) . '%';
$statement = $mysqli -> prepare('
	SELECT name, picture, description 
	FROM users 
	WHERE lower(name) LIKE ?  
	ORDER BY INSTR(title, ?), title
	LIMIT 6 OFFSET ?'
);

if (
	$statement &&
	$statement -> bind_param('ssi', $like, $username, $startFrom) &&
	$statement -> execute() &&
	$statement -> store_result() &&
	$statement -> bind_result($name, $picture, $description)
) {
	$array = [];
	while ($statement -> fetch()) {
		$array[] = [
			'name' => $name,
			'picture' => $picture,
			'description' => $description
		];
	}
	echo json_encode($array);
	exit();
}

Adding some CSS

Let's make our live search more beautiful with CSS.


#textbox {
	padding:10px;
	border-radius:5px;
	border:0;
	box-shadow:0 0 4px 0 rgba(0,0,0,0.2)
}
.row img {
	width:40px;
	height:40px;
	border-radius:50%;
	vertical-align:middle;
}
.row {
	padding:10px;
} 
.row:hover {
	background-color:#eee
}

Conclusion

Many of websites have a live search for many reasons. Here I showed you how to create your own live search with AJAX, PHP, and MYSQL. Also, we added some CSS to our project. If you need to learn these PHP and Javascript statement one by one, I recommend you to download the source code which I have linked at the beginning of this article. It has plenty of comments. So that, you would understand it better.

Never forget to leave out a comment below. Thank you very much for reading.

Tagged: PHP

You can connect with me on Twitter or Linkedin.

Written On

May 19, 2018

Last Updated On

May 19, 2018