0

In my custom resource model, I'm trying to use fetchAssoc to get some data from a table. I'm trying to quote an array into an IN statement, and I've noticed that it doesn't work (returns an empty array) when using named bindings. However, using the second argument of $select->where() to quote the values in works fine.

I'm just curious why this doesn't work...

$bind = array( 
    'expert_id' => $expert_id,
    'product_id' => $adapter->quote($product_id),
);

//Debug:  Array ( [expert_id] => 1, [product_id] => '3032', '3091', '3132' )

$select = $adapter->select()
    ->from($this->getTable('namespace_module/table'), '*')
    ->where('product_id IN(:product_id)')
    ->where('expert_id = :expert_id');

// Debug: SELECT `namespace_module_table`.* FROM `namespace_module_table` WHERE (product_id IN('3032', '3091', '3132')) AND (expert_id = :expert_id)

but this does?

$bind = array( 
    'expert_id' => $expert_id,
);

//Debug: Array(  [expert_id] => 1 )

$select = $adapter->select()
    ->from($this->getTable('namespace_module/table'), '*')
    ->where('product_id IN(?)', $product_id)
    ->where('expert_id = :expert_id');

// Debug: SELECT `namespace_module_table`.* FROM `namespace_module_table` WHERE (product_id IN(:product_id)) AND (expert_id = :expert_id)

Is it a general rule that you can't use bound params in an IN statement? I've seen this and this, but I thought Magento's $adapter->quote method was supposed to take care of turning the array into a list.

3

Magento, and most modern PHP database abstractions, ultimately rely on the PDO library's bound parameter implementation for bound parameter support. PDO doesn't support binding array to an IN parameter for -- reasons? It's been that way as long as I've been using the library, and is super annoying.

To work around this, many PHP database abstractions offer IN support for their own, unbound parameters. In the second code example Magento's underlying SQL abstraction libraries are generating an IN statement for you with hard coded values from the array, and then sending that to the database. This is different from using a bound parameter -- with a bound parameter your'e sending a SQL statement with that bound parameter to the database, and also telling the database which variables apply to which parameters.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.