Magento 2 : Get product name using mysql query

Question

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

• devhs: any thoughts on this ?
• Bilal Usean: do you want to get the product name based on id using MySQL query?
• devhs: Yes name & few custom attributes, that's it
• devhs: not getting how to fetch
• devhs: any thoughts on this ?

Answer 1

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;";