You are here:  » Random Featured Products

Active Forum Topics


Random Featured Products

Submitted by support on Thu, 2015-07-30 11:24 in

Hi everyone,

I'm just re-documenting the Random Featured Products modification for all distributions as whilst line numbers have changed slightly the actual code itself has not.

As an alternative to manually entered Featured Products, products can be selected at random instead. To do this, edit index.php and look for the Featured Products selection SQL construction as follows;

  $sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY sequence";

...and REPLACE with:

  $sql = "SELECT DISTINCT(name),1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 3";

This will return 3 random products from the entire products table.

This method does require a full table sort by MySQL so may not be ideal for larger installations however a significant optimisation can be applied by limiting the selection to a random merchant. To do this, use the following alternative REPLACEment:

  $sql = "SELECT merchant FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 1";
  database_querySelect($sql,$rows);
  $sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($rows[0]["merchant"])."' ORDER BY RAND() LIMIT 3";

Cheers,
David
--
PriceTapestry.com

Submitted by Retro135 on Sat, 2015-12-19 06:48

Is it possible to have random products selected from a specified merchant (or maybe 2-4) + a keyword, + then specify a duration (3 days)? And perhaps have a set (for lack of a better word), of say, 5, 10, 15 (or whatever, maybe be able to also specify) merchants, keywords & duration?

This would enable you to create a rotating featured items display for example, a month in advance. Thinking a seasonal or other theme, best-sellers, etc., could be set up.

Submitted by support on Sat, 2015-12-19 12:54

Hi Retro135,

It's straight forward to extend the above to a list of merchants and / or keywords. Based on the distribution Featured Products implementation in index.php look for the following code at line 12:

  $sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY sequence";

...and REPLACE with:

  $merchants = array("Merchant 1","Merchant 2"); // for all merchants use $merchants = array();
  $keywords = array("Keyword1","Keyword2"); // for all products use $keywords = array();
  $where = " 1 ";
  if (count($merchants))
  {
    $ins = array();
    foreach($merchants as $merchant)
    {
      $ins[] = "'".database_safe($merchant)."'";
    }
    $where .= " AND (merchant IN (".implode(",",$ins).")) ";
  }
  if (count($keywords))
  {
    $likes = array();
    foreach($keywords as $keyword)
    {
      $likes[] = "(name LIKE '%".database_safe($keyword)."%')";
    }
    $where .= " AND (".implode(" OR ",$likes).") ";
  }
  $sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY RAND() LIMIT 3";

Taking this a step further, you could schedule different sets of merchant and keyword combinations over the course of a month with a switch() statement:

  $dayOfMonth = date("j");
  switch(TRUE)
  {
    case ($dayOfMonth < 10):
      $merchants = array("Merchant 1","Merchant 2");
      $keywords = array("Keyword1","Keyword2");
      break;
    case ($dayOfMonth < 20):
      $merchants = array("Merchant 3","Merchant 4");
      $keywords = array("Keyword1","Keyword2","Keyword3");
      break;
    default:
      $merchants = array("Merchant 5","Merchant 6");
      $keywords = array("Keyword4","Keyword5","Keyword6");
      break;
  }
  $where = " 1 ";
  if (count($merchants))
  {
    $ins = array();
    foreach($merchants as $merchant)
    {
      $ins[] = "'".database_safe($merchant)."'";
    }
    $where .= " AND (merchant IN (".implode(",",$ins).")) ";
  }
  if (count($keywords))
  {
    $likes = array();
    foreach($keywords as $keyword)
    {
      $likes[] = "(name LIKE '%".database_safe($keyword)."%')";
    }
    $where .= " AND (".implode(" OR ",$likes).") ";
  }
  $sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY RAND() LIMIT 3";

The above would use the first set of $merchants / $keywords for the 1st of the month to the 9th, the second set from the 10th to the 19th and the final (default) case for the remainder of the month. Always end with a default: case as a catch-all if you wish to ensure that Featured Products are always displayed regardless...

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by marco@flapper on Thu, 2016-04-21 09:20

Hi,
I'm testing the latest version to upgrade my old ones. Nice to see how PT has developed.

Anyway I was wondering if it was posible to have an overall catch all for the random featured products?

I'm trying out the modification "schedule different sets of merchant and keyword combinations over the course of a month with a switch() statement" and I noticed it returns empty if a specific merchant and keyword combination is not fullfilled. Can it be made to show random products in that case?

I currently have this:

$dayOfMonth = date("j");
  switch(TRUE)
  {
    case ($dayOfMonth < 10):
      $merchants = array("Merchant 1","Merchant 2");
      $keywords = array("shirt","Keyword2");
      break;
    case ($dayOfMonth < 20):
      $merchants = array("Merchant 3","Merchant 4");
      $keywords = array("shirt","Keyword2");
      break;
          case ($dayOfMonth < 31):
      $merchants = array("Merchant 5","Merchant 6");
      $keywords = array("Hoodie","Keyword2");
      break;
    default:
      $merchants = array("Merchant 7","Merchant 8");
      $keywords = array();
      break;
  }
  $where = " 1 ";
  if (count($merchants))
  {
    $ins = array();
    foreach($merchants as $merchant)
    {
      $ins[] = "'".database_safe($merchant)."'";
    }
    $where .= " AND (merchant IN (".implode(",",$ins).")) ";
  }
  if (count($keywords))
  {
    $likes = array();
    foreach($keywords as $keyword)
    {
      $likes[] = "(name LIKE '%".database_safe($keyword)."%')";
    }
    $where .= " AND (".implode(" OR ",$likes).") ";
  }
  $sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY RAND() LIMIT 16";

Submitted by support on Thu, 2016-04-21 10:12

Hi Marco,

Thank you for your comments!

To do this, where you have the following code on the last line of your post;

  $sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY RAND() LIMIT 16";

...REPLACE with:

  $sql = "SELECT id FROM `".$config_databaseTablePrefix."products` WHERE ".$where." LIMIT 1";
  if (!database_querySelect($sql,$result))
  {
    $where = " 1 ";
  }
  $sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY RAND() LIMIT 16";

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by marco@flapper on Thu, 2016-04-21 10:27

Thanks it works very nicely.