How to use Mysql SELECT + WHERE + IN with Javascript array?

Asked

Viewed 548 times

2

How can I perform the following SQL query by passing a Javascript string array?

SQL query performed in Mysql - status OK:

SELECT * FROM tab_price_list AS PL
WHERE PL.PartNumber IN ('HDW00008.ELEM', 'HDW00006.ELEM', 'HDW00002.ELEM', 'HDW00004.ELEM')

In my application the value inside the parentheses will be dynamic and will come from the front end can be an array with 1 or N elements, as in the following example:

ProductsDAO.prototype.getProductData = function (part_numbers_array, callback) {
    this._connection.query(`
        SELECT * FROM tab_price_list as PL 
        WHERE PL.PartNumber IN (?);
    `, part_numbers_array, callback)
}

Where the variable "part_numbers_array" is:

part_numbers_array = [
  'HDW00008.ELEM',
  'HDW00006.ELEM',
  'HDW00002.ELEM',
  'HDW00004.ELEM',
]

Currently this code is only bringing the lines where PL.partNumber is equal to the index element 0 of the array. In this case, where PL.partNumber = 'HDW00008.ELEM'.

How do I query if done for all elements of my array?

javascript mysql sql node.js angular

  • It has a php command that "joins" the strings in the array and sends as a single data. $array = array('lastname', 'email', 'phone'); $comma_separated = implode(",", $array); echo $comma_separated; // lastname,email,phone

2 answers

3


You will need to enter the values directly into your query:

const parametros = part_numbers_array.map((item) => "'${item}'").join(',');

this._connection.query(`SELECT * FROM tab_price_list as PL  WHERE PL.PartNumber IN (${parametros});`, part_numbers_array, callback);

  • In this example I have to change "'${item}'" --> ${item}, right?

  • @Leonardovinicius no. The map is just to place plicas.

  • Thank you so much for the help, but to make it work you had to make the following change: const parametros = part_numbers_array.map((item) => "${item}").Join(',');

0

Browser other questions tagged javascript mysql sql node.js angular

You are not signed in. Login or sign up in order to post.