How do I post data from form to a particular row?-CodePudding

My nodejs code...

app.post('/src/grades-form', function(req, res){
  var daa = req.body.daa;
  var os = req.body.os;
  var dldm = req.body.dldm;
  var ptrp = req.body.ptrp;
  var bhr = req.body.bhr;
  var prn = req.query.prn;
  var sql = "INSERT INTO grades (daa, os, dldm, ptrp, bhr) VALUES ('" daa "','"  os "','"  dldm "','"  ptrp "','"  bhr "') WHERE prn = ?";
  connection.query(sql, [prn], function(error, results){
    if(error) throw error;
    res.send(`Data stored successfully !!<br><a href="/src/dashboard-admin">Return to dashboard.</a>`);
  });
});

Error I'm getting...

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 'WHERE prn = '1'' at line 1

How do I overcome it? I will provide more info if this isn't sufficient... Thank you

CodePudding user response：

What you could do is perform a SELECT to check if the row is present in the database, if so perform an update, otherwise insert a new row:

app.post('/src/grades-form', function (req, res) {
  const { daa, os, dldm, ptrp, bhr } = req.body;
  var prn = req.query.prn;
  const sqlSelect = 'SELECT * FROM grades WHERE prn = ?';
  connection.query(sqlSelect, [prn], (err, result) => {
    if (err) throw err;
    if (result.length === 0) {
      // Not found, insert new value
      var sqlInsert =
        'INSERT INTO grades (daa, os, dldm, ptrp, bhr) VALUES (?, ?, ?, ?, ?)';
      connection.query(
        sqlInsert,
        [daa, os, dldm, ptrp, bhr],
        function (error, results) {
          if (error) throw error;
          res.send(
            `Data inserted successfully !!<br><a href="/src/dashboard-admin">Return to dashboard.</a>`
          );
        }
      );
    } else {
      // Value is present, update existing one
      var sqlUpdate =
        'UPDATE grades SET daa = ?, os = ?, dldm = ?, ptrp = ?, bhr = ? WHERE prn = ?';
      connection.query(
        sqlUpdate,
        [daa, os, dldm, ptrp, bhr, prn],
        function (error, results) {
          if (error) throw error;
          res.send(
            `Data updated successfully !!<br><a href="/src/dashboard-admin">Return to dashboard.</a>`
          );
        }
      );
    }
  });
});

CodePudding user response：

The INSERT INTO statement cannot have a WHERE clause.

If you're trying to update an existing row, use:

UPDATE grades SET daa=value, os=value, etc=value WHERE prn = 1