How to pull all the product id, skus, product names, description in magento using only mysql?

Question

How i will pull all the prduct ir, skus , product name (titles) and desxription using mysql from Magento database? I used following query and got all the attributes except product names.

SELECT e.entity_id, e.sku, eav.value AS 'description'
FROM catalog_product_entity e
JOIN catalog_product_entity_text eav
  ON e.entity_id = eav.entity_id
JOIN eav_attribute ea
  ON eav.attribute_id = ea.attribute_id
WHERE ea.attribute_code = 'description'

Answer 1

The title can be different from one store view to an other. Same goes for the description. Also, some store views can use the default values set in the backend.

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).

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

(`at_name`.`store_id` = 1) 

and

(`at_description`.`store_id` = 1)

I don't know why you need this in an sql format. This is a strange and a big error source. You can easily get it through 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
}

Answer 2

Here is another query to show entity_id, product_name, sku

SELECT
    catalog_product_entity_varchar.entity_id,
    catalog_product_entity_varchar.`value` AS product_name,
    catalog_product_entity.sku
FROM
    catalog_product_entity_varchar
INNER JOIN catalog_product_entity ON catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id
WHERE
    catalog_product_entity_varchar.entity_type_id = (
        SELECT
            entity_type_id
        FROM
            eav_entity_type
        WHERE
            entity_type_code = 'catalog_product'
    )
AND attribute_id = (
    SELECT
        attribute_id
    FROM
        eav_attribute
    WHERE
        attribute_code = 'name'
    AND entity_type_id = (
        SELECT
            entity_type_id
        FROM
            eav_entity_type
        WHERE
            entity_type_code = 'catalog_product'
    )
)

Answer 3

For fetching the product name please try

$sql = "SELECT `value`
FROM catalog_product_entity_varchar
WHERE entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product') 
AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))";

$results = $readConnection->fetchAll($sql);