ibtehaz ibtehaz - 6 months ago 16
SQL Question

variable value becomes undefined in NodeJS ExpressJS

I am working with NodeJS using ExpressJS framework in a mysql backend. I am running a query inside a for loop and my loop and work afterwards depends on the return value of the query. I am not very good with mysql query so I am running it through a for loop.
The problem is, due asynchronous [I guess!], the for loop ends long before the query result comes out.

Here is my code:

function search_people_step2(user_id, search_criteria, user_friend)
{
var first_name_friends = [];
var last_name_friends = [];


for(var i = 0; i < user_friend.length; i++)
{
con.query("SELECT first_name, second_name FROM user WHERE userid = ?", user_friend[i],function(err, rows)
{
if(err)
{
//error;
}
else
{
if(rows.length == 0)
{
//nothing gets returned
}
else {
console.log(rows);
first_name_friends[i] = rows[0].first_name;
last_name_friends[i] = rows[0].second_name;
}
}
});
}


Now,I can get the value (using console.log) inside the query statement, however, on the outside, the value becomes empty (undefined) since the rest of the code has already been computed.

How can I solve this?
Thanks in advance.

Answer

The first thing that I find weird in your code is that you are not using an IN statement in your SQL query (not directly related to your problem though) which means you are making as many requests as there are entries in user_friend. The problem is that the SQL library is implemented asynchronously and you cannot avoid it. BUT you can handle it elegantly with Promises which are ES6 features: (I didn't test the code but I think it should work)

function search_people_step2(user_id, search_criteria, user_friend)
{
  return new Promise((resolve,reject)=>{
    var first_name_friends = [];
    var last_name_friends = [];
    var placeHolders=user_friend.map(()=>"?").join(",");
    con.query("SELECT first_name, second_name FROM user WHERE userid IN ("+placeHolders+")",user_friend,(err,rows)=>{
      if(err)
        reject(err);
      else{
        rows.forEach(row=>{
          first_name_friends.push(row.first_name);
          last_name_friends.push(row.second_name);
        });
        resolve({first_name_friends,last_name_friends});
      }
    });
  });
}

And call your function like this :

search_people_step2(id,crit,friends).then(result=>{
  //handle result asynchronously as there is no choice
  console.log(result.first_name_friends);
  console.log(result.last_name_friends);
}).catch(err=>{
  //handle error
});
Comments