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:
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?
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').