Magento – Get product attribute values(name, price, status etc.) from all the stores via SQL

eavmagento-1.9magento-2.1sql

Trying to fetch multiple attribute values from multi-store in Magento via SQL. But it seems not to be an easy task.
Though, I can fetch Website wise attribute value via:

SELECT 
        e.entity_id,
        cw.website_id,
        COALESCE(store_name.value, default_name.value) AS 'name'
FROM `catalog_product_entity` AS `e`
 CROSS JOIN `store_website` AS `cw`
 INNER JOIN `catalog_product_index_website` AS `cwd` ON cw.website_id = cwd.website_id
 INNER JOIN `store_group` AS `csg` ON csg.website_id = cw.website_id AND cw.default_group_id = csg.group_id
 INNER JOIN `store` AS `cs` ON csg.default_store_id = cs.store_id AND cs.store_id != 0
 INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = e.entity_id AND pw.website_id = cw.website_id
LEFT JOIN catalog_product_entity_varchar AS default_name ON default_name.entity_id = e.entity_id AND default_name.store_id = 0 AND default_name.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_varchar AS store_name ON store_name.entity_id = e.entity_id AND store_name.store_id = `cs`.store_id AND store_name.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)

as we have catalog_product_website table which relates between website_id & product_id. But for store<->product relationships, we don't have any special table.

Fetching a single attribute value as per store is fairly easier:

SELECT 
main_tbl.entity_id, 
store_tbl.store_id,
name_tbl.value AS 'name' 
FROM catalog_product_entity main_tbl
LEFT JOIN catalog_product_entity_decimal price_tbl ON price_tbl.entity_id = main_tbl.entity_id AND name.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price' AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_varchar name ON name.entity_id = main.entity_id AND name.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
INNER JOIN store cs ON cs.store_id = cpev.store_id
ORDER BY cpe.entity_id, cs.store_id ASC

But fetching values for multiple attributes at the same time via SQL is trickier (as some attribute have global scope while other having store view or website scope):

+-----------+----------+------+-------+--------+
| entity_id | store_id | name | price | status |
+-----------+----------+------+-------+--------+

Best Answer

Create a new file with below contents and run in your root.This is for magento 1.

<?php
require_once 'app/Mage.php';
umask(0);
Mage::app('default');

$collection = Mage::getModel('catalog/product')->getCollection();

echo '<table border="1"> ';
echo '<tr>';
echo "<th>entity_id <th />";
echo "<th>store_id <th />";
echo "<th>name<th />";
echo "<th>sku <th />";
echo "<th>price<th />";
echo "<th>status<th />";
echo '</tr>';

foreach ($collection as $product_all) {
  $sku = $product_all['sku'];
  $product_id =  $product_all['entity_id'];
  // call product model and create product object
  $product    = Mage::getModel('catalog/product')->load($product_id);

  $pk_sku = $product['sku'];
  $pk_name = $product['name'];
  $store_id = $product->getStoreIds();
  $pk_price = $product['price'];
  $status = $product->getStatus();

  echo '<tr>';
  echo "<td>".$product_id."<td />";
  echo "<td>".$store_id[0]."<td />";
  echo "<td>".$pk_name."<td />";
  echo "<td>".$pk_sku."<td />";
  echo "<td>".$pk_price."<td />";
  echo "<td>".$status."<td />";
  echo '</tr>'; 
 }
echo '</table>';?>
Related Topic