In this tutorial, we will learn Ajax CRUD Operation Insert Update and delete data from MySQL Database in Node JS using Express JS framework. In previous our one of the Node JS tutorial, in which we have already show you how can we perform simple CRUD (Create, Read, Update and Delete) operation with MySQL Database in Node JS Express Application.
This Node JS Tutorial will help you to understand how to use Ajax in Node JS Express Application. So for learn in Web Development for any things first you learn CRUD operation before start learn new things, this is because in CRUD Operation, we can learn all database operation like data listing on table, add data, edit data and delete data from database. So by using CRUD Operation we can manage dynamic data.
So for this things, here we will make single page CRUD Application in Node JS using Ajax with MySQL Database. And under this Single Page Node Express JS CRUD Application using Ajax will mainly perform four operations like Create, Read, Update and Delete and all this operation are the most important operation for build any web application.
If you want to better Understand CRUD Operation, you should see the following points -
- C : CREATE - Insert or Add new data into Database
- R : READ - Fetch or Select Data from Database and display on the web page
- U : UPDATE - Update or Edit Existing data into database
- D : DELETE - Delete or Remove Existing data from database
For Build Ajax CRUD Application in Node Express JS, you should follow below step.
- MySQL Database Structure
- Download & Install Node JS Express Application
- Create MySQL Database Connection
- Fetch & Display Data on Web Page using Ajax
- Insert Data in Node JS using Ajax
- Update Data in Node JS using Ajax
- Delete Data in Node JS using Ajax
1 - MySQL Database Structure
For build any CRUD Application with MySQL database, then we need mysql table. So run following .sql script in your local phpmyadmin database and this script will create sample_data table in your define database.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `sample_data`
--
CREATE TABLE `sample_data` (
`id` int(10) NOT NULL,
`first_name` varchar(250) NOT NULL,
`last_name` varchar(250) NOT NULL,
`age` varchar(30) NOT NULL,
`gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `sample_data`
--
INSERT INTO `sample_data` (`id`, `first_name`, `last_name`, `age`, `gender`) VALUES
(1, 'John', 'Smith', '26', 'Male'),
(2, 'Donna', 'Hubber', '24', 'Female'),
(3, 'Peter', 'Parker', '28', 'Male'),
(4, 'Tom ', 'Muddy', '28', 'Male');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `sample_data`
--
ALTER TABLE `sample_data`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `sample_data`
--
ALTER TABLE `sample_data`
MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
2 - Download & Install Node JS Express Application
For Build Ajax CRUD Application in Node JS, here we have use Express JS framework. So first we need to download Node JS Express Application and by using this framework we can fastly build Node JS single page CRUD Application using Ajax.
Now for Install Node JS Express framework first we have to download and install Express generator. So for this, first you have go to command prompt and you have to create one ajax-crud directory and then after we need to goes into that directory by run following command.
mkdir ajax-crud
cd ajax-crud
Once we have goes into ajax-crud directory, so after this, we have to run following command for install Express generator.
npm install -g express-generator
After install Express generator, next we have to install Express application. So for this, we have to run following command command and this command will download and install express application.
npx express --view=ejs
So here we can see that in above command, ejs is the template engine which we have use in Node JS Application and once Node Express Application has been downloaded then it will display following directory structure of Node JS Express framework.
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
Once our Node Express JS application has been downloaded next we have to install required Node JS module so for this, we have to run following command which will download and install required Node JS Module.
npm install
So this is complete process for install Node Express JS Application and now we can proceed for build Ajax CRUD Application Node JS.
3 - Create MySQL Database Connection
In next step we have to make MySQL Database connection with Node JS Express Application. So for use MySQL Database with Node Express JS Application first we need to download node mysql module, So for this we have go to command prompt and run following command.
npm install mysql
So this command will download Node MySQL module in our Node Express JS application after this for make database connection, we have to create database.js file in our root directory, and under this file, we have to define mysql database configuration details which you can seen below and lastly export mysql database connection file.
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;
So after creating this file, we can make database connection in any routes folder file by simply include this file into that server script file which we can seen in next step.
4 - Fetch & Display Data on Web Page using Ajax
In Node JS Ajax CRUD Application tutorial, now we want to show you how to fetch data from MySQL table and display on web page in HTML table format by using Ajax in Node JS Express Application.
So, first we need to create sample_data.js file in routes directory. Under this file we will write server side script for perform CRUD database operation. Under this file we will create routes for load web page in the browser and then after create another routes for handle Ajax post data request.
routes/sample_data.js
var express = require('express');
var router = express.Router();
var database = require('../database');
router.get("/", function(request, response, next){
response.render('sample_data', {title : 'Node JS Ajax CRUD Application'});
});
router.post("/action", function(request, response, next){
var action = request.body.action;
if(action == 'fetch')
{
var query = "SELECT * FROM sample_data ORDER BY id DESC";
database.query(query, function(error, data){
response.json({
data:data
});
});
}
});
module.exports = router;
After this, we have define routes in app.js file which you can seen below.
app.js
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var sampleDataRouter = require('./routes/sample_data');
var app = express();
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/sample_data', sampleDataRouter);
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404));
});
// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500);
res.render('error');
});
module.exports = app;
After this, for display HTML output in the browser, we need to create sample_data.ejs file in views directory. In this file, we have to create HTML code and jQuery Ajax Code. Under this step, we have make one JavaScript function which will send Ajax request to Node JS Script for fetch data from MySQL database and it will receive data from server in JSON format which will be display on web page in HTML table format.
views/sample_data.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://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></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.0/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>
<title></title>
</head>
<body>
<div class="container">
<h1 class="mt-4 mb-4 text-center text-primary"><b><%= title %></b></h1>
<span id="message"></span>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col col-sm-9">Sample Data</div>
<div class="col col-sm-3">
</div>
</div>
</div>
<div class="card-body">
<div class="table-responsive">
<table class="table table-striped table-bordered" id="sample_data">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Age</th>
<th>Action</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
load_data();
function load_data()
{
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:{action:'fetch'},
dataType : "JSON",
success:function(data)
{
var html = '';
if(data.data.length > 0)
{
for(var count = 0; count < data.data.length; count++)
{
html += `
<tr>
<td>`+data.data[count].first_name+`</td>
<td>`+data.data[count].last_name+`</td>
<td>`+data.data[count].gender+`</td>
<td>`+data.data[count].age+`</td>
<td></td>
</tr>
`;
}
}
$('#sample_data tbody').html(html);
}
});
}
});
</script>
4 - Insert Data in Node JS using Ajax
In next step we have to proceed for how to insert data into MySQL table in Node JS using Ajax. So here for Insert data into MySQL table in Node js, we have use Ajax, Bootstrap 5 Modal and Express JS Application.
First we will make One HTML button, so when we have click on that button then Bootstrap Modal must be pop up on the web page, with HTML Form.
After this, we will Create Bootstrap Modal and under that Modal we will create HTML Form for get data from user. This Modal will be pop up when we have click on Add button by using jQuery.
Next we will write jQuery script for pop up Bootstrap modal on the web page.
And after this, we will write jQuery script for submit form data, so when form has been submitted then Ajax response will be send to Node JS script for Insert data into MySQL table and this Ajax request will receive response from Node js server in JSON format.
views/sample_data.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://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></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.0/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>
<title></title>
</head>
<body>
<div class="container">
<h1 class="mt-4 mb-4 text-center text-primary"><b><%= title %></b></h1>
<span id="message"></span>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col col-sm-9">Sample Data</div>
<div class="col col-sm-3">
<button type="button" id="add_data" class="btn btn-success btn-sm float-end">Add</button>
</div>
</div>
</div>
<div class="card-body">
<div class="table-responsive">
<table class="table table-striped table-bordered" id="sample_data">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Age</th>
<th>Action</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>
<div class="modal" tabindex="-1" id="action_modal">
<div class="modal-dialog">
<div class="modal-content">
<form method="post" id="sample_form">
<div class="modal-header" id="dynamic_modal_title">
<h5 class="modal-title"></h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<div class="mb-3">
<label class="form-label">First Name</label>
<input type="text" name="first_name" id="first_name" class="form-control" />
</div>
<div class="mb-3">
<label class="form-label">Last Name</label>
<input type="text" name="last_name" id="last_name" class="form-control" />
</div>
<div class="mb-3">
<label class="form-label">Gender</label>
<select name="gender" id="gender" class="form-control">
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
</div>
<div class="mb-3">
<label class="form-label">Age</label>
<input type="nummber" name="age" id="age" class="form-control" />
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="id" id="id" />
<input type="hidden" name="action" id="action" value="Add" />
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
<button type="submit" class="btn btn-primary" id="action_button">Add</button>
</div>
</form>
</div>
</div>
</div>
<script>
$(document).ready(function(){
load_data();
function load_data()
{
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:{action:'fetch'},
dataType : "JSON",
success:function(data)
{
var html = '';
if(data.data.length > 0)
{
for(var count = 0; count < data.data.length; count++)
{
html += `
<tr>
<td>`+data.data[count].first_name+`</td>
<td>`+data.data[count].last_name+`</td>
<td>`+data.data[count].gender+`</td>
<td>`+data.data[count].age+`</td>
<td></td>
</tr>
`;
}
}
$('#sample_data tbody').html(html);
}
});
}
$('#add_data').click(function(){
$('#dynamic_modal_title').text('Add Data');
$('#sample_form')[0].reset();
$('#action').val('Add');
$('#action_button').text('Add');
$('#action_modal').modal('show');
});
$('#sample_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:$('#sample_form').serialize(),
dataType:"JSON",
beforeSend:function(){
$('#action_button').attr('disabled', 'disabled');
},
success:function(data)
{
$('#action_button').attr('disabled', false);
$('#message').html('<div class="alert alert-success">'+data.message+'</div>');
$('#action_modal').modal('hide');
load_data();
setTimeout(function(){
$('#message').html('');
}, 5000);
}
});
});
});
</script>
routes/sample_data.js
var express = require('express');
var router = express.Router();
var database = require('../database');
router.get("/", function(request, response, next){
response.render('sample_data', {title : 'Node JS Ajax CRUD Application'});
});
router.post("/action", function(request, response, next){
var action = request.body.action;
if(action == 'fetch')
{
var query = "SELECT * FROM sample_data ORDER BY id DESC";
database.query(query, function(error, data){
response.json({
data:data
});
});
}
if(action == 'Add')
{
var first_name = request.body.first_name;
var last_name = request.body.last_name;
var age = request.body.age;
var gender = request.body.gender;
var query = `
INSERT INTO sample_data
(first_name, last_name, age, gender)
VALUES ("${first_name}", "${last_name}", "${age}", "${gender}")
`;
database.query(query, function(error, data){
response.json({
message : 'Data Added'
});
});
}
});
module.exports = router;
5 - Update Data in Node JS using Ajax
In the Web Development, if we have develop any web application and in that Web based online application, for manage data, CRUD Operation has been used for Insert Update Delete Data from Database. So in last step we have show you How to Insert data, so after inserting of data, so suppose if we want to make any change in existing data then Update or Edit data crud operation has been used.
So now for Update existing data, first we will create edit button in each of data. So when dynamic edit button has been created in each of data. Now under this Node JS Ajax CRUD Application, we will write JavaScript code on edit button event. So when we have click on edit button then it will fetch single row of data from MySQL database and then after it will be display on Bootstrap Modal with fill form data.
After this, we have write JavaScript code for submit form data and which will send form data to Node JS Server Script using Ajax and at Node JS Server side script it will update or edit existing MySQL data based on primary key which has been stored under form hidden field.
views/sample_data.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://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></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.0/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>
<title></title>
</head>
<body>
<div class="container">
<h1 class="mt-4 mb-4 text-center text-primary"><b><%= title %></b></h1>
<span id="message"></span>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col col-sm-9">Sample Data</div>
<div class="col col-sm-3">
<button type="button" id="add_data" class="btn btn-success btn-sm float-end">Add</button>
</div>
</div>
</div>
<div class="card-body">
<div class="table-responsive">
<table class="table table-striped table-bordered" id="sample_data">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Age</th>
<th>Action</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>
<div class="modal" tabindex="-1" id="action_modal">
<div class="modal-dialog">
<div class="modal-content">
<form method="post" id="sample_form">
<div class="modal-header" id="dynamic_modal_title">
<h5 class="modal-title"></h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<div class="mb-3">
<label class="form-label">First Name</label>
<input type="text" name="first_name" id="first_name" class="form-control" />
</div>
<div class="mb-3">
<label class="form-label">Last Name</label>
<input type="text" name="last_name" id="last_name" class="form-control" />
</div>
<div class="mb-3">
<label class="form-label">Gender</label>
<select name="gender" id="gender" class="form-control">
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
</div>
<div class="mb-3">
<label class="form-label">Age</label>
<input type="nummber" name="age" id="age" class="form-control" />
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="id" id="id" />
<input type="hidden" name="action" id="action" value="Add" />
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
<button type="submit" class="btn btn-primary" id="action_button">Add</button>
</div>
</form>
</div>
</div>
</div>
<script>
$(document).ready(function(){
load_data();
function load_data()
{
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:{action:'fetch'},
dataType : "JSON",
success:function(data)
{
var html = '';
if(data.data.length > 0)
{
for(var count = 0; count < data.data.length; count++)
{
html += `
<tr>
<td>`+data.data[count].first_name+`</td>
<td>`+data.data[count].last_name+`</td>
<td>`+data.data[count].gender+`</td>
<td>`+data.data[count].age+`</td>
<td><button type="button" class="btn btn-warning btn-sm edit" data-id="`+data.data[count].id+`">Edit</button></td>
</tr>
`;
}
}
$('#sample_data tbody').html(html);
}
});
}
$('#add_data').click(function(){
$('#dynamic_modal_title').text('Add Data');
$('#sample_form')[0].reset();
$('#action').val('Add');
$('#action_button').text('Add');
$('#action_modal').modal('show');
});
$('#sample_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:$('#sample_form').serialize(),
dataType:"JSON",
beforeSend:function(){
$('#action_button').attr('disabled', 'disabled');
},
success:function(data)
{
$('#action_button').attr('disabled', false);
$('#message').html('<div class="alert alert-success">'+data.message+'</div>');
$('#action_modal').modal('hide');
load_data();
setTimeout(function(){
$('#message').html('');
}, 5000);
}
});
});
$(document).on('click', '.edit', function(){
var id = $(this).data('id');
$('#dynamic_modal_title').text('Edit Data');
$('#action').val('Edit');
$('#action_button').text('Edit');
$('#action_modal').modal('show');
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:{id:id, action:'fetch_single'},
dataType:"JSON",
success:function(data)
{
$('#first_name').val(data.first_name);
$('#last_name').val(data.last_name);
$('#gender').val(data.gender);
$('#age').val(data.age);
$('#id').val(data.id);
}
});
});
});
</script>
routes/sample_data.js
var express = require('express');
var router = express.Router();
var database = require('../database');
router.get("/", function(request, response, next){
response.render('sample_data', {title : 'Node JS Ajax CRUD Application'});
});
router.post("/action", function(request, response, next){
var action = request.body.action;
if(action == 'fetch')
{
var query = "SELECT * FROM sample_data ORDER BY id DESC";
database.query(query, function(error, data){
response.json({
data:data
});
});
}
if(action == 'Add')
{
var first_name = request.body.first_name;
var last_name = request.body.last_name;
var age = request.body.age;
var gender = request.body.gender;
var query = `
INSERT INTO sample_data
(first_name, last_name, age, gender)
VALUES ("${first_name}", "${last_name}", "${age}", "${gender}")
`;
database.query(query, function(error, data){
response.json({
message : 'Data Added'
});
});
}
if(action == 'fetch_single')
{
var id = request.body.id;
var query = `SELECT * FROM sample_data WHERE id = "${id}"`;
database.query(query, function(error, data){
response.json(data[0]);
});
}
if(action == 'Edit')
{
var id = request.body.id;
var first_name = request.body.first_name;
var last_name = request.body.last_name;
var gender = request.body.gender;
var age = request.body.age;
var query = `
UPDATE sample_data
SET first_name = "${first_name}",
last_name = "${last_name}",
age = "${age}",
gender = "${gender}"
WHERE id = "${id}"
`;
database.query(query, function(error, data){
response.json({
message : 'Data Edited'
});
});
}
});
module.exports = router;
6 - Delete Data in Node JS using Ajax
Here we will show you how can we perform delete data operation in Node.js Express Application with Ajax. So data will be deleted from MySQL Table without refresh of web page.
So for delete data operation, first we want to create dynamic delete button in each row of data.
After this, we write JavaScript code for capture delete button click event, so when we have click on delete button, the Ajax request will be send to Node JS Server Script for delete data operation and at Node.js Server side script it wil delete data from MySQL table by primary id key in Node js and it will send back response to Ajax request in JSON format and then after it will display success message on the web page and data will be deleted without refresh of web page.
views/sample_data.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://cdn.datatables.net/1.12.0/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></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.0/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap5.min.js"></script>
<title></title>
</head>
<body>
<div class="container">
<h1 class="mt-4 mb-4 text-center text-primary"><b><%= title %></b></h1>
<span id="message"></span>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col col-sm-9">Sample Data</div>
<div class="col col-sm-3">
<button type="button" id="add_data" class="btn btn-success btn-sm float-end">Add</button>
</div>
</div>
</div>
<div class="card-body">
<div class="table-responsive">
<table class="table table-striped table-bordered" id="sample_data">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th>Age</th>
<th>Action</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>
<div class="modal" tabindex="-1" id="action_modal">
<div class="modal-dialog">
<div class="modal-content">
<form method="post" id="sample_form">
<div class="modal-header" id="dynamic_modal_title">
<h5 class="modal-title"></h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<div class="mb-3">
<label class="form-label">First Name</label>
<input type="text" name="first_name" id="first_name" class="form-control" />
</div>
<div class="mb-3">
<label class="form-label">Last Name</label>
<input type="text" name="last_name" id="last_name" class="form-control" />
</div>
<div class="mb-3">
<label class="form-label">Gender</label>
<select name="gender" id="gender" class="form-control">
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
</div>
<div class="mb-3">
<label class="form-label">Age</label>
<input type="nummber" name="age" id="age" class="form-control" />
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="id" id="id" />
<input type="hidden" name="action" id="action" value="Add" />
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
<button type="submit" class="btn btn-primary" id="action_button">Add</button>
</div>
</form>
</div>
</div>
</div>
<script>
$(document).ready(function(){
load_data();
function load_data()
{
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:{action:'fetch'},
dataType : "JSON",
success:function(data)
{
var html = '';
if(data.data.length > 0)
{
for(var count = 0; count < data.data.length; count++)
{
html += `
<tr>
<td>`+data.data[count].first_name+`</td>
<td>`+data.data[count].last_name+`</td>
<td>`+data.data[count].gender+`</td>
<td>`+data.data[count].age+`</td>
<td><button type="button" class="btn btn-warning btn-sm edit" data-id="`+data.data[count].id+`">Edit</button> <button type="button" class="btn btn-danger btn-sm delete" data-id="`+data.data[count].id+`">Delete</button></td>
</tr>
`;
}
}
$('#sample_data tbody').html(html);
}
});
}
$('#add_data').click(function(){
$('#dynamic_modal_title').text('Add Data');
$('#sample_form')[0].reset();
$('#action').val('Add');
$('#action_button').text('Add');
$('#action_modal').modal('show');
});
$('#sample_form').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:$('#sample_form').serialize(),
dataType:"JSON",
beforeSend:function(){
$('#action_button').attr('disabled', 'disabled');
},
success:function(data)
{
$('#action_button').attr('disabled', false);
$('#message').html('<div class="alert alert-success">'+data.message+'</div>');
$('#action_modal').modal('hide');
load_data();
setTimeout(function(){
$('#message').html('');
}, 5000);
}
});
});
$(document).on('click', '.edit', function(){
var id = $(this).data('id');
$('#dynamic_modal_title').text('Edit Data');
$('#action').val('Edit');
$('#action_button').text('Edit');
$('#action_modal').modal('show');
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:{id:id, action:'fetch_single'},
dataType:"JSON",
success:function(data)
{
$('#first_name').val(data.first_name);
$('#last_name').val(data.last_name);
$('#gender').val(data.gender);
$('#age').val(data.age);
$('#id').val(data.id);
}
});
});
$(document).on('click', '.delete', function(){
var id = $(this).data('id');
if(confirm("Are you sure you want to delete this data?"))
{
$.ajax({
url:"http://localhost:3000/sample_data/action",
method:"POST",
data:{action:'delete', id:id},
dataType:"JSON",
success:function(data)
{
$('#message').html('<div class="alert alert-success">'+data.message+'</div>');
load_data();
setTimeout(function(){
$('#message').html('');
}, 5000);
}
});
}
});
});
</script>
routes/sample_data.js
var express = require('express');
var router = express.Router();
var database = require('../database');
router.get("/", function(request, response, next){
response.render('sample_data', {title : 'Node JS Ajax CRUD Application'});
});
router.post("/action", function(request, response, next){
var action = request.body.action;
if(action == 'fetch')
{
var query = "SELECT * FROM sample_data ORDER BY id DESC";
database.query(query, function(error, data){
response.json({
data:data
});
});
}
if(action == 'Add')
{
var first_name = request.body.first_name;
var last_name = request.body.last_name;
var age = request.body.age;
var gender = request.body.gender;
var query = `
INSERT INTO sample_data
(first_name, last_name, age, gender)
VALUES ("${first_name}", "${last_name}", "${age}", "${gender}")
`;
database.query(query, function(error, data){
response.json({
message : 'Data Added'
});
});
}
if(action == 'fetch_single')
{
var id = request.body.id;
var query = `SELECT * FROM sample_data WHERE id = "${id}"`;
database.query(query, function(error, data){
response.json(data[0]);
});
}
if(action == 'Edit')
{
var id = request.body.id;
var first_name = request.body.first_name;
var last_name = request.body.last_name;
var gender = request.body.gender;
var age = request.body.age;
var query = `
UPDATE sample_data
SET first_name = "${first_name}",
last_name = "${last_name}",
age = "${age}",
gender = "${gender}"
WHERE id = "${id}"
`;
database.query(query, function(error, data){
response.json({
message : 'Data Edited'
});
});
}
if(action == 'delete')
{
var id = request.body.id;
var query = `DELETE FROM sample_data WHERE id = "${id}"`;
database.query(query, function(error, data){
response.json({
message : 'Data Deleted'
});
});
}
});
module.exports = router;
0 comments:
Post a Comment