Show cheapest price for each products group dynamically on home template

[gwinger]

Goal is, to fetch and assign all productgroups names and taglines to a smarty var.

This can be done easily as hook.

 

use Illuminate\Database\Capsule\Manager as Capsule;


add_hook('ClientAreaPage', 1, function($productGroups)
{
   $pdo = Capsule::connection()->getPdo();
   try {
       $statement = $pdo->prepare(
           'SELECT * FROM `tblproductgroups` WHERE `hidden` = 0; '
       );

       $statement->execute();

       $result = $statement->fetchAll();

       return array(
           'productGroups' => $result,
       );

   } catch (\Exception $e) {
       echo "Ooooooooops {$e->getMessage()}";
   }


});

 

 

We receive a assigned smarty array with the results on homepage.tpl to feed a slider.

The slider lines up all productgroups including the link etc.

This is quite nice and makes things easy.

 

template code is like this:

 

{foreach key=num item=productgroup from=$productGroups}
         <div class="slide-item">
	    <!-- Slider Item-->
	    <div class="groups-name">
	         <h2>{$productgroup.name}</h2>
		 <div class="groups-tagline">
	              {$productgroup.tagline}
		  </div>
		  <div class="price">
		       Starting at<span>$10.95</span>/mo
		  </div>
		  <a href="cart.php?gid={$productgroup.id}" class="btn btn-lg btn-primary">{$LANG.show}</a>
	    </div>
  </div>
{/foreach}

 

 

What I try to figure out is a query to fetch the right price for: Starting at<span>$10.95</span>/mo

 

But this seems to be not easy. I have tested some query techniques joining the tables:

 

tblproductgroups, tblproducts and tblpricing

 

It is problematic. Like an example shows:

 

SELECT  prods.*, price.*
FROM tblproducts prods, tblpricing price

             WHERE price.relid = prods.id AND (price.monthly > 0 OR price.quarterly > 0 OR price.semiannually >0 OR price.annually >0 OR price.biennially >0 OR price.triennially >0)

               group by price.monthly, price.quarterly, price.semiannually, price.annually, price.biennially, price.triennially

 

 

 

If you test this query on your database, you will see, that this results unexpected in chaos.

In my case I found out, that the relid in tbl pricing is not unique.

configoptions can have the same relid which is also set for another product.

 

So this part of the database modell is not correctly normalised.

You can not use a relation to fetch the price for the product distinctly.

 

But I hope anybody has more overview in WHMCS database model and can give us a hint or a solution.

 

On success, it would be a great marketing feature everybody can use on landing pages, homepage and sliders, bannerrotators etc

 

Off course this could be featured, too. If we can check, monthly, annualy etc to show it like this, too: starting at {$price} / {$period}

 

So if anybody is interested in the idea, feel free to bring uns in position

 

Regards, Guido

[ServWise.com]

Hi, did you work this out I am trying to do something a little similar,

I have the SQL i need to get the price from value for a group of products in a particular currency but I cant seem to output the data. I am putting this in the php of a custom page

$pdo = Capsule::connection()->getPdo();
   try {
       $statement = $pdo->prepare(
           'SELECT MIN(tp.monthly) AS minprice FROM tblproducts p, tblpricing tp WHERE p.hidden=0 AND p.id = tp.relid AND p.gid = 29 AND tp.currency=1;'
       );

       $statement->execute();

       $result = $statement->fetch();
	  

   } catch (\Exception $e) {
       echo "Ooooooooops {$e->getMessage()}";
   }
print_r($result);

But my result does not match the result if I run the same SQL directly in mysql

Array ( [minprice] => [0] => )

Any ideas? What am I doing wriong

[ServWise.com]

Scrap that the code works I was looking at the wrong database. 🙂