Node js Mysql query IN clasue and array of strings

Question

I am trying to pass array of cities to the IN clause but seems inverted commas are removed while preparing query

Here is the code

let city = ["Moscow", "Paris"];
let sql = 'SELECT* FROM cities WHERE city IN ('+city+')';
console.log(sql);

and in the console I am getting this

SELECT* FROM cities WHERE city IN (Moscow, Paris);

and thus getting exception while executing query!

Any help?

You shouldn't concatenate an array with a string using +. Try with join on the array instead to build your string first — Kaddath
– Kaddath, Commented Feb 8, 2019 at 12:54
can you tell which package are you using for connecting to mysql? — Nikhil G
– Nikhil G, Commented Feb 8, 2019 at 13:00

Nikhil G · Accepted Answer · 2019-02-08 13:07:44Z

4

Try this, it will work

var sql = "SELECT uid FROM cities where city IN ('" + gender.join("','") + "')";

Result :-

SELECT uid FROM cities where city IN ('Male','Female')

answered Feb 8, 2019 at 13:07

Nikhil G

2,50617 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

InTERpLAY · Accepted Answer · 2019-02-08 13:33:04Z

4

Using a Query Builder is a preferable method but a possible solution if you have a long array:

let stringifiedValues = JSON.stringify(city).replace(/\[|\]/g,'');
let sql = `SELECT * FROM cities WHERE city IN (${stringifiedValues})`;

answered Feb 8, 2019 at 13:33

InTERpLAY

1291 silver badge5 bronze badges

1 Comment

Alex Over a year ago

This is an interesting solution for this task! If you don't like RegExp, you can use JSON.stringify(city).slice(1, -1)

molamk · Accepted Answer · 2019-02-08 13:40:39Z

1

Use the map function to transform all items in your array. You want to add quotes to the items like this Moscow => 'Moscow'
Use the join to gather the transformed items into a string. The delimiter can be specified as an argument like this array.join(', ')
Use template literals (string interpolation) to inject your string in your query. Like this ${str}

const city = ["Moscow", "Paris"];
const injectedString = city.map(c => `'${c}'`).join(', ');

let sql = `SELECT * FROM cities WHERE city IN (${injectedString})`;
console.log(sql);

answered Feb 8, 2019 at 13:40

molamk

4,1161 gold badge15 silver badges22 bronze badges

Collectives™ on Stack Overflow

Node js Mysql query IN clasue and array of strings

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related