MySQL query in loop Javascript not work

Question

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);
 });
};

Answer 1

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) {
   });
   });
  });
  

• The Mechanic: I was used the forEach before changed with for loop. But still happens. So i thought about the sql query maybe wrong, and i right! The problem is from the sql query. There is no index on that, so while the update query running, its also update on all records. That why the console.log fired a right result but mysql not. Aw, thanks for your suggestions, very professional!