I need to add write a php script to run from a cron job that will add two Magento Attribute values and store the result as a third attribute in the MySQL database.
Any help would be appreciated. I've attached a screen shot that hopefully clarifies things.
Working directly in SQL when dealing with EAV models is hard. Save yourself the effort and use Magento's classes instead. A minimal cron script might look something like this:
<?php
require 'app/Mage.php';
umask(0);
Mage::app();
$products = Mage::getModel('catalog/product')->getCollection();
foreach ($products as $product) {
$product->setSumValue(
$product->getFirstValue() + $product->getSecondValue()
)
->save();
}
Magento provides magic methods so you can use syntax like getFirstValue(), getSecondValue() and setSumValue() to refer to attributes named first_value, second_value and sum_value respectively. Use the names of your actual attributes instead of the numbers 141, etc.
setTotInventory. You were right second time. If "stock_in_hand" is being set in another script then that is the ideal time to update "tot_inventory" too.I think this should do it, but I haven't tested it.
The basic procedure is to create a temporary table which contains the summed values for those entities you care about, then update the proper attribute rows with those values.
Note that php is not necessary here, only SQL. You may of course want to use php to issue the query.
UPDATE
catalog_product_entity_decimal,
(SELECT entity_id, SUM(value) AS summedvalue FROM catalog_product_entity_decimal as cped
WHERE attribute_id IN (145, 146)
AND EXISTS (SELECT 1 FROM catalog_product_entity_decimal WHERE entity_id=cped.entity_id and attribute_id=141)
GROUP BY entity_id
) AS sums
SET catalog_product_entity_decimal.value = sums.summedvalue
WHERE catalog_product_entity_decimal.entity_id=sums.entity_id
AND catalog_product_entity_decimal.attribute_id=141;
This builds an intermediate table sums that looks like this (based on data in table screenshot):
entity_id | summedvalue
1 | 12
2 | 0
If there's an entity_id without a row with an attribute_id=141, that entity_id won't have a row in this sums table--this is ensured by the EXISTS() condition.
Then we update the catalog_product_entity_decimal table (I'll abbreviate it CPED) by joining it with sums on the entity_id.
CPED.entity_id | CPED.attribute_id | sums.entity_id | sums.summedvalue
1 | 145 | 1 | 12
2 | 145 | 2 | 0
SET CPED.value = sums.summedvalue does the actual setting.
sums pseudo-table have a row somewhere in catalog_product_entity_decimal table with an attribute_id=141. It's a filter. You could accomplish the same thing with a self inner join using that condition. (In fact there are few different SQL solutions.)