MYSQL NOT IN(Array) not working properly

Arc

Hey guys. I am having an issue with a NOT IN() clause. It is returning results that is shouldn't.

Here is the SQL Statement

$theList="1,2,3,1,1,2,3";
$sql = "SELECT  * FROM tblVehicles WHERE VehicleID NOT IN ('$theList')";

$theList is populated from a different SQL statement dynamicaly.

The problem is when I run this code it returns 2 and 3. For some reason it is only working for the first number in $theList. For instance, if the first number is 3, then it will still return 1 and 2.

Shouldn't it return 0 results if all the vehicle ID's are in the $theList?

Thanks for any help🙂

Arc

I've done some more testing and it seems that if I manualy put in the numbers with no quotes it works fine.

 
$sql = "SELECT  * FROM tblVehicles WHERE VehicleID NOT IN (1,2,3,1,1,2,3,2)";

That works exactly as it should. However, if I used quotes

 
$sql = "SELECT  * FROM tblVehicles WHERE VehicleID NOT IN ('1,2,3,1,1,2,3,2')";

It doesnt seem to take into account anything but the first number.

So my problem here is, why with it not work when I use a php variable with no quotes? Like this

 
$sql = "SELECT  * FROM tblVehicles WHERE VehicleID NOT IN ($theList)";

That causes an error that says "You have an error in your SQL statement".

If I use quotes like this

 
$sql = "SELECT  * FROM tblVehicles WHERE VehicleID NOT IN ('$theList')";

I get no errors, but it returns results that shouldn't be there....ugh.

Weedpacket

Arc wrote:
That causes an error that says "You have an error in your SQL statement".

If $theList is as you describe it then it shouldn't (and the error message you quoted would have said more). Have you looked at the query that is actually failing to see what exactly you're sending to MySQL?

mblack0508

If the field is an integer, the quotes will tell mysql to use it as a string. Also the placement of the quotes makes it all one string, not multiple values.