Node executing statements out of sequence within a function

Question

I'm using node-mysql to execute a query and return results. Per standard mysql-node documentation, a callback function is executed once the query is complete passing an error object or result object. Assuming the query executed correctly (and it does). I run through a for-in loop on the result and assign the fields in each row as one continuous message that I plan to send back to the calling so that it can be processed appropriately. So, if a user wants to see the last 5 picks from a fantasy football draft, the function receives the num parameter (which is equal to 5) and then see a returned message showing details about all 5 picks transactions. The function is as follows:

exports.getSelections = function(num){


 var query = "Select b.Player, b.Team, b.Position, a.Pick, c.Owner FROM Players b, Owners c,(Select * FROM Draft2 WHERE Year=2015 AND Player_ID >0 ORDER BY PICK DESC LIMIT " + num + ") a WHERE b.Player_ID = a.Player_ID AND c.Owner_ID = a.Owner_ID ORDER BY a.Pick DESC";


 var connection = mysql.createConnection(EXTERNAL_DATABASE_URL);
 connection.query(query, function(err, results, fields){
                    if(!err){
                        for( var i in results){
                            msg += "\n" + results[i].Pick + ". " + results[i].Owner + " selected " + results[i].Player + ",  " + results[i].Position + " from " + results[i].Team 
                        }

                    }else{
                        console.log(err);
                            msg = "There was an error processing your request";
                    }
                });      


                    connection.end(function(err){
                        if(err){
                            console.log("there was an error")
                        }else{
                            console.log("close connection");
                        }
                    });




                        console.log("message to be sent: " + msg);
                        return msg;         

};

The output through my console is always:

message to be sent: undefined

this is what processed: [shows the details of the sales transactions based on the number that was passed to the function and used in the query]

I see the results of the query in the second line above, as represented by [msg], so I know the query executed appropriately.

Here are my questions:

1. Can I get an explanation why the console would execute the message to be sent before finishing the actual loop.

2. If I want to ensure that the loop is complete before returning the value, how do I approach this?

3. Also, it appears that connection.end doesn't execute either. I presume I'm exiting the function too early??

Thanks for any help.

Answer 1

As @Pointy hinted, the log is executing prior to the completion of the loop because the database operation connection.query is asynchronous. All of your operations are being executed in the correct sequence; its just that async operations resolve on their own timeline, which is why we have callbacks that run upon their completion.

You've already logged the value of msg before your connection.query callback even gets a chance to run.

Try moving your "message to be sent" console.log inside of the callback of the connection.query operation:

exports.getSelections = function(num) {
  var query = "Select b.Player, b.Team, b.Position, a.Pick, c.Owner FROM Players b, Owners c,(Select * FROM Draft2 WHERE Year=2015 AND Player_ID >0 ORDER BY PICK DESC LIMIT " + num + ") a WHERE b.Player_ID = a.Player_ID AND c.Owner_ID = a.Owner_ID ORDER BY a.Pick DESC";
  var connection = mysql.createConnection(EXTERNAL_DATABASE_URL);
  connection.query(query, function(err, results, fields) {
    if (!err) {
      for (var i in results) {
        msg += "\n" + results[i].Pick + ". " + results[i].Owner + " selected " + results[i].Player + ", " + results[i].Position + " from " + results[i].Team
      }
    } else {
      console.log(err);
      msg = "There was an error processing your request";
    }

    console.log("message to be sent: " + msg);

  });
  connection.end(function(err) {
    if (err) {
      console.log("there was an error")
    } else {
      console.log("close connection");
    }
  });

  return msg;
};

Also, might want to make sure msg is of type String before concatenating with msg += "..."