Comment tirer tous les id de produit, skus, noms de produits, description dans magento en utilisant seulement mysql?

Comment je vais me tirer tous les prduct ir, sku , nom du produit (titres) et desxription à l'aide de mysql à partir de Magento base de données? J'ai utilisé la requête suivante et a tous les attributs à l'exception des noms de produits.

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'

source d'informationauteur user3580780

  1. 28

    Le titre peut être différent d'un magasin à un autre. En va de même pour la description. En outre, certains magasins peuvent utiliser les valeurs par défaut définies dans le backend.

    Voici une requête complète sur la façon d'obtenir les données dont vous avez besoin (référence, nom, description) pour tous les produits pour un magasin de vue (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)

    Si vous le souhaitez pour un autre magasin, il suffit de remplacer la valeur 1 avec votre id désirée sur les lignes suivantes

    (`at_name`.`store_id` = 1)

    et 

    (`at_description`.`store_id` = 1)

    Je ne sais pas pourquoi vous avez besoin dans un format sql. C'est une étrange et une grande source d'erreur. Vous pouvez facilement obtenir par le biais d'un 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
}
  2. 10

    Voici une autre requête pour afficher 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'
)
)
  3. 4

    Pour récupérer le nom du produit s'il vous plaît essayer 

    $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);
  4. 1

    Ci-dessous est la manière la plus simple.

    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;
  5. 0

    Cette requête contient des Produits nameimagepricequantitydescription

    SET @etype = (SELECT 
entity_type_id
FROM
eav_entity_type
WHERE
entity_type_code = 'catalog_product');

    Produit name attribut ID

    SET @name  = (SELECT 
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'name'
AND entity_type_id = @etype);

    Produit image attribut ID

    SET @image  = (SELECT 
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'image'
AND entity_type_id = @etype);

    Produit petite image de l'attribut ID de l'

    SET @smallimage = (SELECT 
attribute_id
FROM 
eav_attribute
WHERE 
attribute_code = 'small_image'
AND entity_type_id = @etype);

    Produit price attribut ID

    SET @price  = (SELECT 
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'price'
AND entity_type_id = @etype);

    Produit description attribut ID

    SET @description  = (SELECT 
attribute_id
FROM
eav_attribute
WHERE
attribute_code = 'description'
AND entity_type_id = @etype);

    -- Admin ID magasin
    -- 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 ;