CODEWITHSUNDEEP
javascriptnode.jsloopsclosures
The Mechanic
The Mechanic

Reputation: 145

MySQL query in loop Javascript not work

I used the closures in this loop. But its only print the right data on the console log, and the sql query did not work. The inserted data on MySQL is the last variable of the loop. I thought this is because of writing speed of MySQL. But don't know how to fix it. Any idea? Thanks 

  module.exports = function (callback) {
  queryGetForSend = "SELECT * FROM image WHERE send_request is NULL AND post_request is NOT NULL AND year(moderate_time) = year(curdate()) AND month(moderate_time) = month(curdate()) AND (time(moderate_time) < (curtime() - 15));";
  conn.query(queryGetForSend, function(err, rows, fields){
    for (i in rows) {
      if (rows[i].post_request == 'approve') {
        resultSend = 1
      } else {
        resultSend = 2
      }
      var fileID = rows[i].img_md5;
      queryString = fileID + "=" + resultSend;

      //  Request url:  "http://im-api1.webpurify.com/image_queue/results/?key="
      var d = new Date();
      Date.masks.default = 'YYYY-MM-DD hh:mm:ss';
      sendTime = d.format();

      (function(queryString, sendTime) {
        querySent = "UPDATE image SET send_request=1,result_sent='"+queryString+"',send_time='"+sendTime+"' WHERE send_request is NULL AND post_request is NOT NULL AND year(moderate_time) = year(curdate()) AND month(moderate_time) = month(curdate()) AND (time(moderate_time) < (curtime() - 15));";
        conn.query(querySent, function (err, rows, fields) {
          if (err) throw err;
          console.log("http://google.com?key=" + key + "&" + queryString);
        });
      })(queryString, sendTime);

      (function(queryString){
        request.get("http://google.com" + key + "&" + queryString, function(err, res, body) {
        });
      })(queryString);
    }
//    callback(rows);
  });
};

Upvotes: 0

Views: 560

Answers (1)

mscdex
mscdex

Reputation: 106746

Two suggestions:

  • Avoid concatenation in SQL queries, use placeholders or prepared statements instead (if you care about security).

  • Use array.forEach() instead of a regular for-loop with a closure to avoid accidental use of variables set inside the for-loop:

    conn.query(queryGetForSend, function(err, rows, fields) {
  if (err) throw err;
  rows.forEach(function(row) {
    var resultSend;
    if (row.post_request == 'approve') {
      resultSend = 1
    } else {
      resultSend = 2
    }
    var fileID = row.img_md5;
    var queryString = fileID + '=' + resultSend;

    var d = new Date();
    Date.masks.default = 'YYYY-MM-DD hh:mm:ss';
    var sendTime = d.format();

    var querySent = 'UPDATE image SET send_request=1, result_sent=?, send_time=? WHERE send_request IS NULL AND post_request IS NOT NULL AND year(moderate_time) = year(curdate()) AND month(moderate_time) = month(curdate()) AND (time(moderate_time) < (curtime() - 15));';
    conn.query(querySent, [queryString, sendTime], function (err) {
      if (err) throw err;
      console.log('http://google.com?key=' + key + '&' + queryString);
    });

    request.get('http://google.com?key=' + key + '&' + queryString, function(err, res, body) {
    });
  });
});

Upvotes: 1

Related Questions