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.
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.
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.
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.
rotating_gallery.mdbMicrosoft Access database
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 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 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/
<!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>
<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 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>
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.
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
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.
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
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.
count variable will also be incremented, when it reaches the value of our
total_items variable, we are at the end.
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
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.
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
endrow attributes provide. However this could also be accomplished with
We set the
endrow attributes to our
<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
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 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.
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
<cfelse><!--- if start_item LT end_item --->
The code below is identical to the code above, except for the
endrow values in the
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
<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
</cfif><!--- close if start_item LT end_item ---></cfoutput>
Close our page, and we are finished!
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
"d". See the ColdFusion documentation for a complete discussion of the
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
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
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!
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.
This article originally appeared at http://www.sitepoint.com