CODEWITHSUNDEEP
javascriptmysqlnode.jsarraysexpress
Aku Bagwan
Aku Bagwan

Reputation: 13

cannot push object returned from mysql in array

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.

Upvotes: 0

Views: 474

Answers (2)

Mudzia Hutama
Mudzia Hutama

Reputation: 504

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..')
  }
})

Upvotes: 0

Evgeny Klimenchenko
Evgeny Klimenchenko

Reputation: 1194

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

Upvotes: 1

Related Questions