How to query MySQL column against a posted variable?
I am trying to see whether a connected database has an email that matches the one posted by user:
app.post("/forgot_password", (req, res, next) => {
try {
const { emailId } = req.body;
db.query("SELECT * FROM user WHERE email = ?", async (error, results) => {
if (error) {
console.log(error);
}
if (emailId != results) {
//Change to pop up saying incorrect email
res.render("forgot_password")
return
}
})
} catch (error) {
console.log(error)
}
})
How can I check if any of the data in column "email" match the "emailId" const that is posted here by the here:
<form action="" method="POST">
<label for="email">Email</label>
<input type="email" name="emaild" id="email">
<br>
<input type="submit" value="submit">
</form>
Solution 1:
You got to supply the variable to the query
const { emailId } = req.body;
db.query("SELECT * FROM user WHERE email = ?", [emailId], async (error, results) => {
Notice that I added a parameter to the query function. Its signature is:
db.query(sqlString, arrayOfParameters, callbackFuntion)
Here's a link to the documenation: https://github.com/mysqljs/mysql#performing-queries
The query return an array of rows from the database where the email column matches the value of emailId. I assuume emailId is the actual email the user supplied e.g. [email protected]
The results should be only one row since I assume different users can't have the same email.
So to check if the email matches, simply verify that the number of rows is 1
const { emailId } = req.body;
db.query("SELECT * FROM user WHERE email = ?", [emailId], async (error, results) => {
if (error) {
console.log(error);
return // we want to exit the function
}
// so you can see what the results are
console.log(results)
if (results.length === 1) {
console.log('the user supplied an email that exists in our database')
} else {
console.log('no email found', emailId)
}
})