Implementing AJAX Live Search with PHP and MySQL
AJAX (Asynchronous JavaScript and XML) is a technique used to create dynamic web applications. It allows web pages to be updated asynchronously by exchanging data with a web server behind the scenes. This means that web pages can be updated without requiring a full page reload. AJAX Live Search is a feature that provides real-time search results as users type in a search box. This improves user experience by allowing instant feedback and suggestions. The following steps will guide you through implementing an AJAX live search using PHP and MySQL
Prerequisite
Steps to Create PHP Application Using MySQL Database for AJAX Search
Step 1: Create the Database
Create a new MySQL database named test_db to store your data.
CREATE DATABASE test_db; Step 2: Create the Table
Within the test_db database, create a table named users to hold user names.
USE test_db;
CREATE TABLE users (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(255) NOT NULL
);
Step 3: Insert Sample Data
Add sample data to the users table for testing purposes.
INSERT INTO users (user_name) VALUES ('Alice Johnson');
INSERT INTO users (user_name) VALUES ('Bob Smith');
INSERT INTO users (user_name) VALUES ('Charlie Brown');
INSERT INTO users (user_name) VALUES ('David Wilson');
INSERT INTO users (user_name) VALUES ('Emily Davis');
Step 4: Create Project Files
Create the necessary files for your project. The file structure will look like this:
Note: Place the live-search directory in the htdocs folder of XAMPP or the equivalent directory for your local server.
Step 5: Create HTML File
This HTML file includes a search input and a table to display the results. It uses jQuery to handle the search functionality. Initially, all records are loaded into the table, and as the user types in the search box, AJAX requests fetch matching results from the server.
<!-- live-search/index.html --->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>AJAX Live Search</title>
<link rel="stylesheet" href="style.css">
<script src=
"https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body>
<h1>User List</h1>
<input type="text" id="search" placeholder="Search...">
<table id="result">
<thead>
<tr>
<th>Name</th>
</tr>
</thead>
<tbody>
<!-- Initial data will be loaded here -->
</tbody>
</table>
<script>
$(document).ready(function () {
// Load all records initially
loadAllRecords();
// Perform search
$('#search').on('keyup', function () {
var query = $(this).val();
if (query.length > 2) {
$.ajax({
url: 'search.php',
type: 'POST',
data: { search: query },
success: function (data) {
$('#result tbody').html(data);
}
});
} else {
loadAllRecords();
}
});
function loadAllRecords() {
$.ajax({
url: 'search.php',
type: 'POST',
data: { search: '' },
success: function (data) {
$('#result tbody').html(data);
}
});
}
});
</script>
</body>
</html>
Step 6: Create PHP File
This PHP script handles the AJAX requests and retrieves data from the MySQL database based on the search query. This PHP script connects to the MySQL database, retrieves records based on the search query, and returns the results as HTML table rows. If no search query is provided, it returns all records.
//live-search/search.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test_db";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if (isset($_POST['search'])) {
$search = $conn->real_escape_string($_POST['search']);
$sql = $search ?
"SELECT user_name FROM users WHERE user_name LIKE '%$search%'" :
"SELECT user_name FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row['user_name'] . "</td></tr>";
}
} else {
echo "<tr><td colspan='1'>No results found.</td></tr>";
}
}
$conn->close();
?>
Step 7: Create CSS File
This file provides basic styling for the HTML table and search input. The CSS file styles the search input and table to improve readability and aesthetics.
/*live-search/style.css */
body {
font-family: Arial, sans-serif;
margin: 20px;
}
input[type="text"] {
width: 300px;
padding: 10px;
font-size: 16px;
}
table {
width: 100%;
border-collapse: collapse;
margin-top: 10px;
}
th,
td {
border: 1px solid #ddd;
padding: 8px;
}
th {
background-color: #f4f4f4;
}
Step 8: Run the Project
- Place these files in the web server root directory (e.g., htdocs for XAMPP).
- Start the XAMPP server, click on start of Apache and MySQL.
- Open a web browser.
- Go to http://localhost/live-search/index.html
Output:
