Donny Donny - 18 days ago 5
MySQL Question

using variables versus hardcoding in a where clause with mysql for nodejs

I am using the npm mysql driver for nodejs.
How can I use a variable in the where clause of a query?

//------This does not work.Here I am using the variable in the where clause.------

app.get('/query1', function (req, res) {
var seat_id = req.query.seat_id

connection.query('SELECT * FROM seatpic where seatsid = "seat_id" ',function(err,rows,fields){
if(err){
console.log('There is an error in the query!');
}else {
console.log("value of var seat_id is:" ,seat_id);
console.log('Data received from Db:\n');
console.log(rows);
}

res.json (rows)

});

});


//-----This works. Hardcoding the where clause with a number.---------


app.get('/query1', function (req, res) {
var seat_id = req.query.seat_id

connection.query('SELECT * FROM seatpic where seatsid = 6 ',function(err,rows,fields){
if(err){
console.log('There is an error in the query!');
}else {
console.log("value of var seat_id is:" ,seat_id);
console.log('Data received from Db:\n');
console.log(rows);
}

res.json (rows)

});

});

Answer

You can use ? in your query and provide values as an array as follows

connection.query("SELECT * FROM seatpic where seatsid = ?", [seatid], function(err,rows,fields){...

where [seatid] is an array with your dynamic value.

See the following for more - https://www.npmjs.com/package/mysql#performing-queries