Rotate Product Listings With PHP and jQuery

Today, I’ll teach you a simple way of rotating content on your site. We’ll be retrieving data from a MySQL database and using a dynamic widget that auto refreshes using AJAX.

Download Source

Our Goal

For this tutorial our goal is to create a widget that will pull records from a database at set intervals and display them on our page. We will be using PHP and MySQL to extract and display our data, and jQuery to animate the refresh transition.

Step 1 Database Creation

Open up PHPMyAdmin and create a new database called ‘sampledb’. Within this database we need to add a table called ‘products’. Of course in your own projects you might not be listing products, but for the purposes of this tutorial we will be using the widget to list products from our database.

We want to add the following fields:

  • id – INT – Primary Key – Auto Increment
  • product_title – VARCHAR(150)
  • product_price DECIMAN (6, 2)
  • product_img – VARCHAR(150)

We need to populate the database with random data. I already have 10 images that I’ll be using in the product_img field, and I already know they are all 125px by 125px so I also know I won’t have to force the image sizes later on. For now, lets create ten random products and give them all an image of their own.

You can put in any random data you wish, I used the following in my database:

INSERT INTO `sampledb`.`products` (
`id` ,
`product_title` ,
`product_price` ,
`product_img`
)
VALUES  (
NULL , 'Some Awesome Band', '6.99', 'product_images/001.jpg'
), (
NULL , 'Greatest Hits', '8.99', 'product_images/002.jpg'
), ( 
NULL , 'Brilliant Band', '11.99', 'product_images/003.jpg' 
), ( 
NULL , 'Super Duper', '9.99', 'product_images/004.jpg' 
), (
NULL , 'Random Band', '8.99', 'product_images/005.jpg'
), (
NULL , 'Guitar Heroes', '7.99', 'product_images/006.jpg'
), (
NULL , 'Some Randomers', '4.99', 'product_images/007.jpg'
), (
NULL , 'Could Be Anyone', '8.99', 'product_images/008.jpg'
), (
NULL , 'Super Band ', '5.99', 'product_images/009.jpg'
), (
NULL , 'The Amazing Greats', '12.99', 'product_images/010.jpg'
);

The above inserts 10 records, all using one of my product images that I sourced earlier. The important thing here is the link to the product image is valid, as these will show up in our widget.

Step 2 Creating a Simple Layout

For our widget to show up, we need to create a container for it to load into.

Create a file called ‘index.php’ and in between the body tags insert the following code:

<h2>Our Awesome Products</h2>
 
<div id="widget">
 
    <div id="widgetbox"><!--This is where our products will be loaded into -->
 
    </div><!--End WidgetBOX -->
 
</div><!-- End Widget Container -->

We now need to create a new file called ‘styles.css’ and save this in a new folder called ‘css’.

Back in your ‘index.php’ file we now need to link to this file, so in between you head tags add the following:

<link href="css/styles.css" rel="stylesheet" type="text/css" />

Save everything and now open your styles.css file and we can begin to work on the styling for our widget. The final CSS is listed below but it’s important to point out the key elements which are the #widget and the #widgetbox as these are the main container elements. We won’t go through every single CSS styling because you’ll want to create your own, so here is the final CSS file

@charset "utf-8";
/* Master Styles for Product Listing Widget */
 
body {
	background-color: #1F1F1F; 
	color: #FFFFFF; 
	font-family: Arial, Helvetica, sans-serif;
	margin-left: 25px;
	margin-top: 25px;
}
 
 
/*	This is the main container for our widget	*/
#widgetbox {
	margin-top: 0px;
	margin-right: auto;
	margin-bottom: 0px;
	margin-left: auto;
	padding: 10px;
	height: 100%; /*	Clears our floats	*/
}
 
#widget {
	width: 620px;	/*	Sets the master width of our widget	*/
	height: 200px;
	background-color: #333333;
	margin: 0px;
	padding-top: 20px;
	padding-right: 0px;
	padding-bottom: 20px;
	padding-left: 0px;
	border: 1px solid #999999;
}
 
 
#widget ul {
	list-style: none;
	padding: 0px;
	margin: 0px;
}
 
#widget li {
	display: block;
	float: left;
	width: 138px;
	text-align: center;
	margin: 0px;
	padding-top: 10px;
	padding-right: 5px;
	padding-bottom: 10px;
	padding-left: 5px;
}
 
