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?

• Arshad Muhammad: u want sql query or Magento default way
• Nitin Pawar: i want sql query . I know the default way.
• Chirag Prajapati: why do you want using SQL query?
• Nitin Pawar: I want to access the product names outside magento without using api.

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

• Chirag Prajapati: there is no row_id column, use entity_id instead of row_id.
• Phoenix128_RiccardoT: In Magento 2 you have row_id
• Chirag Prajapati: what? can you share screenshot of your database table?
• nikola99: Magento EE has row_id.

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']);

• jerry xu: ------ sql: 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=2082
• jerry xu: public function mc_get_obj($class) { return \Magento\Framework\App\ObjectManager::getInstance()->get($class); }

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))