I have db airport, there TABLE tourists and COLUMN: id and TABLE flights, and there column: listoftouristsbyid which is array of integers. I need to check if i passed to REST API request integer that is id of Tourist that not EXISTS. Means there is no tourist with given id (id is primary, autoincrementing key). I wrote something like that:
app.post('/flights', function(req, res) {
pool.connect(function(err,client,done) {
if(err) {
return console.log("Error fetching clients", err);
}
for (let i=0;i<req.body.listoftouristsbyid.length();i++) {
if (client.query("EXISTS (SELECT * FROM tourists WHERE tourists.id = " + req.body.listoftouristsbyid[i]) == null)
return console.log("Tourist "+req.body.listoftouristsbyid[i]+" you want to add does not exist");
}
client.query('INSERT INTO flights(departuredate, arrivaldate, numberofseats, listoftouristsbyid, ticketprice) VALUES($1, $2, $3, $4, $5)',
[req.body.departuredate, req.body.arrivaldate, req.body.numberofseats, req.body.listoftouristsbyid, req.body.ticketprice]);
done();
res.redirect('/flights');
})
})
But its obviously not working, and even when i type this command into POSTGRESQL it gives me syntax error. So i need QUERY that gets evaluated, and when its false it should escape function and stop from passing numbers that are IDs of items that does not EXISTS. Please help
EDIT: I tried with legit SELECT query, still to no avail. Return does not break function and POST request adds new flight, even though it should not. Here is code"
app.post('/flights', function(req, res) {
pool.connect(function(err,client,done) {
if(err) {
return console.log("Error fetching clients", err);
}
client.query("SELECT tourists.id FROM tourists WHERE tourists.id = " + req.body.listoftouristsbyid[i], function (err, result) {
done();
if (result.rows === 0) {
return console.log("Tourist "+req.body.listoftouristsbyid[i]+" you want to add does not exist" + err);
}
})
client.query('INSERT INTO flights(departuredate, arrivaldate, numberofseats, listoftouristsbyid, ticketprice) VALUES($1, $2, $3, $4, $5)',
[req.body.departuredate, req.body.arrivaldate, req.body.numberofseats, req.body.listoftouristsbyid, req.body.ticketprice]);
done();
res.redirect('/flights');
})
})
