Ajax Search Box Using Node and MySQL

Ajax search is one of must have feature in any application. It allow user’s to search for any doubt, and will show suggestion based on their input. Just like how Google do or Facebook graph search, although they are very advanced search box.

In this tutorial, i am going to develop an Ajax Search Box using Node and MySQL as database. For front-end i am going to use Bootstrap and Typeahead library.

LIVE DEMO DOWNLOAD
Our approach is to call our Node server for every key input, fetch data from database, pack the response in JSON format and return it to client. In client-end we will extract those JSON response and show it to Search box immediately.

What is Typeahead.js ?

Twitter develop this JavaScript library for efficient web development. By using this library one can simply create an Ajax Search box. You can use this to search for your local data or remote data as well.

It is simple to use, we have to import the script, and by using JavaScript selector or JQuery selector we can initialize and invoke the typeahead.

Database design:

Create any database in MySQL and paste following query to create table inside that database.

CREATE TABLE 'user_name'
(
user_id INT(20) PRIMARY KEY,
first_name TEXT
);

Put some dummy data in that using INSERT command.

package.json
{
"name": "Ajax-Search-Box",
"version": "1.0.1",
"dependencies": {
"express": "~4.6.1",
"ejs": "~1.0.0",
"mysql": "~2.4.1"
}
}

You can install this dependencies by

npm install

You may need sudo access in Linux or Mac based System.

Directory Structure:

Here is a Project directory structure.

-----node_modules
|--express
|--ejs
|--mysql
-----JS
|--typeahead.min.js
-----views
|--index.html
---package.json
---Server.js

HTML and JS Code

We are using typeahead.js library to enable Ajax search feature. So we have to include them in our HTML file as well.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="typeahead.min.js" type="text/javascript"></script>

We will initialize the typeahead in JQuery function. Here have a look.

$(document).ready(function(){
$('input.typeahead').typeahead({
name: 'typeahead',
remote: 'http://localhost:3000/search?key=%QUERY',
limit: 10
});
});

We are calling the remote URL by passing the input keyword provided by user. Here is HTML part of Search box.

<input class="typeahead tt-query" spellcheck="false" autocomplete="off" name="typeahead" type="text" />

This file contains code for loading Server, handling request, handling Database operations and returning response for a valid request.
Our first job is to load the dependencies we want to use.

Server.js
var express=require('express');
var app=express();
var mysql=require('mysql');

Next job is to define the application configuration. We are using ‘view’ folder for storing front end code and JS folder for JavaScript library so we have to tell express to do so by using following code.

Server.js
app.set('views',__dirname + '/views');
app.use(express.static(__dirname + '/JS'));
app.set('view engine', 'ejs');
app.engine('html', require('ejs').renderFile);

Next, we will connect to our MySQL database. This is a code to do so.

Server.js
var connection = mysql.createConnection({
host : 'Host name; localhost for local',
user : 'User name ; root mostly',
password : 'password of MySQL; blank mostly',
database : 'Your database name'
});
connection.connect();

Once we are connected to database, our job is to handle router. Router defines the working of app in different scenario. We are using two routers here, one to deliver home page of app, second to extract key of search and returning result.

Server.js
//Return home page
app.get('/',function(req,res){
res.render('index.html');
});
//Extract the keyword.
//Return the result depending on the keyword.
app.get('/search',function(req,res){
//extract key using req.query.key
//call MySQL Query.
//form JSON response and return.
});

Depending upon your database design your MySQL query may be different than mine. Here is a complete router code.

Server.js
app.get('/',function(req,res){
res.render('index.html');
});

app.get('/search',function(req,res){
connection.query('SELECT first_name from TABLE_NAME where first_name like "%'+req.query.key+'%"',
function(err, rows, fields) {
if (err) throw err;
var data=[];
for(i=0;i<rows.length;i++)
{
data.push(rows[i].first_name);
}
res.end(JSON.stringify(data));
});
});

Next task is to start our app.

Server.js
var server=app.listen(3000,function(){
console.log("We have started our server on port 3000");
});

You can use any type of CSS you want to design your front-end, it is not specific so i am not posting it here. You can download the code from Github and run it at your end.

How to run the app:

Download the source code from Github. Change the MySQL configuration. Run the app by typing

node Server.js

in prompt. Visit localhost:3000 to view the app.

Shahid
Shahid

Founder of Codeforgeek. Technologist. Published Author. Engineer. Content Creator. Teaching Everything I learn!

Articles: 126