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:
- Getting value from the web app page. If
col4 don't exist in your table then you can change to a different column name.
- Send a MysQL query to check if the value exists.
- 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.
- 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.
- If the checking doesn't return anything then
else here will start with getting all the values you want to insert.
- 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.
INSERTis for creating new rows, butWHEREis for matching existing rows. So what could it mean to useWHEREin anINSERTquery?UPDATEorWHERE, you have to use column names.GROUP BYandORDER BY-- they refer to the ordinal position in theSELECTlist, not table columns.ALTER TABLEbefore you can put any data into it.