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:

What is the correct way of sending data out of a async function?
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?

What is your first query? I ask because it's vastly preferable to roll them together into a stored procedure, join / subselect than to have many back-and-forth to the database (it can almost certainly accumulate the data for you). — msanford
– msanford, Commented Oct 13, 2016 at 13:26
The first query goes like this: 'SELECT auctions.name, wowitemdata.itemName, auctions.itemId, auctions.buyout, auctions.quantity FROM auctions INNER JOIN wowitemdata ON auctions.itemId = wowitemdata.itemID ' Edit: Damn formatting — Chris
– Chris, Commented Oct 13, 2016 at 14:40

Simple_Programmer · Accepted Answer · 2016-10-13 12:49:06Z

10

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/

edited Oct 13, 2016 at 12:49

answered Oct 13, 2016 at 12:43

Simple_Programmer

6906 silver badges18 bronze badges

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

1 Comment

Chris Over a year ago

I'm very new to the magic that is async, so please bear with me. I'm having trouble getting my app.get function to send data in the proper order, with the(probably roundabout) way i "fixed" it my searches are one step behind. Say I search for "Tim", nothing shows up. Say I search for "Finn" next, my data on "Tim" shows up. I'm editing my original post to reflect these new questions.

Ryosuke Hujisawa · Accepted Answer · 2018-06-07 00:32:18Z

2

try this

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'pass',
  database: 'db'
});

connection.connect(function(err) 
{
  if (err) throw err;
    connection.query("SELECT * FROM tb", function (err, result, fields) 
    {
      if (err) throw err;
      var test = result;
      var length = Object.keys(result).length;
      console.log(   length   );

      for (var i = 0; i < length; i++) 
      {

      console.log(result[i].column_name);

      };

    });

});

answered Jun 7, 2018 at 0:32

Ryosuke Hujisawa

2,8721 gold badge19 silver badges18 bronze badges

Comments

Joseph · Accepted Answer · 2016-10-13 12:19:22Z

1

If you're just worried about the scoping of the i to get to the value at a specific index, just use array.forEach.

data.forEach(function(datum, i){
  var inserts = [datum['itemId'], datum['buyout']];
  var ssql = mysql.format(sql, inserts);
  datum['undercut'] = i;
  connection.query(ssql, function(err, rows, fields){
    ...
    datum['undercut'] = rows[0].cnt;
    ...
  });
  ...
});

answered Oct 13, 2016 at 12:19

Joseph

120k31 gold badges186 silver badges238 bronze badges

Collectives™ on Stack Overflow

node.js mysql query in a for loop

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related