3
<?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

0

2 Answers 2

5

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

1

Here is the SQL, of course you can change it with NESTED SELECTS:

$query = "SELECT * FROM " . $resource->getTableName('catalog/product') . " p
INNER JOIN " . $resource->getTableName('catalog_product_entity_decimal') . " ped ON p.entity_id = ped.entity_id
INNER JOIN " . $resource->getTableName('eav/attribute') . " ea ON ea.attribute_id = ped.attribute_id
INNER JOIN " . $resource->getTableName('eav/entity_type') . " eet ON eet.entity_type_id = ped.entity_type_id
WHERE p.sku = 'test' AND ea.attribute_code = 'price' AND eet.entity_type_code = 'catalog_product'";

You have to change 'test' with your own SKU. To get only the price here is the code:

$result = $readConnection->fetchRow($query);
echo $result['value'];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.