I am currently working on a website that needs to get the category name, product name, average, minimum, and maximum price of an item from an existing magento database. I am not using magento on this project and I just need to fetch some data for processing.

This is what I have done so far. This query only produces the sku and the product name of an item.

SELECT `e`.`sku`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`
                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) 

                  LIMIT 50;");

Source: https://magento.stackexchange.com/questions/259564/get-all-product-from-magento-sql-query

0 Answers