Node.js API with express & mysql - WHERE IN and bind parameter with mutliple comma seperated values

Question

I have a url like this ... where multiple (comma seperated values can be added to the url)

localhost:4001/api/v1/users/search?title=mr,dr

That's my query:

router.get('/search?', function(req, res, next) {
    var title = req.query.title;

    var paramTitle = '\'' + title.split(',').join('\',\'') + '\''; 

    var setParams = [];
    if (title) {    
        setParams = [paramTitle];
    } 

    var sql = "SELECT * from users WHERE title IN (?)"; 

    connection.query(sql, setParams, function (error, results, fields) {
        res.json({"status": 200, "error": null, "response": results});
    });
});

But I get an Error:

sql "SELECT * from users WHERE title IN '(\\'mr\\',\\'dr\\')' LIMIT 100"

How can I get this:

var sql = "SELECT * from users WHERE title IN ('mr','dr')";

Jim Wright · Accepted Answer · 2018-02-22 15:10:34Z

4

The binding is expecting an array, so you don't need to do the manual joining yourself.

var paramTitle = title.split(','); 

var setParams = [];
if (title) {    
    setParams = [paramTitle];
}

answered Feb 22, 2018 at 15:10

Jim Wright

6,0681 gold badge18 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Node.js API with express & mysql - WHERE IN and bind parameter with mutliple comma seperated values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related