How to append values of an array to an SQL statement in Javascript

Question

let todo = [1, 2, 3, 4, 5, 6, 7, 8];

var stmt = "SELECT * FROM MY_USERS WHERE USER_ID = ?";

connection.query(stmt, todo, function(err, row) {
});

connection.release();

I would like to SELECT * FROM MY_USERS WHERE the USER_ID could be any of the values in the array [1, 2, 3, 4, 5, 6, 7, 8].

Is there a way I could do this without iterating over the array, constructing a new string segment and concatenating it to a final query statement?

Thank you all in advance.

EDIT

I cannot tell the exact number of the array elements in advance, so SELECT * FROM MY_USERS WHERE USER_ID IN (?, ?, ?, ?, ?, ? ,? ,?) will not do.

Your title says insert but you'r query has select. Confusing. — slon
– slon, Commented Jun 5, 2019 at 3:52
@slon I just edited it to How to append values of an array to an SQL statement in Javascript — Program-Me-Rev
– Program-Me-Rev, Commented Jun 5, 2019 at 3:54
You can turn the array to string separated by comma and put into ....USER_ID IN(Comma Separated string) — slon
– slon, Commented Jun 5, 2019 at 4:01

Program-Me-Rev · Accepted Answer · 2019-06-12 13:35:11Z

3

SELECT * FROM MY_USERS WHERE USER_ID IN (?, ?, ?, ?, ?, ? ,? ,?)

Just make sure the number of ? matches the number of elements in your parameter array

Then finally, use todo.length to do something like :

let todo = [1, 2, 3, 4, 5, 6, 7, 8];

var stmt = "SELECT * FROM MY_USERS WHERE USER_ID IN (";

for (let i = 0; i < todo.length; i++) {
  if (i === 0) {
    stmt += "?";
  } else {
    stmt += ", ?";
  }
}

stmt += ")";

connection.query(stmt, todo, function(err, row) {});

connection.release();

edited Jun 12, 2019 at 13:35

Program-Me-Rev

6,73421 gold badges72 silver badges157 bronze badges

answered Jun 5, 2019 at 3:56

Aaron

1472 bronze badges

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

2 Comments

Program-Me-Rev Over a year ago

Thank you for the response @aaron, but there is no way to tell the length of the array in advance.

Aaron Over a year ago

use todo.length then do something like stmt = 'SELECT * FROM MY_USERS WHERE USER_ID IN ('; for (let i=0; i<todo.length; i++){ if (i === 0) {stmt += '?'} else {stmt += ', ?'} }; stmt += ')'

Shubham Dixit · Accepted Answer · 2019-06-05 04:50:12Z

1

let todo = [1, 2, 3, 4, 5, 6, 7, 8];
var stmt = `SELECT * FROM MY_USERS WHERE USER_ID IN (${todo.join(",")})`;
console.log(stmt); // your query

You can use template literals like this,In this way you wouldn't have to bother about number of elements in array.See Array.prototype.join()

let todo = [1, 2, 3, 4, 5, 6, 7, 8];

var stmt = `SELECT * FROM MY_USERS WHERE USER_ID IN (${todo.join(",")})`;

edited Jun 5, 2019 at 4:50

answered Jun 5, 2019 at 4:42

Shubham Dixit

9,2685 gold badges32 silver badges50 bronze badges

Collectives™ on Stack Overflow

How to append values of an array to an SQL statement in Javascript

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related