Support Forum

Active Forum Topics

Shortcode listing top 10 price differences

Submitted by koen on Wed, 2015-11-18 13:35 in Post Installation (Virtual)

Hi David,

I'm using a SQL statement for selecting a top 10 of products from Brand X with the biggest difference betwee the lowest and hightest price.

The SQL statement looks lik:

select `ean`,`name`, MIN(`totalprice`), MAX(`totalprice`), count(1) from pt_products where `brand` = "BRANDX" group by `ean` order by max(`totalprice`) - min(`totalprice`) DESC limit 0, 10

I would like to display the results of this statement into my Wordpress site, for example to list the top-10 per brand (on a page dedicated to this brand). I was looking at an option using the Custom Query Class from Wordpress, but I'm not an WP guru and I realized it maybe would be easier to have this code as an Pricetapestry shortcode....

Can you please help me turn this SQL statenment into a shortcode? Like [pto MINMAX brand=BRANDX] with an output like:

Product X (with a link to the productpage); MIN Price ;MAX Price ; Difference ; Number of instances

Hope you can help and that the question is clear!

Thanks a lot, cheers!
Koen

Hello Koen, It's probably

Submitted by support on Wed, 2015-11-18 14:24

Hello Koen,

It's probably easiest to plumb in as a custom Featured Products "section/" prefix. In pto_featured.php look for the following code at line 25:

if ($section)

...and REPLACE with:

  if (strpos($section,"minmax")!==FALSE)

  {

    $parts = explode(":",$section);

    $sql = "SELECT `ean`,`name`, 1 AS sequence, MIN(`totalprice`), MAX(`totalprice`), count(1) from ".$pto_config_databaseTablePrefix."products where `brand` = '".$wpdb->escape($parts[1])."' group by `ean` order by max(`totalprice`) - min(`totalprice`) DESC limit 0, 10";

  }

  elseif ($section)

Then use the shortcode:

[pto featured="minmax:BrandX"]

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Thanks David, I've

Submitted by koen on Wed, 2015-11-18 15:01

Thanks David,
I've implemented the code, but...

{code saved}

The lines 61 and 63 are repeated a lot of times (10 times I guess....)
Help! ;-)

Sorry Koen, The subsequent

Submitted by support on Wed, 2015-11-18 15:53

Sorry Koen,

The subsequent code relies on the sequence field (which is on pt_featured) but not on pt_products so just needs to be added using a computed column of "1 AS sequence" to the SQL - corrected above...

Cheers,
David.
--
PriceTapestry.com

Hi David, Something is still

Submitted by koen on Wed, 2015-11-18 19:58

Hi David,

Something is still going wrong... I've applied the new code, but...

{code saved}

Do you have an idea?

Sorry Koen, Nearly there!

Submitted by support on Wed, 2015-11-18 21:00

Sorry Koen,

Nearly there! I'd used substr instead of strpos - corrected above.

Regarding the mysql_real_escape_string() warnings, replace each instance of

mysql_real_escape_string

with:

$wpdb->escape

(lines 60 and 62)

Cheers,
David.
--
PriceTapestry.com

Thanks David, It works,

Submitted by koen on Thu, 2015-11-19 19:05

Thanks David,
It works, THANK YOU! But....

How can I create the "table" listing the products and the Min / Max prices?
Like:
Product X (with link) ; Minprice ; Maxprice ; Difference ?

Is it possible to (easily) create an alternative featured product design for this??

Thanks,
Koen

Hi Koen, The WordPress

Submitted by support on Fri, 2015-11-20 10:56

Hi Koen,

The WordPress settings implementation makes it easy to add additional templates. In pto_settings.php look for the following code at line 43:

$pto_settings_html[] = "pto_html_featured_before";

...and REPLACE with:

  $pto_settings_html[] = "pto_html_minmax_before";

  $pto_settings_html[] = "pto_html_minmax_each";

  $pto_settings_html[] = "pto_html_minmax_after";

  $pto_settings_html[] = "pto_html_featured_before";

And then the following code at line 313:

print "<h4>Featured Products</h4>";

...and REPLACE with:

    print "<h4>Min Max</h4>";

    print "<blockquote>";

    print "<table class='form-table'>";

    pto_settings_html("Before","pto_html_minmax_before",3);

    pto_settings_html("Each","pto_html_minmax_each",10);

    pto_settings_html("After","pto_html_minmax_after",3);

    print "</table>";

    print "</blockquote>";

    print "<h4>Featured Products</h4>";

Then in pto_featured.php, the new templates can be used if $parts is set (indicating a minmax request) so modify as follows;

Line 32:

$html .= $pto_html_featured_before;

...REPLACE with:

$html .= (isset($parts)?$pto_html_minmax_before:$pto_html_featured_before);

Line 73:

$each = $pto_html_featured_each;

...REPLACE with:

$each = (isset($parts)?$pto_html_minmax_each:$pto_html_featured_each);

and line 175:

$html .= $pto_html_featured_after;

...REPLACE with:

$html .= (isset($parts)?$pto_html_minmax_after:$pto_html_featured_after);

In terms of placeholders, you can use the %DB_field% to include the computed columns, e.g.

%DB_minPrice%
%DB_maxPrice%
%DB_numMerchants%

The only placeholder not currently available for the output you want to generate is difference, so to add this, look for the following code at line 135:

$each = str_replace("%PRICE%",$pto_config_currencyHTML.$row->minPrice,$each);

...and REPLACE with:

  $difference = $row->maxPrice-$row->minPrice;

  $each = str_replace("%DIFFERENCE%",$pto_config_currencyHTML.($difference),$each);

  $each = str_replace("%PRICE%",$pto_config_currencyHTML.$row->minPrice,$each);

Before you make a big edit to the new templates make sure they're saving correctly first - just put "TESTING" in each and make sure they save as expected, and then construct your templates as required based on the Featured Products templates, but with the layout you required.

Hope this helps!

Cheers,
David.
--
PriceTapestry.com