Hi,
Does anyone know how to use this script to display 4 random products on any web page? The page itself is not,repeat not part of price tapestry, but rather another script. I figured out how to do 4 random products, and 4 featured product names, but I want to be able to select 4 random featured products, together with all their details (screenshot, link, price etc.) and diplay anywhere i like.
Thanks,
Andrew SHort
Hi David,
Would it be possible to do this random selection but on a daily basis only. i.e. Have a random selected product change every 24 hrs only.
This would allow product promotion for 24 hrs and then change to another product on the site.
Could be used to offer a special price or other interesting ideas.
Can it be done? Thanks
Yes - that can be done. I've just looked up the RAND() function on the MySQL website, and you can provide it with a seed value that causes a repeatable sequence of numbers. Therefore, all we need is a seed value that changes every day, and we can use the date() function for that.
In the above code, where you have the following line:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY RAND() LIMIT 4";..change this as follows:
$seed = intval(date("Ymd"));
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY RAND(".$seed.") LIMIT 4";That should do the trick...
Cheers,
David.
Thanks David,
So this would have the random product change every 24 hrs depending on this variable.
$seed = intval(date("Ymd"));
We leave this as is, correct?
We do not change anything and this will use the built in date function to check today's date and then only change product if the date changes - Correct?
Thanks in advance.... this will help!
David.
That's correct, leave everything as it is and you get a different seed number every day, which in turn results in different products!
The date() function returns a number based on the letters in the string that you pass to it; so Y returns the year (2007), m the month and d the day.
Cheers,
David.
If I wanted to put random featured products on a page outside the price tapestry installation folder and refer to the PT database, can I do this.
I tried this, but get an error
Fatal error: Call to undefined function: translate() in public_html/shopUK/html/featured.php on line 4
Fatal error: Call to undefined function: database_queryselect() in ....index.htm on line 132
This is the code I tried to use to put the featured prods on a page outside the installation, and I thought that by providing a full url for the require files, it may still display.....do I need to refer to a full url somewhere for the database queries and is this possible?
<?php
require("http://www.mysite.com/shopUK/includes/common.php");
require("http://www.mysite.com/shopUK/html/featured.php");
$seed = intval(date("Ymd"));
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY RAND(".$seed.") LIMIT 4";
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$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";
database_querySelect($sql,$rows);
// the 4 random featured products with all product data etc. are now in $rows
}
?>Hi Clare,
The following won't work because the files are parsed by the server and nothing will be returned:
require("http://www.mysite.com/shopUK/includes/common.php");
require("http://www.mysite.com/shopUK/html/featured.php");Instead, what you have to do is link to the include files directly (you can't use common.php because it only works in the main Price Tapestry directory or /admin/). This can be done as follows:
<?php
$path = "../../relative/path/to/pricetapestry/";
require($path."config.php");
require($path."includes/"."javascript.php");
require($path."includes/"."tapestry.php");
require($path."includes/"."translate.php");
require($path."includes/"."database.php");
?>
Follow that with your database code to populate the $featured array, and then bring in the HTML module:
<?php
require($path."html/"."featured.php");
?>
Hope this helps!
Cheers,
David.
I am still getting one error...
Fatal error: Call to undefined function: database_queryselect() in ..../index.htm on line 134
line 134 being
if (database_querySelect($sql,$rows))
I put as below...
<?php
$path = "http://www.mysite.com/shopUK/";
require($path."config.php");
require($path."includes/"."javascript.php");
require($path."includes/"."tapestry.php");
require($path."includes/"."translate.php");
require($path."includes/"."database.php");
$seed = intval(date("Ymd"));
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY RAND(".$seed.") LIMIT 4";
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$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";
database_querySelect($sql,$rows);
// the 4 random featured products with all product data etc. are now in $rows
}
require($path."html/"."featured.php");
?>Hi Clare,
You need to work out the actual path on the server to the Price Tapestry files - i'm afraid you can't use a web address within the include() function. Therefore, this line:
$path = "http://www.mysite.com/shopUK/";...needs to look something like this:
$path = "../shopUK/";The "../" mean "up one directory", so if you are in the folder "/example/", and Price Tapestry is in the folder "/shopUK/", then you need "../shopUK/" as the $path value.
Also note that there isn't quite enough code in your snippet to populate the $featured variable so that products are displayed by the featured products HTML module. Assuming that your path to Price Tapestry is ../shopUK/ as described above, the following should be most of the way there, and includes another modification to bring in the value of baseHREF as you are working in a different directory to Price Tapestry:
<?php
$path = "../shopUK/";
require($path."config.php");
require($path."includes/"."javascript.php");
require($path."includes/"."tapestry.php");
require($path."includes/"."translate.php");
require($path."includes/"."database.php");
$seed = intval(date("Ymd"));
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY RAND(".$seed.") LIMIT 4";
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$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";
database_querySelect($sql,$rows);
// the 4 random featured products with all product data etc. are now in $rows
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
if ($config_useRewrite)
{
$featured["products"][$k]["productHREF"] = $config_baseHREF."product/".tapestry_hyphenate($product["name"]).".html";
$featured["products"][$k]["reviewHREF"] = $config_baseHREF."review/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$featured["products"][$k]["productHREF"] = $config_baseHREF."products.php?q=".urlencode($product["name"]);
$featured["products"][$k]["reviewHREF"] = $config_baseHREF."reviews.php?q=".urlencode($product["name"]);
}
}
}
require($path."html/"."featured.php");
?>Hope this helps!
Cheers,
David.
Hi,
In the code for random products, it says LIMIT 4 and shows a max number of 4 products , sometimes less. Is there a way to specify that you want exactly 5 random products each time for example?
Hi Clare,
The code above should pick 4 at random from the featured products table. How many products are in your feature products table for it to choose from?
Cheers,
David.
Actually yes, when I was getting the odd numbers of products I think I had alot less feautured products, since having added more today they are showing 4 always in fact. thanks
Sorry about this, I thought they were now ok as a few times it was 4 showing, but in fact again I am getting odd numbers still..sometimes 1, 2,3
I have 14 products in the featured products list, should I have more?
eg..
http://www.hairstylezone.com/christmas-gifts-uk/
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY RAND() LIMIT 4";
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$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";
database_querySelect($sql,$rows);
// the 4 random featured products with all product data etc. are now in $rows
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
if ($config_useRewrite)
{
$featured["products"][$k]["productHREF"] = $config_baseHREF."product/".tapestry_hyphenate($product["name"]).".html";
$featured["products"][$k]["reviewHREF"] = $config_baseHREF."review/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$featured["products"][$k]["productHREF"] = $config_baseHREF."products.php?q=".urlencode($product["name"]);
$featured["products"][$k]["reviewHREF"] = $config_baseHREF."reviews.php?q=".urlencode($product["name"]);
}
}
}
if (isset($featured)) require("html/featured.php");Hi Clare,
I'm wondering if this is something to do with the way RAND() is working. Could you try changing the SQL from:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY RAND() LIMIT 4";...to just:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` LIMIT 4";...and see what happens? It should be the same 4 products every time...
Cheers,
David.
I have done that and now it is showing 1st 4 each time
Hi Clare,
Thanks - so it is a result of weird behavior by the RAND() function! Ok, just needs a little more code to pick all the featured products and then choose 4 randomly in PHP instead. Here goes...
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured`";
if (database_querySelect($sql,$rows))
{
// randomise the results
shuffle($rows);
// and pick $n
$n = 4;
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$featured["name"]."'";
$n--;
if (!$n) break;
}
$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";
database_querySelect($sql,$rows);
// the 4 random featured products with all product data etc. are now in $rows
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
if ($config_useRewrite)
{
$featured["products"][$k]["productHREF"] = $config_baseHREF."product/".tapestry_hyphenate($product["name"]).".html";
$featured["products"][$k]["reviewHREF"] = $config_baseHREF."review/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$featured["products"][$k]["productHREF"] = $config_baseHREF."products.php?q=".urlencode($product["name"]);
$featured["products"][$k]["reviewHREF"] = $config_baseHREF."reviews.php?q=".urlencode($product["name"]);
}
}
}
if (isset($featured)) require("html/featured.php");Hope this works better!
Cheers,
David.
Hi, thanks..it seems to still be showing 2, 3, 4 products, I have put new code and cleared cache but still getting the odd numbers?
Hi Clare,
Can you confirm that all products in your featured products table are still in your database? If not, this will explain why sometimes they are not all displayed....
Cheers,
David.
Hi,
They should be yes. I have 14 in there...I did just change them a few minutes ago, but they shouldnt have been missing for more than a few minutes. Apart from that they should all always be there.
Odd since I changed them, I think I had 18 before, now 14 and even less show, so maybe it is just because I dont have many in there, maybe I need to put a few hundred or something?
It is not all that important if there is no apparent reason for it, it doesnt really matter how many it shows, although I would like 4 it doesnt really matter how many show there, I just wanted to check it wasnt just some error I made.
Hi Clare,
There's something weird going on - so should be tracable with a bit of debug code.
Can you try the following version. This will print the SQL used if the number of featured products is less than 4, so we can study that and try and find out what's going on...
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured`";
if (database_querySelect($sql,$rows))
{
// randomise the results
shuffle($rows);
// and pick $n
$n = 4;
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$featured["name"]."'";
$n--;
if (!$n) break;
}
$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";
database_querySelect($sql,$rows);
// the 4 random featured products with all product data etc. are now in $rows
$featured["products"] = $rows;
if (count($featured["products"]) < 4)
{
print "[".$sql.]";
}
foreach($featured["products"] as $k => $product)
{
if ($config_useRewrite)
{
$featured["products"][$k]["productHREF"] = $config_baseHREF."product/".tapestry_hyphenate($product["name"]).".html";
$featured["products"][$k]["reviewHREF"] = $config_baseHREF."review/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$featured["products"][$k]["productHREF"] = $config_baseHREF."products.php?q=".urlencode($product["name"]);
$featured["products"][$k]["reviewHREF"] = $config_baseHREF."reviews.php?q=".urlencode($product["name"]);
}
}
}
if (isset($featured)) require("html/featured.php");Cheers,
David.
Hi Clare,
Could you post the SQL that was printed out - and then you can remove the mod so that it isn't displayed on your site anymore...
Cheers,
David.
Yes, it showed the following code, which has 4 prods in i, but only 3 were showing when it showed this code..
[".SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `hszukproducts` WHERE name IN ('Awake and Wide Eyed Kit by NV Perricone','Zirh Age Defying Duo Mens Skincare Gift Set','Gianna Rose Hydrangea Soap Gift Set','Best Of The Body Shop Collection') GROUP BY name.]
Hi Clare,
Thanks. The next step is to double-check those products, so could you confirm that you can find the product page for the following, exactly as written (without the quotes):
"Awake and Wide Eyed Kit by NV Perricone"
"Zirh Age Defying Duo Mens Skincare Gift Set"
"Gianna Rose Hydrangea Soap Gift Set"
"Best Of The Body Shop Collection"
Cheers,
David.
Hi,
All are showing when I do a search using search box for each. The one that wasnt showing in the featured was "Awake and Wide Eyed Kit by NV Perricone" , but it does show when doing a product search for that same, so it is definitely there with that exact name.
Clare
Thanks, Clare.
Bear with me on this one as I don't understand why it's not being picked out by the SQL. I'll try to recreate scenario on my test server.
Cheers,
David.
OK thanks, if you want to look at it on my server let me know, and i will send you details.
Clare
Hi Clare,
If you could email me a link to the product page for "Awake and Wide Eyed Kit by NV Perricone" on the site in question, i'll send you back some test code to upload to that site that should help me find out what's going on for you...
Cheers,
David.
Hi Andrew,
You basically want to copy the way the index page selects the featured products, but change the SQL slightly to choose random featured products rather than all of them. Here's the relevant code:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY RAND() LIMIT 4";if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$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";
database_querySelect($sql,$rows);
// the 4 random featured products with all product data etc. are now in $rows
}
Hope this helps!
Cheers,
David.