In today's fast-paced world, users expect web applications to be responsive and efficient. One of the ways to achieve this is by implementing an Ajax live search feature on your website. This feature allows users to search for content on your website without having to reload the page. In this article, we will show you how to implement an Ajax live search feature using Node.js and MySQL.
Node.js is an open-source, cross-platform JavaScript runtime environment that allows developers to build server-side applications with JavaScript. MySQL is a popular open-source relational database management system that is widely used in web development. Together, they provide a powerful combination for building web applications.
To get started, you will need to have Node.js and MySQL installed on your machine. You will also need to have a basic understanding of JavaScript, HTML, and CSS.
Step 1: Setting up the Database
The first step is to set up the database. You will need to create a table to store the data that will be searched. For this example, we will create a table called "customers" with columns for "customer_id", "customer_first_name", "customer_last_name", "customer_email" and "customer_gender". Below you can find .sql script for create customers table and it will insert some sample data into that table.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `customers`
--
CREATE TABLE `customers` (
`customer_id` int(11) NOT NULL,
`customer_first_name` varchar(200) NOT NULL,
`customer_last_name` varchar(200) NOT NULL,
`customer_email` varchar(300) NOT NULL,
`customer_gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `customers`
--
INSERT INTO `customers` (`customer_id`, `customer_first_name`, `customer_last_name`, `customer_email`, `customer_gender`) VALUES
(1, 'Mathilda', 'Garnam', 'mgarnam0@rediff.com', 'Female'),
(2, 'Tymon', 'McEniry', 'tmceniry1@free.fr', 'Male'),
(3, 'Margarette', 'Danilyak', 'mdanilyak2@joomla.org', 'Female'),
(4, 'Ermanno', 'Huebner', 'ehuebner3@wikipedia.org', 'Male'),
(5, 'Agnes', 'Barabich', 'abarabich4@delicious.com', 'Female'),
(6, 'Hamil', 'De Zuani', 'hdezuani5@hostgator.com', 'Male'),
(7, 'Corrie', 'Althorpe', 'calthorpe6@themeforest.net', 'Female'),
(8, 'Ricard', 'Dumelow', 'rdumelow7@china.com.cn', 'Male'),
(9, 'D\'arcy', 'Crayton', 'dcrayton8@furl.net', 'Male'),
(10, 'Kayle', 'Stonier', 'kstonier9@thetimes.co.uk', 'Female'),
(11, 'Julissa', 'Bogeys', 'jbogeysa@w3.org', 'Female'),
(12, 'Saul', 'Hyam', 'shyamb@senate.gov', 'Male'),
(13, 'Saleem', 'Pettengell', 'spettengellc@si.edu', 'Male'),
(14, 'Ives', 'Osmon', 'iosmond@forbes.com', 'Male'),
(15, 'Wendie', 'Acton', 'wactone@army.mil', 'Female'),
(16, 'Ches', 'Redd', 'creddf@un.org', 'Male'),
(17, 'Sarine', 'Cossum', 'scossumg@hubpages.com', 'Female'),
(18, 'Farly', 'Arsmith', 'farsmithh@linkedin.com', 'Male'),
(19, 'Althea', 'Tavernor', 'atavernori@printfriendly.com', 'Female'),
(20, 'Gayla', 'Billes', 'gbillesj@miitbeian.gov.cn', 'Female'),
(21, 'Teodoor', 'Shreenan', 'tshreenank@hp.com', 'Male'),
(22, 'Rich', 'Frusher', 'rfrusherl@cbc.ca', 'Male'),
(23, 'Lissie', 'Glazier', 'lglazierm@elpais.com', 'Female'),
(24, 'Glyn', 'Dealey', 'gdealeyn@unesco.org', 'Male'),
(25, 'Kathy', 'Swansborough', 'kswansborougho@amazon.de', 'Female'),
(26, 'Dudley', 'Hopkyns', 'dhopkynsp@mozilla.com', 'Male'),
(27, 'Byran', 'Ealles', 'beallesq@engadget.com', 'Male'),
(28, 'Kenon', 'MacGray', 'kmacgrayr@stumbleupon.com', 'Male'),
(29, 'Sibilla', 'Norres', 'snorress@smugmug.com', 'Female'),
(30, 'Melita', 'Redman', 'mredmant@a8.net', 'Female'),
(31, 'Ki', 'Brydson', 'kbrydsonu@google.ca', 'Female'),
(32, 'Bernardine', 'Follis', 'bfollisv@unc.edu', 'Female'),
(33, 'Farleigh', 'Sudy', 'fsudyw@unc.edu', 'Male'),
(34, 'Belle', 'Dearness', 'bdearnessx@google.co.uk', 'Female'),
(35, 'Onfre', 'Kee', 'okeey@archive.org', 'Male');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
ADD PRIMARY KEY (`customer_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;
Step 2: Setting up the Server
Next, we will set up the server using Node.js. We will use the Express framework to handle the HTTP requests and responses. You can install Express using npm, the package manager for Node.js, by running the following command in your terminal:
cd f:
In my computer, We will create node application in f: drive, so we have goes into this f: drive.
cd node
After this we have goes into node in which we will create Ajax Live data Search Node Application.
mkdir ajax_live_data_search
This command will create ajax_live_data_search directory under node directory.
cd ajax_live_data_search
After this we have goes inti this ajax_live_data_search directory in which we will download different node module.
npm install express mysql
This command will download Node Express Module and MySQL Module under this ajax_live_data_search directory.
After installing Express, create a new file called "app.js" and add the following code:
app.js
const express = require('express');
const mysql = require('mysql');
const app = express();
const port = 3000;
const pool = mysql.createPool({
connectionLimit : 10,
host : 'localhost',
user : 'root',
password : '',
database : 'testing'
});
app.get('/', (request, response) => {
response.sendFile(__dirname + '/index.html');
});
app.listen(port, () => {
console.log(`Server listening on port ${port}`);
});
This code sets up a basic Express server that listens on port 3000. You can test that the server is working by running "node app.js" in your terminal and navigating to "http://localhost:3000" in your browser and it will load HTML content of index.html file.
Step 3: Implementing the Ajax Live Search
Now we will implement the Ajax live search feature. We will use Vanilla JavaScript to make Ajax requests to the server and update the search results in real-time. You can include Vanilla JavaScript in your index.html file by adding the following code:
index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Ajax Live Data Search in Node.js with MySql</title>
<link href="https://getbootstrap.com/docs/5.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
</head>
<body>
<div class="container mt-5 mb-5">
<h1 class="text-primary text-center"><b>Ajax Live Data Search in Node.js with MySql</b></h1>
<div class="mt-3 mb-3">
<div class="card">
<div class="card-header">Customer Data</div>
<div class="card-body">
<div class="mb-3">
<input type="text" id="search" placeholder="Search..." class="form-control" autocomplete="off">
<table class="table table-bordered mt-3">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Gender</th>
</tr>
</thead>
<tbody id="results">
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
<script type="text/javascript">
const searchInput = document.querySelector('#search');
const results_body = document.querySelector('#results');
load_data();
function load_data(query = '')
{
const request = new XMLHttpRequest();
request.open('GET', `/search?q=${query}`);
request.onload = () => {
const results = JSON.parse(request.responseText);
let html = '';
if(results.length > 0)
{
results.forEach(result => {
html += `
<tr>
<td>`+result.customer_id+`</td>
<td>`+result.customer_first_name+`</td>
<td>`+result.customer_last_name+`</td>
<td>`+result.customer_email+`</td>
<td>`+result.customer_gender+`</td>
</tr>
`;
});
}
else
{
html += `
<tr>
<td colspan="5" class="text-center">No Data Found</td>
</tr>
`;
}
results_body.innerHTML = html;
};
request.send();
}
searchInput.addEventListener('input', () => {
const query = searchInput.value;
load_data(query);
});
</script>
This code listens for keyup events on the search input field and makes an Ajax request to the server with the search query. The server responds with a JSON object containing the search results, which are then displayed in the search results list.
To handle the Ajax requests on the server, add the following code to "app.js":
app.js
app.get('/search', (request, response) => {
const query = request.query.q;
var sql = '';
if(query != '')
{
sql = `SELECT * FROM customers WHERE customer_first_name LIKE '%${query}%' OR customer_last_name LIKE '%${query}%' OR customer_email LIKE '%${query}%'`;
}
else
{
sql = `SELECT * FROM customers ORDER BY customer_id`;
}
pool.query(sql, (error, results) => {
if (error) throw error;
response.send(results);
});
});
This code creates a MySQL connection and listens for GET requests to "/search". It retrieves the search query from the GET request URL and executes a SQL query to search for customers whose first name, last name or email matches the query. The results are returned as a JSON object.
Step 4: Run Node Application
So here our code is ready now for check output in the browser, we have goes to terminal and run following command.
node app.js
This command will start node server and we can access this node application in browser by this http://localhost:3000 url.
Conclusion
In this article, we have shown you how to implement an Ajax live search feature using Node.js and MySQL. By using these technologies, you can build responsive and efficient web applications that provide a great user experience. With a few lines of code, you can easily add this feature to your web application and improve its functionality.
Complete Source Code
app.js
const express = require('express');
const mysql = require('mysql');
const app = express();
const port = 3000;
const pool = mysql.createPool({
connectionLimit : 10,
host : 'localhost',
user : 'root',
password : '',
database : 'testing'
});
app.get('/', (request, response) => {
response.sendFile(__dirname + '/index.html');
});
app.get('/search', (request, response) => {
const query = request.query.q;
var sql = '';
if(query != '')
{
sql = `SELECT * FROM customers WHERE customer_first_name LIKE '%${query}%' OR customer_last_name LIKE '%${query}%' OR customer_email LIKE '%${query}%'`;
}
else
{
sql = `SELECT * FROM customers ORDER BY customer_id`;
}
pool.query(sql, (error, results) => {
if (error) throw error;
response.send(results);
});
});
app.listen(port, () => {
console.log(`Server listening on port ${port}`);
});
index.html
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Ajax Live Data Search in Node.js with MySql</title>
<link href="https://getbootstrap.com/docs/5.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
</head>
<body>
<div class="container mt-5 mb-5">
<h1 class="text-primary text-center"><b>Ajax Live Data Search in Node.js with MySql</b></h1>
<div class="mt-3 mb-3">
<div class="card">
<div class="card-header">Customer Data</div>
<div class="card-body">
<div class="mb-3">
<input type="text" id="search" placeholder="Search..." class="form-control" autocomplete="off">
<table class="table table-bordered mt-3">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Gender</th>
</tr>
</thead>
<tbody id="results">
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
<script type="text/javascript">
const searchInput = document.querySelector('#search');
const results_body = document.querySelector('#results');
load_data();
function load_data(query = '')
{
const request = new XMLHttpRequest();
request.open('GET', `/search?q=${query}`);
request.onload = () => {
const results = JSON.parse(request.responseText);
let html = '';
if(results.length > 0)
{
results.forEach(result => {
html += `
<tr>
<td>`+result.customer_id+`</td>
<td>`+result.customer_first_name+`</td>
<td>`+result.customer_last_name+`</td>
<td>`+result.customer_email+`</td>
<td>`+result.customer_gender+`</td>
</tr>
`;
});
}
else
{
html += `
<tr>
<td colspan="5" class="text-center">No Data Found</td>
</tr>
`;
}
results_body.innerHTML = html;
};
request.send();
}
searchInput.addEventListener('input', () => {
const query = searchInput.value;
load_data(query);
});
</script>
