Zend Framework DB Adapter fetchCol problem with "IN"

Question

When making selects using the fetchCol method of the Zend_Db_Adapter class, queries are not working as I expected eg: (where $db is a reference to a DB_Adapter)

$ids = array(1,2,3,4);
$idString = implode(",", $ids);
$query = "SELECT id FROM some_table WHERE id IN (?)";
$result = $db->fetchCol($query, $idString);

You would expect this to return an array of ids that matched the idString, but it only returns an array with a single item - the first id matched. If I were to rewrite it like this there would be no problem:

$ids = array(1,2,3,4);
$idString = implode(",", $ids);
$query = "SELECT id FROM some_table WHERE id IN ($idString)";
$result = $db->fetchCol($query);

Is the expected behaviour or a bug in ZF? The main problem I have with it is it isn't an obvious error to track, no functionality is broken I just have fewer results.

Tim Fountain · Accepted Answer · 2011-06-21T15:02:47.287

3

ZF is clever enough to know what to with arrays, so all you need is pass in the array itself, skip the implode:

$query = "SELECT id FROM some_table WHERE id IN (?)";
$result = $db->fetchCol($query, array($ids));

with your code, since you are passing in a string it gets quoted, so the query you are running ends up being something like this:

SELECT id FROM some_table WHERE id IN ('1, 2, 3, 4')

edited Jun 21 '11 at 15:02

answered Jun 21 '11 at 14:39

Tim Fountain

33,093
5
41
69

That makes sense in terms of why I'm only getting one result, thank you! However, using an array throws an exception "Invalid parameter number: number of bound variables does not match number of tokens" I'm assuming because it is expecting to replace several ? – baseten Jun 21 '11 at 14:59
That worked for me using Zend_Db_Select, but since you are calling fetchCol directly you may need to pass your array inside another array - see my edited answer above. – Tim Fountain Jun 21 '11 at 15:03

score 1 · Answer 2 · edited May 23 '17 at 12:27

1

Try:

$query = "SELECT id FROM some_table WHERE id IN (?)";
$result = $db->fetchAll($query, $idString);

OR

create a DbTable for 'sometable'

class SomeTable extends Zend_Db_Table_Abstract {
  protected $_name = 'sometable';
}

Fetch results like this

$table = new SomeTable();
$select = $table->select();
$select->where('id IN (?)',  $idString );
$result = $table->fetchAll( $select );

See following POST as well:

Zend Framework: Proper way to interact with database?

edited May 23 '17 at 12:27

Community

1
1

answered Jun 21 '11 at 14:00

Naveed

41,517
32
98
131

Run same type of query in database directly and see how many results you get. – Naveed Jun 21 '11 at 14:12
Also check `print_r($idString)` to sure that your array contains correct values. – Naveed Jun 21 '11 at 14:13
Yep, already tried all that, it works every way except through the Db Adapter – baseten Jun 21 '11 at 14:24
Thanks, building a select statement in this way does solve the problem - I'm not a huge fan of Zend_Db_Table_Abstract though as I'm happy writing MySQL and I need to do quite a few joins. – baseten Jun 21 '11 at 15:02

score 0 · Answer 3 · answered Jun 21 '11 at 16:02

Try this:

$ids    = array( 1, 2, 3 );
$db->fetchCol( 'SELECT id FROM some_table WHERE id IN (' . str_repeat( '?,', sizeof( $ids )-1 ) . '?)', $ids );

you can alse do something like this:

$db->fetchCol( 'SELECT id FROM some_table WHERE id IN (' . $db->quoteInto( '?', $ids ) . ')' );

Zend Framework DB Adapter fetchCol problem with "IN"

3 Answers3