CODEWITHSUNDEEP
mysqlnode.js
Sjn
Sjn

Reputation: 31

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

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

Upvotes: 1

Views: 2383

Answers (2)

Connor
Connor

Reputation: 1855

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

Upvotes: 0

darklightcode
darklightcode

Reputation: 2772

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...');

  }
});

Upvotes: 3

Related Questions