如何仅使用mysql在magento中提取所有产品ID，Skus，产品名称，说明? [英] How to pull all the product id, skus, product names, description in magento using only mysql?

问题描述

我将如何使用Magento数据库中的mysql提取所有产品ir，skus，产品名称(标题)和desxription?我使用以下查询并获得了除产品名称以外的所有属性.

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'

推荐答案

一个商店视图的标题可能与另一个商店视图的标题不同.相同的描述.另外，某些商店视图可以使用后端中设置的默认值.

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.

这里是有关如何为特定商店视图(id 1)的所有产品获取所需数据(sku，名称，描述)的完整查询.

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) 

如果您希望将其用于其他商店视图，只需在以下几行中将值1替换为所需的ID

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) 

和

(`at_description`.`store_id` = 1)

我不知道为什么您需要sql格式的文件.这是一个奇怪且大的错误源.您可以通过代码轻松获得它:

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
}

这篇关于如何仅使用mysql在magento中提取所有产品ID，Skus，产品名称，说明?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！