Build a Rotating Gallery in ColdFusion
About the Author
Morgan Kelsey
Morgan has a degree in jazz piano but also likes food and money, so he writes code. Morgan lives in western Ct, USA with his wife and children. He runs Kelsey Print and Web.
By: Morgan Kelsey
March 10th, 2002
They came, they bought... but did they come back? An ecommerce site’s most important visitors are the ones that actually give up the plastic, and once you’ve got a live one on the line, you need them to come back -- and to bring their friends too. How? Well, shoppers are people, and people like to feel a part of something. Why not make your customers a part of your site by creating a picture gallery for them to populate?
I know what you’re thinking: galleries like this tend to grow too big, and when they do, they start to lose their appeal. I had a client recently with an interesting solution: make the gallery rotate.
Sit in Your Car and Rotate
My client wanted their gallery to only show twelve pictures at a time, and rotate one old item out, and one new item in, each day. This client sells street rod parts and kits, and their customers love to send in pictures of themselves at the throttles of their T Buckets, popping wheelies in the company parking lot and scaring grandmas. Here’s an example [1] that shows it in action. This example is set to rotate once per second, so refresh your browser a few times to see it in action.
So I was driving around the twisty backroads where I live, listening to Eric Dolphy, when I figured out how I was going to get this to work. I was going to use a table in the database, so the client could add pictures through an admin section. We needed a way to shift forward one record every day, regardless of the recordcount. I could see, too, there was going to be an overlap from the last record to the first that would need smoothing out.
Shake that Mody
It occurred to me that if I had a large number that incremented daily, I could divide it by the recordcount to calculate which record to begin with. For instance, if we count the number of days since Jan 1, 2000 (let’s call it the ticker), and we then divide the ticker by the recordcount, the remainder will always be between 0 and one less than the recordcount. Becuase our ticker will steadily increment (if the sun rises), our remainder will also increment at the same rate. If we wanted, we could make the gallery rotate every hour, minute, or second by adjusting how the ticker is calculated.
Getting Started
This example assumes you have a basic understanding of HTML and the ColdFusion language. If you are just starting out with ColdFusion, check out Chris Beasley's excellent 3-part series here [2].
ColdFusion Server 5 is now available free to developers; the trial version becomes a single-user license when the 30-day trial expires. You can download it here [3].
You'll need this ZIP file [4].
It contains:
- rotating_gallery.cfm ColdFusion template
- sitepoint_gallery.mdb Microsoft Access database
- 26 thumbnails (.gif)
The database contains only one table, named 'gallery'. The fields in it are:
- ID (autoincrement, primary key)
- img_name – the filename of the image (12.gif)
- img_x – image width
- img_y – image height
- img_title – text to display with this image
There are 26 records, one for each of the 26 thumbnails.
Unzip to a folder mapped to the ColdFusion server. Hit Alt-M in ColdFusion studio to open the mappings window, read the ColdFusion documentation if you need help. You'll also need to create a System DSN named 'sp_gallery' that points to the access .mdb file. Again, if you need help, try the documentation.
Point your browser to the ‘rotating_gallery.cfm’ file. You should see a small gallery of 12 numbered images, similar to the example from above [5].
Get Out Your Airgun
Let’s take a look at ‘rotating_gallery.cfm’.
<cfprocessingdirective suppresswhitespace="Yes">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Rotating Gallery</title>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;
charset=ISO-8859-1">
</head>
<body>
Start the page like any other, the cfprocessingdirective tag helps to suppress extra white space generated by the loops below. This makes for a smaller file when the template is parsed and sent to the browser, speeding up the visitor’s experience.
<cfquery name="qGallery" datasource="sp_gallery"
cachedwithin="#CreateTimeSpan(0,0,10,0)#">
SELECT ID,pic_thumb,pic_x,pic_y,pic_title FROM gallery
ORDER BY ID;
</cfquery>
We run a simple query to retrieve all the records from the gallery table.
<!--- initialize variables --->
<cfset start_day = DateDiff("y","01/01/2000",Now())>
This is our ticker value, which we need incremented every day. We use the DateDiff function to tell us how many days ("y") have passed since 01/01/2000 and today, and assign it to the variable 'start_day'. This value must be larger than your recordcount, so push the date back if you have a large number of items to cycle through.
<cfset total_items = 12>
Set total_items to the total number of items to display on the page. We’ll use this variable to determine the stopping point.
<cfset start_item = (start_day MOD qGallery.recordcount) + 1>
The variable start_item will hold the value of the item in the first position. We find it by adding one to the remainder of the start_day variable divided by the record count from our query. To find the remainder (or the modulus) of a division equation, we employ the MOD math function. In ColdFusion (5 MOD 2) returns 1, the remainder of 5 / 2. The modulus will always be an integer lower than the divisor. If you think about it, any number divided by 4 will always produce a remainder (or modulus) of 0, 1, 2 or 3.
<cfset end_item = start_item + (total_items - 1)>
We also need to know which record to stop at, we can find it easily by subtracting one from the 'total_items' variable and adding that value to the 'start_item' variable. We assign this result to the variable 'end_item'.
<cfif end_item GT qGallery.recordcount>
<cfset end_item = end_item - qGallery.recordcount>
</cfif>
What happens if start_item is record 20, and there are only 23 records? When we add 11 we get 31! We'll need to deal with the overlap from the last record to the first. We still need to know what record to stop at, so if the 'end_item' variable is greater than the recordcount, we subtract the recordcount from the 'end_item' value to find the correct 'end_item'.
<cfset columns = 4>
Set the 'columns' variable to the desired number of table columns.
<cfset col_tick = 0>
We'll increment the 'col_tick' variable as we output the table cells in each row.
<cfset count = 0>
Count will also be incremented, and when it reaches the value of our total_items variable, we're at the end.
Now our query has been run and all necessary variables initialized. On with the fun part: building the gallery!
<div align="center">
<table width="70%" border="0" cellpadding="0" cellspacing="0">
<tr>
We start with a table, and open a row.
<cfif start_item LT end_item>
If our end_item value is higher than the start car, we can perform a very straightforward output. If not, we must deal with the overlap from the last record to the first record.
I'm using CFLOOP below for the clarity that the startrow and endrow attributes provide. However this could also be accomplished with CFOUTPUT.
<cfloop query="qGallery" startrow="#start_item#"
endrow="#end_item#">
We set the startrow and endrow attributes to our start_item and end_item values.
<cfset col_tick = IncrementValue(col_tick)>
<cfset count = IncrementValue(count)>
We’ll keep track of the total number of items output with the count variable, when it matches total_items, we are finished. The col_tick variable is used to count the table columns, when col_tick equals columns we need to start a new table row and reset col_tick to zero.
<cfoutput>
We open a cfoutput tag, as we know need to print our variable values to the browser.
<td width="40"><img src="#pic_thumb#" height="#pic_y#"
width="#pic_x#" alt="#pic_title#"></td>
At last, we write our img tag to the browser.
<cfif count EQ total_items>
</tr></table>
<cfelseif col_tick EQ columns>
</tr><tr><cfset col_tick = 0>
</cfif>
Here we compare our count variable to the total_items variable. When they match, we are through and need to close our table. If not true, we check to see if our col_tick variable matches our columns variable. If they match, we need to close our current table row and start a new one.
</cfoutput>
Close our cfoutput tag.
</cfloop>
Close the loop.
<cfelse><!--- if start_item LT end_item --->
If our end_item value is less than the start_item value, we'll need to deal with the overlap from the last record to the first. We can do this by outputting the query results twice. Once to the end of the recordset, and then from the beginning of the recordset to the end_item value.
The code below is identical to the code above, except for the startrow and endrow values in the cfloop tags.
For the first output we set startrow to the start_item value as above, but this time we set the endrow value to match the recordcount from our query. Also, we don’t need the <cfif count EQ total_items> statement, as we know this loop will not complete the gallery.
<cfloop query="qGallery" startrow="#start_item#"
endrow="#qGallery.recordcount#">
<cfset col_tick = IncrementValue(col_tick)>
<cfset count = IncrementValue(count)>
<cfoutput>
<td width="40"><img src="#pic_thumb#" height="#pic_y#"
width="#pic_x#" alt="#pic_title#"></td>
<cfif col_tick EQ columns>
</tr><tr><cfset col_tick = 0>
</cfif>
</cfoutput>
</cfloop>
The second output will be from the start of the recordset to the end_item value.
<cfloop query="qGallery" startrow="1" endrow="#end_item#">
<cfset col_tick = IncrementValue(col_tick)>
<cfset count = IncrementValue(count)>
<cfoutput>
<td width="40"><img src="#pic_thumb#" height="#pic_y#"
width="#pic_x#" alt="#pic_title#"></td>
<cfif count EQ total_items>
</tr></table>
<cfelseif col_tick EQ columns>
</tr><tr><cfset col_tick = 0>
</cfif>
</cfoutput>
</cfloop>
</cfif><!--- close if start_item LT end_item --->
Now we close our if statement.
</div>
</body>
</html>
</cfprocessingdirective>
Close our page, and we're finished!
Considerations
This code could just as easily be applied to blocks of text or banners. If you'd like it to rotate at an interval other than daily, adjust this line:
<cfset start_day = DateDiff("y","01/01/2000",Now())>
The "y" returns the number of days between 01/01/2000 and the present. To make the gallery rotate on the hour, change the "y" to an "h". See the ColdFusion documentation for a complete discussion of the DateDiff function.
The contents of our loops are all very similar, if we leave in the if statements we omitted during the second sets of output, we could put this code into an include file.
Add some more fields to the database, and you could have the thumbnails lead to larger pics, accompanied by descriptive paragraphs.
With some modification, this can become a great custom tag! I've used it to rotate on sale items for other sites. Feel free to modify the code, let me know what you come up with!
Conclusion
We've made our client happy, and created a better gallery. The visitor's thrill of having their picture on the Website is only augmented by the fact that it will be there for a set time, disappear for a spell, and then return later. If you had just spent one year and two gazillion bucks building a space-tube chassis 1923 T-Bucket with a screaming powerplant and Weber stacks, wouldn't you want to show your friends the site you posted it to? The customers are now performing targeted marketing for the site owners!
Now go forth and multiply--or rather, divide and conquer.
[1] http://www.morgankelsey.com/sitepoint/rotating_gallery.cfm
[2] http://www.webmasterbase.com/article/310
[3] http://www.macromedia.com/software/coldfusion/downloads/
[4] http://www.webmasterbase.com/examples/kelsey_sitepoint_gallery.zip
[5] http://www.morgankelsey.com/sitepoint/rotating_gallery.cfm