/*	Gives our widget items a nice roll-over effect	*/
#widget li:hover {
	background-color: #666666;
	cursor:pointer;
}
 
/*	Basic styling for the elements within our widget	*/
#products h4 {
	padding: 0px;
	margin-top: 5px;
	margin-right: 0px;
	margin-bottom: 5px;
	margin-left: 0px;
	color: #FFFFFF;
	font-size: 11px;
}
#products img {
	border: 1px solid #FFFFFF;
	margin: 0px;
	padding: 0px;
}
#products p {
	font-size: 11px;
	margin: 0px;
	padding: 5px;
	color: #999999;
	font-weight: bold;
}

If we preview this in the browser you won’t see anything other than a heading we put in earlier. This is because we haven’t loaded any content in. To load the content, we need to create some PHP files that will first make a connection to the database, and then extract what we want to display.

Step 3 Connecting to the Database

In order to display any products, we need a connection to the database. It’s generally good practice to create a separate ‘database.php’ file and require it for the pages that need it, so our first job is to create this file.

We need to connect to the database we created earlier, and so what this file is doing is picking out that database. You might need to change the username/passwords to mirror what your own set up is. It is very important to have a password if you are planning on making this site live on the Internet.

<?php
 
$db_name = "sampledb";		// The database we created earlier in phpMyAdmin.
$db_server = "localhost";	// Change if you have this hosted.
$db_user = 'root';			// Your USERNAME	
$db_pass = ''; 				// Your PASSWORD. Working locally, mine is blank. Change if you plan on deploying.
 
$mysqli = new MySQLi($db_server, $db_user, $db_pass, $db_name) or die(mysqli_error());
 
?>

We’re assigning all our database credentials to variables, which I’ve commened on.

We’re also creating a variable called "$mysqli" and are setting it to a new instance of the "MySQLi" object. We need to pass in four parameters – these are what we’ve assigned above it.:

  • database name
  • the server
  • username
  • password

That’s it for the ‘database.php’ file, we can go ahead and close this after saving it of course.

Step 4 Pulling Out Our Products

Our first job is to create a ‘getProducts.php’ file. This will be the file where we extract the products from the database using a query, and then we will be generating the HTML that will then be loaded into the widgetbox we created earlier.

First of all, we need to require the ‘database.php’ file we created earlier. We do this with the following code:

require 'database.php'; // Allows us to connect to the database.

Our second job is to create a query. Now since we actually need everything we could use the SELECT * from PRODUCTS statement, however some feel that is a lazy way to code and open to attack, therefore I will be picking out all the fields manually.

Our query will look like this:

SELECT id,  product_title, product_price, product_img FROM Products ORDER BY Rand() LIMIT 4

This will pull out the product title, it’s price, it’s image location and limit the results to 4 at random using the Rand() function. This is the meat of this whole tutorial, so if you are planning on using this yourself you will want to make some changes to this query – for example you may wish to select only records where a ‘FEATURED’ field is equal to true.

Here is what our query looks like, alongside the lines that actually execute the query.

//store our query in a variable named $query
$query = "SELECT id, product_title, product_price, product_img FROM Products ORDER BY Rand() LIMIT 4";
 
// run the query and store the results in the $result variable.
$result = $mysqli->query($query) or die(mysqli_error($mysqli));

To recap, we’ve connected to the database by requiring the ‘database.php’ file, we’ve created a MySQL query and then executed it. Now, we need to loop through the results and output the field contents into the elements we want loaded into our widget.

 
if ($result) {
 
  echo "<ul id='products'>"; //Open up an unordered list.
 
  while ($row = $result->fetch_object()) {
 
    $title = $row->product_title;
    $price = $row->product_price;
	$image = $row->product_img;
    $id = $row->id;
 
	// for each product, output a new list-item and then the products image, title and price
	echo "<li><img src='$image' alt='$title' />"; 
    echo "<h4>$title</h4>" ;
    echo "<p>&pound;$price</p></li> \n";	
 
    }
 
	// when the loop is complete, close off the list.
	echo "</ul> \n";
}

I have commented the code so it should be quite easily understood, but essentially what we are doing is

  1. For each row, pull out the title, price, image and id
  2. Store these in a variable
  3. Output as a list-item
  4. Loop through until there are no more records:
  5. Closing the loop, and the unordered list.

That is is it for our ‘getProducts’ file, we can save and close this and now move onto loading this into our widget. The entire code for the ‘getProducts.php’ file should look like this:

