how to push the result of each element in an array to a global variable in javascript nodejs?

Question

var container = [];

var number = ['1234567890', '646494964', '4498549646', '46654654664', '546646546']

for (k = 0; k < number.length; k++) {

    var sql1 = "SELECT name,age FROM  `table` WHERE  `mobile_number` LIKE '%" + number[k] + "%'"

    mysql.query(sql1, function(error, result) {
        // I will be getting the result of first element in the array here
        console.log(result);
        container.push(result);
    });
}

I need to push the result of each element in the array to the container variable. i need something like.

var container = [{name:"steve",age:"22"},{name:"john",age:"22"},{name:"boss",age:"24"},{name:"jeff",age:"28"},{name:"michael",age:"29"}]

if each query has to be `LIKE` - then perhaps code like this https://jsfiddle.net/q9c70w7t/ would help - that assumes `result` is an array, I don't know mysql in nodejs very well — Jaromanda X, Aug 10 '17 at 11:07
but the length of "var container" is unpredictable. it can have n number of contacts. — Jagadeesh, Aug 10 '17 at 11:10
var container will have as many contacts as are returned from the SQL query(s) — Jaromanda X, Aug 10 '17 at 11:11
"SELECT name,age FROM `table` WHERE `mobile_number` LIKE '%" + number[k] + "%'"" this is how will be forming the query? i couldnt get how to allign as per your code?help? — Jagadeesh, Aug 10 '17 at 11:13
yes, I can see what your code is, not sure what you need to align, I've posted as an answer, so we can discuss the parts you don't understand there if you like :p — Jaromanda X, Aug 10 '17 at 11:16
You can push the results into your global `container` but if you ever attempt to read it synchronously you will always get an empty array. — Redu, Aug 10 '17 at 13:03
@Jagadeesh If your `msql.query` function is able to return Promises then I would advise you to have a look into Promise object. I will give an example later today. — Redu, Aug 10 '17 at 13:10

aSoler · Answer 1 · 2017-08-10T10:59:04.283

1

container.push({name: result.name, age: result.age}));

I think it could help. But I recommend you to do a single query.

"SELECT name,age FROM table WHERE mobile_number IN (" + number.join(',') + ")"

edited Aug 10 '17 at 10:59

answered Aug 10 '17 at 10:55

aSoler

145
1
9

how to achieve this in single query? – Jagadeesh Aug 10 '17 at 10:58
original query uses `LIKE '%123%` ... `LIKE '%456%'` .. `IN (123,456)` isn't a replacement – Jaromanda X Aug 10 '17 at 10:59
yes @JaromandaX i need to use wildcards %LIKE% in every element to search? – Jagadeesh Aug 10 '17 at 11:02
You need to use Contains (for a combination of LIKE and IN) in Sql. Refer [this](https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql). – Siddhant Patra Aug 10 '17 at 11:02
@JaromandaX yeah, but I'm not sure if Jagadeesh want a LIKE or it could be a equal. If a LIKE is needed, see https://stackoverflow.com/a/1865377/2638774 – aSoler Aug 10 '17 at 11:03
the code in the question uses `LIKE` - so ... I don't know, should we ignore the code in the question? – Jaromanda X Aug 10 '17 at 11:04
but the problem is the container array can have "n" number of elements within it. how could i use "or" method? – Jagadeesh Aug 10 '17 at 11:07
@Jagadeesh You really need LIKE or the numbers will be EQUAL? If you really need a LIKE statement, you should do a loop and build the query concatenating. – aSoler Aug 10 '17 at 11:10
@aSoler i need LIKE for sure.. and i need to know how to concatenate. – Jagadeesh Aug 10 '17 at 11:11
@SiddhantPatra can you give me the sql query as per the reference? – Jagadeesh Aug 11 '17 at 03:28

score 1 · Answer 2 · answered Aug 10 '17 at 11:15

As mentioned in another answer, the more performant way to do this is a single query, built up in a loop

var number = ['1234567890', '646494964', '4498549646', '46654654664', '546646546']

