Node.Js MySQL Query in a for Loop

node.js mysql query in a for loop

The best way to do this in my opinion is to use a node module called async to run things in parallel and have a single callback when everything finishes.

There are a variety of methods defined in async module for these kind of situations and the one which I would recommend is forEachOf.

Given that your data parameter is an array, it goes something like this -

function checkUndercut(data, length){
    var Select = 'SELECT COUNT(auctions.itemId) AS cnt ';
    var From = 'From `auctions` ';
    var Where = 'WHERE auctions.itemId LIKE ? AND buyout < ?';
    var sql = Select + From + Where;
    async.forEachOf(data, function (dataElement, i, inner_callback){
        var inserts = [dataElement['itemId'], dataElement['buyout']];
        var ssql = mysql.format(sql, inserts);
        dataElement['undercut'] = i;
        connection.query(ssql, function(err, rows, fields){
            if(!err){
                console.log("check Undercut: " + rows[0].cnt);
                dataElement['undercut'] = rows[0].cnt;
                inner_callback(null);
            } else {
                console.log("Error while performing Query");
                inner_callback(err);
            };
        });
    }, function(err){
        if(err){
          //handle the error if the query throws an error
        }else{
          //whatever you wanna do after all the iterations are done
        }
    });
}

So what is basically does is, it loops over your data array and runs the query for every element of that array. Once the query is executed, it gives a call to the callback method local to that iteration. Once all the iterations are done(i.e the last local callback is called), it calls the final callback in which you can do whatever you want to do later when all your queries are done executing.

More about forEachOf here - https://caolan.github.io/async/docs.html#eachOf

Async module - https://caolan.github.io/async/

Execute mysql query one after the other inside for loop in node js

The easiest way to handle this is with async / await. Notice that the await syntax lets you use asynchronous method codes in a sequence in an easy-to-read form. Also notice that you can await a function that returns a promise, or you can await an async function. They're actually the same thing with slightly different syntax.

Something like this.

 /* promisified query method, suitable for await */
 function doQuery(conn,sql,args) {
     return new Promise (function( resolve, reject ) {
        conn.query(sql, args, function (error, results, fields) {
            if (error) return reject(error)
            resolve({results, fields})
        }) 
     })
 }

 /* your loop operation */
 async function whatever ( args ) {
      for (whatever conditions) {
         let queryOutput = await doQuery (conn, sql, [arg, arg])
         let firstColumn = queryOutput.results['first_column']
      }
      return resultValue
 }

 /* call your async function */
 whatever(args)
 .next (function (resultValue) {  whatever you need } )
 .catch (console.error)

nodejs mysql query data to loop array

const dictionaryData = [
      { id: 0, language: 'English' },
      { id: 1, language: 'Indonesian' }
    ]
const languageData = [
      {
        language_id: 0,
        key_language: '_availablestorage',
        description: 'Available Storage'
      },
      {
        language_id: 0,
        key_language: '_prayschedule',
        description: 'Pray Schedule'
      },
      {
        language_id: 0,
        key_language: '_mainmenu',
        description: 'Main Menu'
      },
      {
        language_id: 1,
        key_language: '_availablestorage',
        description: 'Tersedia'
      },
      {
        language_id: 1,
        key_language: '_prayschedule',
        description: 'Jadwal'
      },
      {
        language_id: 1,
        key_language: '_mainmenu',
        description: 'Menu Utama'
      }
    ]

The response of the query is like this, then the below code will work.

const dictionary = {}
dictionaryData.forEach((data) => {
    const setLang = {}
    languageData.forEach((language) => {
       if (data.id === language.language_id) {
         setLang[language.key_language] = language.description
       }
    })
    dictionary[data.language] = setLang
})
console.log(dictionary)

The response is:

{
  English: {
    _availablestorage: "Available Storage",
    _mainmenu: "Main Menu",
    _prayschedule: "Pray Schedule"
  },
  Indonesian: {
    _availablestorage: "Tersedia",
    _mainmenu: "Menu Utama",
    _prayschedule: "Jadwal"
  }
}

Now change foreach loop with your query loop. and it will work.
The fiddle link is attached

How to add MySQL query results from a loop in Nodejs?

The problem is that the forEach returns void.

