如何仅使用 mysql 在 magento 中提取所有产品 id、skus、产品名称、描述？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23337745/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pull all the product id, skus, product names, description in magento using only mysql?
提问by user3580780
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.
我将如何使用 Magento 数据库中的 mysql 提取所有产品 ir、skus、产品名称（标题）和 desxription？我使用了以下查询并获得了除产品名称之外的所有属性。
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'
回答by Marius
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).
这是有关如何获取特定商店视图 (id 1) 的所有产品所需数据（sku、名称、描述）的完整查询。
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 1with your desired id at the following lines
如果您希望将其用于其他商店视图，只需1在以下几行将值替换为您想要的 id
(`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:
我不知道你为什么需要 sql 格式的这个。这是一个奇怪而大的错误源。您可以通过代码轻松获取：
$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
}
回答by Umair
Here is another query to show entity_id, product_name, sku
这是要显示的另一个查询 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'
)
)
回答by VishalPandita
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);
回答by Sonpal Singh Sengar
This query contain Product name, image, price, quantity, description
此查询包含 Product name、image、price、quantity、description
SET @etype = (SELECT
entity_type_id
FROM
eav_entity_type
WHERE
entity_type_code = 'catalog_product');
Product nameattribute ID
产品name属性 ID
SET @name = (SELECT
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'name'
AND entity_type_id = @etype);
Product imageattribute ID
产品image属性 ID
SET @image = (SELECT
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'image'
AND entity_type_id = @etype);
Product small image attribute ID
商品小图属性 ID
SET @smallimage = (SELECT
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'small_image'
AND entity_type_id = @etype);
Product priceattribute ID
产品price属性 ID
SET @price = (SELECT
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'price'
AND entity_type_id = @etype);
Product descriptionattribute ID
产品description属性 ID
SET @description = (SELECT
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'description'
AND entity_type_id = @etype);
-- Admin store ID -- SET @store = 0;
-- 管理商店 ID -- SET @store = 0;
SELECT
e.entity_id AS 'id',
e.sku,
v1.value AS 'name',
v2.value AS 'image',
s2.value AS 'small_image',
si.qty AS 'stock qty',
d1.value AS 'price',
s1.value AS 'description'
FROM
catalog_product_entity e
LEFT JOIN
cataloginventory_stock_item si ON e.entity_id = si.product_id
LEFT JOIN
catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id
AND v1.store_id IN (0,1,2)
AND v1.attribute_id = @name
LEFT JOIN
catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id
AND v2.store_id IN (0,1,2)
AND v2.attribute_id = @image
LEFT JOIN
catalog_product_entity_varchar s2 ON e.`entity_id` = s2.entity_id
AND s2.store_id IN (0,1,2)
AND s2.`attribute_id` = @smallimage
LEFT JOIN
catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id
AND d1.store_id IN (0,1,2)
AND d1.attribute_id = @price
LEFT JOIN
catalog_product_entity_text s1 ON e.entity_id = s1.entity_id
AND s1.store_id IN (0,1,2)
AND s1.attribute_id = @description ;
回答by aaditya mundhalia
Below is the simple way.
下面是简单的方法。
select
sku.entity_id, sku.sku, #get sku and entity
productName.value, #get name
description.value #get description
from
catalog_product_entity as sku,
catalog_product_entity_varchar as productName,
catalog_product_entity_text as description
where
productName.attribute_id = 73
and
sku.entity_id = productName.entity_id
and
description.attribute_id = 75
and
sku.entity_id = description.entity_id;
