I am getting this error while I am trying to send a put request to update a row of a product table in mysql using nodejs. Here's my code:
exports.updateProduct=(req,res)=>{
const productId=req.params.productId;
const keys=Object.keys(req.body);
const data=keys.map(e=>{
const value=e+'='+req.body[e];
return value
})
const sql='update product set ? where productId=?';
connectDB.query(sql,[data,productId],(err,result)=>{
if(err){
res.status(400).json({
status:'failed',
message:err.message
})
}
else{
res.status(200).json({
status:'success',
message:result
})
}
})
}
while I am trying to send request using postman I'm getting this error:
ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''productTitle=accha product' where productId='19'' at line 1"
I'm not sure what sql client you are using as connectDB but according to this page https://www.w3schools.com/sql/sql_update.asp, the correct syntax is like this (replace columnName with your own column name)
UPDATE product
SET columnName= ?
WHERE productId = ?;
My guess is you're missing the columnName= part.
As mentioned by @ardritkrasniqi when I added double quote to the req.body[e] at line five it started working!
Also instead of using placeholder(?) for data I used it directly in the SQL query using template literals.
exports.updateProduct = (req, res) => {
const productId = req.params.productId;
const keys = Object.keys(req.body);
const data = keys.map(e => {
const value = e + '=' + '"' + req.body[e] + '"';
return value
})
const sql = `update product set ${data} where productId=?`;
connectDB.query(sql, [productId], (err, result) => {
if (err) {
res.status(400).json({
status: 'failed',
message: err.message
})
} else {
res.status(200).json({
status: 'success',
message: result
})
}
})
}