So you can't wait for the asynchronous code to run before you return it.

When we're dealing with an array of promises such as db queries ( like in your case ) or API calls, we should wait for every one of them to be executed.

That's when we use the Promise.all

Try doing it like this:

const queryResults = await Promise.all(
   Object.keys(obj).map(async (key) => {
    const state1 = key;
    const state2 = obj[key];

    const sql_select = 'SELECT column1,column2 from database WHERE state = ? OR state=?';

    return new Promise((resolve, reject) => 
      db.query(sql_select,[state1,state2], (err, result) => {
        if (err) 
          return reject(err)
        else
          return resolve(result)
      })
    )
  })
)
console.log('queryResults', queryResults)
// now you give this queryResults back to your FE

Small tips for your fresh start:

• never use var, try always use const or if needed, let.
• try always use arrow functions ( () => {...} ) instead of regular functions ( function () {...} ), It's hard to predict which scope this last one is using

nodejs query in query result loop

You have to pass to the foreach method the proper handler on each item:

let new_rows = Array()
rows.forEach(row => {
    new_rows.push(await getImages(row, i))
});

Plus, if you want to get the images on another array you should use map, its cleaner:

let new_rows = rows.map(row => {
    return await getImages(row, i)
});

Wait for mysql to finish queries in a for loop before finishing a function in nodeJS

first solution:

app.get("/get-top-trending", (request, response) => {
const req = request.query
let query = 'SELECT Ticker, Mentions FROM trend_data ORDER BY Date DESC, ' + req.by + ' DESC LIMIT 3';

cryptoDB.query(query, (err, tickers) => {
    if (err) throw err;

    getData(tickers).then(function (returnData) {
        response.send(returnData)
    });
});

async function getData(tickers) {
    const returnData = {};

    const querys = ((ticker) => {
        return new Promise((resolve, reject) => {
            cryptoDB.query('SELECT HistoricalJSON FROM historical_trend_data WHERE Ticker=? LIMIT 1', [ticker['Ticker']], (err, rows2) => {
                if (err) reject(err);
                returnData[ticker['Ticker']] = rows2[0]['HistoricalJSON'];
                resolve();
            });
        })
    })

    for (let i = 0; i < tickers.length; i++) {
        await querys(tickers[i]);
    }

    return returnData
}

});

second solution:

app.get("/get-top-trending", (request, response) => {
    const req = request.query
    let query = 'SELECT Ticker, Mentions FROM trend_data ORDER BY Date DESC, ' + req.by + ' DESC LIMIT 3';


    cryptoDB.query(query, (err, tickers) => {
        if (err) throw err;

        getData(tickers).then(function(returnData) {
            response.send(returnData)
        }).catch(error => throw error);
    });

    async function getData(tickers) {
        let returnData = {};
        for (let i = 0; i < tickers.length; i++) {
            returnData[tickers[i]['Ticker']] = await getTickerQuery([tickers[i]['Ticker']]);
        }
        return returnData;
    }
    
    function getTickerQuery(ticker) {
        return new Promise((resolve, reject) => {
            cryptoDB.query('SELECT HistoricalJSON FROM historical_trend_data WHERE Ticker=? LIMIT 1', ticker, (err, rows2) => {
                if (err) throw reject(err);
                resolve(rows2[0]['HistoricalJSON']);
            });
        })
    }
});

I recommend second solution for readability

JavaScript nodejs mysql with queries in a loop

The problem is that getConnection is asynchronous and that Javascript doesn't have a block scope, which means that by the time the callback for getConnection is called, the i variable will point to the value of what it had last in the loop (which is 5).

You can use a trick to create a partial function (think of it as a function with the first argument already applied to it) for each turn of the loop, which will pass the current value of i as the first argument of the getConnection callback:

for (var i = 0; i<5; i++) {
  mysqlPool.getConnection(function(i, err, connection) {
    ...
  }.bind(mysqlPool, i));
};

FWIW, your code will open 5 connections (and perform 5 queries) to your database almost instantly (that's how asynchronous I/O works). That's probably not a big issue, but it's something worth realising if that 5 could get higher :)

Also, the for loop will generate [0, 1, 2, 3, 4], whereas in your example query, you write WHERE id IN (1, 2, 3, 4, 5).

---

Related Topics