Zend Select and NOT IN array

Question

I want to select table entries by id that are not in an other table. Is used the following code:

$table2 = new OfferTable();
$select = $table2->select()->from('offers', array('aId as id'))
              ->where('mem_id =?', $memId);

$table = new otherTable();
$select2 = $table->select()->where('id NOT in (?)', $select);
$result = $table->fetchAll($select);

This works fine, but is very slow when OfferTable has a lot of entries and the first select selects only a few entries.

So I tried the following:

$table2 = new OfferTable();
$select = $table2->select()->from('offers', array('aId as id'))
              ->where('mem_id =?', $memId);
$fetch = $table2->fetchAll($select);
$id_array = array();
foreach($fetch as $value){
    $id_array[] = $value->id;
}

$table = new otherTable();
$select2 = $table->select()->where('city = ?', $city)
              ->where('id NOT in (?)', $id_array);
$result = $table->fetchAll($select);

This seems to work much faster, but sometimes I get the following error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AND (id NOT IN ('3956', '3821'))' at line 1

I don't understand why I get this error and especially why I don't get it all the time. Thanks for your help!

View the query and try to run it in phpmyadmin. – Yogesh Suthar Aug 05 '13 at 09:53 — Yogesh Suthar, Aug 05 '13 at 09:53

score 1 · Accepted Answer · answered Aug 06 '13 at 10:15

1

I think I solved the problem:

In the original code I used two ->where('id NOT in (?)', $id_array) statements. The error occured when one of the arrays was empty.

answered Aug 06 '13 at 10:15

ilse2005

11,189
5
51
75

Zend Select and NOT IN array

1 Answers1