Node JS
Node js Using CRUD Operation With Mysql DB
Prerequisites:
1. Nodejs
2. Expressjs
3. Mysql
4. EJS
5. Body Parser
Steps:
·
Create
Directory Mkdir node_crud
·
Npm init
–y Type this command automatically Created Package.json
·
Then
Install Dependencies
·
Npm install
–save express ejs body-parser mysql nodemon
·
Switch root
directory create index.js or else whatever you want file name
·
Write
some codes in index.js file
Index.js:
const express=require('express');
const mysql=require('mysql');
const bodyparser=require('body-parser');
const ejs=require('ejs');
const path=require('path');
const session = require('express-session');
const app = express();
const conn=mysql.createConnection({
host:'localhost',
username:'root',
password:'',
database:'test'
});
conn.connect(function(error){
if(!error){
console.log("conncetion succesfully");
}else{
console.log(error);
}
})
app.set('views', path.join(__dirname, 'public'));
app.set('view engine','ejs');
app.use(bodyparser.json());
app.use(bodyparser.urlencoded({ extended: false }));
app.use(session({secret:"Helloworld@123;"}));
app.get("/",(req,res)=>{
var sql="select * from booking";
// var cnt=req.session.cnt++;
var query=conn.query(sql,(err,rows)=>{
res.render('index',{
data:rows,
//pagecnt:cnt
});
});
})
app.get("/signup",(req,res)=>{
res.render('signup');
})
app.get("/login",(req,res)=>{
res.render("login");
});
app.post("/authenticate",(req,res)=>{
var name=req.body.title;
var no=req.body.mobile_no;
var sql="select * from booking where Title='"+name+"' and mobile='"+no+"'";
var query=conn.query(sql,(err,result)=>{
if(result.length > 0 ){
req.session.username=name;
res.send("Valid Credentials welcome Mr/Ms "+req.session.username);
}else{
res.send("Not Valid Credentials");
}
})
});
app.post("/register",(req,res)=>{
var title=req.body.title;
var mobile=req.body.mobile_no;
var start=req.body.start;
var end=req.body.end;
var sql="insert into booking values(NULL,'"+title+"','"+mobile+"','"+start+"','"+end+"')";
var query=conn.query(sql,(err,result)=>{
res.redirect("/");
})
});
app.post("/update",(req,res)=>{
var title=req.body.title;
var mobile=req.body.mobile_no;
var start=req.body.start;
var end=req.body.end;
var id=req.body.id;
var sql="update booking set Title='"+title+"',mobile='"+mobile+"',Start='"+start+"',End='"+end+"' where Id='"+id+"'"
var query=conn.query(sql,(err,result)=>{
res.redirect("/");
})
});
app.get("/edit/:id",(req,res)=>{
var id=req.params.id;
var sql="select * from booking where Id='"+id+"'";
var query=conn.query(sql,(err,records)=>{
res.render("edit",{
user_details:records
})
//res.send(records);
})
});
app.get("/delete/:id",(req,res)=>{
var id=req.params.id;
var sql="delete from booking where Id='"+id+"'";
var query=conn.query(sql,(err,results)=>{
res.redirect("/");
});
});
app.listen(2000,()=>{
console.log("running");
})
// HTML files
Index.ejs:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<h2 class="text-center">Crud Using Nodejs</h2>
<br>
<br>
<a href="/signup" class="btn btn-success">Register</a>
<a href="/login" class="btn btn-info">Login</a>
<table class="table table-responsive table-striped">
<thead>
<tr>
<th>Title</th>
<th>Mobile</th>
<th>Start</th>
<th>End</th>
<th>Action</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<% data.forEach(function(row){%>
<tr>
<td><%= row.Title %></td>
<td><%= row.mobile %></td>
<td><%= row.Start %></td>
<td><%= row.End %></td>
<td><a href="/edit/<%= row.Id %>" class="btn btn-info">Edit</a></td>
<td><a href="/delete/<%= row.Id %>" class="btn btn-danger">Delete</a></td>
</tr>
<% }); %>
</tbody>
</table>
</div>
</body>
</html>
Register.ejs:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Bootstrap Example</title>
<meta charset="utf-8">
</head>
<body>
<div class="container">
<h2>Signup form </h2>
<form action="/register" method="post">
<div class="form-group">
<label for="email">Title:</label>
<input type="text" class="form-control" id="email" placeholder="Enter Title" name="title">
</div>
<div class="form-group">
<label for="pwd">Mobile:</label>
<input type="number" class="form-control" id="pwd" placeholder="Enter Mobile" name="mobile_no">
</div>
<div class="form-group">
<label>Start</label>
<input type="date" name="start" class="form-control" />
</div>
<div class="form-group">
<label>End</label>
<input type="date" name="end" class="form-control" />
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
</div>
</body>
</html>
Edit.ejs:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Bootstrap Example</title>
<meta charset="utf-8">
</head>
<body>
<div class="container">
<h2>Update form </h2>
<form action="/update" method="post">
<div class="form-group">
<label for="email">Title:</label>
<input type="hidden" name="id" value="<%= user_details[0]['Id'] %>" />
<input type="text" value="<%= user_details[0]['Title'] %>" class="form-control" id="email" placeholder="Enter Title" name="title">
</div>
<div class="form-group">
<label for="pwd">Mobile:</label>
<input type="number" value="<%= user_details[0]['mobile'] %>" class="form-control" id="pwd" placeholder="Enter Mobile" name="mobile_no">
</div>
<div class="form-group">
<label>Start</label>
<input type="date" name="start" value="<%= user_details[0]['Start'] %>" class="form-control" />
</div>
<div class="form-group">
<label>End</label>
<input type="date" name="end" value="<%= user_details[0]['End'] %>" class="form-control" />
</div>
<button type="submit" class="btn btn-primary">Publish</button>
</form>
</div>
</body>
</html>
File Directory Structure:
Root
Node_modules
index.js
package.json
Public
edit.ejs
index.ejs
register.ejs
Then Run Node index.js
