Select using an array?

Question

Select using an array?

Navigation

#1 by (1 votes)

0

I have the following array:

var arr = ['A', 'B', 'C'...];

And in my database I have the categoria column where the values are saved in the following way: 'A,B,C...' , how could I make a SELECT by taking each category of arr and finding the corresponding values of my bank? Since there are so many categories, I would like to avoid using a loop in arr .

I've tried something like this:

var categorias = ["A", "B", "C", "D", "E"];
var sql = "SELECT id_item, titulo, resumo, tipo FROM series WHERE categoria IN ('"+categorias.join()+"')";

But it does not work like PHP.

mysql node.js select

asked by anonymous 07.07.2018 / 22:48

1 answer

Mobile angle keypad How to edit data in a Struct using a function

score 1 · Accepted Answer

Since the values you are using for IN are texts ( varchar ) they must come within double quotes, each individually, which is not what happens at the moment for your sql variable %.

See for yourself:

var categorias = ["A", "B", "C", "D", "E"];
var sql = "SELECT id_item, titulo, resumo, tipo FROM series WHERE categoria IN ('"+categorias.join()+"')";

console.log(sql);

When they should look like this: ('A','B','C','D','E')

To do this transformation you can use the map for example, to put quotation marks, before doing join with:

categorias.map(cat => ''${cat}'').join(",")

See the example that works:

var categorias = ["A", "B", "C", "D", "E"];
var sql = "SELECT id_item, titulo, resumo, tipo FROM series WHERE categoria IN ("+categorias.map(cat => ''${cat}'').join(",")+")";

console.log(sql);

Note that I had to get the initial and final quotation mark that I had placed manually inside IN . I took advantage of and put the join caratere to make it clear that you are doing join by , .