NOT IN WHERE Clause Error

Question

I want to fetch a list of items from my database that are not currently within my item array.

I have researched that 'NOT IN' is the perfect solution to do multiple != 'NOT EQUAL TO' in large quantities.

But my code to generate the list:

    echo $query = sprintf("SELECT * FROM %s WHERE profile_id='%s' AND id NOT IN (%s)", $table_array[$item], $profile_id, implode(',', $shop_window_items));

Causes this error:

SELECT * FROM event WHERE profile_id='945b4dbf5bf8ee1ac08a73e9e25a939772c9c9b8' AND id NOT IN (4d96b83d18a8c2db79089ac002e346fd9dea5c43,3803bb535015174e9675090fbb680a5f286ba6bf,58f07f83a8ea10ec4db9c8b7c7f39f0a6c3a2079,57c2db2ce32925bcb49b83e22513ca74cc9bcadc)Unknown column '4d96b83d18a8c2db79089ac002e346fd9dea5c43' in 'where clause

Can anyone tell me what im doing wrong?

Thanks in advance

Actually, for very large quantities, not in is far from perfect. Quite simply it's slow. Better options are not exists and, depending on the db type, minus/except. — Dan Bracuk, Mar 14 '13 at 00:50
by large quantities I meant, upto 8 values, so in terms of efficieny as of this function, its very practical for my problem — cwiggo, Mar 14 '13 at 00:56

score 3 · Accepted Answer · answered Mar 14 '13 at 00:47

3

You need single quotes around strings, otherwise they are assumed to be system names.

...NOT IN ('%s')", $table_array[$item], $profile_id, implode("','", ...
           ^  ^                                               ^ ^

answered Mar 14 '13 at 00:47

Kermit

33,827
13
85
121

score 0 · Answer 2 · answered Mar 14 '13 at 00:47

0

Pretty sure you need quotes around the values inside the IN

echo $query = sprintf("SELECT * FROM %s WHERE profile_id='%s' AND id NOT IN (%s)", $table_array[$item], $profile_id, "'" . implode("','", $shop_window_items) . "'");

answered Mar 14 '13 at 00:47

Steven V

16,357
3
63
76

Also need quotes around `%s` – Kermit Mar 14 '13 at 00:49
I put them on either side of the implode? Probably not as pretty as Aarolama's solution. – Steven V Mar 14 '13 at 00:59
That will only put single quotes *between* the strings, not at the beginning and end; thus the need for single quotes around `%s` – Kermit Mar 14 '13 at 01:03
I have the quotes on the outside of the implode. `"'" . implode("','", array('test','test','test')) . "'"` will end up like `'test','test','test'`. – Steven V Mar 14 '13 at 01:06
1

I must have overlooked them. My apologies. – Kermit Mar 14 '13 at 01:09

NOT IN WHERE Clause Error

2 Answers2