mysql - JavaScript Array in a SQL query? - Stack Overflow

admin管理员组

文章数量:1320888

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);
    });
});

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);
    });
});

• javascript
• mysql
• express

Share Improve this question Follow edited Jan 12, 2017 at 21:27 s0ckan asked Jan 12, 2017 at 21:22 s0ckans0ckan 5711 gold badge11 silver badge77 bronze badges 3

• Only if you send it to the server using for example AJAX – mplungjan Commented Jan 12, 2017 at 21:27
• Why don't you just bine the two queries with JOIN? – Barmar Commented Jan 12, 2017 at 21:30
• 1 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 Commented Jan 12, 2017 at 21:31

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 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 ma, so they can be used in the on clause.

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) {

本文标签： mysqlJavaScript Array in a SQL queryStack Overflow