How to run direct SQL query on magento and bind WHERE IN params?

Question

I have an array like this:

$stockUpdates = [
 'DVD-UBOX-UK' => 37,
 'DVD-UCARD-UK' => 39 
];

For these skus, I need to know their product ID's, so ran the following script to run a direct sql query on magento core_read connection with parameter binding like this:

 // Load db resource
 $resource = Mage::getSingleton('core/resource');
 $readConn = $resource->getConnection('core_read');
 $cpe_table = $resource->getTableName('catalog_product_entity');
 // Parse sku product ids
 $result = $readConn->query(
 "SELECT entity_id, sku FROM $cpe_table WHERE sku IN (:skuList)",
 array('skuList' => array_keys($stockUpdates)));
 while ($row = $result->fetch()) {
 var_dump($row); // debug test
 }

When the script executes, it looks like this (not what I was expecting):

enter image description here

All I am trying to do is get a list of entity_id, sku for an array of sku list I have. What am I doing wrong here?

• zhartaunik: It's totally bad approach to use directly core_read adapter. With last magento update all queries should be wrapped in Zend_Db_Cond (don't remember exact class name). What is the general task? Maybe we can offer better algorithm

Answer 1

Why don't you just use the following:

Mage::getSingleton('catalog/product')->getIdBySku($productSku);

You can also get multiple products from a collection:

$collection = Mage::getSingleton('catalog/product')->getCollection();
$collection->addAttributeToFilter('sku', array('in' => array('sku1', 'sku2', ...)));
foreach ($collection as $product) {
 echo $product->getId();
}

If you want to keep the SQL approach then use something like this:

$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_write');
$productTable = $resource->getTableName('catalog/product');
$qry = $readConnection->select()->from($productTable)->where('sku=?', $productSku);
$rows = $readConnection->fetchAll($qry);
foreach ($rows as $row) {
...
}

This is just an example, so fix it with your logic.

For escape you can also use:

$readConnection->quote($myValueToEscape)

BUt you should not directly type SQL. Starting from Magento 1.6 they completely isolated the SQL layer, so you should only use the PDO calls.

Answer 2

Okay, magento sql uses PDO, so I have solved it like this:

// Parse sku product ids
$dbParamPlaceholders = array();
$dbParamValues = array();
foreach (array_keys($stockUpdates) as $index => $sku) {
 $dbParamPlaceholders[] = ':sku'. $index;
 $dbParamValues[':sku'. $index] = $sku;
}
$result = $readConn->query
(
 'SELECT entity_id, sku FROM '. $cpe_table .' WHERE sku IN ('. implode(',', $dbParamPlaceholders) .')',
 $dbParamValues
);
while ($row = $result->fetch()) {
 var_dump($row); // works!
}