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: https://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list — cmbuckley, Sep 30 '19 at 09:21

score 6 · Accepted Answer · answered Sep 30 '19 at 09:11

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 '19 at 09:11

Nina Scholz

376,160
25
347
392

This results in the following query for me: '(\\\"378\\\", \\\"464\\\")' which ends up throwing parsing errors in mysql – anderwald Sep 30 '19 at 09:42
as i aready askes, where do hand over this? please add the wanted format and why the string has escaped backslashes. – Nina Scholz Sep 30 '19 at 09:45
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! – anderwald Sep 30 '19 at 09:46
had you have a look to the linked question in the above comment section? – Nina Scholz Sep 30 '19 at 09:47

score 4 · Answer 2 · answered Sep 30 '19 at 09:11

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);
}

score 2 · Answer 3 · answered Sep 30 '19 at 09:08

2

Try Array.prototype.toString()

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

answered Sep 30 '19 at 09:08

Saurabh Agrawal

7,581
2
27
51

score 0 · Answer 4 · answered Sep 30 '19 at 10:09

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 '19 at 10:09

Ly Thanh Ngo

394
1
2
15

Convert Javascript Array to MySQL Array

4 Answers4