Learn Infinity

Learn Some Thing New

CRUD Operation using NodeJS ExpressJS MySQL – Part 2

CRUD Operation using NodeJS ExpressJS MySQL – Part 2

In the previous post, we explain the Read & Listing database value using NodeJs and Express. Here we are going to cover the remaining position in CRUD using NodeJS ExpressJS MySQL database (like Create, Update, and Delete). For the Create and Update process, we used the Boostrap Form and EJS template engine. Following steps help us to do the above mention process.

Step 1: Create User Information

In this step, we will be going to make User information form with required inputs and HTTP request handling function. For New User Form, we need to create a file with EJS format and named it as user_add.ejs. After creating a file, add the following code into it.

views/user_add.ejs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<!DOCTYPE html>
<html lang="en">
<head>
  <title><%= title %></title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
 
<div class="container">
  <h2><%= title %></h2>
  <form action="/save" method="post">
    <div class="form-group">
      <label for="name">Name:</label>
      <input type="text" class="form-control" id="email" placeholder="Enter Name" name="name" required>
    </div>
    <div class="form-group">
      <label for="email">Email:</label>
      <input type="email" class="form-control" id="email" placeholder="Enter email" name="email" required>
    </div>
    <div class="form-group">
      <label for="phone">Phone No:</label>
      <input type="text" class="form-control" id="pwd" placeholder="Enter Phone No" name="phone_no" required>
    </div>
    <button type="submit" class="btn btn-primary">Submit</button>
  </form>
</div>
 
</body>
</html>

After finishing the New User Form creation, updates the app.js file with following code. It will help us to handle our HTTP POST request and store the New User Form data to the database using MySQL connection.

app.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
.....
.....
 
app.get('/add',(req, res) => {
    res.render('user_add', {
        title : 'CRUD Operation using NodeJS / ExpressJS / MySQL'
    });
});
 
app.post('/save',(req, res) => {
    let data = {name: req.body.name, email: req.body.email, phone_no: req.body.phone_no};
    let sql = "INSERT INTO users SET ?";
    let query = connection.query(sql, data,(err, results) => {
      if(err) throw err;
      res.redirect('/');
    });
});
 
......
......

Step 2: Read / Update User Information

In this step, we will be going to make User information form with required inputs and HTTP request handling function. For Update User Form, we need to create a file with EJS format and named it as user_edit.ejs. After creating a file, add the following code into it.

views/user_edit.ejs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<!DOCTYPE html>
<html lang="en">
<head>
  <title><%= title %></title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
 
<div class="container">
  <h2><%= title %></h2>
  <form action="/update" method="post">
    <input type="hidden" value="<%= user.id %>" name="id">
    <div class="form-group">
      <label for="name">Name:</label>
      <input type="text" class="form-control" id="email" placeholder="Enter Name" value="<%= user.name %>" name="name" required>
    </div>
    <div class="form-group">
      <label for="email">Email:</label>
      <input type="email" class="form-control" id="email" placeholder="Enter email" value="<%= user.email %>" name="email" required>
    </div>
    <div class="form-group">
      <label for="phone">Phone No:</label>
      <input type="text" class="form-control" id="pwd" placeholder="Enter Phone No" value="<%= user.phone_no %>" name="phone_no" required>
    </div>
    <button type="submit" class="btn btn-primary">Submit</button>
  </form>
</div>
 
</body>
</html>

After finishing the Update User Form creation, updates the app.js file with following code. It will help us to handle our HTTP GET request and fetch the User information from the database using a MySQL connection.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
......
......
 
app.get('/edit/:userId',(req, res) => {
    const userId = req.params.userId;
    let sql = `Select * from users where id = ${userId}`;
    let query = connection.query(sql,(err, result) => {
        if(err) throw err;
        res.render('user_edit', {
            title : 'CRUD Operation using NodeJS / ExpressJS / MySQL',
            user : result[0]
        });
    });
});
 
......
......

