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

databasequerysql

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?

Best Answer

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.

Related Topic