SQL Query to Get Price by SKU in Magento 1.7

magento-1.7sql

<?php
$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$query = 'SELECT * FROM ' . $resource->getTableName('catalog/product');
$results = $readConnection->fetchAll($query);
 var_dump($results);

i need sql query for get product price by sql

Best Answer

Magento is saved Product price at catalog_product_index_price and Here magento is saved customer group wise products price fields.So you need to inner join with this table.But is give you multiple rows for a products.By default it take one row when customer is not logged-in.

  1. Product ID
  2. Customer Group ID
  3. Website ID
  4. Tax Class ID
  5. Price Final
  6. Price Min
  7. Price Max
  8. Price Tier
  9. Price Group price

See table:

enter image description here

Here the Query:

    SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, 

`price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, 

LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS 
`minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, 
`price_index`.`tier_price` FROM `catalog_product_entity` AS `e` INNER JOIN 
`catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND
 price_index.website_id = '0' AND price_index.customer_group_id = 0

Here i have put customer group id and its 0 for getting on a records product price

Related Topic