I currently have an app, which displays certain data which is retrieved from a MySQL cloud database (JawsDB). I am having some difficulty passing in a specific field into my SQL statement.
It essentially retrieves the job from a dropdown menu, then uses axios to fetch the data from my database.
useEffect(() => {
(async() => {
const result = await axios.get('/submissions/${jobCategory}')
setSubmissions(result.data.submissions)
})()
}, [])
Here is the get request:
app.get('/submissions/:jobCategory', (req, res) => {
sql_database.getSubmissionsCategory((error, submissions) => {
if (error) {
res.send({error: error.message});
return
}
res.send({submissions});
})
});
And finally, here is the sql statement:
function getSubmissionsCategory(callback) {
const query = `
SELECT * FROM submissions
WHERE jobCategory="data analyst"
`
connection.query(query, (error, results) => {
if (error) {
callback(error)
return
}
callback(null, results)
})
}
exports.getSubmissionsCategory = getSubmissionsCategory;
My code works when I manually specify the specific job type such as above. My question is now that I have passed in the jobCategory into axis, how do I dynamically update my SQL statement according to what is being passed in?
E.g. I pass in "Software Engineer", I would like the SQL statement to be : SELECT * FROM submissions WHERE jobCategory="Software Engineer"
You need to pass the value stored in jobCategory to the getSubmissionsCategory function. Then you need to alter the query based on your request.
For example:
app.get('/submissions/:jobCategory', (req, res) => {
sql_database.getSubmissionsCategory(req.params.jobCategory, (error, submissions) => {
// ...
}
})
function getSubmissionsCategory(jobCategory, callback) {
// ...
connection.query("SELECT * FROM table WHERE id = ?", [jobCategory], callback)
// ...
}
If you are using the npm package mysql, take a look at the docs about performing queries with variables. (or for mysql2).