<?php
 
require 'database.php'; // Allows us to connect to the database.
 
//store our query in a variable named $query
$query = "SELECT id, product_title, product_price, product_img FROM Products ORDER BY Rand() LIMIT 4";
 
// run the query and store the results in the $result variable.
$result = $mysqli->query($query) or die(mysqli_error($mysqli));
 
if ($result) {
 
  echo "<ul id='products'>"; //Open up an unordered list.
 
  while ($row = $result->fetch_object()) {
 
    $title = $row->product_title;
    $price = $row->product_price;
	$image = $row->product_img;
    $id = $row->id;
 
	// for each product, output a new list-item and then the products image, title and price
	echo "<li><img src='$image' alt='$title' />"; 
    echo "<h4>$title</h4>" ;
    echo "<p>&pound;$price</p></li> \n";	
 
    }
 
	// when the look is complete, close off the list.
	echo "</ul> \n";
}
?>

Step 5 Using jQuery and Loading Content

Right, this is where the magi happens!

What we are going to do now is

  1. Link to to jQuery library
  2. Tell jQuery to load our ‘getProducts.php’ and populate our widget
  3. Tell jQuery to do all that while making some nice transition effects.

Okay so firstly, open up ‘index.php’ and in the header section, link to jQuery.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>

Now that we have linked to the jQuery file, we need to write some jQuery. Our main objective is to fade out and empty the current content from the widget before loading our ‘getProducts.php’ file which in turn re-fills the widget. Once it’s done, we want to fade it in nice and slowly.

We could stop there, and it’d look okay, but in order for it to work correctly we need to set a refresh rate, so what we need to do is create all the above in a function called ‘refreshProducts’. Then later, we can call this function but also specific how often to call it.

<script type="text/javascript">
 
		<!-- start a function that will fade out the current content-->
        function refreshProducts() {
 
           $("#widgetbox").fadeOut(750, function() {
 
		   		<!-- empty the current content and then fetch new data -->
				$("#widgetbox").empty
				<!-- load the getProducts.pho file which will replace all the content -->
                $("#widgetbox").load("getProducts.php", function() {
                    $("#widgetbox").fadeIn();
                });
            });
 
        };
 
		<!-- initiate the function above -->
    	$(function(){
    		refreshProducts();
 
			<!-- set the interval for refreshing, default set to 6.5 seconds -->
    		var int = setInterval("refreshProducts()", 6500);
 
    	});
    </script>

We’re finished!

That’s it! You can now preview in the brower and wait, and you should see your content fade out every 6.5 seconds before re-appearing with new content! The uses for this range from news tickers, product listings, portfolio projects as well as even a rotating advertising wall.

The final code for the ‘index.php’ page looks like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Product Listing Demo</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script> 
<link href="css/styles.css" rel="stylesheet" type="text/css" />
 
 <script type="text/javascript">
 
		<!-- start a function that will fade out the current content-->
        function refreshProducts() {
 
           $("#widgetbox").fadeOut(750, function() {
 
		   		<!-- empty the current content and then fetch new data -->
				$("#widgetbox").empty
				<!-- load the getProducts.pho file which will replace all the content -->
                $("#widgetbox").load("getProducts.php", function() {
                    $("#widgetbox").fadeIn();
                });
            });
 
        };
 
		<!-- initiate the function above -->
    	$(function(){
    		refreshProducts();
 
			<!-- set the interval for refreshing, default set to 6.5 seconds -->
    		var int = setInterval("refreshProducts()", 6500);
 
    	});
    </script>
 
</head>
 
<body>
 
    <h2>Our Awesome Products</h2>
 
    <div id="widget">
 
        <div id="widgetbox"><!--This is where our products will be loaded into -->
 
        </div>
        <!--End WidgetBOX -->
 
    </div><!-- End Widget Container -->
 
</body>
</html>

The actual widget looks like the below, and can easily be customised by amending the CSS to match your own site.

We’ve created quite a bit, yet it all seemed quite simple didn’t it? There are plenty of ways to move this forward, and some of you will be keen to include links to the products themselves and that is why I included the list-item hover effect. Some other modifications/improvements would me:

  • Include an admin section to insert new records (including an image resizer…)
  • Lightbox functionality to bring up full details of the product.
  • Secure database connections
  • Plenty more…



32

