1

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?

  • 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 Feb 23 '16 at 13:36
3

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.

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

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.