MySQL connectivity : Insert,Select Data
index.js
//index.js
const express=require('express')
const mysql=require('mysql2')
const path=require('path')
const bodyParser=require('body-parser')
const app=express()
app.set('view engine','ejs')
app.set(express.static("/public"))
//app.use(bodyParser.urlencoded({extended:true}))
app.use(bodyParser.urlencoded({extended:true}))
app.listen(8080,(err)=>{
if(err)
{
console.log("something went wrong")
}
else
{
console.log("server is running at port 8080")
}
})
app.get('/student',function(req,res){
res.sendFile(path.join(__dirname,'/public/student.html'))
})
app.get('/',function(req,res){
res.send(`This is home page : `)
})
app.get('/sum',function(req,res){
let num1=100;
let num2=20
let sum=num1+num2
//res.send(This is sum ${sum})
res.sendFile(path.join(__dirname,'public/',"student.html"))
})
app.post('/save_student',function(req,res){
// res.send("called savefunction")
const data=req.body
let strData=`Name :${data.stname} <br>Rollno : ${data.strollno} Fee : ${data.stfee} `
//res.send(Name : ${data.stname})
const con=mysql.createConnection({
host:'localhost',
database:'compuhelp',
user:'root',
password:'Ghelp@123'
})
let datavalues=[]//['rohit',345,5678]
datavalues.push(data.stname)
datavalues.push(data.strollno)
datavalues.push(data.stfee)
//{name:data.stname,rollno:data.strollno,fee:data.stfee}
con.query("insert into student values(?,?,?)",datavalues,function(err,result){
if(err)
{
console.error('something went wrong :'+ err.message)
}
else
{
console.log("data inserted into the student table")
con.close()
//res.render('datasaved')
}
})
//res.send(strData)
})
app.get('/save_student',function(req,res){
const data=req.body
res.send(Name : ${data.stname})
})
app.get('/display',function(req,res){
const stdata={stname:'Sunil',strollno:177,stfee:45000}
res.render('display',{data:stdata})//to display ejs files
})
app.get('/displaystudent',function(req,res){
const con=mysql.createConnection({
host:'localhost',
database:'compuhelp',
user:'root',
password:'Ghelp@123'
})
con.query('select * from student',function(err,results){
if(err){
console.log('something went wrong '+ err.message)
}
else
{
res.render('displaystudents',{data:results})
}
})
})
student.html
<!--student.html-->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Student : </title>
</head>
<body>
<h1>This is student form :</h1>
<form action="save_student" method="post">
<input type="text" placeholder="Name" id="stname" name="stname"/>
<input type="text" placeholder="Enter rollno : " id="strollno" name="strollno"/>
<input type="text" placeholder="Enter fee :" id="stfee" name="stfee"/>
<input type="submit" value="submit"/>
</form>
</body>
</html>
display.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Display data</title>
</head>
<body>
<h1>Display data ejs file</h1>
<h2>Name <%=data.stname%></h2>
<h2>Rollno <%=data.strollno%></h2>
<h2>Fee <%=data.stfee%></h2>
<% for(i=0;i<=8;i++){%>
<h3>this is heading 3 <%=i%></h3>
<%}%>
</body>
</html>
displaystudents.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Students data</title>
</head>
<body>
<h1>Data of Student Table</h1>
<ul> <% data.forEach(item => { %>
<li><%= item.name %>
: <%= item.rollno %>
: <%= item.fee %>
</li>
<% }) %>
</body>
</html>
datasaved.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Data Saved</title>
</head>
<body>
<h1>Data saved Successfully</h1>
</body>
</html>