You are here:  » Categorised Featured Products with sql backfilling


Categorised Featured Products with sql backfilling

Submitted by ChrisNBC on Mon, 2015-04-13 15:18 in

Hi David,

As you know, I implemented categorised featured products (node 3243) some time ago. If possible, I would like to expand this by still allowing featured items to be chosen manually but if the maximum number is not set I would like to set the remainder with a query which would select by brand (I have used brand for my categories) and also by savings (highest first). I already calculate a value for savings on import and store this in a field named "discountgbp" (which is decimalised). Ideally, I would like to mix the manually and sql selected products within each category to make the result look more natural.

I searched the forum for a mod but couldn't spot anything that looked suitable. I wondered if you could suggest of any existing mod that would work for the above?

Thanks in advance.

Regards
Chris

Submitted by support on Wed, 2015-04-15 09:15

Hi Chris,

This is relatively straight forward, essentially combining categorised and random featured products - but rather than literally random (using ORDER BY RAND()) selecting by brand where brand is the value used for $featuredproducts_category, ordering by your `discountgbp` field. Finally, the results can be shuffle()'ed.

Assuming that you've made a featuredproducts.php script as per node 3243 then here is an extended version to do exactly the above, with a limit of 4 products. You can change the total by modifying the value of $max at line 27:

<?php
  
unset($featured);
  
$sql "SELECT * FROM `".$config_databaseTablePrefix."featured` WHERE name LIKE '".$featuredproducts_category."/%' ORDER BY sequence";
  if (
database_querySelect($sql,$rows))
  {
    
$sqlNames = array();
    
$sqlCase "CASE normalised_name";
    foreach(
$rows as $featured)
    {
      
$featured["name"] = str_replace($featuredproducts_category."/","",$featured["name"]);
      
$featured["name"] = tapestry_normalise($featured["name"]);
      
$sqlNames[] = "'".$featured["name"]."'";
      
$sqlCase .= " WHEN '".database_safe($featured["name"])."' THEN ".$featured["sequence"];
    }
    
$sqlCase .= " END AS sequence";
    
$sqlIn implode(",",$sqlNames);
    
$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";
    
database_querySelect($sql,$rows);
    
$featured["products"] = $rows;
    foreach(
$featured["products"] as $k => $product)
    {
      
$featured["products"][$k]["productHREF"] = tapestry_productHREF($product);
      
$featured["products"][$k]["reviewHREF"] = tapestry_reviewHREF($product);
    }
    
$count count($featured["products"]);
    
$max 4;
    if (
$count $max)
    {
      
$extra $max $count;
      
$notIns = array();
      foreach(
$featured["products"] as $product)
      {
        
$notIns[] = "'".database_safe($product["name"])."'";
      }
      
$sqlNotIn implode(",",$notIns);
      
$sql "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE brand = '".database_safe($featuredproducts_category)."' AND name NOT IN (".$sqlNotIn.") ORDER BY discountgbp DESC LIMIT ".$extra;
      if (
database_querySelect($sql,$rows))
      {
        
$sqlNames = array();
        
$sqlCase "CASE normalised_name";
        foreach(
$rows as $featured2)
        {
          
$featured2["name"] = str_replace($featuredproducts_category."/","",$featured2["name"]);
          
$featured2["name"] = tapestry_normalise($featured2["name"]);
          
$sqlNames[] = "'".$featured2["name"]."'";
          
$sqlCase .= " WHEN '".database_safe($featured2["name"])."' THEN ".$featured2["sequence"];
        }
        
$sqlCase .= " END AS sequence";
        
$sqlIn implode(",",$sqlNames);
        
$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";
        
database_querySelect($sql,$rows);
        
$featured2["products"] = $rows;
        foreach(
$featured2["products"] as $k => $product)
        {
          
$featured2["products"][$k]["productHREF"] = tapestry_productHREF($product);
          
$featured2["products"][$k]["reviewHREF"] = tapestry_reviewHREF($product);
        }
      }
      foreach(
$featured2["products"] as $product)
      {
        
$featured["products"][$count++] = $product;
      }
      
shuffle($featured["products"]);
    }
  }
  if (isset(
$featured)) require("html/featured.php");
?>

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by ChrisNBC on Wed, 2015-04-15 13:09

Hi David,

Thanks as always for your quick response and also for the above mod which works perfectly.

Best regards
Chris