Magento 2: How to get product name by sql query using product ID

Question

I want to access the product names by query database using product ID. How can I achieve it?

Answer 1

Magento uses an EAV table structure for products. Names are stored in catalog_product_entity_varchar. If you have the product ID X, you can query this table directly:

SELECT entity_id, value, store_id FROM catalog_product_entity_varchar
  WHERE entity_id = X AND attribute_id = (
    SELECT attribute_id FROM eav_attribute
    WHERE entity_type_id=4 AND attribute_code='name'
  )

The subquery determines the attribute id for "name", entity_type_id 4 is always the type ID for products.

Note that in a multistore setup you might get multiple values per product. Pay attention to the "store_id" column. "0" stands for the default value.

Answer 2

If your product's id is 1234:

select value from catalog_product_entity_varchar left join eav_attribute on
    eav_attribute.attribute_id = catalog_product_entity_varchar.attribute_id
where
    eav_attribute.attribute_code='name' and 
    catalog_product_entity_varchar.entity_id=1234

Answer 3

Use below query to find name from product_id

SELECT value FROM catalog_product_entity_varchar WHERE entity_id=2 AND (attribute_id IN(SELECT attribute_id FROM eav_attribute WHERE attribute_code = "name" AND entity_type_id = 4))

Change entity_id=2 as per your product id

Answer 4

$_connection = $this->mc_get_obj(\Magento\Framework\App\ResourceConnection::class);//get class

$db_connection = $_connection->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);//connection

$category = $db_connection->fetchAll('select * from '.'`catalog_product_entity_varchar` '.'left join '.'`eav_attribute` '.'on '.
'`eav_attribute`.'.'`attribute_id`='.'`catalog_product_entity_varchar`.'.'`attribute_id` '.'where '.'`eav_attribute`.'.'`attribute_code`='.'"name" '.'and '. '`catalog_product_entity_varchar`.'.'`entity_id`='.$vi['product_id']);

Answer 5

Or you can try this also,for single record, X is your product_id you have to replace it by your own product id.

SELECT DISTINCT value FROM catalog_product_entity_varchar WHERE entity_id=X AND (attribute_id IN(SELECT attribute_id FROM eav_attribute WHERE  attribute_code = "name" AND entity_type_id = 4))