I had created a pet shop web app using nodeJS and MySql. I am able to insert pets info in data successfully but I am not able to delete pets using pet_id. I am getting following error
[ code: 'ER_TRUNCATED_WRONG_VALUE', errno: 1292, sqlMessage: "Truncated incorrect DOUBLE value: 'pa04'", sqlState: '22007', index: 0, sql: "DELETE FROM pets WHERE pet_id=
pet_id= 'pa04'" ] .
But when I give pet_id as Integer i.e; 5 nothing happens, even error didn't come.I had tried putting a Ajax code in my ejs but I failed. Please help me. Thanks in advance :)
const { count } = require('console');
var express = require('express');
var mysql=require('mysql');
const { createConnection } = require('net');
var app = express();
var bodyparser=require('body-parser');
app.set("view engine","ejs");
app.use(bodyparser.urlencoded({extended:true}));
app.use(express.static(__dirname +"/public"))
var connection=mysql.createConnection({
host:'localhost',
user:'root',
database:'mini_project',
password:'password'
});
app.get("/animals", function(req, res){
var q="select animals.pet_id,pet_category,breed,weight,age,height,fur,cost from animals join pets on animals.pet_id= pets.pet_id";
connection.query(q,function(err,results){
if(err) throw err;
res.render("dogs_home",{data:results});
});
});
app.get("/birds", function(req, res){
var q="select birds.pet_id,type,noise,cost from birds join pets on birds.pet_id= pets.pet_id";
connection.query(q,function(err,results){
if(err) throw err;
res.render("birds_home",{data:results});
});
});
app.get("/",function(req,res){
res.render("home");
});
app.get("/accessories", function(req, res){
var q="select * from pet_products";
connection.query(q,function(err,results){
if(err) throw err;
res.render("accessories",{data:results});
});
});
app.get("/sales", function(req, res){
var q="select * from sales_details";
connection.query(q,function(err,results){
if(err) throw err;
res.render("sales",{data:results});
});
});
app.get("/customers", function(req, res){
var q="select * from customer";
connection.query(q,function(err,results){
if(err) throw err;
res.render("customer",{data:results});
});
});
app.get("/ani_add", function(req, res){
res.render("ani_add");
});
app.get("/animal_id_add", function(req, res){
res.render("animal_id_add");
});
app.post("/register_dogs",function(req,res){
var dog={pet_id:req.body.pet_id,
breed:req.body.breed,
weight:req.body.weight,
height:req.body.height,
age:req.body.age,
fur:req.body.fur};
var q="insert into animals set ?"
connection.query(q,dog,function(err,results){
if(err) throw err;
res.redirect("/animals");
});
});
app.post("/register_dogs_id",function(req,res){
var dog={pet_id:req.body.pet_id,
pet_category:req.body.pet_category,
cost:req.body.cost};
var q="insert into pets set ?"
connection.query(q,dog,function(err,results){
if(err) throw err;
res.redirect("/ani_add");
});
});
//route for delete data
app.post('/delete',(req, res) => {
let q = "DELETE FROM pets WHERE pet_id=?";
let dog={pet_id:req.body.pet_id};
connection.query(q,dog,function(err,results){
if(err) throw err;
res.redirect("/animals");
});
});
******** dogs_home.ejs *******
<html>
<link href="https://fonts.googleapis.com/css?family=Roboto:100,300,400" rel="stylesheet">
<link rel="stylesheet" href="/app.css">
<body>
<table id="table" border="1%" width="99.5%"
style="background-color: rgba(169, 169, 169, 0.726)"
>
<thead>
<tr>
<th>pet id</th>
<th>Pet Category</th>
<th>Breed</th>
<th>Weight(Kgs)</th>
<th>Age(Yrs)</th>
<th>Height(Inchs)</th>
<th>Fur</th>
<th>cost</th>
</tr>
</thead>
<tbody>
<% data.forEach(function (user) { %>
<tr>
<td><%= user.pet_id %></td>
<td><%= user.pet_category %></td>
<td><%= user.breed %></td>
<td><%= user.weight %></td>
<td><%= user.age %></td>
<td><%= user.height %></td>
<td><%= user.fur %></td>
<td><%= user.cost %></td>
</tr>
<% }) %>
</tbody>
</table>
<div>
<a href="/animal_id_add"><button>Add new animal</button></a>
</div>
<div>
<form action="/delete" method="post">
<input type="text" class="form" name="pet_id" placeholder="Pet Id">
<button>DELETE</button>
</form>
</div>
</body>
</html>
******* animal_id_add.ejs *******
<html><body>
<div>
<form method="POST" action='/register_dogs_id'>
<input type="text" class="form" name="pet_id" placeholder="Pet Id">
<input type="text" class="form" name="pet_category" placeholder="pet_category">
<input type="text" class="form" name="cost" placeholder="cost">
<button type="submit">Add</button>
</form>
</div>
</body></html>
******* ani_add.ejs ********
<html>
<body>
<div>
<form method="POST" action='/register_dogs'>
<input type="text" class="form" name="pet_id" placeholder="Pet Id">
<input type="text" class="form" name="breed" placeholder="breed">
<input type="text" class="form" name="weight" placeholder="weight">
<input type="text" class="form" name="height" placeholder="height">
<input type="text" class="form" name="age" placeholder="age">
<input type="text" class="form" name="fur" placeholder="fur">
<button type="submit">Add</button>
</form>
</div>
</body>
</html>
