2

I am using Magento ver. 2.4.2

Is their any way we can get product attributes by id using mysql query ?

I also need some custom attributes for product in same query

THE BELOW SQL QUERY WORKS BUT GET ONLY NAME ATTRIBUTE I NEED OTHER ATTRIBUTES ALSO

select
 nametable.value as product_name,
 parent.type_id as product_type, parent.sku as sku,
 child.type_id as child_product_type, child.sku child_sku
from catalog_product_entity as parent
join catalog_product_relation as link
    on link.parent_id = parent.entity_id
join catalog_product_entity as child
    on child.entity_id = link.child_id
join catalog_product_entity_varchar as nametable ON nametable.row_id = parent.entity_id
AND nametable.store_id = 0
AND nametable.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'))
where parent.sku = 'P22756' limit 1
7
  • any thoughts on this ?
    – devhs
    Jul 14, 2021 at 11:01
  • do you want to get the product name based on id using MySQL query? Jul 14, 2021 at 11:10
  • Yes name & few custom attributes, that's it
    – devhs
    Jul 14, 2021 at 11:11
  • not getting how to fetch
    – devhs
    Jul 14, 2021 at 11:19
  • any thoughts on this ?
    – devhs
    Jul 14, 2021 at 12:25

1 Answer 1

0

hey Try out below Query.

<?php 
$tableName = "catalog_product_entity";
$productId = 13;
$storeId = 0;

$sql = "SELECT ce.sku, ea.attribute_code,
                    CASE ea.backend_type 
                        WHEN 'varchar' THEN ce_varchar.value 
                        WHEN 'int' THEN ce_int.value 
                        WHEN 'text' THEN ce_text.value 
                        WHEN 'decimal' THEN ce_decimal.value 
                        WHEN 'datetime' THEN ce_datetime.value
                    END AS VALUE
                FROM
                    (
                        SELECT cpe.sku, eas.entity_type_id, cpe.entity_id
                        FROM ". $tableName ." AS cpe, eav_attribute_set AS eas
                        WHERE cpe.entity_id = ". $productId ." AND cpe.attribute_set_id = eas.attribute_set_id
                    ) AS ce
                LEFT JOIN eav_attribute AS ea
                    ON
                    ce.entity_type_id = ea.entity_type_id
                LEFT JOIN catalog_product_entity_varchar AS ce_varchar
                ON
                    ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar' AND ce_varchar.store_id = $storeId
                LEFT JOIN catalog_product_entity_int AS ce_int
                ON
                    ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int' AND ce_int.store_id = $storeId
                LEFT JOIN catalog_product_entity_text AS ce_text
                ON
                    ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text' AND ce_text.store_id = $storeId
                LEFT JOIN catalog_product_entity_decimal AS ce_decimal
                ON
                    ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal' AND ce_decimal.store_id = $storeId
                LEFT JOIN catalog_product_entity_datetime AS ce_datetime
                ON
                    ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime' AND ce_datetime.store_id = $storeId;";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.