How to use result array of a query in another query in Mysql+Node JS?

Question

I want to use the result of first query(sql1) in second query(sql2). The result of first query(sql1) is getting in .ejs page but no result for the second query(sql2).

 var sql1 = 'SELECT * FROM `Rooms`';
    con.query(sql1,function(err,rows,fields){
    if(!err)
    {
        var count=rows.length;
        for(i=0;i<count;i++)
        {
            arr_room[i]=rows[i].room_name;
        }
    }
    else
    {
        console.log('error...');

    }
  });

    var sql2 = 'SELECT * FROM Lights WHERE room_name =? ' ;
    con.query(sql,arr_room[0],function(err,rows,fields){
          if(!err)
          {
              var count=rows.length;
              for(i=0;i<count;i++)
              {
                arr_icon[i]=rows[i].icon;

              }
          }
          else
          {
              console.log('error...');

          }

     res.render('lighting',{arr_icon,arr_room}); 
    });

darklightcode · Accepted Answer · 2018-12-06 11:09:26Z

3

You need to nest sql2 into sql1, in nodejs everything is asynchronous, that means you must wait for something to finish first.

And you had a typo on the second query, you called sql instead of sql2

var sql1 = 'SELECT * FROM `Rooms`';
con.query(sql1, function(err, rows, fields) {
  if (!err) {
    var count = rows.length;
    //for (i = 0; i < count; i++) {
    //  arr_room[i] = rows[i].room_name;
    //}

    if (count) {

      // simplified version instead of for
      var arr_room = rows.map(i => i.room_name); 

      // you can safely use it inline since data comes straight from the database
      // query will use `in` condition: room_name in ('roomX','roomY', 'roomZ')
      var sql2 = `SELECT * FROM Lights WHERE room_name in ('${arr_room.join("','")}') `; 
      con.query(sql2, function(err, rows, fields) {
        if (!err) {
          var count = rows.length;
          for (i = 0; i < count; i++) {
            arr_icon[i] = rows[i].icon;

          }
        } else {
          console.log('error...');

        }

        res.render('lighting', {
          arr_icon,
          arr_room
        });
      });

    } else {

      console.log('no records to pass for sql2');

    }

  } else {
    console.log('error...');

  }
});

answered Dec 6, 2018 at 11:09

darklightcode

2,7721 gold badge16 silver badges19 bronze badges

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

1 Comment

Pv66 Over a year ago

Ah, yes indeed!! Forgot this silly thing and I was struggling because of this. Thanks!!

Connor · Accepted Answer · 2018-12-06 15:53:52Z

0

I know this question has an answer already, but I wanted to contribute a SQL-only solution: using a subquery instead of executing two separate queries and operating on their results in Node.js.

SELECT
  *
FROM lights
WHERE
  room_name IN (
    SELECT room_name FROM rooms
  );

answered Dec 6, 2018 at 15:53

Connor

1,8553 gold badges17 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to use result array of a query in another query in Mysql+Node JS?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related