How to pull all the product id, skus, product names, description in magento using only mysql?
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
}
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'
)
)
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);