Scribd
Upload
95 views8 pages

CrudExpressj Mysql

The document describes creating a Node.js/Express/MySQL web application to perform CRUD operations on a university database. It includes code to: 1. Define routes and database connection 2. Display, add, edit and delete records using EJS templates 3. Create HTML forms using Bootstrap for data entry

Uploaded by

Jaineel Bhavsar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
95 views8 pages

CrudExpressj Mysql

The document describes creating a Node.js/Express/MySQL web application to perform CRUD operations on a university database. It includes code to: 1. Define routes and database connection 2. Display, add, edit and delete records using EJS templates 3. Create HTML forms using Bootstrap for data entry

Uploaded by

Jaineel Bhavsar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 8

CSPIT_CE CE377-AWT 19CE006

ASSIGNMENT
AIM: Create html form using bootstrap with following information

1. Course-id, Course Name [text box]


2. Department Name [Radio Button]
3. Institute Name [Check Box]
4. University Name [Drop Down]

Display all Record into dynamic table with edit and delete operation.

CODE:
//server.js
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 university";
let query = connection.query(sql, (err, rows) => {
if(err) throw err;
CSPIT_CE CE377-AWT 19CE006

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 = {cid: req.body.cid, cname: req.body.cname, dname: req.body.dname, iname: req.body.iname,
uname: req.body.uname};
let sql = "INSERT INTO university 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 university 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 university SET cid='"+req.body.cid+"', cname='"+req.body.cname+"', dname='"+req.b
ody.dname+"', iname='"+req.body.iname+"', uname='"+req.body.uname+"' where id ="+userId;
let query = connection.query(sql,(err, results) => {
if(err) throw err;
res.redirect('/');
});
});

app.get('/delete/:userId',(req, res) => {


CSPIT_CE CE377-AWT 19CE006

const userId = req.params.userId;


let sql = `DELETE from university where id = ${userId}`;
let query = connection.query(sql,(err, result) => {
if(err) throw err;
res.redirect('/');
});
});

// Server Listening
app.listen(5000, () => {
console.log('Server is running at port 5000');
});
//user_edit.ejs
<!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="cid">Course ID</label>
<input type="text" class="form-
control" id="cid" placeholder="Enter Course ID" value="<%= user.cid %>" name="cid" required>
</div>
<div class="form-group">
<label for="cname">Course name:</label>
<input type="text" class="form-
control" id="cname" placeholder="Enter Course id" value="<%= user.cname %>" name="cname" require
d>
</div>
<div class="form-group">
<label for="dname">department name:</label>
<input type="text" class="form-
control" id="dname" placeholder="Enter Course name" value="<%= user.dname %>" name="dname" req
uired>
</div>
CSPIT_CE CE377-AWT 19CE006

<div class="form-group">
<label for="iname">Institute name:</label>
<input type="text" class="form-
control" id="iname" placeholder="Enter department name" value="<%= user.iname %>" name="iname" r
equired>
</div>
<div class="form-group">
<label for="uname">University name:</label>
<input type="text" class="form-
control" id="uname" placeholder="Enter University name" value="<%= user.uname %>" name="uname" r
equired>
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
</div>

</body>
</html>
//user_add.ejs
<!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="cid">Course id:</label>
<input type="text" class="form-
control" id="cid" placeholder="Enter Course id" name="cid" required>
</div>
<div class="form-group">
<label for="cname">Course name:</label>
<input type="text" class="form-
control" id="cname" placeholder="Enter Course name" name="cname" required>
</div>
<div class="form-group">
<label for="dname">department name:</label>
CSPIT_CE CE377-AWT 19CE006

<input type="text" class="form-


control" id="dname" placeholder="Enter department name" name="dname" required>
</div>
<div class="form-group">
<label for="iname">Institute name:</label>
<input type="text" class="form-
control" id="iname" placeholder="Enter Institute name" name="iname" required>
</div>
<div class="form-group">
<label for="uname">University name:</label>
<input type="text" class="form-
control" id="uname" placeholder="Enter University name" name="uname" required>
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
</div>

</body>
</html>

//user_ejs
<!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>
<a href="/add" class="btn btn-sm btn-primary">Add User</a><br/><br/>
<table class="table table-dark table-striped">
<thead>
<tr>
<th>Course id</th>
<th>Course name</th>
<th>department name</th>
<th>institute name</th>
<th>university name</th>
</tr>
</thead>
CSPIT_CE CE377-AWT 19CE006

<tbody>
<% users.forEach(function(university){ %>
<tr>
<td><%= university.cid %></td>
<td><%= university.cname %></td>
<td><%= university.dname %></td>
<td><%= university.iname %></td>
<td><%= university.uname %></td>
<td>
<a href="edit/<%= university.id %>" class="btn btn-sm btn-primary">Edit</a>
<a href="delete/<%= university.id %>" class="btn btn-sm btn-danger">Delete</a>
</td>
</tr>
<% }); %>
</tbody>
</table>
</div>

</body>
</html>

OUTPUT:
CSPIT_CE CE377-AWT 19CE006

//inserting data

//displaying all data inserted

//Editing current data


CSPIT_CE CE377-AWT 19CE006

//deleting data

You might also like