I am looking to create a page similar to featured.php that can work alongside it to create 20 random products.
The overall effect would then be like froogle with a list of so-called 'latest searches'.
I did try to hack away at the featured.php file but that would mean having either the featured section or the latest searches - I am loking to have both.
Hope you can help.
Cheers,
Dave
PS) I think the search query I need is
<?php
$randomsearches = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 20";
?>
Would I be able to use random.php and featured.php concurrently on the same page or would create a conflict?
Shouldn't be a problem. The easiest way is probably to run 2 queries and then merge the results (assuming you want some fixed and some random products).
Instead of:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY sequence";
if (database_querySelect($sql,$rows))Try something like this:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY sequence";
database_querySelect($sql,$rows1);
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 3";
database_querySelect($sql,$rows2);
$rows = array_merge($rows1,$rows2);
if (database_querySelect($sql,$rows))That would suppliment the featured products with 3 random products.
Cheers,
David.
That's great - I've uploaded it to Random Gift Idea Generator if anyone wants to see the results.
If choosing random products from the products table takes a little too long, would that be sped up doing a products cache to query, like with the brand and category cache you have said about before?
Hi Clare,
A cache would work if you were happy to randomise once per day (or perhaps hour), but if you want every page view to be a random selection from the entire products table then i'm afraid a caching solution wouldn't be practical.
What I would recommend perhaps is selecting random products from a particular merchant; since the merchant field is indexed which should result in a much faster query. This could be done, in place of the following:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 3";Try:
$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."feeds` ORDER BY RAND() LIMIT 1";
database_querySelect($sql,$result);
$merchant = $result[0]["merchant"];
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($merchant)."' ORDER BY RAND() LIMIT 3";Cheers,
David.
Thanks yes, that is better and really speeds things up
Hi
I want several random products on the home page in place of featured. I tried changing the code in index.php but all I get is a blank page. Suggestions?
Before
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY sequence";
After
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 10";
Using the above example I keep getting a blank page when replacing the code in Index.php. Suggestions?
Before
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY sequence";
After
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 10";
Hi,
Under the latest version you need to add a "sequence" value to the query.... Try this:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 10";(selecting name on its own will also improve performance)
Cheers,
David.
Hey David,
I actually got the previous code to work, it was an error on my part since I was replacing the code around line 16.
The above code works also but only shows the name, I'm assuming this is from SELECT name? Trying to get my head around all this.
Thanks,
Tommy
Hi Tommy,
Do you mean that it is selecting random featured products; but only the name is being displayed - no image or price etc.? The first query only needs to select names, as the second summary query is the one that provides the source for html/featured.php....
Feel free to email me your modified files if you'd like me to take a closer look (email address here)...
Cheers,
David.
Hey David,
After reading what I wrote I really don't know what I was talking about... Must have been from lack of sleep. Another question I have is with the Random products being pulled is there a way to base random products from a keyword. For example if it's Halloween I would like random halloween products pulled. Possible?
Thanks again,
Tommy
Hello Tommy,
You can certainly add anything you like in a WHERE clause in the SQL to select random products. If you currently have:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 10";...then a modification to select only products with halloween in the name might be:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE name LIKE '%halloween%' ORDER BY RAND() LIMIT 10";Cheers,
David.
Hey David,
Thanks for the information. I had to modify the above suggestion slightly from:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE name LIKE '%halloween%' ORDER BY RAND() LIMIT 9";
to:
$sql = "$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name LIKE '%halloween%' ORDER BY RAND() LIMIT 9";
Works now :) Before it was pulling the products but with no prices or images for some reason.
Thanks again,
Tommy
Hi Dave,
That SQL looks fine. Several users have infact done exactly that and used random products instead of featured products. All you need to do is change the $sql on line 45 of index.php exactly as you have, although perhaps with a lower limit:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 20";To create a random products page; I would create a copy of index.php called random.php; and then make a copy of the html module html/featured.php as something like html/randomproducts.php - also updating the require() statement in your new random.php file.
Finally, you can edit your new html/randomproducts.php to display the items as required.
Cheers,
David.