Can't insert element from array in mysql database with nodejs

Question

I actually have a problem saving some data from an array in a mysql database with nodejs.

This is my code

for (var i = 0; i < data.data.length; i++) {
    var imageObject = data.data[i];
    var url = imageObject.images.standard_resolution.url;
    var id = imageObject.id;
    var sql = 'SELECT COUNT(*) AS imageIDCount FROM images WHERE id = ?'
    var ids
    connection.query(sql, [id], function(err, rows, fields) {
        console.log(rows[0].imageIDCount);
        if (err) throw err;
        if (rows[0].imageIDCount == 0) {
            console.log(id + " doesn't exist"); // ### the ID at this point is always the last from that array
            //insertImage(id, url);
        } else {
            // console.log("ID exists");
        }
    });
}

This code run's when I get a response from an rest-api with the request-framework.

So my problem is that at the point I get the result from the count-query and there is no element with the specific id I get always the same id.
I think that's because I use the same variable "id" there but how can I fix it ? I hope somebody can help me.

Answer 1

In this case, you're a victim of Node's asynchronous event loop. You're executing a synchronous for-loop and defining id:

for (var i = 0; i < data.data.length; i++) {
    var id = imageObject.id;
}

This works in normal Javascript if you try and do something with id, but because the database module you're using runs asynchronously, that entire loop will have already completed before your first db query completes, effectively clobbering the value of id.

You'll need to re-write your function to behave asynchronously instead, or use something like node-async to help.

Here's a quick example of how that might look. Note that I didn't write your insertImage function for you; you'll need to rewrite that to support a callback as well.

async.each(data.data, function(imageObject, callback) {
    var sql = 'SELECT COUNT(*) AS imageIDCount FROM images WHERE id = ?'
    connection.query(sql, [imageObject.id], function(err, rows, fields) {
        if (err) callback(err);
        if (rows[0].imageIDCount == 0) {
            console.log(id + " doesn't exist");
            insertImage(imageObject.id, imageObject.images.standard_resolution.url, function(err) {
                callback(err); // fires the callback to async
            })
        } else {
            console.log(id + " already exists");
            callback(); // maybe you want an error here too?
        }
    });
}, function(err, results) {
    // all of your db queries are completed
});