0

I'm writing a script that will be run every hour to update a field of any product updated in the last hour. The script itself works, but it also updates the updated_at field upon saving the product.

After doing some research, I found that the best way to update the single field would be to use a direct sql statement to modify that single field.

So, I have the product id, the attribute id, and the new attribute value. The attribute uses a textarea as its input. I don't know which table to write the change to.

For example, if product_id is 1234 and attribute_id is 567, what would the sql statement look like? Something like this?

$val = "Some generated value.";
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
$sql = 'UPDATE `<insert table name here>` SET `value` = ? WHERE `entity_id` = ? AND `attribute_id` = ?';
$connection->query($sql, array($val, 1234, 567));

Also, is there some kind of reference for this sort of thing that I can look at in the future?

1

The EAV tables responsible for saving the values of Magento product attributes are :

catalog_product_entity_datetime
catalog_product_entity_decimal
catalog_product_entity_gallery
catalog_product_entity_group_price
catalog_product_entity_int
catalog_product_entity_media_gallery
catalog_product_entity_media_gallery_value
catalog_product_entity_text
catalog_product_entity_tier_price
catalog_product_entity_varchar

In your case, depending on the type you chose for the attribute, it will be _text, to hold as much data as possible from the text area field.

An example would be, the description field in a product. knowing that the product entity type ID and the attribute code, I can get the details of it from eav_attribute :

SELECT * FROM eav_attribute where attribute_code = 'description' AND entity_type_id = '4' \G

Which will show me the backend_type

   attribute_id: 64
 entity_type_id: 4
 attribute_code: description
attribute_model: NULL
  backend_model: NULL
   **backend_type: text**
  backend_table: NULL
 frontend_model: NULL
 frontend_input: textarea
 frontend_label: Description
 frontend_class: NULL
   source_model: NULL
    is_required: 1
is_user_defined: 0
  default_value: NULL
      is_unique: 0

From there, I will be able to determine where the data is saved for a specific entity, which leads to your question again, now I can be able to get the value by:

SELECT * FROM catalog_product_entity_text WHERE entity_id='1234' AND attribute_id='567' \G

Or update it with:

UPDATE catalog_product_entity_text SET value='My New Value' WHERE entity_id='1234' AND attribute_id='567'

Please note, that there is also a store_id which you have to take into consideration, if you have content per store.

| improve this answer | |

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.