After getting and displaying the user information, updates the app.js file with the following code. It will help us to handle our HTTP POST request. And update the User Form data to the database based on unique user ID using MySQL connection.

app.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
.....
.....
 
app.post('/update',(req, res) => {
    const userId = req.body.id;
    let sql = "update users SET name='"+req.body.name+"',  email='"+req.body.email+"',  phone_no='"+req.body.phone_no+"' where id ="+userId;
    let query = connection.query(sql,(err, results) => {
      if(err) throw err;
      res.redirect('/');
    });
});
 
.....
.....

Step 3: Delete User Information

Here we are going to Remove or Delete user information from a database using a unique user ID. We need Updates the app.js file with the following code to proceed the delete process.

app.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
....
....
 
 
app.get('/delete/:userId',(req, res) => {
    const userId = req.params.userId;
    let sql = `DELETE from users where id = ${userId}`;
    let query = connection.query(sql,(err, result) => {
        if(err) throw err;
        res.redirect('/');
    });
});
 
....
....

Step 4: Finally app.js file code following details.

app.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
const path = require('path');
const express = require('express');
const ejs = require('ejs');
const bodyParser = require('body-parser');
const mysql = require('mysql');
const app = express();
 
const connection=mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'',
    database:'node_crud'
});
 
connection.connect(function(error){
    if(!!error) console.log(error);
    else console.log('Database Connected!');
});
 
//set views file
app.set('views',path.join(__dirname,'views'));
//set view engine
app.set('view engine', 'ejs');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
 
 
 
app.get('/',(req, res) => {
    // res.send('CRUD Operation using NodeJS / ExpressJS / MySQL');
    let sql = "SELECT * FROM users";
    let query = connection.query(sql, (err, rows) => {
        if(err) throw err;
        res.render('user_index', {
            title : 'CRUD Operation using NodeJS / ExpressJS / MySQL',
            users : rows
        });
    });
});
 
 
app.get('/add',(req, res) => {
    res.render('user_add', {
        title : 'CRUD Operation using NodeJS / ExpressJS / MySQL'
    });
});
 
app.post('/save',(req, res) => {
    let data = {name: req.body.name, email: req.body.email, phone_no: req.body.phone_no};
    let sql = "INSERT INTO users SET ?";
    let query = connection.query(sql, data,(err, results) => {
      if(err) throw err;
      res.redirect('/');
    });
});
 
app.get('/edit/:userId',(req, res) => {
    const userId = req.params.userId;
    let sql = `Select * from users where id = ${userId}`;
    let query = connection.query(sql,(err, result) => {
        if(err) throw err;
        res.render('user_edit', {
            title : 'CRUD Operation using NodeJS / ExpressJS / MySQL',
            user : result[0]
        });
    });
});
 
 
app.post('/update',(req, res) => {
    const userId = req.body.id;
    let sql = "update users SET name='"+req.body.name+"',  email='"+req.body.email+"',  phone_no='"+req.body.phone_no+"' where id ="+userId;
    let query = connection.query(sql,(err, results) => {
      if(err) throw err;
      res.redirect('/');
    });
});
 
 
app.get('/delete/:userId',(req, res) => {
    const userId = req.params.userId;
    let sql = `DELETE from users where id = ${userId}`;
    let query = connection.query(sql,(err, result) => {
        if(err) throw err;
        res.redirect('/');
    });
});
 
 
// Server Listening
app.listen(3000, () => {
    console.log('Server is running at port 3000');
});

 

Now you are ready to see the demonstration of CRUD using NodeJS ExpressJS MySQL.

Download  Git Repository

Prev post CRUD Operation using NodeJS ExpressJS MySQL - Part 1
Next post File Upload Using Multer in Node.js and Express

Learn Infinity

Learn Infinity is the most famous Programming & Web Development blog. Our principal is to provide the best online tutorial on web development. We execute the best tutorials for web experts this will help developers, programmers, freelancers and proving free resource you can download or preview the tutorials.

Related Posts

Leave a Reply

Your email address will not be published.

Back to top