2

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 enter image description here

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

8
  • 3
    INSERT is for creating new rows, but WHERE is for matching existing rows. So what could it mean to use WHERE in an INSERT query? Commented Dec 26, 2021 at 22:31
  • You can't use column numbers in UPDATE or WHERE, you have to use column names. Commented Dec 26, 2021 at 22:32
  • The only places where you can use numbers in place of names is in GROUP BY and ORDER BY -- they refer to the ordinal position in the SELECT list, not table columns. Commented Dec 26, 2021 at 22:33
  • What do you mean by updating a nonexistent column? If the column doesn't exist, you have to add it with ALTER TABLE before you can put any data into it. Commented Dec 26, 2021 at 22:34
  • Please tell us in words and with a sample table before and after the query what you want to get as an endresult. Your query doesn't make sense, e.g. from seeing your query we cannot even guess what you actually want to do. Commented Dec 27, 2021 at 0:02

2 Answers 2

1

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 (?, ?, ?, ?)
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.