How to insert row if ID is not found?

Question

I'm trying to insert User Matches in a table, but I don't want to repeat. Thus I'm running a COUNT query, but when I try to insert the new record I'm getting undefined so I can't insert the record.

I've read about using callbacks but being fairly new to NodeJS I'm currently struggling.

if(user_matches.length !== 0){
    var connection = mysql.createConnection({
        'host': 'localhost',
        'user': 'root',
        'password': '',
        'database': 'test'
    });

    connection.connect();

    //connection.query('TRUNCATE matches');
    // I was originally truncating the table, but this just doesn't cut it

    for (var x = 0; x < user_matches.length; x++){
        var countQuery = connection.query('SELECT COUNT(*) as count FROM matches WHERE user_id_1 = ?', [ user_matches[x]['user_1']['id'] ]);
        countQuery.on('result', function(){

            // Here I get the UNDEFINED error, can't insert
            connection.query('INSERT INTO matches SET ?', {
                'user_id_1': user_matches[x]['user_1']['id'],
                'user_id_2': user_matches[x]['user_2']['id']
            });

        });
    }

    connection.end();
}

possible duplicate of [JavaScript closure inside loops – simple practical example](http://stackoverflow.com/questions/750486/javascript-closure-inside-loops-simple-practical-example) — user2864740, Sep 02 '15 at 23:09

Yuri Zarubin · Accepted Answer · 2015-09-02T23:20:08.947

1

Install async

npm install async --save

Then try this:

if(user_matches.length !== 0){
 var connection = mysql.createConnection({
      'host': 'localhost',
      'user': 'root',
      'password': '',
      'database': 'test'
  });

  connection.connect();

  async.each(user_matches, function(x, callback){
    var countQuery = connection.query('SELECT COUNT(*) as count FROM matches WHERE user_id_1 = ?', [ x['user_1']['id'] ]);

    countQuery.on('result', function(){
      var insertQuery = connection.query('INSERT INTO matches SET ?', {
          'user_id_1': x['user_1']['id'],
          'user_id_2': x['user_2']['id']
      });

      insertQuery.on('result', callback);

    });
  }, function(err){
    console.log('done');
  });
}

edited Sep 02 '15 at 23:20

answered Sep 02 '15 at 22:59

Yuri Zarubin

11,439
4
30
33

Ok, that's something. I'm currently having another issue now, it's saying "Cannot enqueue Query after invoking exit" :( – Mauro Casas Sep 02 '15 at 23:04
@user2864740 there are issues with the control flow, but he's just performing inserts of a count, so there's no race conditions. – Yuri Zarubin Sep 02 '15 at 23:08
@MauroCasas the problem is that connection.end() is being called before your queries finish. You need to use something like the `async` library instead of a `for loop`, and call connection.end() after all of the queries finish. – Yuri Zarubin Sep 02 '15 at 23:10

How to insert row if ID is not found?

1 Answers1