Even when MySQL "where" clause is not met, it returns a row

Question

edit: table structure, http://puu.sh/bQKRz/785c731604.png

/*
 *  Let's get a RANDOM question from the questions table
 *  Ignore questions the group has already
 */
Group.prototype.getRandomQuestion = function(callback){
    var self = this;
    var answered = this.answered.join();
    var categories = this.categories;

    var sql = "SELECT * FROM `questions` WHERE (`id` NOT IN (?) AND `category` IN (?)) ORDER BY RAND() LIMIT 1";

    this.mysql_pool.getConnection(function(err, conn){
        if(!err){
            conn.query(sql, [answered, categories], function(err, r){
                conn.release();

Above is my code. For some damn reason, it keeps fetching at least one result from the database when it SHOULDN'T. The where clause should NOT be met.

I opened up PhpMyAdmin, ran the same exact query with the same exact data plugged into it. It returned empty. Good. So why isn't this block of code returning empty too?

Here are the values of answered and categories respectively:

enter image description here

answered = "1,2"

categories = "1,2,3,4"

In my questions table, there are only TWO questions. Both questions have the ID 1 & 2. So, why is this block of code still returning a row?

Also, have you tried forcing `categories` to be `this.categories.join();` instead of `this.categories`? Perhaps `mysql` is doing something unexpected with array values. — mscdex, Sep 28 '14 at 03:33

score 2 · Accepted Answer · answered Sep 28 '14 at 03:48

Assume we have a table which contains two row with id 1 and 2 respectively.

select * from table where id in ('1, 2') ==> you will get only one row which has the id 1

select * from table where id not in ('1, 2') ==> you will also get only one row which has the id 2

The point is ('1, 2') is not the same as ('1', '2').

RobNHood · Answer 2 · 2014-09-28T03:45:03.853

0

Can you give the full SQL with the arrays that are returning ID 2? sorry about putting it as an answer... not allowing me to leave comment.

edited Sep 28 '14 at 03:45

answered Sep 28 '14 at 03:38

RobNHood

119
1
2
11

@Jiang that's why I was curious about the full SQL if its a numeric array the single quotes aren't even needed a simple (1,2) is all that is needed. – RobNHood Sep 28 '14 at 03:52
id = new Array(1,2); b = id.join(","); That with the NOT IN shouldn't include 2 at all – RobNHood Sep 28 '14 at 04:05
@RobNHood Ok, I understand. If you write them out like `IN (1,2,3,4...)` it works, but how do I do that in javascript? I still have the problem – X33 Sep 28 '14 at 04:37
try " NOT IN (" + id.join(',') + ")" – RobNHood Sep 28 '14 at 04:38
or this should work better "NOT IN (' " + ID.join(" ',' ") + " ')" that will put a single quote around each one I think that is probably what the problem is. just remove the spaces I only put them in so you can see the single quotes. – RobNHood Sep 28 '14 at 04:47
@X33 I posted an example.. Did I help you at all? – RobNHood Sep 28 '14 at 05:03

score 0 · Answer 3 · answered Sep 28 '14 at 05:01

var ID = new Array(1,2);
var Categories = new Array(1,2,3,4,5);

var IDstring = "'" + ID.join("','") + "'";
var Categoriesstring  = "'" + Categories.join("','") + "'";

   var sql = "SELECT * FROM `questions` WHERE `id` NOT IN (" + IDstring + ") AND `category` IN (" + Categoriesstring + ") ORDER BY RAND() LIMIT 1";

Even when MySQL "where" clause is not met, it returns a row

3 Answers3