Select using an array?

Asked

Viewed 44 times

0

I have the following array:

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

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

I tried something like:

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

But it doesn’t work like in PHP.

mysql node.js select

  • The correct is ('"+categorias.join()+"')

  • I fixed the code, I forgot to write the quotes

  • If the values are texts, then each one within the IN should come in quotes, in case it would stay IN ('A','B','C','D','E'). For this you can use map or various other functions.

  • @Isac could make me an example of what it would be like?

1 answer

1


Since the values you’re using for the IN are texts (varchar) they have to come in quotes, each individually, which is not what happens at the time for their variable sql.

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 be like this: ('A','B','C','D','E')

To make this transformation you can use the method map for example, to put the quotes, before doing the join with:

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

See the example working:

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 remove the initial and final quotation mark that I had manually placed inside the IN. I took advantage and put the character of join to make it clearer what you’re doing join for ,.

Browser other questions tagged mysql node.js select

You are not signed in. Login or sign up in order to post.