Skip to main content

Get all product attributes for a magento object using SQL

Sometimes you may want to quickly compare all of the attributes that object (category / product) has in magento. The EAV structure of the database makes this difficult to do this simply, so this is a query that will do it for you



SELECT * FROM ( SELECT ce.sku, ea.attribute_id, ea.attribute_code, CASE ea.backend_type WHEN 'varchar' THEN ce_varchar.value WHEN 'int' THEN ce_int.value WHEN 'text' THEN ce_text.value WHEN 'decimal' THEN ce_decimal.value WHEN 'datetime' THEN ce_datetime.value ELSE ea.backend_type END AS value, ea.is_required AS required FROM catalog_product_entity AS ce LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id LEFT JOIN catalog_product_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar' LEFT JOIN catalog_product_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int' LEFT JOIN catalog_product_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text' LEFT JOIN catalog_product_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal' LEFT JOIN catalog_product_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime' WHERE ce.sku = YOUR_SKU ) AS tab WHERE tab.value != "";

Comments

Popular posts from this blog

Parsing Domain Name From URL In PHP

To get Domain name from the url, we can use parse_url() php function. This would filter the domain name from the given url. $domain = str_ireplace ( 'www.' , '' , parse_url ( $url , PHP_URL_HOST )); This would return the google.com for both http://google.com and http://www.google.com

get directory paths, get Base URLs, get URLs in static block, get secure URL in Magento

1) Get Directory paths  Mage::getBaseDir()  //output : /var/www/html/magento Mage::getBaseDir('app')  //output : /var/www/html/magento/app Mage::getBaseDir('media') //output : /var/www/html/magento/media Mage::getBaseDir(‘design’) => Get design directory path Mage::getBaseDir(‘code’) => Gives code directory file path Mage::getBaseDir(‘lib’) => Gives lib directory file path Mage::getBaseDir(‘skin’) => Gives skin directory file path Mage::getBaseDir(‘var’) => Gives var directory file path Mage::getBaseDir(‘cache’) => Gives cache directory file path Mage::getBaseDir(‘log’) => Gives log directory file path       2) Get Base URL  Mage::getBaseUrl() => Get base url path e.g. http://yourwebsite.com/  Mage::getBaseUrl('media') => Get MEDIA folder path e.g. http://yourwebsite.com/media/  Mage::getBaseUrl('js') => Get JS folder path e.g. http://yourwebsite.com/js/  Mage::getBaseUrl('skin') => Get...