Hi,
I have a frontpage with categorised featured products (http://www.pricetapestry.com/node/3243).
The featured products are based on a selection of categories. How whould I mix that with products from a specific merchant?
So for example one section with products from a specified category and another section with (random selection) of products from a specified Merchant.
<?php
unset($featured);
if ($featuredproducts_categoryRandom)
{
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE category = '".$featuredproducts_categoryRandom."' ORDER BY merchant LIMIT 50";
}
else
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` WHERE name LIKE '".$featuredproducts_category."/%' ORDER BY merchant";
$featuredproducts_search = $featuredproducts_category;
}
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
if ($featuredproducts_category)
{
$featured["name"] = str_replace($featuredproducts_category."/","",$featured["name"]);
}
$sqlNames[] = "'".database_safe($featured["name"])."'";
}
$sqlIn = implode(",",$sqlNames);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name IN (".$sqlIn.") GROUP BY name ORDER BY merchant";
database_querySelect($sql,$rows);
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
if ($config_useRewrite)
{
$featured["products"][$k]["productHREF"] = "product/".tapestry_hyphenate($product["normalised_name"]).".html";
$featured["products"][$k]["reviewHREF"] = "review/".tapestry_hyphenate($product["normalised_name"]).".html";
}
else
{
$featured["products"][$k]["productHREF"] = "products.php?q=".urlencode($product["name"]);
$featured["products"][$k]["reviewHREF"] = "reviews.php?q=".urlencode($product["name"]);
}
}
}
if (isset($featured)) require("html/featured.php");
$featuredproducts_category = "";
$featuredproducts_categoryRandom = "";
?>Thanks, Marco - very easy to modify, see below, I've added a $featuredproducts_merchantRandom which you can set in your calling code in place of $featuredproducts_categoryRandom...
<?php
unset($featured);
if ($featuredproducts_merchantRandom)
{
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE merchant = '".$featuredproducts_merchantRandom."' ORDER BY merchant LIMIT 50";
}
elseif ($featuredproducts_categoryRandom)
{
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE category = '".$featuredproducts_categoryRandom."' ORDER BY merchant LIMIT 50";
}
else
{
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` WHERE name LIKE '".$featuredproducts_category."/%' ORDER BY merchant";
$featuredproducts_search = $featuredproducts_category;
}
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
if ($featuredproducts_category)
{
$featured["name"] = str_replace($featuredproducts_category."/","",$featured["name"]);
}
$sqlNames[] = "'".database_safe($featured["name"])."'";
}
$sqlIn = implode(",",$sqlNames);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name IN (".$sqlIn.") GROUP BY name ORDER BY merchant";
database_querySelect($sql,$rows);
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
if ($config_useRewrite)
{
$featured["products"][$k]["productHREF"] = "product/".tapestry_hyphenate($product["normalised_name"]).".html";
$featured["products"][$k]["reviewHREF"] = "review/".tapestry_hyphenate($product["normalised_name"]).".html";
}
else
{
$featured["products"][$k]["productHREF"] = "products.php?q=".urlencode($product["name"]);
$featured["products"][$k]["reviewHREF"] = "reviews.php?q=".urlencode($product["name"]);
}
}
}
if (isset($featured)) require("html/featured.php");
$featuredproducts_category = "";
$featuredproducts_categoryRandom = "";
$featuredproducts_merchantRandom = "";
?>
Cheers,
David.
--
PriceTapestry.com
Hi,
It works with one merchant but if I put two merchant (sections) in index.php it doesn't work.
I changed the limit to 3 in:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE merchant = '".$featuredproducts_merchantRandom."' ORDER BY merchant LIMIT 50";
}How can I make the selection more in a specific range? I tried unsuccesfully:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE merchant = '".$featuredproducts_merchantRandom."' WHERE price >= 9.00 AND price <= 999.00 ORDER BY RAND() LIMIT 3";Hi Marco,
To filter by price, your code is nearly correct, but rather than another WHERE, it should be AND, like this:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE merchant = '".$featuredproducts_merchantRandom."' AND price >= 9.00 AND price <= 999.00 ORDER BY RAND() LIMIT 3";I checked over the code and I can't see why it doesn't work for two merchants,
are there no results for either merchant, or is it just the second section
that does not work?
Cheers,
David.
--
PriceTapestry.com
Hi,
It works very nicely.
Regarding the two merchants it works also. I forgot to change the $featuredproducts_categoryRandom to $featuredproducts_merchantRandom for that second merchant section.
Thank you very much.
Hi Marco,
Could you post the code from the featuredproducts.php script that you created and I'll modify it to support a $featuredproducts_merchant variable as an alternative...
Cheers,
David.
--
PriceTapestry.com