5

I’ve installed magento edition version 1.9.2.0 and added a custom attribute catalog_special_name (text field) and i want to know which table stores the custom attribute values for each product.

4 Answers 4

22

When you create an attribute for a product, EAV (Entity Attribute Value) comes into the picture. To get a value you need to know the basic ways Magento stores EAV data.

For this, the following tables play important roles:

  1. eav_attribute

    Whenever you create an attribute, this table gets entry which stores all the important data that helps to make a relation, such as entity_type_id, attribute_code, backend_type and many more (but these are the important fields).

  2. eav_entity_type

    Magento has 8 types of entity by which the type of attribute is decided. In your case it is catalog_product.

    1. customer
    2. customer_address
    3. catalog_category
    4. catalog_product
    5. order
    6. invoice
    7. creditmemo
    8. shipment

How do we determine it's catalog_product in your case?

When you create a new attribute there will be an entry in the eav_attribute table. If you check this entry there will be a column entity_type_id whose value is 4, which matches up to catalog_product from the eav_entity_type table.

The eav_attribute table also has a column known as attribute_code which is nothing but the value of Attribute Code entered while making a new attribute from admin.

Since, in your case, we find that it's catalog_product, we can look for all tables whose names start with catalog_product_entity. You will get tables whose endings are like datetime, decimal, text, varchar, int etc.

catalog_product_entity is the primary table for products. Our product id is nothing but the value of entity_id in this table.

How do we know which catalog_product_entity_* table to use?

In the eav_attribute table, there's a column backend_type. This tells us which table to use to find the values. In your case it might be varchar, so we would look for the catalog_product_entity_varchar table. In this table we can search for an attribute_id and an entity_id (which is a product id and which we can see in the admin grid and which is equal to entity_id in the catalog_product_entity table).

It's time for a practical!

Suppose your product id is 13 and your attribute code is catalog_special_name.

So in the eav_attribute table we will search for a record whose attribute_code is catalog_special_name. This will give us useful information:

> select attribute_id,entity_type_id,backend_type from eav_attribute where attribute_code = 'catalog_special_name';
+--------------+----------------+--------------+
| attribute_id | entity_type_id | backend_type |
+--------------+----------------+--------------+
|          138 |              4 | varchar      |
+--------------+----------------+--------------+

So as entity_type_id is 4, we will look in the eav_entity_type table and confirm that our attribute is for catalog_product.

> select entity_type_code from eav_entity_type where entity_type_id = 4;
+------------------+
| entity_type_code |
+------------------+
| catalog_product  |
+------------------+

So our entity type is catalog_product and our backend type is varchar, so we use the catalog_product_entity_varchar table. Here we will search for a record whose attribute_id is 138 and entity_id is 13 (our product id), and that's it!

> select value from catalog_product_entity_varchar where attribute_id = 138 and entity_id = 13;
+-----------------+
| value           |
+-----------------+
| Office Supplies |
+-----------------+

There are still some other important tables. For more details on EAV you can follow:

1
  • @ Ashish Madankar : If my custom attribute author,, how can i find author values in db
    – Gem
    Nov 17, 2016 at 5:15
5

Short answer ... make use of getBackendTable()

$code = 'catalog_special_name';
$attribute = Mage::getSingleton('eav/config')->getAttribute(Mage_Catalog_Model_Product::ENTITY, $code);

For text attributes echo $attribute->getBackendTable() will return ...

{prefix_}catalog_product_entity_varchar


Since eav_entity_type can change do not use static numbers like catalog_product = 4 or use fixed attribute IDs.

$attribute->getEntityTypeId()
$attribute->getAttributeId()

Same for entity names, instead of catalog_product you can use Mage_Catalog_Model_Product::ENTITY

If you want the filter by EAV attributes entity_type_id, do not use

$attributes = Mage::getResourceModel('eav/entity_attribute_collection')
    ->addFieldToFilter('entity_type_id', 4)

Better get correct entity_type_id from DB first ...

$entityTypeId = Mage::getModel('eav/config')
    ->getEntityType(Mage_Catalog_Model_Product::ENTITY)
    ->getEntityTypeId();
1
  • How to find which table hold attribute id = 56? Thanks @sv3n
    – zus
    Dec 30, 2019 at 11:46
-1

If you have the email of customer, you can probably try this list of queries:

  1. Get the customer entity id: (Ex: 10)

    SELECT entity_id FROM customer_entity where email='user@example.com'

  2. Get the attribute id of attribute code: (Ex: 225)

    SELECT attribute_id FROM eav_attribute where attribute_code='my_attribute'

  3. Get the value of the customer attribute, "my_attribute":

    SELECT value FROM customer_entity_varchar where attribute_id=225 and entity_id=10

1
  • how to get the select attribute ? in which table it stores? Aug 27, 2018 at 11:05
-2

In the database if you use the flat tables you can find your attribute in to the catalog_product_flat_1 table.

further more all attribute are store in the eav_attribute table. you can find your attribute in this table with attribute_id and use it to get more info.

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.