CODEWITHSUNDEEP
mysqlnode.js
Jithin Sebastian
Jithin Sebastian

Reputation: 581

IN clause in mysql nodejs

I have a simple nodejs application which executes the following query.

select * from User where userid in (?)

The userids i get is a JSON array send from client side. How can i use that in this select query ? I tried 1. As itself but not working. 2. Convert this to Javascript array, not working

Upvotes: 24

Views: 22250

Answers (8)

Jerry Thomas
Jerry Thomas

Reputation: 305

If you pass an array to the parameter it works with node mysql2. Parameters are already passed as arrays, so your first parameter needs to be an array [[1,2,3]].

select * from User where userid in (?)

const mysql = require('mysql2/promise');

async function main(){
  let db = await mysql.createPool(process.env.MYSQL_URL);
  let SQL = 'select * from User where userid in (?)';

  let [res, fields] = await db.query(SQL, [[1,2,3]]);
  console.log(res)
  return res;
}

main().then(() => {process.exit()})

Upvotes: 6

Jithin Sebastian
Jithin Sebastian

Reputation: 581

Update: Please see this answer. It is the correct way to do what is asked in the question.

The methods I have tried are:

  1. Expand JSON array to a string in the required format. Concatenate it with query using '+'. (Beware of SQL injections)
  2. Dynamically add '?' using length of JSON array holding user ids. Then use the array to provide user ids.

Both works. I then changed my logic with a better approach so now i don't need then 'in' clause anymore.

Upvotes: 0

Amit Rana
Amit Rana

Reputation: 43

let val = ["asd","asd"]
let query = 'select * from testTable where order_id in (?)';
connection.query(query, [val], function (err, rows) {
});

In Node, you need to put array in the array.

Upvotes: 0

mnojind
mnojind

Reputation: 101

// get query string data with commas var param=req.params['ids'];

//damy data var param = [1,2,3,4,5];

var array = params.split(",").map(Number);

//Note in select query don't use " and ' ( inverted commas & Apostrophe)
// Just use ` (Grave accent) first key off numeric keys on keyboard before one 

con.query(`select * from TB_NAME where COL IN(?)`,[array],(err,rows,fields)=>{

res.json(rows); });

Upvotes: 0

Madura Pradeep
Madura Pradeep

Reputation: 2454

If you are using node module like mysql, the 2nd approach should work.

var query=select * from User where userid in (?);
var data=['a','b','c'];
var queryData=[data];

conn.query(query, queryData, function (err, results) {})

According to the documentation, "Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b'". So this approach should work (I have used it practically).

Upvotes: 37

d-_-b
d-_-b

Reputation: 23181

Something like this could work!

// get your possible IDs in an array
var ids = [1,2,3,4,5];

// then, create a dynamic list of comma-separated question marks
var tokens = new Array(ids.length).fill('?').join(',');

// create the query, passing in the `tokens` variable to the IN() clause
var query = `SELECT * FROM User WHERE userid IN (${tokens})`;

// perform the query
connection.query(query, ids, (err, data) => {
   // do something with `err` or `data`
});

Upvotes: 1

Rodrigo
Rodrigo

Reputation: 48

Revisiting this, since the original approach on the question is valid, but with some caveats. If your only escaped argument is the one on the IN clause, then you have to specify it as nested array; something like: [['usrId1', 'usrId2', 'usrIdN']]. This is because the un-escaping functionality expects an array, replacing each '?' with the corresponding array element. So, if you want to replace your only '?' with an array, that array should be the first element of all arguments passed. If you had more than one '?', the syntax is more intuitive, but at the end consistent and the same; in this case, you could have your arguments similar to: ['myOtherArgument1', 'myOtherArgument2', ['usrId1', 'usrId2', 'usrIdN'], 'myOtherArgument3']

Upvotes: 1

Md.Alauddin Hossain
Md.Alauddin Hossain

Reputation: 186

You can do like this: select * from User where userid in (?,?,?,?)

var array = []; 
array.push(value);
array.push(value);
array.push(value);
array.push(value);

then use array as parameter that should be bind.

Upvotes: 0

Related Questions