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.
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.
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.eav_entity_type
Magento has 8 types of entity, by which, type of attribute is decided. in your case it is catalog_product
customer
customer_address
catalog_category
catalog_product
order
invoice
creditmemo
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
- 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
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
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();
If you have the email of customer, you can probably try this list of queries:
Get the customer entity id: (Ex: 10)
SELECT entity_id FROM
customer_entity
where email='user@example.com'Get the attribute id of attribute code: (Ex: 225)
SELECT attribute_id FROM
eav_attribute
where attribute_code='my_attribute'Get the value of the customer attribute, "my_attribute":
SELECT value FROM
customer_entity_varchar
where attribute_id=225 and entity_id=10
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.