node js crud operations example using express with mysql – onlinecode

var express = require('express');
var router  = express();
var dateTime = require('node-datetime');
var dt = dateTime.create();
var curdate = dt.format('Y-m-d H:M:S');
const { check, validationResult } = require('express-validator');


//following route to the get of student list
router.get('/', function(req, res, next) {
    req.getConnection(function(error, conn) {
        conn.query('SELECT * FROM students ORDER BY id DESC',function(err, rows, fields) {
            if (err) {
                res.render('index', {
                    title: 'Student List', 
                })
            } else {
                res.render('index', {
                    title: 'Student List', 
                    data: rows,
                    errors :''
                });
            }
        });
    });
});
 
//following route to the show add student form
router.get('/add', function(req, res, next){    
    res.render('add', {
        title: 'Add New Student',
        errors :'',
        txtFname: '',
        txtLname: '',
        txtEmail: '',        
        txtMobile: ''        
    })
});

//following route to the add student data
router.post('/add', [
    check('txtFname').not().isEmpty().withMessage('First Name is Required Field'),
    check('txtLname').not().isEmpty().withMessage('Last Name is Required Field'),
    check('txtEmail').isEmail().normalizeEmail().withMessage('Email is Not Valid'),
    check('txtMobile').not().isEmpty().withMessage('Mobile is Required Field')
  ],
  function (req, res) {
    const errors = validationResult(req);

    if(!errors.isEmpty()){
        res.render('add',{
            errors :errors.array(),
            title: 'Add New User',
            errors :'',
            txtFname: req.body.txtFname,
            txtLname: req.body.txtLname,
            txtEmail: req.body.txtEmail,   
            txtMobile: req.body.txtMobile
        });
       
    }else{

        var student = {
            first_name: req.body.txtFname.trim(),
            last_name: req.body.txtLname.trim(),
            email: req.body.txtEmail.trim(),
            mobile: req.body.txtMobile.trim(),
            created_at: curdate
        }
        
        req.getConnection(function(error, conn) {
            conn.query('INSERT INTO students SET ?', student, function(err, result) {
                           
                req.flash('success', 'Data added successfully!')
                res.render('add', {
                    title: 'Add New Student',
                    errors :'',
                    txtFname: '',
                    txtLname: '',
                    txtEmail: '',        
                    txtMobile: ''                      
                });
                
            });
        });
    }
  });

//following route to the show edit form student data
router.get('/edit/(:id)', function(req, res, next){
    req.getConnection(function(error, conn) {
        conn.query('SELECT * FROM students WHERE id = ' + req.params.id, function(err, rows, fields) {
       
            if (rows.length <= 0) {
                req.flash('error', 'Student not found with id = ' + req.params.id);
                res.redirect('/');
            }
            else { 
                
                res.render('edit', {
                    title: 'Edit Student', 
                    errors :'',
                    id: rows[0].id,
                    txtFname: rows[0].first_name,
                    txtLname: rows[0].last_name,
                    txtEmail: rows[0].email,                    
                    txtMobile: rows[0].mobile,
                    created_at: curdate                    
                });
            }            
        });
    });
});

//following route to the edit student data
router.put('/edit/(:id)', [
    check('txtFname').not().isEmpty().withMessage('First Name is Required Field'),
    check('txtLname').not().isEmpty().withMessage('Last Name is Required Field'),
    check('txtEmail').isEmail().normalizeEmail().withMessage('Email is Not Valid'),
    check('txtMobile').not().isEmpty().withMessage('Mobile is Required Field')
  ],
  function (req, res) {
    const errors = validationResult(req);

    if(!errors.isEmpty()){
        res.render('edit',{
            errors :errors.array(),
            title: 'Edit User',
            id: req.params.id,
            txtFname: req.body.txtFname,
            txtLname: req.body.txtLname,
            txtEmail: req.body.txtEmail,   
            txtMobile: req.body.txtMobile
        });
       
    }else{

        var student = {
            first_name: req.body.txtFname.trim(),
            last_name: req.body.txtLname.trim(),
            email: req.body.txtEmail.trim(),
            mobile: req.body.txtMobile.trim()
        }
        
        req.getConnection(function(error, conn) {
            conn.query('UPDATE students SET ? WHERE id = ' + req.params.id, student, function(err, result) {
                           
                req.flash('success', 'Data updated successfully!')
                res.render('edit', {
                    title: 'Edit Student',
                    errors :'',
                    id: req.params.id,
                    txtFname: req.body.txtFname,
                    txtLname: req.body.txtLname,
                    txtEmail: req.body.txtEmail,   
                    txtMobile: req.body.txtMobile                   
                });
                
            });
        });
    }
  });

 
//Below following route to the delete student data
router.delete('/delete/(:id)', function(req, res, next) {
    var student = { id: req.params.id };
    
    req.getConnection(function(error, conn) {
        conn.query('DELETE FROM students WHERE id = ' + req.params.id, student, function(err, result) {
            if (err) {
                res.redirect('/');
            } else {
                req.flash('success', 'Student deleted successfully! id = ' + req.params.id);
                res.redirect('/');
            }
        });
    });
});
 
module.exports = router;