2

I'd like to directly query sku, special price, special price from date, special price to date from database.

We are offering special sale every week with different items. Sale items are supposed to have from date and to date. Unfortunately many of them are missed to date. We haven't cleared old/previous sale information for a long time. So I don't know how many items are on sale now.

Please advise me.

  • Which is your Magento version? – Khoa TruongDinh Aug 27 '16 at 1:46
  • It is "Magento ver. 1.14.0.1 Ent." Thank you. – David Aug 29 '16 at 15:48
2

You may use following code

$collection=Mage::getResourceModel('catalog/product_collection')
            ->addAttributeToSelect('name')
            ->addAttributeToSelect('sku')
            ->addAttributeToSelect('special_price')
            ->addAttributeToSelect('price');
$collection->addAttributeToFilter('special_from_date',array(array('from' => Mage::getModel('core/date')->gmtDate()),
                                 array('special_from_date', 'null'=>true)));
$collection->addAttributeToFilter('special_to_date',array(array('to' => Mage::getModel('core/date')->gmtDate()),
                                 array('special_to_date', 'null'=>true)));
  • Thank you, Any chance that I can make the same result by sql query? – David Aug 29 '16 at 15:49
  • you may get sql query like this echo $collection->getSelect(); – sanjeev Aug 29 '16 at 18:25
1

Use below code :

$_productCollection = Mage::getModel('catalog/product')->getCollection();
$_productCollection->addAttributeToSelect(array(
                                   'sku',
                                   'name'
                   ))
                   ->addFieldToFilter('visibility', array(
                               Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH,
                               Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG
                   )) 
                   ->addFinalPrice()                       
                   ->getSelect()
                   ->where('price_index.final_price < price_index.price');

with the above collection, you will get list of products which have sale. You can also run this code from Magento root testing file so you get idea which products have sale or other information.

For information print, you need to code :

<?php foreach ($_productCollection as $_product): ?>
[Display Info What You want]
<?php endforeach; ?>
  • Thank you, I will try now. Do you have any idea that I get get same result by SQL query? – David Aug 29 '16 at 15:47
  • If you get proper result using above code then you can create SQL query as well. – user41152 Sep 3 '16 at 4:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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