CODEWITHSUNDEEP
javascriptmysql
zack_falcon
zack_falcon

Reputation: 4376

MySQL IN returning only the results of the first item in the array

I have the following query:

SELECT prefix FROM username_prefix_pool LEFT JOIN user_creds ON user_creds.prefix_id = username_prefix_pool.id WHERE user_creds.id IN (?);

With the following parameter:

[ 96, 62, 95, 92, 91 ]

I've tested this query via MySQL workbench, and it does return results, five of them.

However, in my code (node.js), it only ever returns the result of the first item in the list.

.then((mapListResults) => {
    // ^^ results from another query
    mapList = mapListResults; // <--- Stores the result
    for (let i = 0; i < mapList.length; i++) {
        authorIds.push(mapList[i].author_id.toString());
    }

    console.log('Author IDs');
    console.log(authorIds); // this is the [ 96, 62, 95, 92, 91 ] 

    let searchsql = `SELECT prefix FROM username_prefix_pool LEFT JOIN user_creds ON user_creds.prefix_id = username_prefix_pool.id WHERE user_creds.id IN (?)`;
    return new Promise((resolve, reject) => {
        connection.query(searchsql, authorIds, (err, rows) => {
            if (err) {
                console.log(err);
                reject(err);
            } else {
                console.log(rows);
                resolve(rows);
            }
        });
    });

})

I have tried turning the array into an array of strings, but to no avail. I have also tried removing the parenthesis on the SQL statement (that resulted in a query error, so I put it back).

Is there something I'm missing?

Upvotes: 1

Views: 780

Answers (1)

nacho
nacho

Reputation: 5397

The placeholder it is just taking the first element of your list, because it thinks there are more placeholders for the rest of elements. You should provide just a string.

You should change your code, so you convert your list to string with something like this:

console.log('Author IDs');
console.log(authorIds); // this is the [ 96, 62, 95, 92, 91 ] 
authorIds=authorIds.join(); // add this line in your code
console.log(authorIds); // you will get a string with 96,62,95,92,91
let searchsql = `SELECT prefix FROM username_prefix_pool LEFT JOIN user_creds ON user_creds.prefix_id = username_prefix_pool.id WHERE user_creds.id IN (?)`;

Upvotes: 1

Related Questions