getting error while trying to make put request to mysql in nodejs

user_19040773 0
Viewed 54

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"

express node.js mysql
2 Answers

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.

user_80368140

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
            })
        }
    })
}
user_190407730
Related