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.
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>
var textBox = document.getElementById('textbox'),
resultContainer = document.getElementById('result')
var ajax = null;
var loadedUsers = 0;
textBox.onkeyup = function() {
var val = this.value;
val = val.replace(/^\s|\s $/, "");
if (val !== "") {
searchForData(val);
} else {
clearResult();
}
}
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();
}
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";
}
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.
In this step, I will show you how to create search.php to perform search and send suggestions in JSON format.
1. Connecting to MYSQLFirst, 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 variablesNever 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 statementAlways 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();
}
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
}
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.