4

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.

14

When you create a attribute for a product EAV comes in a picture. To get a value you need to know basics, which magento uses to store eav data.

For this following tables plays a important role.

  1. eav_attribute

    Whenever you create a attribute this table gets entry which stores all important data which helps to make a relation, like entity_type_id,attribute_code,backend_type and many more but this are the important fields.

  2. eav_entity_type

    Magento has 8 types of entity, by which, 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 we say it is catalog_product in your case ?

So the answer is as i said above when you create a new attribute their will be a entry in eav_attribute and if you check this entry their will be a column as entity_type_id whose value is 4 which is nothing but a catalog_product of eav_entity_type table.

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

So as in your case we find it as a catalog_product we will look for all tables whose initial are from catalog_product you will get a table whose ending is like datetime,decimal,text,varchar,text,int etc.

catalog_product_entity is the primary table for product, our product id is nothing but a value of entity_id of this table.

How we get a desired table ?

in eav_attribute table their is a column backend_type by which we decide on which table we get a values. In your case it might be varchar. So we are going to look for a catalog_product_entity_varchar table. in this table we will search for a attribute_id and entity_id which is a product id and which we can see in admin grid and which is equal to entity_id of catalog_product_entity table.

Its time for a practical !!!

  • suppose your product id is 13
  • your attribute code is catalog_special_name

So in eav_attribute table we will search for a record whose attribute_code = catalog_special_name and we will get a useful information which is attribute_id,entity_type_id and backend_type.

lets say attribute_id = 138,entity_type_id = 4 and backend_type = varchar.

so as we got entity_type_id = 4 we will look in eav_entity_type table and confirm that our attribute is for catalog_product.

Then its time for catalog_product_entity_varchar table here we will search a record for whose attribute_id = 138 and entity_id = 13 which is our product id and that's it here you get a value.

Hope this helps you.

Their are still some more important table in it. For more details on EAV you can follow

and many more..

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

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

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

  • how to get the select attribute ? in which table it stores? – jafar pinjar Aug 27 '18 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.