StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMagento bulk price changes
text
Body
copied!<p>What I'm trying to do is, a bulk price change on products in a certain category. We receive suggested retail prices from our vendor, but sometimes these don't work for us. So we need to take the cost price of the product, and for example, add 20% to the product so.. easy enough cost = cost + 0.2*cost. Now I need to do this on all the products in the selected category, so here is what I have thus far...</p> <pre><code>$category = Mage::getModel('catalog/category')->load(189); // load products from category id '189' $products = Mage::getModel('catalog/product') ->getCollection() ->addCategoryFilter($category) ->addAttributeToSelect('id'); foreach($products as $product) { // get the current cost of the product $cost = $db->fetchRow("SELECT value FROM `m_catalog_product_entity_decimal` WHERE entity_id='" . $product->getId() . "' AND attribute_id='68'"); $cost = $cost['value']; $newCost = $cost + $cost*$percentage; // update the product with a new cost $db->query("UPDATE `m_catalog_product_entity_decimal` SET value='$newCost' WHERE entity_id='" . $product->getId() . "' AND attribute_id='64'"); } </code></pre> <p>Now, I need to use raw SQL because my php server can't handle all of the magento product loading and saving (Magento 1.4 has a memory leak in the product model). This is why I'm simply selecting the "id" from the product, to get the very least amount of data. I also understand that doing all of these SQL queries is a waste of resources, and thats why I'm here. If each of my categories only had say, 10 products, I would use the product model to update the cost and save the products, but I have sometimes up to 500 or more products in a each category as one time. </p> <p>I'm hoping to condense this to one SQL query, and get rid of the foreach loop and the product collection. The cost attribute id is 68, and the price attribute id is 64. Any help on this would be much appreciated. </p> <p><em><strong>EDIT</strong></em><br> Magento uses an EAV model for their database. So for the attributes I need to access, which are "cost" and "price", they are both located in <code>m_catalog_product_entity_decimal</code> </p> <p>So a products price attribute would look like this in the table</p> <pre><code>value_id entity_type_id attribute_id store_id entity_id value 6401 4 64 0 2184 399.9500 </code></pre> <p>The value_id is just the rows unique value, entity_type_id 4 means this is a product attribute. The attribute_id is associated with the actual attribute. In this case, 64 is the attribute_id for "price". Store_id is irrelevant. Entity_id is the actual product id, and the value is the actual price of the item. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload