node.js mysql query in a for loop

Question

I have two queries.
The first,

SELECT auctions.name, wowitemdata.itemName, auctions.itemId, 
auctions.buyout, auctions.quantity
FROM auctions
INNER JOIN wowitemdata ON auctions.itemId = wowitemdata.itemID;

returns data like this:

{
name: 'somename',
itemName: 'someitemname',
itemId: '0000',
buyout: '0001',
quantity: '5',
}

The 2nd query uses data from the #1 to get the count() of items cheaper than itemId. The response is to be added to #1 as a new element -> 'undercut'.

My function:

function checkUndercut(data, length){
    var Select = 'SELECT COUNT(auctions.itemId) AS cnt ';
    var From = 'From `auctions` ';
    var Where = 'WHERE auctions.itemId LIKE ? AND buyout < ?';
    var sql = Select + From + Where;
    for(i = 0, len = length; i < len; i++){
        var inserts = [data[i]['itemId'], data[i]['buyout']];
        var ssql = mysql.format(sql, inserts);
        data[i]['undercut'] = i;
        connection.query(ssql, function(err, rows, fields){
            if(!err){
                console.log("check Undercut: " + rows[0].cnt);
                data[i]['undercut'] = rows[0].cnt;
            } else {
                console.log("Error while performing Query");
            };
        });
    };
}

Due to the asynchronous nature of the query I can't use the for loop i var to append the data.
How do I get around this?

Edit: My new problem:
When I search for a name(searchobj), the returned data is one step behind.
Say I search for "Tim", nothing shows up.
Say I search for "Finn" next, my data on "Tim" shows up.

I suspect this is because of the way I got data out of my checkUndercut function.
I made a new top level var, appdata and after using @simple_programmers suggestion I put my new code like this:

function(err){
        if(err){
          //handle the error if the query throws an error
        }else{
            appdata = data;
            console.log(appdata);
          //whatever you wanna do after all the iterations are done
        }
    });

The console.log sends out the correct information so my problem lies with the get function sending the response prematurely.

My get function:

app.get('/test',function(req, res) {
    console.log("app.get "+searchobj);
    var sqlSELECT = 'SELECT auctions.name, wowitemdata.itemName, auctions.itemId, auctions.buyout, auctions.quantity ';
    var sqlFROM = 'FROM `auctions` ';
    var sqlINNER ='INNER JOIN `wowitemdata` ON auctions.itemId = wowitemdata.itemID ';
    var sqlWHERE = 'WHERE auctions.name LIKE ?';
    var sql = sqlSELECT + sqlFROM + sqlINNER + sqlWHERE;
    var inserts = [searchobj];
    var sql = mysql.format(sql, inserts);
    //console.log("Query: "+sql);
    connection.query(sql, function(err, rows, fields) {
      if (!err){
        var rowLen = rows.length;
        checkUndercut(rows, rowLen);
        console.log(appdata);
        res.send(appdata);
                } else {
    console.log('Error while performing Query.');
    };
    
    });
}); 

My questions:

1 What is the correct way of sending data out of a async function?

2 Is there some way I can make my app.get or res.send wait til my data is retrieved before sending it?

Edit 2: I can get it working by placing all the code inside my app.get(), but there has to be a more elegant and easier to read solution?

Answer 1

The best way to do this in my opinion is to use a node module called async to run things in parallel and have a single callback when everything finishes.

There are a variety of methods defined in async module for these kind of situations and the one which I would recommend is forEachOf.

Given that your data parameter is an array, it goes something like this -

function checkUndercut(data, length){
    var Select = 'SELECT COUNT(auctions.itemId) AS cnt ';
    var From = 'From `auctions` ';
    var Where = 'WHERE auctions.itemId LIKE ? AND buyout < ?';
    var sql = Select + From + Where;
    async.forEachOf(data, function (dataElement, i, inner_callback){
        var inserts = [dataElement['itemId'], dataElement['buyout']];
        var ssql = mysql.format(sql, inserts);
        dataElement['undercut'] = i;
        connection.query(ssql, function(err, rows, fields){
            if(!err){
                console.log("check Undercut: " + rows[0].cnt);
                dataElement['undercut'] = rows[0].cnt;
                inner_callback(null);
            } else {
                console.log("Error while performing Query");
                inner_callback(err);
            };
        });
    }, function(err){
        if(err){
          //handle the error if the query throws an error
        }else{
          //whatever you wanna do after all the iterations are done
        }
    });
}

So what is basically does is, it loops over your data array and runs the query for every element of that array. Once the query is executed, it gives a call to the callback method local to that iteration. Once all the iterations are done(i.e the last local callback is called), it calls the final callback in which you can do whatever you want to do later when all your queries are done executing.

More about forEachOf here - https://caolan.github.io/async/docs.html#eachOf

Async module - https://caolan.github.io/async/