can't make nodejs mysql query results a global variable

Question

I'm trying to use the mysql module to get some data from a mysql database and then write it to an HTML page but it seems stuck inside the query function itself. The code looks like this:

rooms = [];

var mysql = require('mysql');

var con = mysql.createConnection({
    host: "localhost",
    user: "MYUSERNAME",
    password: "MYPASSWORD",
    database: "travel"
});

con.connect(function(err) {
    if (err) throw err;
    con.query("SELECT * FROM rooms", function (err, result, fields) {
        if (err) throw err;
        var rooms = result;
        console.log(rooms[9]);
    });
});

console.log(rooms);

The first console.log outputs the results properly, but the second one returns the empty array as declared in the first line and prints first. I'm new to Javascript so I'm probably missing something very obvious. Thanks in advance.

a while ago, i got a similar issue with node.js. the problem wasn't that i couldn't fill the global variable within a function. the problem was, that the database query wasn't just finished yet. you need to make sure, you're trying to log `rooms` when the database call is finished! just look up a tutorial about async functions and callback functions. saves you a lot of time. — David, Feb 19 '18 at 16:59
Possible duplicate of [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — Patrick Hund, Feb 19 '18 at 17:02
You are assuming that the data is fetched from the database synchronously, it's not, it's an async function call, see related question: https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call — Patrick Hund, Feb 19 '18 at 17:03
There are also a closure problem because `rooms` is declare inside anonymous function and not in global — ekans, Feb 19 '18 at 17:04

score 2 · Answer 1 · answered Feb 19 '18 at 17:01

2

I think you are recreating another variable because adding "var " before. Have you tried without it?

If it doesn't work, here another posible solution:

global.rooms = [];
global.rooms = result;

https://nodejs.org/api/globals.html#globals_global

answered Feb 19 '18 at 17:01

zelda11

425
2
7

score 0 · Answer 2 · answered Feb 19 '18 at 17:06

var rooms = [];

var mysql = require('mysql');

var con = mysql.createConnection({
    host: "localhost",
    user: "MYUSERNAME",
    password: "MYPASSWORD",
    database: "travel"
});

con.connect(function(err) {
    if (err) throw err;
    con.query("SELECT * FROM rooms", function (err, result, fields) {
        if (err) throw err;
        rooms = result;
        console.log(rooms[9]);
    });
});

console.log(rooms);

you missed out to declare rooms as global variable.

score 0 · Answer 3 · answered Feb 19 '18 at 17:10

approach using a callback function

rooms = [];

var mysql = require('mysql');

var con = mysql.createConnection({
    host: "localhost",
    user: "MYUSERNAME",
    password: "MYPASSWORD",
    database: "travel"
});


function query(callback) {
  con.connect(function(err) {
      if (err) throw err;
      con.query("SELECT * FROM rooms", function (err, result, fields) {
          if (err) throw err;
          var rooms = result;
          callback(rooms);
          console.log(rooms[9]);
      });
  });
}

function log_it_out() {
  console.log(rooms);
}

query(log_it_out);

score 0 · Accepted Answer · answered Jun 02 '19 at 14:55

I don't think it matters for you anymore since it's been roughly a year since you asked this basic question, however maybe someone else that searches for this question might find this information helpful since I had the same problem.

**} else if(req.url === "/api/labeat") {
    res.writeHead(200, {"Content-Type": "application/json"});
    res.end(JSON.stringify(information));**

IMPORTANT When you try to return something to a website beside the variable declarations, when you use res.end();, make sure to turn the result or whatever kind of information you're trying to work with into a string or buffer since the res.end() expects either a string or a buffer. Also specify the Content-Type as I did (application/json).

can't make nodejs mysql query results a global variable

4 Answers4