TK92 TK92 - 6 months ago 21
SQL Question

MySQL Select IN with NodeJs detect empty fields

im pretty new to nodejs in gernal and im facing a problem with mysql selects.
So, lets say i have a array of ids and i want to select them from my MySQL Database like:


Con.query('SELECT * FROM Table WHERE Id IN (1,2,3,4)' , function (err,
results, fields) { results contains: 1,2,3 because 4 dont exists in DB }


So i would receive the data for Rows with ID 1,2,3 but not for ID 4 because it dont exists. I wonder how i can detect, that ID 4 dont exist.

Is there a way to retrieve all the Ids i asked for in the query Function or kind of an option which i can enable to receive NULL for the given id if it dont exists like:

ID 1: Data
ID 2: Data
ID 3: Data
ID 4: null - because dont exists

If there is no way to solve this with IN Statement, can i simply make 4 single Selects with explicit ID ( because i will receive 0 if the data dont exists ) or will it overload MySQL if i have like 1000 Selects in like 1 minute?

Btw, I also tryed to save the IDs in a Array and make a loop to detect which ids are missing, but it seems that the Query Function of MySQL is in another scope, so i cant access them as long as i dont make them global.

Thanks in Advance

Answer

You have to check it via loop, if you put ID in array;

var ids = [1,2,3,4]
Con.query('SELECT * FROM Table WHERE Id IN (1,2,3,4)' , function (err, results, fields) { 
for(var i = 0;i<ids.length;i++){
 if(ids.indexOf(fields[i]['id'])>-1){
 console.log(" found ID = "+ids[i])
 }else{
 console.log(" ID NOT FOUND = "+ids[i])
 }
}

});