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
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
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";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
I am using this featured page modification. However, whenever I refresh the page new product reloads, is there a way to limit this behave. Because normally a given page should not refresh or have new data every second (this is classified as spamming behavior) and is not good for SEO. It could be better a reload of new products should be based on days specified of the month, this tells search engine that page is managed by human, instead of bots which could lead the page to be classified as spam.
Hi,
You could use a fully numeric version of the date as the optional seed parameter of MySQL's RAND() function in the SQL; so where you have
ORDER BY RAND()...REPLACE with:
ORDER BY RAND(".date("Ymd").")The product selection will then only change each day...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Is it possible to display only products sold by at least 2 sellers?
Thanks,
Marcos
Hello Marcos;
Referring to the original post in this thread; you could use the following replacement to select 3 random products (or as required) with at least 2 sellers;
$sql = "SELECT COUNT(id) AS numMerchants,name,1 AS sequence FROM `".$config_databaseTablePrefix."products` GROUP BY name HAVING numMerchants > 1 ORDER BY RAND() LIMIT 3";Cheers,
David.
--
PriceTapestry.com
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.