Advertisement

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

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.

Before you start, you can download the source code or see the 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;


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();
	}
}


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();
}


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";
}


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.

Live Search MYSQL Table, User Table

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();
}


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.

Author SupunKavinda