CODEWITHSUNDEEP
mysqlnode.js
Nick
Nick

Reputation: 936

Loop through MySQL rows and store results in array

I am trying to store details of affectedRows from a MySQL INSERT query using NodeJS. My mind is melting trying to comprehend callbacks and Promises. Being a single-man dev team I wanted to reach out and ask for the clearest explanation as to how a callback can be applied here in a foreach loop.

The goal should be clear from these few lines of code; store data in the affected_rows[] array.

var affected_rows = [];

asset_array.forEach(function(asset) { // Populate the asset table
    var query_string = "INSERT IGNORE INTO " + asset_table + " SET symbol = '" + asset[0] + "', name = '" + asset[1] + "'";
    connection.query(query_string, function(err, rows, fields) {
        if (err) throw err;
        if ( rows.affectedRows > 0 ) {
            data_to_push = [asset_table, asset[0], asset[1]];
            affected_rows.push(data_to_push);
        }
    });
});

console.log(affected_rows); // [] for obvious async reasons

Upvotes: 0

Views: 1467

Answers (2)

ralixyle
ralixyle

Reputation: 785

One option would be to process the asset_array inside a function and pass a callback into it and when loops through asset_array check if the current index matches the asset_array length (-1). If so call the callback.

    var affected_rows = [];

    function processAssets(cb) {
        var array_len = asset_array_len.length
        asset_array.forEach(function(asset, index) {
            var query_string = 'INSERT IGNORE INTO ' + asset_table + ' SET symbol = \'' + asset[0] + '\', name = \'' + asset[1] + '\'';
            connection.query(query_string, function(err, rows, fields) {
                if (err) throw err

                if (rows.affectedRows > 0) {
                    data_to_push = [asset_table, asset[0], asset[1]];
                    affected_rows.push(data_to_push);
                }
                if (index === (array_len - 1)) cb()
            });
        });
    }

    processAssets(function() {
        console.log(affected_rows)
    })

Upvotes: 2

codeslayer1
codeslayer1

Reputation: 3686

Will suggest you to have a look at async Queue.

You can change your code like this to use it.

//2nd Step - Perform each task and then call callback() to move to next task
var q = async.queue(function(query_string, callback) {
    connection.query(query_string, function(err, rows, fields) {
        if (err) throw err;
        if ( rows.affectedRows > 0 ) {
            data_to_push = [asset_table, asset[0], asset[1]];
            affected_rows.push(data_to_push);
        }

        callback(); //call next task
    });
}, 2); //here 2 means concurrency ie 2 tasks will run in parallel

//Final Step - Drain gives you end of queue which means all tasks have finished processing
q.drain = function() {
    //Do whatever you want after all tasks are finished
};

//1st Step - create a queue of all tasks that you need to perform
for (var i = 0; i < asset_array.length ; i++) {
    var query_string = "INSERT IGNORE INTO " + asset_table + " SET symbol = '" + asset[0] + "', name = '" + asset[1] + "'";
    q.push(query_string);
}

Upvotes: 0

Related Questions