var sqlPre = "SELECT name,age FROM  `table` WHERE";
var sqlArray = number.map(item => "`mobile_number` LIKE '%" + item + "%'");
var sql1 = `${sqlPre} ${sqlArray.join(" OR ")}`;
mysql.query(sql1, function(error, result) {
    container = result.map(({name, age}) => ({name, age}));
});

Note: the above uses many ES2015+ concepts, if they are unfamiliar to you, then in ES5 it would be

var number = ['1234567890', '646494964', '4498549646', '46654654664', '546646546'];

var sqlPre = "SELECT name,age FROM  `table` WHERE";
var sqlArray = number.map(function (item) {
    return "`mobile_number` LIKE '%" + item + "%'";
});
var sql1 = sqlPre + ' ' + sqlArray.join(" OR ");
mysql.query(sql1, function (error, result) {
    container = result.map(function (_ref) {
        var name = _ref.name,
            age = _ref.age;
        return { name: name, age: age };
    });
});

I'm assuming that results is an array of objects, I don't know how mysql in nodejs works

score 1 · Answer 3 · answered Aug 10 '17 at 15:18

Well I don't know if mySQL.query() function returns a Promise object or not. Assuming that it doesn't, lets try to simulate our mySQL database which includes a query method which would return a result asynchronously within 200ms.

mySQL = { '1234567890' : {name: "John Doe", age: 22},
          '646494964'  : {name: "Mary Jones", age: 28},
          '4498549646' : {name: "Sam Johnson", age: 44},
          '46654654664': {name: "Terry Gibson", age: 18},
          '546646546'  : {name: "Patricia Snow", age: 31},
          query        : function(q,cb){
                           setTimeout(function(db){
                                        var result = db[q];
                                        !!result ? cb(false,result)
                                                 : cb("Nothing found for query #: " + q, void 0);
                                      }, Math.random()*200, this);
                         }
         }

Now the following snippet will promisify any asynchronous call which takes a data and an error first type callback (cb(err, res)).

function promisify(fun){
  return function(data){
           return new Promise((v,x) => fun(data, (err,data) => !!err ? x(err)
                                                                     : v(data)));
         }
}

Once we promisify mySQL.query like promisfy(mySQL.query.bind(mySQL))it will start returning promises. (We use bind here since when the fun argument variable of the promisify function gets assigned to mySQL.query, it will invoke mySQL.query with the this as promisify's scope and we don't want that. So we secure fun to be bound to mySQL all the time. (Bonus JS knowledge here..!)

Well OK we are almost ready, let's wrap it yup and "sequentially" query our matchstick DB which supports promises. In order to sequentially tie a series of promise-returning-queries in an array, it's best to use a .reduce() to chain up the .then() stages of the previous query to the next. Cool..!

function promisify(fun){
  return function(...data){
           return new Promise((v,x) => fun(...data, (err,data) => !!err ? x(err) : v(data)));
         };
}

function mysqlQuery(query,cb){
  setTimeout(function(){
               var result = database[query];
               !!result ? cb(false,result) : cb("Nothing found for query #: " + query, void 0);
             }, Math.random()*200);
}

var numbers  = ['1234567890', '646494964', '122', '4498549646', '46654654664', '546646546'],
    mySQL    = { '1234567890' : {name: "John Doe", age: 22},
                 '646494964'  : {name: "Mary Jones", age: 28},
                 '4498549646' : {name: "Sam Johnson", age: 44},
                 '46654654664': {name: "Terry Gibson", age: 18},
                 '546646546'  : {name: "Patricia Snow", age: 31},
                 query        : function(q,cb){
                                  setTimeout(function(db){
                                               var result = db[q];
                                               !!result ? cb(false,result)
                                                        : cb("Nothing found for query #: " + q, void 0);
                                               }, Math.random()*200, this);
                                }
                },
    contents = [];
                
numbers.reduce((p,n) => p.then(c => promisify(mySQL.query.bind(mySQL))(n))
                         .then(d => contents.push(d), e => console.log(e)), Promise.resolve())
       .then(_ => console.log(contents));

.as-console-wrapper { max-height: 100% !important; top: 0; }

how to push the result of each element in an array to a global variable in javascript nodejs?

3 Answers3