Online stores can sell goods and services, but one of the frequent uses of an online store is to sell goods that are downloadable, such as software, music, videos, and pictures. There are many ways to dynamically supply the files to your customers, but this article will show one way that is secure and also saves on bandwidth because it will use Zip files for the delivery. The article will use ColdFusion and SQL Server, but the principles will apply to any database because I am assuming a generic e-store database. The article uses a CFC that was written by fellow CMXer Arman Danesh, and available here:

http://www.communitymx.com/abstract.cfm?cid=7B115

You should read that article and familiarize yourself with the CFC before attempting this tutorial.

Setting up the Database

I'm going to assume you already have a database that contains the following:

• A Users table, with username, password, and userid
• A Products table, with the file location of the downloadable files stored.
• An Orders table that contains orders processed.
• An OrderDetails table that contains individual items for each order.

Every e-store will have these tables, and every implementation will be slightly different. The following basic scripts will generate minimal sample tables required for the tutorial for SQL Server:

DROP database teststore
CREATE DATABASE teststore
GO

use teststore
GO

CREATE TABLE OrderDetails (
OrderDetailsID int IDENTITY (1, 1) NOT NULL ,
OrderID int NULL ,
ProductID int NULL ,
ProductPrice decimal(18, 0) NULL
)
GO

CREATE TABLE Products (
ProductID int IDENTITY (1, 1) NOT NULL ,
ProductName varchar (50) NULL ,
ProductFilename varchar (255) NULL ,
ProductPrice decimal(18, 0) NULL
)
GO

CREATE TABLE Orders (
UserID int NULL ,
OrderID int IDENTITY (1, 1) NOT NULL ,
OrderDate datetime NULL ,
OrderTotal decimal(18, 0) NULL
)
GO

CREATE TABLE Users (
UserID int IDENTITY (1,1) NOT NULL ,
Username varchar (50) NULL ,
UserPassword varchar (50) NULL,
Email varchar(128) NULL
)
GO

INSERT Users (Username, UserPassword, Email)
VALUES ('tom','password','tom@communitymx.com')
INSERT Users (Username, UserPassword, Email)
VALUES ('jack','password','jack@yadayadayada.com')

