cannot push object returned from mysql in array

Question

I cannot push the object result1 into the array a . The array returns as empty everytime.

  app.get('/freezer', (req, res) => {
  var a = [];
  var sql = `SELECT freezer_id FROM freezer_data`;
  db.query(sql, (err, result) => {
    if (err) throw err;
    result.forEach((rs) => {
      var sql1 = `SELECT * FROM freezer_data JOIN live_records ON freezer_data.freezer_id = live_records.freezer_id WHERE live_records.freezer_id = '${rs.freezer_id}' ORDER BY insert_datetime DESC LIMIT 1`;
      db.query(sql1, (err, result1) => {
        if (result1.length) {
          a.push(result1[0]);
        }
      });
    });
    console.log('arrobj', a);
    res.render('freezer.html', { freezers: a });
  });
});

I maybe making some silly mistake here but i am new to this.

have you checked if sql1 returns a result? it could be that '$ {rs.freezer_id}' should go without quotes — Mario
– Mario, Commented Jul 26, 2020 at 17:37
Yes it returns a result.if i console log result there it returns what i want — Aku Bagwan
– Aku Bagwan, Commented Jul 26, 2020 at 17:43

Evgeny Klimenchenko · Accepted Answer · 2020-07-26 17:28:31Z

1

Your console.log calles before the array a was updated, since the db.query() is async and have a callback which is called sometime in the future.

If you try to check the array here:

db.query(sql1, (err, result1) => {
        if (result1.length) {
          a.push(result1[0]);
          console.log(a);
        }
      });

It won't be empty

answered Jul 26, 2020 at 17:28

Evgeny Klimenchenko

1,1941 gold badge7 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aku Bagwan Over a year ago

Yes you are right it is not empty there but I want to use it outside the loop . I want to pass it as parameter.

Evgeny Klimenchenko Over a year ago

make you function async and use await or you will have to nest callbacks

Evgeny Klimenchenko Over a year ago

developer.mozilla.org/en-US/docs/Learn/JavaScript/Asynchronous/…

Evgeny Klimenchenko Over a year ago

But your program is asynchronous there is no way to get the correct array outside the callback function, because callback function will be called in the future

Evgeny Klimenchenko Over a year ago

You can use make this function asynchronous and this will help you

|

Mudzia Hutama · Accepted Answer · 2021-05-27 18:41:45Z

That is important to know that query use function as parameter (callback), so you cannot return the value outside the query. Use async/await or promise maybe can help you. For example, i am using util (built-in express module).

const util = require('util');
const mysql = require('mysql');

// in this code, i am using connection pool
const databasePool = mysql.createPool({
  host: 'somehost', // ex. localhost 
  port: 'someport', // ex. 3306
  user: 'someuser', // ex. root
  password: 'somepass', // ex. 123456
  database: 'somedbname', // ex. mydatabase
});

// i create variable for querying and use util for async/await
let performQuery = util.promisify(databasePool.query).bind(databasePool); // register databasePool.query and bind databasePool

then in your route :

app.get('/someroute', async (req,res,next) => {
  // declaring empty array
  const myArray = []
  // write query string here and use try to success result and catch the error 
  const myQuery = `SELECT freezer_id FROM freezer_data`;
  try {
    // executing the query string and use await
    let getResult = await performQuery(myQuery)
    getResult.forEach((items) => {
      const myDeepQuery = `SELECT * FROM freezer_data JOIN live_records ON freezer_data.freezer_id = live_records.freezer_id WHERE live_records.freezer_id = ${items.freezer_id} ORDER BY insert_datetime DESC LIMIT 1`;
      let getDeepResult = await performQuery(myDeepQuery);
      myArray.push(getDeepResult)
    });
    res.send(myArray)
  } catch (err) {
    res.send('oopss..')
  }
})

Collectives™ on Stack Overflow

cannot push object returned from mysql in array

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related