PHP如何添加存储在行而不是列中的值？

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.

我需要添加一个php脚本来从一个cron作业运行,该作业将添加两个Magento属性值并将结果存储为MySQL数据库中的第三个属性。

Any help would be appreciated. I've attached a screen shot that hopefully clarifies things.

任何帮助,将不胜感激。我附上了一个屏幕截图,希望澄清事情。

2 个解决方案

#1

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:

在处理EAV模型时直接在SQL中工作很难。省下自己的努力,改用Magento的课程。最小的cron脚本可能看起来像这样:

<?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.

Magento提供了魔术方法,因此您可以使用getFirstValue(),getSecondValue()和setSumValue()等语法分别引用名为first_value,second_value和sum_value的属性。使用实际属性的名称而不是数字141等。

#2

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.

注意这里不需要php,只有SQL。您当然可以使用php来发出查询。

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.

如果没有带有attribute_id = 141的行的entity_id,则该entity_id在此sums表中将没有行 - 这由EXISTS()条件确保。

Then we update the catalog_product_entity_decimal table (I'll abbreviate it CPED) by joining it with sums on the entity_id.

然后我们通过将其与entity_id上的sum相加来更新catalog_product_entity_decimal表(我将其缩写为CPED)。

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.

SET CPED.value = sums.summedvalue执行实际设置。

#1