INSERT Products (ProductName, ProductFilename, ProductPrice)
VALUES ('TestProduct','test.txt,20)
INSERT Products (ProductName, ProductFilename, ProductPrice)
VALUES ('TestProduct2','test2.txt,20)
INSERT Products (ProductName, ProductFilename, ProductPrice)
VALUES ('TestProduct3','test3.txt,20)

INSERT Orders (UserID, OrderDate, OrderTotal)
VALUES (1, getdate(), 20)
INSERT Orders (UserID, OrderDate, OrderTotal)
VALUES (2, getdate(), 40)

INSERT OrderDetails (OrderID, ProductID, ProductPrice)
VALUES (1, 1, 20)
INSERT OrderDetails (OrderID, ProductID, ProductPrice)
VALUES (2, 2, 20)
INSERT OrderDetails (OrderID, ProductID, ProductPrice)
VALUES (2, 3, 20)

Application logic

The logic is as follows: a user will go to the site and view a list of products from the Products table that are downloadable. He will make the purchase and receive a notification via email to download his products. The Order will be stored in the Orders table, and the line items of the order (the products) will be stored in the OrderDetails table. The user will log into the site and a page will display all downloadable merchandise that exists for that user. He will choose the items he wishes to download and click a submit button. The files will be zipped into a package and a download prompt will ask the user to save the file.

This approach has several advantages:

• Zip files will give your users shorter download times.
• Products can be downloaded at any time, however you can also put time constraints or download attempt limits on your products as well.
• User can download one or more files at a time, and doesn't have to choose all products (if he is a frequent purchaser).
• Files are not stored in a location that can be hacked into or downloaded (they can be anywhere on the hard drive of the web server, with read permissions turned off for the web server.)
• Zip filenames are random so that a hacker cannot guess locations or filenames.
• Zip files can alternatively be emailed to the end user

The entire operation will use only one file, which is named downloads.cfm. The page contains a form which will post back to the page and execute the code which will zip the files and allow the user to download them. The page contains two submit buttons: one for downloading the file and one for emailing the file. The code is below, commented inline:

<cfsilent>
<!--- Set a userid for demo purposes --->
<cfset session.userid = 1>

<!--- Get the current folder for page operations --->
<cfset currentDirectory = getDirectoryFromPath(getTemplatePath())>

<!--- If the form is submitted, begin the download process --->
<cfif IsDefined ("form.cmx_download") OR IsDefined("form.cmx_email")>

  <!--- Create a zip filename using the current time in milliseconds --->
  <cfset cmx_zipfilename = "cmx_#gettickcount()#.zip">

<!--- Set a variable to the incoming checkbox string which is a list of filenames --->
  <cfset cmx_filelist="#form.cmx_multi_download#">

<!--- Loop through the list of checked filenames --->
  <cfloop index="files"
   from="1"
   to="#ListLen(cmx_filelist)#">
    <cfset fullfile= ListGetAt(cmx_filelist, files)>

    <!--- Set the full location of the file into the file list in place of the filename only --->
    <cfset cmx_filelist = ListSetAt(cmx_filelist, files,"c:\myPrivateFolder\#fullfile#")>
  </cfloop>

  <!--- Instantiate the cmx Zip component --->
  <cfobject name="zipObj" component="zip">

  <!--- Create the zip file, add the list of files to it, and close it --->
  <cfset zipObj.init("#currentDirectory#\#cmx_zipfilename#")>
  <cfset zipObj.addFiles(#cmx_filelist#)>
  <cfset zipObj.close()>

<!--- Optionally, send yourself or the site admin an email notifying them of the download --->
  <cfmail to="me@mydomain.com" from="me@mydomain.com"
   subject="download for UserID #Session.UserID#"
   type="html">
   <cfloop list="#cmx_filelist#" index="i">
#i#<br>
   </cfloop>
  </cfmail>

<cfif IsDefined ("form.cmx_download")>
<!--- Download button was clicked, so stream the file as a download --->

<cfheader name="Content-Disposition" value="inline; filename=#cmx_zipfilename#">
<cfcontent type="application/unknown" file="#currentDirectory#\#cmx_zipfilename#">
<cfelse><!--- Email button was clicked, so attach file and email to the user --->
<!--- First, grab the email address for the user from the database--->
<cfquery name="rsGetEmail" datasource="testdatabase">
SELECT Email FROM Users
WHERE UserID = #Session.UserID#
</cfquery>

<!--- Mail the file to the user --->
<cfmail to=#rsGetEmail.Email#
from="me@mydomain.com"
subject="Your files #form.cmx_multi_download#"
mimeattach="#currentDirectory#\#cmx_zipfilename#">
Your files #form.cmx_multi_download# are attached in a zip file.

Thanks!
</cfmail>
<cflocation url="thankyou.cfm"><!--- Redirect to a "thankyou" page --->
</cfif>
</cfif>

<!--- Display portion of page: get the customer's order details for download list --->
<cfquery name="getCustomerOrders" datasource="testdatabase">
select o.OrderID,
od.OrderDetailsID,
p.ProductName,
p.ProductFilename
FROM Orders o
INNER JOIN OrderDetails od
ON o.OrderID = od.OrderID
INNER JOIN Products p
ON p.ProductID = od.ProductID
WHERE o.UserID = '#Session.UserID#'
</cfquery>

<!--- Grab all files in private folder --->
<cfdirectory action="list"
directory="c:\myPrivateFolder\"
name="tempDirectory">

<!--- Join the two queries using a query-of-query so that ONLY the downloads for the user are listed --->
<cfquery name="multidownload" dbtype="query">
SELECT *
FROM tempDirectory, getCustomerOrders
WHERE UPPER(tempdirectory.name) = UPPER(getCustomerOrders.ProductFilename)
</cfquery>
</cfsilent>
<!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=iso-8859-1" />
<title>Downloads</title>
<style>
/* Set up some styles for the page */
th{ background-color:#666; padding:5px; color: #FFF; }
td{padding:5px;}
table {padding:0; border-collapse:collapse;}
th a:link, th a:hover, th a:active, th a:visited{ color: #FFF; }
.even {background-color:#FFF;}
.odd {background-color:#CCC;}
.right{text-align:right;}
.center{text-align:center;}
input {width:150px;}
</style>

<script type="text/javascript">
var boxesChecked = true;
function toggleCheckboxes(){ // toggle checkboxes on and off
  var countBoxes = document.form1.cmx_multi_download.length;
  if(!countBoxes){
    document.form1.cmx_multi_download.checked = boxesChecked;
  }else{
    for (var i=0; i<countBoxes ;i++){
      document.form1.cmx_multi_download[i].checked = boxesChecked;
    }
  }
  boxesChecked = !boxesChecked;
}
</script>
</head>

<body>
<h1>Download Your Files</h1>
<cfif multidownload.RecordCount GTE 1>
<div>
<form name="form1" method="post"
  action="<cfoutput>#cgi.script_name#</cfoutput>">

<!--- Put the file list into a table --->
<table>
  <tr>
    <th>Product</th>
    <th>Date Modified</th>
    <th>Size</th>
    <th>Download<br /><a href="javascript:toggleCheckboxes()" >select all</a></th>
  </tr>

<cfset alternate = 0><!--- Variable for alternate colored rows --->
<cfoutput query="multidownload">
  <cfset alternate = alternate + 1>
  <cfif multidownload.Name NEQ "." AND multidownload.Name NEQ ".."><!--- Don't show directory --->
  <tr class="#IIF(alternate MOD 2, DE('odd'), DE('even'))#">
    <td>#multidownload.ProductName#</td>
    <td class="right">#DateFormat(multidownload.DateLastModified,"M/D/YYYY")#</td>
    <td class="right">#multidownload.Size#</td>
    <td class="center"><input type="checkbox" name="cmx_multi_download" value="#multidownload.Name#"></td>
  </tr>
  </cfif>
</cfoutput>
</table>

<!--- Show submit buttons, one for download, one for email --->
<table>
  <tr>
    <td>Download All Checked Files: </td>
    <td><input type="submit" name="cmx_download" value="Download Now">
      <input type="hidden" name="cmx_multi_download_required" value="You must check at least one box ;-)">
    </td>
  </tr>
  <tr>
    <td>Email All Checked Files:</td>
    <td><input type="submit" name="cmx_email" value="Email the files"></td>
  </tr>
</table>
</form>
<cfelse>
<p>There are no downloads at this time. If this message is in error, <a href="mailto:me@mysite.com">contact us</a>.</p>
</cfif>
</div>
</body>
</html>

<!--- Delete zip files over 1 hour old.
         This is because there is no way in CF to delete a file after it has been downloaded --->
<cfdirectory action="list"
 directory="#currentDirectory#"
 name="zipfiles">
<cfloop query="zipfiles">
  <cfif Find(".zip",zipfiles.name) And DateDiff("n",zipfiles.datelastmodified, now()) GT 60>
    <cffile action = "delete"
     file = "#currentDirectory#\#zipfiles.name#">
  </cfif>
</cfloop>

Making it work

The following things need to be in place before attempting to make this procedure work:

1. Database must be in place, with the sample data provided.
2. File locations are hard-coded in downloads.cfm as "c:/myPrivateFolder". Place the temporary test files included in the zip package at the end of this article into that directory (test.txt, test2.txt, and test3.txt). You can also change the folder name to something that is suited for your machine, but also make sure you change the references to that folder in the downloads.cfm file.
3. The zip.cfc file must be in the directory where the downloads.cfm file is served from.

With that in place, you should be able to browse the downloads.cfm page and download the files for the user (UserID 1 is hard-coded into the file.) Change the user to UserID 2 and you'll see a different set of downloads for that user. With your own authentication system, you can also create a login page that directs the user to this page, and also restrict access to this page to people who are logged in. The downloads.cfm page should easily fit into any ColdFusion-based e-store by changing the queries to match the field names for your own e-store. Figure 1 shows the page in use.

Figure 1: The completed downloads page

Conclusion

Serving files to authenticated users can be done in a variety of different ways, but the method shown in the article is both efficient and secure.