Where field NOT IN ("a", "b", "c") ?

monkeyboy

Joining a table for pull down list, but need to EXCLUDE certain unneeded IDs
The following code does not seem to work (The FIRST where causes the error)
Does PHP support IN / NOT IN (LIST)? If so, can you provide an example? (or suggest my error?)

        ->where('table1.id', "(5,6,7,8,9,11)", 'NOT IN')
        ->where('table2.status', "cancelled", 'NOT LIKE')
        ->where('table2.archive', 1, '!=')
        ->where('table2.deleted', 1, '!=')
        ->leftJoin( 'table1',  'table1.id', '=', 'table2.pulldownId' )

I get this error:

DataTables warning: table id=newEntryTable - SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''(5,6,7,8,9,11)' AND table2.status NOT LIKE 'cancelled'' at line 1

allan

The issue is that like that the (...) is being treated as a string and quoted, rather than being seen as an array. That is the default behaviour to prevent SQL injection attacks. You would need to use:

->where( function($q) {
  $q->where( 'table1.id', "(5,6,7,8,9,11)", 'NOT IN', false);
} )

More information about using more advanced conditions such as this is available here.

Note that you have to use the anonymous function as the Editor where() method doesn't provide an option to disable the binding. Again, that is for security to help stop people shooting themselves in the foot!

Allan

monkeyboy

Ow, Ow, My foot?

Thanks Allan! As usual - you rock!