You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.
This is a easy little hack to add a new menu block to your site that displays a random item from you products. Nothing scientific about this one and really easy to setup
You would add the code below to your skin.php file for which ever menu block you want it in.
<div class="skin_fenavh">Random Item</div> <div class="skin_fenavmini"> <?php $host = 'localhost'; $dbUser = 'dbuser'; $dbPass = 'dbpassword'; $db = 'dbname'; mysql_connect("$host", "$dbUser", "$dbPass") or die(mysql_error()); mysql_select_db("$db") or die(mysql_error()); $result = mysql_query("SELECT id,imgsm,name FROM ccp0_prod order by rand() LIMIT 1" ) or die(mysql_error()); while($row = mysql_fetch_array( $result )) { print "<a href=\"/linux/" . $row['id'] . ".html\"><img alt=\"\" src=\"media/ccp0/prodsm/" . $row['imgsm'] . "\" /><br />" . $row['name'] . "</a> "; } ?> </div>
Personally I would have all database stuff in a directory off webroot but this script makes your pages a bit more dynamic. If you change the limit to 5 you would show 5 items. Some html adjustments would be necessary.. Remove the image and call db a bit differently and you have what I was looking for easier..
See it at http://www.linuxcdshop.com
Offline
Thanks Perkster!
I had to modify it to work with the UK version and aligned the title and image centrally.
<!-- Start of Random Product --> <div class="skin_fenavh" align="center">Random Product</div> <div class="skin_fenavmini" align="center"> <?php $host = 'localhost'; $dbUser = 'dbusername'; $dbPass = 'dbpassword'; $db = 'dbname'; mysql_connect("$host", "$dbUser", "$dbPass") or die(mysql_error()); mysql_select_db("$db") or die(mysql_error()); $result = mysql_query("SELECT id,imgsm,name FROM gbu0_prod order by rand() LIMIT 1" ) or die(mysql_error()); while($row = mysql_fetch_array( $result )) { print "<a href=\"/gbu0-prodshow/" . $row['id'] . ".html\"><img alt=\"\" src=\"media/gbu0/prodsm/" . $row['imgsm'] . "\" /><br />" . $row['name'] . "</a> "; } ?> </div> <!-- End of Random Product -->
Offline
Hi,
Great Little Hack... thanks...
Can we get it to only pick up items flagged for Featured Items? instead of all items.
Cheers,
Bruce.
Offline
Change
$result = mysql_query("SELECT id,imgsm,name FROM gbu0_prod order by rand() LIMIT 1" ) or die(mysql_error());
to
$result = mysql_query("SELECT `id`,`imgsm`,`name` FROM `gbu0_prod` WHERE `featuredisp` = 1 order by rand() LIMIT 1" ) or die(mysql_error());
Offline
Hi Dave,
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `featuredisp` = 1' at line 1
Getting the above error now.
Cheers,
Bruce.
Offline
Sorry, I've corrected my post. The where has to come before the order by.
Offline
How did i miss this hack..great work guys.
Is it possible to display the price display too?
Offline
Prices aren't as simple. There may be a regular price, a sale price and a volume price. You have to determine which one is being used for the product and, if it's volume, call a routine in CCP_Cart to get a correct dollar amount. Feasible but not really cut and dry.
Offline
Ah, never mind, still a great mod...
What about pulling products from a specific category rather then randomly from the database or featured items?
Offline
$result = mysql_query("SELECT `id`,`imgsm`,`name` FROM `gbu0_prod` WHERE `xcat` = 'yourcategoryidhere' order by rand() LIMIT 1" ) or die(mysql_error());
Of course that would only work if the product was only associated with that specific category. At the risk of picking up more products than you want in the randomness this would probably work better:
$result = mysql_query("SELECT `id`,`imgsm`,`name` FROM `gbu0_prod` WHERE `xcat` LIKE '%yourcategoryidhere%' order by rand() LIMIT 1" ) or die(mysql_error());
Offline
Is there a way of pulling items in the order the appear in the database rather than random?
Offline
Hi John,
This does not seem to work, returns an SQL syntax error for both ASC and DESC...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC() LIMIT 1' at line 1
Running Apache 2.0.55 and MYSQL 4.1
Offline
Ascending and descending don't use the () and that's the source of your error.
Offline
Thanks Dave, but this does not fix this problem, here's what we have...
$result = mysql_query("SELECT id,imgsm,name FROM gbu0_prod order by ASC LIMIT 4" ) or die(mysql_error());
The returns the same sql syntax error (bar the () )
Offline
You also have to tell it which field to order by Steve. But if you want them in the order they are in the database simply remove the order by asc completely and just leave the limit 4. Otherwise this should work assuming you want the products sorted by their IDs:
$result = mysql_query("SELECT id,imgsm,name FROM gbu0_prod order by id ASC LIMIT 4" ) or die(mysql_error());
Offline
Hi,
I really like this hack and it looks good on the sites ive seen but i cant get it to work on mine.
This is the error i get:
mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Access denied for user 'dbusername'@'localhost' (using password: YES)
Any thoughts?
Offline
Well the message pretty much says it all Rob. One or more pieces of the information to connect to your DB isn't correct.
Offline
Call me daft but it not obvious to me as i no nothing about this.
From what i can see it must be the password. If that's the case where has the "(using password: YES)" come from.
Where would i find the info to connect to the database?
Offline
You've supplied a user name and password for the database connection but one or both of those values are not correct. The password will be the same one that was used during the installation of CCP. The user name may be obtained by running the CCP installer to the point where the DB information is entered (the DB name and server info is there too) or, usually, via your providers control panel.
Offline
i found the user name database name via the cPanel and used the installer password, this brought the same error up.
So I ran the installer to the point you suggested and I think the problem is the password. The db name I used was the same but the password must be different to the installer. I entered a new one which brought an error up and sent me back to the previous step.
How can i change it?
UPDATE: Ive got it to work now, Ive created a new user via cPanel and add the user to the database but I'd rather use my own user name and password.
Howard installed ccp for me so i sure hes not going to remember so is they another way of retrieving /changing it?
Last edited by thezazzi (08-16-2009 01:17:23)
Offline
If you've changed your MySQL user name and password run the CCP installer to the point where that information is asked for and supply the new information. If it's correct you'll get to the next step of the installer and may stop.
Offline
I'm at Step 11 of 13: Database Configuration and after reading it properly it says:
Database Username*
Enter the username for the database that was configured for use with this software. This is the username which will be used by this software to connect to the database.
Database Password*
Enter the password for the username entered above. This is the password which will be used by this software to connect to the database.
Dave wrote:
If you've changed your MySQL user name and password run the CCP installer to the point where that information is asked for and supply the new information.
Where abouts was Username & Password originally entered?
Looking though my hosts cPanel it shows the database and database users, there's no option to change passwords only to add a user to that database so I'm not to sure where about i need to look.
I'm not to fussed about having to add a new user so i can access the db but its the fact that it should be me who knows all the passwords and no one else.
Offline