update products based on attribute value

Question

I am writing a cron job to update products, I cannot use SKU as that is magento only. For every product in magento I have an attribute auto_cool_number I need to update it by that value (which is varchar).

I found this script on magepsycho which is awesome, it works great and updates my price and qty based on SKU, how can I modify the query to inner jOIN attribute values and update all products which share that attribute value.

     function _updateStocks($data){
         $connection     = _getConnection('core_write');
         $sku            = $data[0];
         $newQty         = $data[1];
         $productId      = _getIdFromSku($sku);
         $attributeId    = _getAttributeId();

         $sql            = "UPDATE " . _getTableName('cataloginventory_stock_item') . " csi,
                            " . _getTableName('cataloginventory_stock_status') . " css
                            SET
                            csi.qty = ?,
                            csi.is_in_stock = ?,
                            css.qty = ?,
                            css.stock_status = ?
                            WHERE
                            csi.product_id = ?
                            AND csi.product_id = css.product_id";
         $isInStock      = $newQty > 0 ? 1 : 0;
         $stockStatus    = $newQty > 0 ? 1 : 0;
         $connection->query($sql, array($newQty, $isInStock, $newQty, $stockStatus, $productId)); }

     function _updatePrices($data){
         $connection     = _getConnection('core_write');
         $sku            = $data[0];
         $newPrice       = $data[2];
         $productId      = _getIdFromSku($sku);
         $attributeId    = _getAttributeId();

         $sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
                     SET  cped.value = ?
                 WHERE  cped.attribute_id = ?
                 AND cped.entity_id = ?";
         $connection->query($sql, array($newPrice, $attributeId, $productId)); }

~

Answer 1

(note that the script you are using looks like it will only work for single store setups so I won't worry about storeid etc.)

If I have understood correctly, what you need is not really a join. (A multi table join would be a tad messy, though if your attribute is in the flat table then it would be a reasonable join)

Best to just get the ID correctly before the SQL.

You need to replace this line

$productId      = _getIdFromSku($sku);

so that you get the ID from a search based on the attribute. To make it clearer I'd change all three lines.

     $auto_cool_number = $data[0];
     $newQty           = $data[1];
     $productId        = getIdFromCool($auto_cool_number);

and again later

     $auto_cool_number = $data[0];
     $newPrice         = $data[2];
     $productId        = getIdFromCool($auto_cool_number);

Next we need to write the getIdFromCool - there is the completely clean general approach, building a collection and fetching one product, but depending on your setup there are performance shortcuts you can use (is it in flat table?) and there might be some pitfalls (are you using configurable? options?). I am assuming that auto_cool_number is unique which means that searching for a product by auto_cool_number will always return just one.

function getIdFromCool($my_auto_cool_number) {

      $collection = Mage::getModel('catalog/product')->getCollection();
      $collection->addAttributeToFilter('auto_cool_number', array('eq' => $my_auto_cool_number));       
      return array_pop($collection->getAllIds());
     // if multiple: return implode(',',$collection->getAllIds());

}

This ought to be given a little bit of error handling just in case, but hopefully this gets you started

EDIT: if multiple results with the same cool_number, return the full list from the subroutine

function getIdFromCool($my_auto_cool_number) {

      $collection = Mage::getModel('catalog/product')->getCollection();
      $collection->addAttributeToFilter('auto_cool_number', array('eq' => $my_auto_cool_number));       
      return implode(',',$collection->getAllIds());

}

and change your sql to be "where csi.product_id in ( $ ) "

Answer 2

ok, i figured it out...

I disabled the _getIdFromSku function and added this loop within both the update price and update stock function (if someone could find a way to write it into a function i would be open to it)

function _updateStocks($data){ $connection = _getConnection('core_write'); $sku = $data[0]; $newQty = $data[1]; //$productId = _getIdFromSku($sku); $attributeId = _getAttributeId();

$sql_prod        = "SELECT entity_id FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE value = $sku";
$sqlQuery = $connection->query($sql_prod);
while ($row = $sqlQuery->fetch() ) {
$entity_id = $row['entity_id'];

$sql            = "UPDATE " . _getTableName('cataloginventory_stock_item') . " csi,
                   " . _getTableName('cataloginventory_stock_status') . " css
                   SET
                   csi.qty = ?,
                   csi.is_in_stock = ?,
                   css.qty = ?,
                   css.stock_status = ?
                   WHERE
                   csi.product_id = ?
                   AND csi.product_id = css.product_id";
$isInStock      = $newQty > 0 ? 1 : 0;
$stockStatus    = $newQty > 0 ? 1 : 0;
$connection->query($sql, array($newQty, $isInStock, $newQty, $stockStatus, $entity_id));


}

}

function _updatePrices($data){ $connection = _getConnection('core_write'); $sku = $data[0]; $newPrice = $data[2]; //$productId = _getIdFromSku($sku); $attributeId = _getAttributeId();

$sql_prod        = "SELECT entity_id FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE value = $sku";
$sqlQuery = $connection->query($sql_prod);
while ($row = $sqlQuery->fetch() ) {
$entity_id = $row['entity_id'];

$sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
            SET  cped.value = ?
        WHERE  cped.attribute_id = ?
        AND cped.entity_id = ?";
$connection->query($sql, array($newPrice, $attributeId, $entity_id));


}

}