midhun k midhun k - 6 months ago 17
Node.js Question

undefined value returning while executing sql query in nodejs

I am fetching values from my database having tables:
tables

My code:

function listshops(callback)
{
client.connection.query('select * from shop',function(err,rows){
if(rows.length>0)
{
for(var i=0;i<rows.length;i++)
{
var shopIdFetched = rows[i].shopId;
client.connection.query('select * from image where shopId=?',shopIdFetched,function(err,data){
if(data.length > 0){
console.log(rows[i],data);
}

});

}

}

});
}


But when displaying result, the first query shows a undefined value.
enter image description here

When i gives rows [0] and rows1 the values are fetching. But i need to implement rows[i].

Answer

You misunderstand how asynchronous calls are make.

What happens when you run this part of code?

     for(var i=0;i<rows.length;i++)
     {   
       var shopIdFetched = rows[i].shopId;
       client.connection.query(...) //these are asynchronous methods    
     }

For rows.length=10, it will call 10 times the client.connection.query, which is unfortunately asynchronous method, therefore it is not executed yet, but it put 10 asynchronous methods to Event Stack.

After this method synchronously finishes and one of method indicates, the call to database is finished, the method is executed, which is this

        if(data.length > 0){
             console.log(rows[i],data);
        }

However at this point, the for-cycle already finished, the i=10, therefore rows[10] is undefined (because for rows.length=10 you have data in rows[0] to rows[9]


One workaround can be to put another method to the inner scope, something like this

for(var i=0;i<10;i++)
{
    x(i);
}

function x(i){
    console.log(i);
    //this i will be same even after asynchronous paradighm
}

The same thing can be written as this

for (var i = 0; i < 10; i++) {
    (function(i){
        console.log(i);
    })(i)
}

In your case

 for(var i=0;i<rows.length;i++)
 {   
   (function(i){
   var shopIdFetched = rows[i].shopId;
    client.connection.query('select * from image where shopId=?',shopIdFetched,function(err,data){
       if(data.length > 0){
        console.log(rows[i],data);
        }

    });
    })(i);
 }