Find the table for custom attribute values

Question

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.

Answer 1

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:

• http://excellencemagentoblog.com/blog/2011/09/07/magento-eav-database-structure/
• http://inchoo.net/magento/magentos-database-layout-and-its-eav-structure/
• http://divante.co/blog/efficient-magento-code-database-flat-eav-part-1/
• http://magecert.com/eav.html

Answer 2

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();

Answer 3

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='[email protected]'

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

Answer 4

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.