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

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?

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 — zhartaunik
– zhartaunik, Commented Feb 23, 2016 at 13:36

Phoenix128_RiccardoT · Accepted Answer · 2016-02-23 13:45:09Z

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.

Latheesan · Accepted Answer · 2016-02-23 13:38:38Z

0

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!
}

answered Feb 23, 2016 at 13:38

Latheesan

1,03821 silver badges37 bronze badges

Add a comment |

Stack Exchange Network

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

2 Answers 2

Your Answer

Linked

Hot Network Questions

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

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions