Magento Forum

   
Database tables and fields of products and categories
 
osol
Member
 
Avatar
Total Posts:  35
Joined:  2009-11-10
 

can anybody give me a clear idea on how product and category info ( name,description,image) etc are stored in tables in magento?
I couldnt get much info from the database diagram http://www.magentocommerce.com/wiki/development/magento_database_diagram
Any help will be highl appreciated

 
Magento Community Magento Community
Magento Community
Magento Community
 
osol
Member
 
Avatar
Total Posts:  35
Joined:  2009-11-10
 

my primary aim is to extend category importer component by santosh http://www.magentocommerce.com/boards/viewthread/26022/#t110067 to inlcude category images also.Any help on that will also be highly appreciated
I ma testing this code

<?php
include('app/Mage.php');
Mage::app('default');
$category Mage::getModel('catalog/category')->load(4);
//echo "URL_PATH:".$category->getdata('url_path');echo "<pre>";print_r($category);echo "</pre>";exit;
$category->setImage('testing.jpg');
$category->save();

?>
since i am writing for newbies like me,let me quote the naming conventions in magento
Mage::getModel(’catalog/category’); call refers to the class in Mage/Catalog/Model/Category.php

Mage::getModel(’catalog/layer_filter_category’); call refers to the class in Mage/Catalog/Model/Layer/Filter/Category.php
similarly
Mage::helper(’catalog/category’) refers to class in Mage/Catalog/Helper/Category.php and so on

One thing i found odd is that when category image is deleted fromt eh admin panel,only database entry is changed,the file is not deleted.Not sure it has got anythignto do with configuration or permissions

Category Info

Few hours of digging helped me get this info
Category info is stored in 7 tables

catalog_category_entity
catalog_category_entity_datetime
catalog_category_entity_decimal
catalog_category_entity_int
catalog_category_entity_text
catalog_category_entity_varchar
catalog_category_flat

catalog_category_entity is the master table with the following fields.the other tables wit name like catalog_category_entity_{data_type} holds the data of the data type type indicated as in the name

no idea so far regarding what is ‘catalog_category_flat’ for it has the following fields
“entity_id”;"store_id”;"parent_id”;"path”;"level”;"position”;"children_count”;"created_at”;"updated_at"

if anybody has any idea about this ,please add it to this thread

To get data of type varchar of a given type following query can be

SELECT `eav_entity_type`.entity_type_code,eav_attribute.attribute_code, catalog_category_entity_varchar.value
FROM eav_entity_type, eav_attribute, catalog_category_entity_varchar
WHERE catalog_category_entity_varchar.attribute_id = eav_attribute.attribute_id
AND catalog_category_entity_varchar.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

other info can also be pulled similarly

SELECT eav_attribute.attribute_code, catalog_category_entity_datetime.value
FROM eav_entity_type, eav_attribute, catalog_category_entity_datetime
WHERE catalog_category_entity_datetime.attribute_id = eav_attribute.attribute_id
AND catalog_category_entity_datetime.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

SELECT eav_attribute.attribute_code, catalog_category_entity_text.value
FROM eav_entity_type, eav_attribute, catalog_category_entity_text
WHERE catalog_category_entity_text.attribute_id = eav_attribute.attribute_id
AND catalog_category_entity_text.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

SELECT eav_attribute.attribute_code, catalog_category_entity_int.value
FROM eav_entity_type, eav_attribute, catalog_category_entity_int
WHERE catalog_category_entity_int.attribute_id = eav_attribute.attribute_id
AND catalog_category_entity_int.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

SELECT eav_attribute.attribute_code, catalog_category_entity_decimal.value
FROM eav_entity_type, eav_attribute, catalog_category_entity_decimal
WHERE catalog_category_entity_decimal.attribute_id = eav_attribute.attribute_id
AND catalog_category_entity_decimal.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

category image is saved in media\catalog\category folder

product info

catalog_product_entity
catalog_product_entity_datetime
catalog_product_entity_decimal
catalog_product_entity_gallery
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
catalog_product_link
catalog_product_link_attribute
catalog_product_link_attribute_decimal
catalog_product_link_attribute_int
catalog_product_link_attribute_varchar
catalog_product_link_type
catalog_product_super_attribute
catalog_product_super_attribute_label
catalog_product_super_attribute_pricing
catalog_product_super_link
catalog_product_website

SELECT `eav_entity_type`.entity_type_code,eav_attribute.attribute_code, catalog_product_entity_varchar.value
FROM eav_entity_type, eav_attribute, catalog_product_entity_varchar
WHERE catalog_product_entity_varchar.attribute_id = eav_attribute.attribute_id
AND catalog_product_entity_varchar.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

SELECT eav_attribute.attribute_code, catalog_product_entity_datetime.value
FROM eav_entity_type, eav_attribute, catalog_product_entity_datetime
WHERE catalog_product_entity_datetime.attribute_id = eav_attribute.attribute_id
AND catalog_product_entity_datetime.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

SELECT eav_attribute.attribute_code, catalog_product_entity_text.value
FROM eav_entity_type, eav_attribute, catalog_product_entity_text
WHERE catalog_product_entity_text.attribute_id = eav_attribute.attribute_id
AND catalog_product_entity_text.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

SELECT eav_attribute.attribute_code, catalog_product_entity_int.value
FROM eav_entity_type, eav_attribute, catalog_product_entity_int
WHERE catalog_product_entity_int.attribute_id = eav_attribute.attribute_id
AND catalog_product_entity_int.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

SELECT eav_attribute.attribute_code, catalog_product_entity_decimal.value
FROM eav_entity_type, eav_attribute, catalog_product_entity_decimal
WHERE catalog_product_entity_decimal.attribute_id = eav_attribute.attribute_id
AND catalog_product_entity_decimal.entity_type_id = `eav_entity_type`.`entity_type_id`
AND entity_id =2

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top