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 = oot;   // 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 wouldatabase.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> "; 
 
    }
 
 // when the loop is complete, close off the list.
 echo "</ul> ";
}

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 wouldatabase.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> "; 
 
    }
 
 // when the look is complete, close off the list.
 echo "</ul> ";
}
?>

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…