JavaScript Array in a SQL query?

Question

Is it possible to use a javascript array in a sql query?

I want to use studentsId as a parameter in the second query.

db.query('SELECT users.* FROM users JOIN classes ON users.class_id=classes.id WHERE classes.id ="' + req.body.classSelect + '" ', function (err, rows) {

    // Saves all my users id that i get from my first query
    var studentsId = [];
    for (var i = 0; i < rows.length; i++) {
        studentsId.push(rows[i].id);
    }
    console.log(studentsId);

    // in this query i want to use studentsId
    db.query("SELECT users.id, users.first_name, users.last_name,attendances.type_id,attendances.attendance_timestamp FROM users JOIN attendances ON attendances.user_id = users.id WHERE attendance_timestamp BETWEEN '2016-01-01' AND '2016-12-31' AND user_id IN ('"+ studentsId +"') ", function (err, result) {
        if (err) {
            throw err;
        }
        console.log(result);
    });
});

Don't put quotes around studentsId when you concatenate it. That makes it just a single element in the IN() list, instead of a list of IDs. — Barmar
– Barmar, Commented Jan 12, 2017 at 21:31

Sherlock · Accepted Answer · 2017-06-22 10:17:24Z

5

Yes, but not exactly as the way you do it.

I would change

studentsId.push(rows[i].id);

to

studentsId.push('"' + rows[i].id + '"');

Next, change this line

db.query("SELECT users.id, users.first_name, users.last_name,
   attendances.type_id,attendances.attendance_timestamp FROM users 
   JOIN attendances ON attendances.user_id = users.id WHERE 
   attendance_timestamp BETWEEN '2016-01-01' AND '2016-12-31' 
   AND user_id IN ('"+ studentsId +"') ", function (err, result) {

to this

db.query("SELECT users.id, users.first_name, users.last_name,
   attendances.type_id, attendances.attendance_timestamp FROM users 
   JOIN attendances ON attendances.user_id = users.id WHERE
   attendance_timestamp BETWEEN '2016-01-01' AND '2016-12-31' 
   AND user_id IN (' + studentsId.join(',') + ') ", function (err, result) {

Then your code should run fine.

The first change adds the quotes to the ID's. This might not be nessecary if your ID's are numeric.

The second change joins the ID's together using a comma, so they can be used in the on clause.

edited Jun 22, 2017 at 10:17

Sherlock

6108 silver badges21 bronze badges

answered Jan 12, 2017 at 21:35

Kontsnor

1464 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Barmar Over a year ago

When an array is converted to a string, it automatically joins with comma, so that's effectively what he was doing, except for the added quotes around it.

Kontsnor Over a year ago

Wait what, all these years I've been using join for nothing -.-

Barmar Over a year ago

If you're displaying to the user, you usually want to put a space after the comma.

Barmar · Accepted Answer · 2017-01-12 21:34:51Z

0

Just add the JOIN with attendances into the first query, instead of making a separate query.

db.query('SELECT users.* FROM users JOIN classes ON users.class_id=classes.id join attendances ON attendances.user_id = users.id WHERE classes.id ="' + req.body.classSelect + '" AND attendance_timestamp BETWEEN "2016-01-01" AND "2016-12-31" ', function (err, rows) {

answered Jan 12, 2017 at 21:34

Barmar

789k57 gold badges554 silver badges669 bronze badges

Collectives™ on Stack Overflow

JavaScript Array in a SQL query?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related