3

I need to show best selling product on product view page. I tried with the code:

$storeId = Mage::app()->getStore()->getId();
            $_productCollection = Mage::getResourceModel('reports/product_collection')
                    ->addOrderedQty()
                    ->addAttributeToSelect('*')
                    ->setStoreId($storeId)
                    ->addStoreFilter($storeId)
                    ->setOrder('ordered_qty', 'desc');
            Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($_productCollection);
            Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($_productCollection);
            $_productCollection->setPageSize(16)->setCurPage(1);
            $this->setProductCollection($_productCollection);

But this give error

a:5:{i:0;s:813:"SELECT SUM(order_items.qty_ordered) AS `ordered_qty`, `order_items`.`name` AS `order_items_name`, `order_items`.`product_id` AS `entity_id`, `e`.`entity_type_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`sku`, `e`.`has_options`, `e`.`required_options`, `e`.`created_at`, `e`.`updated_at` FROM `sales_flat_order_item` AS `order_items`
 INNER JOIN `sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state  'canceled'
 LEFT JOIN `catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped', 'configurable', 'bundle')) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4 WHERE (parent_item_id IS NULL) AND (((e.visibility = '4') OR (e.visibility = '2'))) GROUP BY `order_items`.`product_id` HAVING (SUM(order_items.qty_ordered) > 0) ORDER BY `ordered_qty` desc

";i:1;s:4910:"#0 /public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

please help

1
  • This same code works fine on local (desktop), but not on server (live). Please suggest, why this happened?
    – Mace Long
    Commented May 10, 2016 at 9:16

4 Answers 4

2

you can try the following solutions.

1) try extension.

https://www.magentocommerce.com/magento-connect/best-seller-products-extension.html

2) create your modules

http://inchoo.net/magento/bestseller-products-in-magento/

https://blog.amasty.com/how-to-display-bestselling-products-in-magento-source-code/

http://way2discuss.blogspot.in/2012/05/magento-best-selling-product-lists-on.html

http://www.magikcommerce.com/blog/how-to-display-best-selling-products-on-magento-store-home-page/

https://vasavaa.wordpress.com/2014/01/29/magento-best-selling-or-top-selling-product/

2
  • i have tried all this, but nothing will work. As I mentioned above code is working in local but not on live website.
    – Mace Long
    Commented May 10, 2016 at 9:36
  • Any idea about getting Magento 1.9 best selling products based on category-wise? @Ashish Madankar M2 Professiona
    – zus
    Commented Dec 5, 2019 at 9:01
1

I have achieved this through creating a Bestseller Product Block.

Create a Bestseller.php file and put it here

app/code/local/Mage/Catalog/Block/Product/Bestseller.php

Now add this code to this file

class Mage_Catalog_Block_Product_Bestseller extends Mage_Catalog_Block_Product_Abstract {

public function __construct() {

 parent::__construct();
 $storeId = Mage::app()->getStore()->getId();
 $products = Mage::getResourceModel('reports/product_collection')
 ->addOrderedQty()
 ->addAttributeToSelect('id')
 ->addAttributeToSelect(array('name', 'price', 'small_image'))
 ->setStoreId($storeId)
 ->addStoreFilter($storeId)
 ->setOrder('ordered_qty', 'desc'); // most best sellers on top
  Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($products);
  Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($products);
  $products->setPageSize(3)->setCurPage(1);
  $this->setProductCollection($products);
   }
 }

Create bestseller.phtml file and put it here

app/design/frontend/yourtheme/template/catalog/product/bestseller.phtml

<?php

 $_products = $this->getProductCollection();
 Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($_products); 

// for qty stock check for print Query

//echo $_products->getSelect();
//print $_products->getSize();

if ($_products->getSize()): : ?>
<div>
<?php $i=0; foreach ($_products->getItems() as $_product): ?>
 <?php if ($i>5): continue; endif; ?>
<div>
<div>
<a href="<?php echo $_product->getProductUrl() ?>" title="<?php echo $this->htmlEscape($_product->getName()) ?>">
<img src="<?php echo $this->helper('catalog/image')->init($_product, 'small_image')->resize(65,65); ?>" alt="<?php echo $this->htmlEscape($_product->getName()) ?>"/>
</a>
<?php echo $_product->getDescription(); 
//also getShortDescription ?>
</div>
<div>
<p><a href="<?php echo $_product->getProductUrl() ?>" title="<?php echo $this->htmlEscape($_product->getName()) ?>)"><?php echo $_product->getName() ?>      </a></p>
<?php //echo $this->helper('review/product')->getSummaryHtml($_product, 'short') //product review link ?>
<?php echo $this->getReviewsSummaryHtml($_product, false, true)?>
<?php //echo $this->helper('catalog/product')->getPriceHtml($_product) ?>
<?php echo $this->getPriceHtml($_product) ?>
<?php echo $_product->getProductId(); ?>
<?php if($_product->getevent_date()) {echo $_product->getevent_date();} ?>
</div>
</div>
<?php $i++; endforeach; ?>
<?php for($i;$i%5!=0;$i++): ?>
<?php endfor ?>
</div>
<?php endif; ?>

Now to show the bestselling products you can use block statement. Just add following, where you want to show bestselling products.

{{block type="catalog/product_bestseller" template="catalog/product/bestseller.phtml"}}

Hope that will help

1
  • It should work. but it didn't work, gives error Fatal error: Call to a member function joinField() on a non-object in \app\code\core\Mage\CatalogInventory\Model\Resource\Stock.php on line 197
    – Mace Long
    Commented May 10, 2016 at 8:56
0

Use the below Query to display the best selling products in Homepage:

<?php $products = Mage::getResourceModel('reports/product_collection')
            //->addAttributeToSelect('*')
            ->addAttributeToSelect(array('name','price','small_image'))
            ->addOrderedQty()
            ->setStoreId($storeId)
            ->addStoreFilter($storeId)
            ->setOrder('ordered_qty','desc'); //best sellers on top
?>
0

Create a block that show the top sold products ( can be set through cms or .xml)

Create the following template file (/app/design/frontend/default/YourTheme/template/custom/bestseller.phtml):
<?php

$totalPerPage = ($this->show_total) ? $this->show_total : 6;
$counter = 1;
$visibility = array(
                      Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH,
                      Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG
                  );

$storeId = Mage::app()->getStore()->getId();
$_productCollection = Mage::getResourceModel('reports/product_collection')
                              ->addAttributeToSelect('*')
                              ->addOrderedQty()
                              ->addAttributeToFilter('visibility', $visibility)
                              ->setOrder('ordered_qty', 'desc');
?>

<div class="content-box-header">Bestsellers</div>
<div class="content-box">
<?php foreach($_productCollection as $product): ?>


<?php if($counter <= $totalPerPage): ?>

<?php $productUrl =  $product->getProductUrl() ?>
  <a href="<?php echo $productUrl ?>" title="View <?php echo $product->name ?>">
    <h4><?php echo $product->name ?></h4>
  </a>
  <small><?php echo $this->__('Total sold quantities') ?>: <?php echo (int)$product->ordered_qty ?></small><br />

  <a href="<?php echo $productUrl ?>" title="View <?php echo $product->name ?>">
  <img src="<?php echo $this->helper('catalog/image')->init($product, 'image')->resize(120); ?>" alt="Product image"  />
  </a> <br />
  <p><?php echo $product->short_description ?></p>

<?php endif; $counter++; ?>
<?php endforeach; ?>
</div>
1
  • 1
    All fails but this one finally work ---- Commented Sep 10, 2017 at 19:08

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.