Loop through MySQL rows and store results in array

Question

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

Not sure I'm understanding you correctly - why not just write a foreach as normal and insert into array? — ProEvilz
– ProEvilz, Commented Sep 15, 2017 at 11:33
If I understand correctly, you're suggesting I run the foreach query across asset_array[] and insert to DB after? — Nick
– Nick, Commented Sep 15, 2017 at 11:37

ralixyle · Accepted Answer · 2017-09-15 13:24:47Z

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)
    })

codeslayer1 · Accepted Answer · 2017-09-15 11:35:56Z

0

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);
}

answered Sep 15, 2017 at 11:35

codeslayer1

3,6863 gold badges19 silver badges13 bronze badges

1 Comment

Nick Over a year ago

Is there a route to achieving this with just the use of functions in my original code? I'm looking to avoid libraries where possible as only need to store this data once.

Collectives™ on Stack Overflow

Loop through MySQL rows and store results in array

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related