MySQL array within function not returning?

Question

I'm using a function to collect data from multiple MySQL tables to build an array that will be returned. I'm not sure why but when I go to return the below code I get undefined although when I use console.log all the data displays correctly? I'm not sure if this is due to the return statement being faster than the MySQL statements but I would've presumed that Node would wait till all the SQL Statements had completed before returning? Any suggestions would be awesome.

function testfunction(){
    
  let variable = "false",
      variable2 = "false";


  connection.query("SELECT COUNT(*) AS rowCount FROM `users`;",[], function (error, rows, fields) {
      if (error) {console.log(error);}
      let users = rows[0].rowCount;

      connection.query("SELECT COUNT(*) AS rowCount FROM `towns`",[], function (error, rows, fields) {
          if (error) {console.log(error);}
          let towns = rows[0].rowCount;

          let array = {
              "data":{
                  "users":users,
                  "towns":towns,
                  "variable1":variable,
                  "variable2":variable2
              }
          };
          console.log(array)
          return array;
      });
  });
};

Output:
return output

undefined

console.log output

{ data:
   { towns: 1, users: 1, variable1: 'false', variable2: 'false' } }

Thanks in advance for the help, this isn't a problem I've ran into before and I'm eager to learn what I'm doing wrong :)

Does this answer your question? [Nested query in node js using mysql](https://stackoverflow.com/questions/29344879/nested-query-in-node-js-using-mysql) — kmoser, Apr 08 '21 at 04:33

score 2 · Accepted Answer · answered Apr 08 '21 at 04:32

2

Hi As I can see you used nested approach for queries. I would suggest to use async/await to make it better. Try below approach.

let variable = "false", variable2 = "false";
const users = await connection.query( 'SELECT COUNT(*) AS rowCount FROM `users`' );
const towns = await connection.query( 'SELECT COUNT(*) AS rowCount FROM `towns`' );
let array = {
              "data":{
                  "users":users,
                  "towns":towns,
                  "variable1":variable,
                  "variable2":variable2
              }
          };
console.log(array)

answered Apr 08 '21 at 04:32

Ashish Sharma

446
5
14

Thanks for the quick reply, I've never touched Async functions so I guess now's my time to learn. I've also never made a query like this before, how do I then pull info out of the query? Normally I'd do what I've done in my above code `rows[0].rowCount`. Being that I can't actually build the array I haven't tried this yet but Visual Studio is spitting out an error on the 'await' saying it has no 'effect' on this expression? I've put 'async' in front of the function (just a presumption again I've never done this before). Any support would be appreciated, love learning new thing XD, Thanks again – Charlie Simon Apr 09 '21 at 03:15
if you direct return statement, you dont need to put await. Ex function testfunction(){ return connection.query( 'SELECT COUNT(*) AS rowCount FROM `towns`' ) } – Ashish Sharma Apr 09 '21 at 05:33

MySQL array within function not returning?

1 Answers1