MySQL Query: Select...Not In () in php

Question

I have a value of $completedId="1,2,3,4,5";

When I run this query in phpmyadmin, it gives the perfect output which is 6,7,8,9,10.

select cmp_id from comprehension_master where cmp_id NOT in(1,2,3,4,5);

But when I run the below given query in php, it shows output as 2,3,4,5,6,7,8,9,10. Query only is being executed for value 1.

$completedId="1,2,3,4,5";
$query="select cmp_id from comprehension_master where cmp_id NOT in('".$completedId."')";
$result=$con->query($query);
$cnt=0;
while($row=$result->fetch_array()){
    $cmpId[$cnt]=$row[0];
    $cnt++;
}
$cnt=0;
print_r($cmpId);

Please help me.

Edwin Alex · Accepted Answer · 2016-02-29T11:15:31.933

2

Remove single quote from the query. It will treat it as string and truncate other ids.

Take a look at the below query and try with this.

 $query="select cmp_id from comprehension_master where cmp_id NOT in(".$completedId.
 ")";
 $result=$con->query($query);

edited Feb 29 '16 at 11:15

answered Feb 29 '16 at 11:07

Edwin Alex

5,118
4
28
50

score 1 · Answer 2 · answered Feb 29 '16 at 11:14

Actually when run as:

$query="select cmp_id from comprehension_master where cmp_id NOT in('".$completedId."')";

It treated as:

$query="select cmp_id from comprehension_master where cmp_id NOT in('1,2,3,4,5')";

Issue:

MYSQL consider it as a full string ('1,2,3,4,5')

What you need here?

Just remove quotes, no need to use quotes around your IDs like this example:

$query="SELECT cmp_id FROM comprehension_master WHERE cmp_id NOT IN ($completedId)";

If you echo your query it will looks like:

SELECT cmp_id FROM comprehension_master WHERE cmp_id NOT IN (1,2,3,4,5)

MySQL Query: Select...Not In () in php

2 Answers2