I am using postman to test my api and this is a post request.

when i put the request url in postman it should say http://localhost:3000/product/1/food/ with the 1 being :id in my code

I want to execute this sql statement in my node.js

INSERT INTO food (userid, rating, fooditem) VALUES (?, ?, ?) WHERE productid=?' 

productid is my :id

to get my table to show

the foodid and timestamps are auto incremented

but it gives me

ER_PARSE_ERROR

I have tried using

UPDATE food
SET userid=? , rating=? , review=?
WHERE productid=?
            

based off other posts I have found here, however nothing pops up in my table when i do this although it is not counted as an error in my code

What should I do? thank you

CodePudding user response：

you don't use WHERE to specify the productid column, it should just be another column that you insert into.

INSERT INTO food (userid, rating, fooditem, productid) VALUES (?, ?, ?, ?)

CodePudding user response：

If your column names are actually using numbers, I suggest you change that first, probably to something like col1, col2 ... I think those were just quick examples you posted but just in case.

For the INSERT part, here's an idea:

let col4val = [req.body.col4val]; /*1*/

let cquery = `SELECT * FROM table WHERE col4=?`; /*2*/
db.query(cquery, [col4val], function(err, result) {
  if (err) {
    return res.status(500).send(err); /*3*/
  }
  if (result.length > 0) {
    res.redirect("/page?Insert=failed") /*4*/
  } else {
    let col1val = [req.body.col1val]; /*5*/
    let col2val = [req.body.col2val];
    let col3val = [req.body.col3val];

    let iquery = `INSERT INTO table VALUES(?, ?, ?)`; /*6*/
    db.query(uquery, [col1val, col2val, col3val], function(err, result) {
      if (err) {
        return res.status(500).send(err);
      }
      res.redirect("/page?Insert=success");
    })
  }
})
}

This is based on a web app I develop a couple of years ago; the code might not be something you're familiar with but what matters here is the idea behind it. I'll try to break it down:

1. Getting value from the web app page. If col4 don't exist in your table then you can change to a different column name.
2. Send a MysQL query to check if the value exists.
3. Return MySQL error message on the web app IF the query have errors. Note that this might not return anything even if there's an error so you might want to try outputting the error message through console module.
4. If the checking returns result then redirect to a page saying that insert failed (assuming you have one). You probably can work something like showing a popup instead of redirecting to a different page.
5. If the checking doesn't return anything then else here will start with getting all the values you want to insert.
6. The INSERT query being sent to MySQL server with desired values. The operation is largely similar to the checking part with error returning is first (if any) then redirecting to a success page.

The code above actually coming from my "update user password" module and the table I had wasn't with the correct PRIMARY KEY. However, just now I realize that this code can be much shorter if I'm utilizing the PRIMARY KEY. I'm not sure if I did it that way because of the redirect operation or not. Here's a shorter code you can try:

let col1val = [req.body.col1val];
let col2val = [req.body.col2val];
let col3val = [req.body.col3val];

let iquery = `INSERT IGNORE INTO table VALUES(?, ?, ?)`;
db.query(uquery, [col1val, col2val, col3val], function(err, result) {
  if (err) {
    return res.status(500).send(err);
  }
  res.redirect("/page?Insert=success");
})

Maybe, with PRIMARY KEY on all three of the col1val, col2val, col3val columns.