Zend Framework DB Adapter fetchCol problem with "IN"

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.

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')

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?

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 ) . ')' );