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
Hi David,
Thanks as always for your quick response and also for the above mod which works perfectly.
Best regards
Chris
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