You are here:  » Multiple Featured Products Pages

Active Forum Topics


Multiple Featured Products Pages

Submitted by ChrisNBC on Tue, 2015-11-03 11:40 in

Hi David,

Hope all is going well. Seems ages since I last posted on the forum, hopefully this means my php & sql knowledge is improving! I wondered if you might be able to help me with some code that I’m stuck on. I’m going to add two additional index pages to one of my sites so that I can show separate featured product pages by gender. I already have a ‘gender’ field in my dB. In addition to the two additional index pages, I originally also planned to create two additional versions of featuredproducts.php. Currently my featured products are set firstly from the featured table and then from high discount products using the code below:

{code saved}

I wondered if you could suggest if there might be a way to have the two extra index pages that I originally planned but then to only have one version of featuredproducts.php where the gender would be set by specifying an additional variable in the index page (in the same way as the $featuredproducts_category variable is set?

Thanks in advance.

Regards
Chris

Submitted by support on Tue, 2015-11-03 13:08

Hello Chris,

Sure - the main SELECT SQL is at line 17 of the code from your post;

    $sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, ".$sqlCase." FROM `".$config_databaseTablePrefix."products` WHERE normalised_name IN (".$sqlIn.") GROUP BY normalised_name ORDER BY sequence";

The selection at this point is already limited by $featuredproducts_category which specifies the category/ prefix as used in Featured Products admin to specify the group of products to be displayed. To further restrict this by a new variable $featuredproducts_gender, REPLACE the above as follows:

    $sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, ".$sqlCase." FROM `".$config_databaseTablePrefix."products` WHERE normalised_name IN (".$sqlIn.") ".($featuredproducts_gender?" AND gender='".database_safe($featuredproducts_gender)."' ":"")." GROUP BY normalised_name ORDER BY sequence";

The above uses a "ternary" - a nice shorthand IF/THEN/ELSE e.g. (IF?THEN:ELSE) so that $featuredproducts_gender is optional - specify in your calling exactly as per $featuredproducts_category as required...

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by ChrisNBC on Tue, 2015-11-03 15:19

Hi David,

Thanks as always for the quick response and for the code above. I’ve made the changes but something is not quite right.... I have noticed that If no products are set in the featured product table, the featured products are no longer backfilled with discounted products as they were before. I wondered if you could suggest how I might resolve this?

Thanks in advance.

Regards
Chris

Submitted by support on Tue, 2015-11-03 16:12

Hi Chris,

If you wanted support by the same script for either $featuredproducts_category or $featuredproducts_gender, but not both, that would be straight forward. Where you have the initial selection at line 3:

  $sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` WHERE name LIKE '".$featuredproducts_category."/%' ORDER BY sequence";

...REPLACE with:

  if ($featuredproducts_category)
  {
    $sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` WHERE name LIKE '".$featuredproducts_category."/%' ORDER BY sequence";
  }
  else
  {
    $sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE gender = '".$featuredproducts_gender."' ORDER BY RAND() LIMIT 8";
  }

Make sure in your calling code that you set one or the other and the other to an empty string e.g.

  $featuredproducts_category = "section";
  $featuredproducts_gender = "";
  require("featuredproducts.php");

..or

  $featuredproducts_category = "";
  $featuredproducts_gender = "Male";
  require("featuredproducts.php");

Note that for the _gender case 8 random products would be selected, so the backfill from discountgbp wouldn't come into it...

Apologies if not totally understood - it may be that separate scripts would be the best option for either category or gender case as required...

Cheers,
David.
--
PriceTapestry.com