2

I want to get all products that are stored in the database. I want those products that have these attributes:

SKU, code_brand, code_business_unit, code_line, code_group, code_business

The problem is that after executing my query it does not show any errors but it is not displaying the products (empty rows).

This is the Database diagram:

Image

  • Try removing where clause see if you get any result. – Akhil Gupta Jan 29 at 4:27
2

Here is a full query on how to get the data you need (sku, name, description) for all the products for a specific store view (id 1) and you can do it the same for other attribute as well

    SELECT 
    `e`.`sku`, 
    IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`,
    IF(at_description.value_id > 0, at_description.value, at_description_default.value) AS `description`

FROM 
   `catalog_product_entity` AS `e` 
    INNER JOIN 
         `catalog_product_entity_varchar` AS `at_name_default` 
               ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND 
                  `at_name_default`.`store_id` = 0 
    LEFT JOIN 
          `catalog_product_entity_varchar` AS `at_name` 
               ON (`at_name`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_name`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'name' AND et.entity_type_code = 'catalog_product')) AND 
                  (`at_name`.`store_id` = 1) 
    INNER JOIN 
         `catalog_product_entity_text` AS `at_description_default` 
               ON (`at_description_default`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_description_default`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'description' AND et.entity_type_code = 'catalog_product')) AND 
                  `at_description_default`.`store_id` = 0 
    LEFT JOIN 
          `catalog_product_entity_text` AS `at_description` 
               ON (`at_description`.`entity_id` = `e`.`entity_id`) AND 
                  (`at_description`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'description' AND et.entity_type_code = 'catalog_product')) AND 
                  (`at_description`.`store_id` = 1)

If you want it for an other store view, just replace the value 1 with your desired id at the following lines

If you want get the products from code

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect(array('sku', 'name', 'description'));
foreach ($collection as $item) {
$sku = $item->getSku();
$name = $item->getName();
$description = $item->getDescription(); 
//do something with $sku, $name & $description
}

I hope this will help

  • Thank you for replying! Can you please explain me how your query works? – programming_amazing Jan 29 at 22:53
  • @programming_amazing You can get sku attribute from catalog product entity table directly but for other attributes you have to check the attribute type then join particular table like for varchar catalog_product_entity_varchar and for int there is catalog_product_entity_int so in my query name is varchar and description is text field plus I get the name and description attribute value store wise like store 0 and store 1. I prefer store 1 if store 1 has value then it shows store 1 value otherwise store 0 you can see this on my select. I hope it gives more understanding – Muhammad Hasham Jan 30 at 5:20
  • Yeah, i know that.. but it is still not workig – programming_amazing Jan 31 at 12:44
  • @programming_amazing it is working. I tested this query if you want I can share the screenshot – Muhammad Hasham Jan 31 at 12:45
  • Maybe they changed something in the database, any relations? – programming_amazing Jan 31 at 12:47

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.