javascriptmysqlnode.jssql-updatemysql-error-1054

Error 1054 - Variable used as field in WHERE is taken as column


i'm trying to update a mysql db but when i execute the query i get an error where var id (used as filed in WHERE) is used as column name. Am i reading it wrong? what i have to fix to let this work?

Console Log

var message = '';
var id = req.session.user.id;
console.log(req.session.user.id);
var post  = req.body;
var worker= post.worker;
var farmer= post.farmer;
var soldier= post.soldier;
var defender= post.defender;
         
var sql= "UPDATE stats SET worker = `"+worker+"`, farmer = `"+farmer+"`, soldier = `"+soldier+"`, defender = `"+defender+"` WHERE `stats`.`id` = `"+id+"` ";
      
db.query(sql, function(err, results){
  if(results){
    res.send('/home/dashboard.ejs')
  };
            
  if(err){console.log(err)};
 });
}


Solution

  • Try this:

    var sql= "UPDATE stats SET worker = '"+worker+"', farmer = '"+farmer+"', soldier = '"+soldier+"', defender = '"+defender+"' WHERE id = "+id+" ";
    

    I removed the back ticks from the id column name at the end (unnecessary) and properly surrounded the strings with single quote instead of back ticks. Also no need for the table name.