Summary

I want to check if any values in my array are in a database and if not then add them in?

I’m programming in node.js and have a “.js” script that parses data from an HTML file and adds records into an array.

The values are stored in an array called “json” and are strings,

The database is a MySQL database and I use xampp/PHPMyAdmin to view the data in, using module “MySQL” in my node.js program.

The function I use is below, it’s an asynchronous function that returns the record(if there is one) to the result variable.

Check pseudo-code at the bottom for a clear picture of what I’m trying to do.

sorry for bad English 🙂

Issues/Problems

Problems I’ve had is the fact is the “get-record-from-the-database”/”con.query” function runs and then doesn’t have time to actually get the value before the program ends because its asynchronous,

I’ve tried await and promises but am very inexperienced and wanting to learn.

I know the solution is something with awaits and promises but I’ve tried for hours and researched for hours aswell.

I need the function to completely finished before the rest of the program.

Get a record from database function

var json = ['value1', 'value2'];
var sql = "SELECT * FROM `table` WHERE `table_name` = '"+ json[i] +"'"; 

con.connect(function(err) {
con.query(sql, function (err, result, fields) {
    console.log(result);
    });
});

I expect that any records that are in my array that haven’t been added to the database to be added,

This is because the program will run over and over so I don’t want to add the same record over and over, only new records.

Pseudo-code

LOOP FOR ALL RECORDS IN jsonArray
{
    CHECK IF THE ARRAY RECORD IS IN DATABASE

    IF(arrayRecord IS NOT IN database)
    {
       ADD arrayRecord INTO database
    }

    IF(arrayRecord IS IN database)
    {
        DO NOTHING
    }
}

Using loops – UPDATE 1

Using this loop to check if the item is in the database, then if not, add it:

for (var i = 0; i < json.length; i++) 
{
    var sql = "SELECT * FROM `table` WHERE `table_name` = '" + json[i] + "'";

    con.connect(function(err) {
        con.query(sql, function(err, result, fields) {

            if (result == null) 
            {
                var sql = "INSERT INTO table (table_name) VALUES('" + json[i] + "')";
                con.query(sql, function(err, result) {
                    console.log("1 record inserted" + ":" + sql + ":" + i + ":" + json[i]);
                });
            }

        });
    });
}

I get the result below when using loops

console.log("1 record inserted" + ":" + sql + ":" + i + ":" + json[i]);

1 record inserted:INSERT INTO table (table_name) VALUES('undefined'):6:undefined
1 record inserted:INSERT INTO table (table_name) VALUES('undefined'):6:undefined
1 record inserted:INSERT INTO table (table_name) VALUES('undefined'):6:undefined
1 record inserted:INSERT INTO table (table_name) VALUES('undefined'):6:undefined
1 record inserted:INSERT INTO table (table_name) VALUES('undefined'):6:undefined
1 record inserted:INSERT INTO table (table_name) VALUES('undefined'):6:undefined

It appears to quickly cycle through the loop without checking if the code above was actually worked, then when the loop hits 6 it then fires all the con.query functions.

Solution to check if records are in the database – UPDATE 2

function check_database()   {

//Define sql query text
var sql = "";

//compile all my queries for each record in array so that they can be .queried
for (var i = 0; i < json.length; i++){
    sql += 'SELECT * FROM table WHERE table_name = "' + json[i] + '"; ';
}

//This is a single multi query, I enter my long string of all queries ready to be checked.
connection.query(sql, [1, json.length], function(err, results) {
    if (err) throw err;

    //Once done I need to cycle through the results array to check if any have returned a record hence are inside the db
    for (var i = 0; i < json.length; i++){

        //If the record has a length then it's returned a record so it's added.
        if(!results[i].length)
        {
            //Otherwise No length means no record return, means no record in database
            console.log("Record for: " + json[i] + " ||| NEEDS ADDING");
        } else {

            console.log("Record for: " + json[i] + " ||| IS A RECORD");
        }
    }
});

connection.end();

}

“json” is the name of the array I have the records that need to be checked inside.

Entire created end solution – UPDATE 3

find solution to post below.