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
– Yogesh Suthar

2013-08-05 09:53:47 +00:00
Commented Aug 5, 2013 at 9:53 — Yogesh Suthar
– Yogesh Suthar, Commented Aug 5, 2013 at 9:53

ilse2005 · Accepted Answer · 2013-08-06 10:15:44Z

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 6, 2013 at 10:15

ilse2005

11.5k5 gold badges57 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Zend Select and NOT IN array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related