Bestseller/Latest/search to show only products with images

[patej]

Hi everyone,
Not all products in my webshop have images (and not all ever will, that's another story) so I would need to modify the Latest and Bestseller modules as well search results to display only those products that have images. I found the function that gets the Bestseller products (getBestSellerProducts() in catalog/model/catalog/product.php), but I don't know where and what should I edit to get only products with images:

Code: Select all

$query = $this->db->query("SELECT op.product_id, COUNT(*) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE o.order_status_id > '0' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);

This is for Bestseller module, but once I get this working I think the Latest and Search would be quite easily modified..? I'm running 1.5.3.1.

Any help appreciated,
--patrik

[MarketInSG]

You don't need to edit the model file. Edit each template eg. I'm using catalog/view/theme/default/template/module/latest.tpl

Find

Code: Select all

<div>
        <?php if ($product['thumb']) { ?>
        <div class="image"><a href="<?php echo $product['href']; ?>"><img src="<?php echo $product['thumb']; ?>" alt="<?php echo $product['name']; ?>" /></a></div>
        <?php } ?>
        <div class="name"><a href="<?php echo $product['href']; ?>"><?php echo $product['name']; ?></a></div>
        <?php if ($product['price']) { ?>
        <div class="price">
          <?php if (!$product['special']) { ?>
          <?php echo $product['price']; ?>
          <?php } else { ?>
          <span class="price-old"><?php echo $product['price']; ?></span> <span class="price-new"><?php echo $product['special']; ?></span>
          <?php } ?>
        </div>
        <?php } ?>
        <?php if ($product['rating']) { ?>
        <div class="rating"><img src="catalog/view/theme/default/image/stars-<?php echo $product['rating']; ?>.png" alt="<?php echo $product['reviews']; ?>" /></div>
        <?php } ?>
        <div class="cart"><input type="button" value="<?php echo $button_cart; ?>" onclick="addToCart('<?php echo $product['product_id']; ?>');" class="button" /></div>
      </div>


And change it to

Code: Select all

<?php if ($product['thumb']) { ?>
<div>
        <div class="image"><a href="<?php echo $product['href']; ?>"><img src="<?php echo $product['thumb']; ?>" alt="<?php echo $product['name']; ?>" /></a></div>
        <div class="name"><a href="<?php echo $product['href']; ?>"><?php echo $product['name']; ?></a></div>
        <?php if ($product['price']) { ?>
        <div class="price">
          <?php if (!$product['special']) { ?>
          <?php echo $product['price']; ?>
          <?php } else { ?>
          <span class="price-old"><?php echo $product['price']; ?></span> <span class="price-new"><?php echo $product['special']; ?></span>
          <?php } ?>
        </div>
        <?php } ?>
        <?php if ($product['rating']) { ?>
        <div class="rating"><img src="catalog/view/theme/default/image/stars-<?php echo $product['rating']; ?>.png" alt="<?php echo $product['reviews']; ?>" /></div>
        <?php } ?>
        <div class="cart"><input type="button" value="<?php echo $button_cart; ?>" onclick="addToCart('<?php echo $product['product_id']; ?>');" class="button" /></div>
      </div>
<?php } ?>


[patej]

Thanks for your reply, but that's not really what I meant. I tried that at first, but with that change I see only e.g. 4/5 products in the Bestseller block. I need to retreive the Bestsellers of those products that have image added, not only show those products of the Bestsellers that have images...

It's a bit hard to explain, but here goes another try:
- I have 100 products total, but only 50 of them have images
- There are two possible "bestseller lists": one is the bestsellers top 5 of the 100 products, other one the bestsellers of the 50 products with images
- I don't want to show those 3 images of the Bestsellers of the 100 products, but all 5 images of the Bestsellers of the 50.

Hopefully that made my point clearer. That is why I went to edit the model file.
--patrik

[MarketInSG]

Change your limit values in your module.

[patej]

Change your limit values in your module.

Not really... Limit value changes the number of products the module retrieves from the database. It is 4 now and I want it to be 4 in the future, too, but I want the module to show the 4 best selling products from those products that have an image. Does that help explain my problem? Like I said at first, I would like to know how to change the DB query so that it limits the results to those products that have images. Everything else should stay as it is.

--patrik

[MarketInSG]

Code: Select all


$query = $this->db->query("SELECT op.product_id, COUNT(*) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE o.order_status_id > '0' AND p.image != '' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);


Try this.

[patej]

Thanks, but no... After this (and deleting its cache in system/cache) the whole module disappeared without any error (none in the error log either).

[patej]

GOT IT! I realised to look into the database itself and noticed that all products with no image have "data/" as image's value, so changing your solution to "p.image != 'data/'" made it work!

Thanks a lot for pointing me to the right direction!
--patrik

[MarketInSG]

all products without images should have the column blank. Not sure why your database is wrongly defined...

[patej]

all products without images should have the column blank. Not sure why your database is wrongly defined...

Ah, now I got it.. It's because during import via Total Import Pro I add "data/" in front of the each product image's name in the image path column because I don't want to have it included in the spreadsheet I import from. Didn't realize it will of course add it to the empty cells, too. I got to change that then so that I'll add the data/ before importing.

[oskar321]

Thanks MarketInSG... It works perfect for me