Comments
  • thelastpulse says:

    Why do you guys always release the coolest articles when I’m at work and can’t read it? Can’t wait to read this on my break.

  • Jack F says:

    Awesome tutorial! Thanks very much!

  • Luis says:

    No live demo?

  • Braden Keith says:

    Great article, I’m implementing this currently. All I see now adays is MySQLi – why is that? Have standards changed? What’s advantages here and how do I make sure my database supports it? Do I need to do anything to existing table?

    I’d appreciate any direction you can offer. Thanks again Jeff.

  • iwpdesign says:

    very very nice. thanks

  • Nice! But where is the demo?

  • clerik says:

    Really Nice! and please explain the modifications/improvements too :)

  • Jeff Adams says:

    Hi Guys

    Thanks for the feedback, it’s much appreciated.

    For a demo please visit http://www.jeffadams.co.uk/product_listing/

    I did provide the source files for a demo however obviously I can’t control what gets put up on here as a demo (to be honest I’m just grateful I got it published :-D )

    Enjoy.

    Jeff

  • Jeff Adams says:

    @clerik – if there’s enough requests for those extra functions, I’ll definately consider doing a “Part 2″ of this tut.

  • fallsemo says:

    Hoverintent?

  • Kayla says:

    I was looking to do something EXACTLY like this a few months ago, but could never get it completed or kept running into problems. This is perfect…thank you for this!

  • Ariyo says:

    SWEET. That is exactly what I needed. Thanks a lot. I’ll be waiting for part 2.

  • Hi Jeff

    This is a cool tutorial. I have thought of doing such a thing long before, but have never managed to get it done, may be the lack of motivation. This jquery is an amazing tool i have ever seen.

    Thanks very much Jeff.

  • mehdi says:

    Nice Tutorial And Tanks ;)

    i Found a Misspell product_price DECIMAN (6, 2) must be DECIMAL ;)

  • sergej says:

    Thank you! very usefull

  • You should stop the fadein/fadeout effect when a user’s mouse is hover a product so he have plenty of time to decide and click. In the demonstration this isn’t a real issue ’cause you don’t have a lot of products. Nice Tutorial Though ;)

  • shin says:

    Which chmod do you recommend for database.php?

    640 or 644?

  • kemo says:

    Shin: 640 should be enough!

  • shin says:

    I think the line $(“#widgetbox”).empty should be $(“#widgetbox”).empty();

  • Jelmer says:

    Very cool, thanks!

  • AREA 1 says:

    Nice tutorial, it’s very useful. Thanks!

  • Jeff Adams says:

    Hey Guys.

    Thanks for all the feedback.

    I’m not sure on the techie questions about ports and what not though I gather some of you already answered.

    @Samuel Garneau – that’s a very good point, in fact for Part Two it became an issue which I’ve yet to work around. When I get time to finish it off you can expect that to be sorted – cheers.

  • Akonkagva says:

    Hello

    I have watched your guide it’s very good.
    I have a question: how can we make with jQuery such upload bar as it is on the http://www.failiem.lv/ site when we click on Saglabat failus and specify the file. It shows how much of the file has been uploaded, how to do the same thing with jQuery ?

  • Thanks wonderful tuts!

  • pepeguayo says:

    I would love to see those improvements in a 2d part too.. this is great..

  • Tracey says:

    This works beautifully in Firefox and I can see many applications for it. However, in IE7 the images don’t update. Is this happening for anyone else?

  • Bolix says:

    Very nice work, I noticed the RAND() function will sometimes rotate the same content 2 or 3 times in a row. Will sequential rotation be considered in part 2 ?
    I can’t wait for part 2… When I grow up I would like to be a coder like you :)

  • Manish says:

    Hi Tracey

    Same here. it is not updating the images in IE 7.
    I tried and copied the code on my local machine. Still same result :(

    Please provide an update to run it on any browser

  • caminomaster says:

    I suggest you to insert a demo link near the ‘download sourde’ link.
    We also need it working in ie7; if I got success on it, i’ll tell you.

  • caminomaster says:

    I’ll get it :D
    just add a random var in the load url:

    old:
    $(“#widgetbox”).load(“getProducts.php”, function() {

    new:
    $(“#widgetbox”).load(“getProducts.php?a=”+Math.random(), function() {

    this prevent browser (ie in this case) from loading cached file (getProducts.php)

    that’s all :D

  • Jeff Adams says:

    Nice job caminomast. I have to admit that testing locally it works but caching is an issue for IE7 so cheers for that!