Convert Javascript Array to MySQL Array

Question

i would like to convert a javascript array ids = [ 378, 464 ] to an array that MySQL is successfully parsing.

My SQL query includes the array like this:

Do something WHERE id IN ("472", "467").

This query works in MySQL Workbench.

I am struggling to get the array in the needed structure. I tried the following javascript structures within my api but i cannot get it to work.

("378", "464")
[ 378, 464 ]
('472', '467')

MYSQL Error Message:

  code: 'ER_PARSE_ERROR',
  errno: 1064,

You should also consider prepared statements - here's a way to deal with this for a variable-length IN clause: stackoverflow.com/questions/327274/… — cmbuckley
– cmbuckley, Commented Sep 30, 2019 at 9:21

Nina Scholz · Accepted Answer · 2019-09-30 09:11:03Z

6

You could stringify a stringed value and join all values with comma.

var ids = [378, 464],
    formatted = `(${ids.map(v => JSON.stringify(v.toString())).join(', ')})`;

console.log(formatted);

answered Sep 30, 2019 at 9:11

Nina Scholz

388k26 gold badges367 silver badges417 bronze badges

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

4 Comments

anderwald Over a year ago

This results in the following query for me: '(\\\"378\\\", \\\"464\\\")' which ends up throwing parsing errors in mysql

Nina Scholz Over a year ago

as i aready askes, where do hand over this? please add the wanted format and why the string has escaped backslashes.

anderwald Over a year ago

I am inserting this via ``` db.query(('DO something WHERE id IN ?'), [formatted]) your fix works with: db.query(('DO something WHERE id IN ' + formatted) Thanks!

Nina Scholz Over a year ago

had you have a look to the linked question in the above comment section?

nick zoum · Accepted Answer · 2019-09-30 09:11:32Z

4

You can use Array#join('", "') to convert the array into the string format you desire.

var list = ["123", "354"];

if (!list.length) {
  console.log("Empty");
} else {
  var query = `select * from table where id in ("${list.join('", "')}")`;
  console.log(query);
}

answered Sep 30, 2019 at 9:11

nick zoum

6,6637 gold badges43 silver badges93 bronze badges

Comments

Saurabh Agrawal · Accepted Answer · 2019-09-30 09:08:14Z

2

Try Array.prototype.toString()

console.log("(",["378", "464"].toString(),")",);

answered Sep 30, 2019 at 9:08

Saurabh Agrawal

7,7553 gold badges30 silver badges52 bronze badges

Comments

Ly Thanh Ngo · Accepted Answer · 2019-09-30 10:09:38Z

0

Try it: Use join in array ("${arrItems.join('","')}")

var arrItems = [1,2,3,4,5,6,7,8,9];
var strSql = `SELECT * FROM Example WHERE ID IN ("${arrItems.join('","')}")`;
console.log(strSql);

answered Sep 30, 2019 at 10:09

Ly Thanh Ngo

4761 gold badge3 silver badges16 bronze badges

Collectives™ on Stack Overflow

Convert Javascript Array to MySQL Array

4 Answers 4

4 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Linked

Related