Main Page Content
Build A Rotating Gallery With Coldfusion
The Problem:
My client wanted a database-driven picture gallery, but was worried that it would grow out of control, and didn't want to ugly up a page with hundreds of thumbnails. They wanted to show only twelve pictures at a time, and be able to add new items at will. 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.
The Solution:
Only show a certain number of gallery items at a time, rotating through the whole set at some time interval.
This article will show you how to make a database-driven picture gallery that only shows a certain number of pictures at a time, regardless of how many records are currently in the database. Each day, the gallery code rotates one old item out, and one new item in. This offers great flexibility for both you the developer, and your clients, as adding new items is as easy as adding records to the database.
Shake that Mody
If we have a large number that increments daily, and we divide it by the number of records in our gallery database, the remainder will tell us the record to start 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. Because our ticker will be steadily incrementing (if the sun rises), our remainder will also increment at the same rate. Using this method, 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. 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 from here: http://www.macromedia.com/software/coldfusion/downloads/.
You'll need this ZIP file.
It contains:
rotating_gallery.cfmColdFusion templaterotating_gallery.mdbMicrosoft Access database- 26 thumbnails (
*.gif)
The database contains only one table, named 'gallery'.
The fields in it are:
ID(autoincrement, primary key)img_thumb- the filename of the image (12.gif)img_x- image widthimg_y- image heightimg_title- text to display with this image
There are 26 records, one for each of the 26 thumbnails.
Unzip to a folder mapped to your 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 'rotating_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: http://www.morgankelsey.com/code/rotating_gallery/
Get Out Your Airgun
Let's take a look at 'rotating_gallery.cfm'.
We start like any other typical HTML page.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/1999/REC-html401-19991224/loose.dtd"><html> <head> <title>Rotating Gallery</title> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> </head><body>
We run a simple query to retrieve all the records from the gallery table.
<cfquery name="qGallery" datasource="rotating_gallery" cachedwithin="#CreateTimeSpan(0, 0, 10, 0)#"> SELECT ID , img_thumb , img_width , img_height , img_title FROM gallery ORDER BY ID;</cfquery>
Initialize some variables
<!--- initialize variables ---><cfset start_day = DateDiff("y","01/01/2000",Now())><cfset total_items = 12><!--- total items to display ---><cfset start_item = (start_day MOD qGallery.recordcount) + 1><cfset end_item = start_item + (total_items - 1)><cfset columns = 4><cfset col_tick = 0><cfset count = 0><cfif end_item GT qGallery.recordcount>
<cfset end_item = end_item - qGallery.recordcount></cfif>The start_day variable 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.
Set total_items to the total number of items you'd like displayed on the page. We'll use this variable to determine the stopping point.0
The start_item variable 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 recordcount 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.
The variable end_item tells us 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.
Set the columns variable to the desired number of table columns.
We'll increment the col_tick variable as we output the table cells in each row.
The count variable will also be incremented, when it reaches the value of our total_items variable, we are at the end.
The if statement is there to deal with the overlap from the last record to the first. For instance, 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.
Now our query has been run and all necessary variables initialized. On with the fun part, building the gallery!
We start with a table, and open a row.
<div align="center"><table width="70%" border="0" cellpadding="0" cellspacing="0"> <tr>
We open a <cfoutput> tag, since we now need to print our variable values to the browser.
<cfoutput>
If our end_item value is higher than the start_item, we can perform a very straightforward output. If not, we must deal with the overlap from the last record to the first record.
<cfif start_item LT end_item>
I'm using the <cfloop> tags below for the clarity that the startrow and endrow attributes provide. However this could also be accomplished with <cfoutput> tags.
We set the startrow and endrow attributes to our start_item and end_item values.
<cfloop query="qGallery" startrow="#start_item#" endrow="#end_item#">
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.
<cfset col_tick = IncrementValue(col_tick)> <cfset count = IncrementValue(count)>
At last, we write our <img> tag to the browser within a <td> tag.
<td width="40"><img src="#img_thumb#" height="#img_height#" width="#img_width#" alt="#img_title#"></td>
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.
<cfif count EQ total_items> </tr></table><cfelseif col_tick EQ columns> </tr><tr><cfset col_tick = 0></cfif>
Close the loop.
</cfloop>
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.
<cfelse><!--- if start_item LT end_item --->
The code below is identical to the code above, except for the startrow and endrow values in the <cfloop> tag.
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. We also don't need the <cfif count EQ total_items> statement, since 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)> <td width="40" ><img src="#img_thumb#" height="#img_height#" width="#img_width#" alt="#img_title#"></td> <cfif col_tick EQ columns> </tr><tr><cfset col_tick = 0> </cfif></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)> <td width="40" ><img src="#img_thumb#" height="#img_height#" width="#img_width#" alt="#img_title#"></td> <cfif count EQ total_items> </tr></table> <cfelseif col_tick EQ columns> </tr><tr><cfset col_tick = 0> </cfif></cfloop>
Now we close our if statement and out <cfoutput> block.
</cfif><!--- close if start_item LT end_item ---></cfoutput>
Close our page, and we are finished!
</div></body></html>
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())>
"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 "d". See the ColdFusion documentation for a complete discussion of the DateDiff() function.If you have a very large recordset, and only want to show a portion, the initial query that returns all the gallery records may create too much overhead. In that case, you may want to run an initial query that pulls only the keys to determine the recordcount, and calculate the startitem and enditem values. Then run a second query that only retrieves the records you are interested in. The overlap from the last record to the first will rear its ugly head here too, you may choose to run a UNION query, or two queries and reassemble them with ColdFusion's QueryAddRow() and QuerySetCell() functions.
The contents of our loops are all very similar, if we leave in the if statements we omitted during the second set of outputs, 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.
About This Article
This article originally appeared at http://www.sitepoint.com
