In this tutorial, we will show you How to use jQuery DataTables in Node.js Express framework and you can learn How to populating MySQL table data in jQuery DataTables with Node.js. In this tutorial we will use DataTables Server-side processing of data in Node.js Express Application with MySQL table. So if you are looking for tutorial on Server side jQuery DataTable with MySQL Database and Node.js then this Node.js tutorial will help you to understand server side jQuery DataTable in Node JS by using Ajax.
Here jQuery DataTables has been used Ajax request for Server-side process in Node.js Express application. In jQuery DataTable all feature like live searching of MySQL table data, sorting of data pagination link will be generated without writing of code. Because jQuery DataTables has been convert our HTML table into advanced table with feature like pagination, instant search, multi column ordering. So when we have type in search box then jQuery DataTables has send Ajax request to Node JS script for instant search in MySQL table and sample way we have click on pagination link of jQuery DataTable then it has send Ajax request to Node JS script for fetch next page data from MySQL table and send response to jQuery DataTables in JSON format. So this way it has perform all process at server side with Node.js.
In this tutorial, we have use following web technology.
Client-side
- jQuery DataTables
- jQuery
- Bootstrap 5
Server-side
- Node.js
- Express Framework
- MySQL
Steps for Use jQuery DataTables in Node.js
You have to follow below steps for implement jQuery DataTables Server-side processing in your Node.js Express Application.
- MySQL Database Structure
- Install Node.js Express Application
- Connection Node.js Express Application with MySQL Database
- Create Views Template File
- Create Routes File
- Check Output in the browser
MySQL Database Structure
In the first steps we have show you MySQL database structure, so first we want to create one customer_table in mysql database. So for create MySQL table, we have to run following command which will create MySQL table in your MySQL database.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `customer_table`
--
CREATE TABLE `customer_table` (
`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;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `customer_table`
--
ALTER TABLE `customer_table`
ADD PRIMARY KEY (`customer_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `customer_table`
--
ALTER TABLE `customer_table`
MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT;
Install Node.js Express Application
In tis tutorial, we have use Node.js with Express framework, so first we have want to download and install Node.js Express framework. So first we have to go into directory in which we have run our node application.
After this, we have to create node_datatable directory, so for this, we have to run following command.
mkdir node_datatable
After this, we have goes into this directory, so for this, we have to run following command, so after run this command, we will goes into node_datatable directory.
cd node_datatable
After goes into node_datatable directory, now we want to download and install Node.js Express framework. So here first we want to install express generator, so for this, we have to run following command.
npm install -g express-generator
Once we have install express generator, next we need to download and install Node.js Express framework, with EJS template engine, so for this we have to run following command in command prompt.
npx express --view=ejs
This command will download Node.js Express framework and here we have use EJS template engine under this Node.js Express application and after run this command you will get following output in your command prompt window.
create : public\
create : public\javascripts\
create : public\images\
create : public\stylesheets\
create : public\stylesheets\style.css
create : routes\
create : routes\index.js
create : routes\users.js
create : views\
create : views\error.ejs
create : views\index.ejs
create : app.js
create : package.json
create : bin\
create : bin\www
install dependencies:
> npm install
run the app:
> SET DEBUG=crud:* & npm start
Next we want to install node.js dependencies, so for this in command prompt we have to run following command.
npm install
So after run this command here our Node.js Express application has been downloaded and installed in our computer.
Connection Node.js Express Application with MySQL Database
After download and install Node.js Express Application, now we want to make database connection. So for this, first we have to download and install node mysql module. So for this, we have go into command prompt and run following command.
npm install mysql
This command will download and install node mysql module in our Node.js Express application. Now for make database connection, here first we need to create database.j file in the root directory and under this file, first we need to include node mysql module under this file and then after we have to define mysql database configuration under this file which will make MySQL database connection under this Node.js Express application.
database.js
const mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
database : 'testing',
user : 'root',
password : ''
});
connection.connect(function(error){
if(error)
{
throw error;
}
else
{
console.log('MySQL Database is connected Successfully');
}
});
module.exports = connection;
Create Views Template File
In the Node.js Express framework, Views directory file has been used for display HTML output in the browser. So for this, we have to open views/index.ejs file.
Under this file, we have to include jQuery, Boostrap 5 library link.
After this, we have to create HTML table with id customer_data and we will initialize jQuery DataTable plugin on the table and convert HTML table adavanced table.
Next, we have to write jQuery code for initialize jQuery Datatables plugin by using DataTable() with required option which you can seen below.
views/index.ejs
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.1.3/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.12.1/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<title><%= title %></title>
</head>
<body>
<div class="container">
<h1 class="text-center text-primary mt-3 mb-3">jQuery DataTables with Node.js Express & MySQL</h1>
<div class="card">
<div class="card-header">Customer Data</div>
<div class="card-body">
<div class="table-responsive">
<table id="customer_data" class="table table-bordered">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Gender</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.1/js/dataTables.bootstrap5.min.js"></script>
</body>
</html>
<script>
$(document).ready(function(){
var dataTable = $('#customer_data').DataTable({
'processing' : true,
'serverSide' : true,
'serverMethod' : 'get',
'ajax' : {
'url' : '/get_data'
},
'aaSorting' : [],
'columns' : [
{ data : 'customer_first_name' },
{ data : 'customer_last_name' },
{ data : 'customer_email' },
{ data : 'customer_gender' }
]
});
});
</script>
Create Routes File
In the routes file, we have mainly write Node.js Server side script under this Node.js Express framework. So routes file has bee stored under routes directory. So by default under this directory index.js file has been created.
So we have to open views/index.js file and under this file, first we want to make database connection by including database.js file.
After this, we have goes into get('/') route and this file has been load views/index.ejs file in the browser for display HTML output on the web page.
Next we have to create another get('/get_data') route for handle Ajax request which has been send by jQuery DataTable.
And under this route, it has received data from jQuery Datatable Ajax request and based on that data, under this route it has calculate total records in MySQL table, total records with filtering and fetch data from MySQL customer_table and then after it has send data to Ajax request in JSON format which has been load in jQuery DataTable without refresh of web page.
routes/index.js
var express = require('express');
var router = express.Router();
var database = require('../database');
/* GET home page. */
router.get('/', function(req, res, next) {
res.render('index', { title: 'Express' });
});
router.get('/get_data', function(request, response, next){
var draw = request.query.draw;
var start = request.query.start;
var length = request.query.length;
var order_data = request.query.order;
if(typeof order_data == 'undefined')
{
var column_name = 'customer_table.customer_id';
var column_sort_order = 'desc';
}
else
{
var column_index = request.query.order[0]['column'];
var column_name = request.query.columns[column_index]['data'];
var column_sort_order = request.query.order[0]['dir'];
}
//search data
var search_value = request.query.search['value'];
var search_query = `
AND (customer_first_name LIKE '%${search_value}%'
OR customer_last_name LIKE '%${search_value}%'
OR customer_email LIKE '%${search_value}%'
OR customer_gender LIKE '%${search_value}%'
)
`;
//Total number of records without filtering
database.query("SELECT COUNT(*) AS Total FROM customer_table", function(error, data){
var total_records = data[0].Total;
//Total number of records with filtering
database.query(`SELECT COUNT(*) AS Total FROM customer_table WHERE 1 ${search_query}`, function(error, data){
var total_records_with_filter = data[0].Total;
var query = `
SELECT * FROM customer_table
WHERE 1 ${search_query}
ORDER BY ${column_name} ${column_sort_order}
LIMIT ${start}, ${length}
`;
var data_arr = [];
database.query(query, function(error, data){
data.forEach(function(row){
data_arr.push({
'customer_first_name' : row.customer_first_name,
'customer_last_name' : row.customer_last_name,
'customer_email' : row.customer_email,
'customer_gender' : row.customer_gender
});
});
var output = {
'draw' : draw,
'iTotalRecords' : total_records,
'iTotalDisplayRecords' : total_records_with_filter,
'aaData' : data_arr
};
response.json(output);
});
});
});
});
module.exports = router;
Check Output in the browser
Once we have process all above code, so we have to save all files and then after we have go to command prompt and run following command.
npm start
This command will start Node.js server and now we are ready for check output in the browser. So for this we have to hit following url in the browser.
http://localhost:3000
So once you have follow all above steps then you can able to use jQuerh DataTables in Node.js Express Application.
0 comments:
Post a Comment