Untitled Document In the past few weeks , I found myself looking for a tutorial on how to build an e-commerce shopping cart. Though I found a few out there, they didn’t exactly teach me what I needed to know. The actual bottom line of how a shopping cart actually works! So I decided that I would create a tutorial on the subject for people to use. (I learned it the hard way, but hopefully with my tutorial you wont have to)

The tutorial consists of two things,

1) A database
2) ColdFusion Templates that actually create the shopping cart system.

The first thing that I will explain will be the database, now keep in mind that this is just to show how the shopping cart system works, and should not be a complete product for your needs. By this I mean that you will definitely have to customize this solution.

Let’s begin by reviewing what the database looks like. The database contains 5 tables:

  • Categories
    This is the table that contains what categories your product will belong to, so you can have multiple categories such as (Books, Movies, Software, etc).
  • Clients
    This table contains information about your clients such as (Full Name, Email, etc).
  • Products
    This table contains information about your products, such as (Product Name, Product Price, etc).
  • Purchased Products
    This table will contain the information for products purchased by your clients.
  • WebCart
    This is a temporary table that contains the information about what the client currently has in their shopping cart (this is the
    shopping basket)

A full breakdown of the database tables are as follows:

Categories:
int_CategoryID int IDENTITY
txt_Category nvarchar(100)
bit_Active bit

Clients:
int_ClientID int IDENTITY
txt_ClientName nvarchar(100)
txt_ClientEmail ntext(16)
txt_Username nvarchar(30)
txt_Password nvarchar(30)
bit_Active bit
int_CFID int
txt_CFTOKEN nvarchar(100)

Products:
int_ProductID int IDENTITY
txt_ProductTitle nvarchar(100)
txt_ProductDescription ntext(16)
flt_ProductPrice float
bit_Active bit
int_CategoryID int

PurchasedProducts:
int_saleCart int IDENTITY
int_ClientID int
int_ProductID int
int_Quantity int

WebCart:
int_CartID int IDENTITY
int_ProductID int
int_Quantity int
int_ClientID int

I originally created the database on Microsoft SQL Server 2000, so I am therefore going to include the creation scripts for you to easily mimic what I have done. You can skip this section if you don't know what it is, just create a table that looks like the legend above.:

<!--- --------------------------- SQL Create Script Begins ---------------------------- --->
if exists (select * from sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Categories]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Clients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Clients]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Products]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[PurchasedProducts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PurchasedProducts]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[WebCart]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WebCart]
GO

CREATE TABLE [dbo].[Categories] (
[int_CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[txt_Category] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bit_Active] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Clients] (
[int_ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[txt_ClientName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txt_ClientEmail] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txt_Username] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txt_Password] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bit_Active] [bit] NULL ,
[int_CFID] [int] NULL ,
[txt_CFTOKEN] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
[int_ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[txt_ProductTitle] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[txt_ProductDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[flt_ProductPrice] [float] NULL ,
[bit_Active] [bit] NULL ,
[int_CategoryID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[PurchasedProducts] (
[int_saleCart] [int] IDENTITY (1, 1) NOT NULL ,
[int_ClientID] [int] NULL ,
[int_ProductID] [int] NULL ,
[int_Quantity] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WebCart] (
[int_CartID] [int] IDENTITY (1, 1) NOT NULL ,
[int_ProductID] [int] NULL ,
[int_Quantity] [int] NULL ,
[int_ClientID] [int] NULL
) ON [PRIMARY]
GO
<!--- --------------------------- SQL Create Script Ends ---------------------------- --->

That pretty much allows you to see what is going on in the back-end (the database), now let’s take a look at the files that will be needed.

There are 12 files associated with this application, they are:

  1. addItem.cfm
  2. Application.cfm
  3. cartItems.cfm
  4. checkout.cfm
  5. checkout2.cfm
  6. index.cfm
  7. listProducts.cfm
  8. OnRequestEnd.cfm
  9. Site_footer.cfm
  10. Site_Header.cfm
  11. updateItem.cfm
  12. viewCart.cfm

So I will show you all the file code next, and explain as I go (explanations will be posted as code comments)

addItem.cfm
<!--- This will add an item yo your shopping cart basket --->
<cfquery name="queryAddItem"
               datasource=
"#Application.databaseDatasource#"
               username=
"#Application.databaseUsername#"
               password=
"#Application.databasePassword#">
    insert into WebCart(int_ProductID,int_Quantity,int_ClientID)
    values(#int_ProductID#,1,#session.int_ClientID#)
</cfquery>

<!--- This will take the visitor/customer to the main page of your application, so they can purchase more items --->
<cflocation url="index.cfm" addtoken="no">

Application.cfm
<!--- Define what the application name will be and all the necessary details that will allow the application to function correctly --->
<cfapplication name="TheWebCart" sessionmanagement="yes" sessiontimeout="#CreateTimeSpan(0,0,30,0)#">

<!--- Now Define Some Application Scope Variables Available To The Application --->
<cfscript>
    Application.databaseDatasource = "webCart";
    Application.databaseUsername = "webCart";
    Application.databasePassword = "webCart";
</cfscript>

<!--- If the current user is does not have an ID, assign them one --->
<cfif isDefined("session.int_ClientID") EQ "false">
    <cfquery name=
"queryInsertClientToDatabase"
                   datasource=
"#Application.databaseDatasource#"
                   username=
"#Application.databaseUsername#"
                    password=
"#Application.databasePassword#">
            insert into Clients(int_CFID, txt_CFTOKEN)
            values(#CFID#,'#cftoken#')
    </cfquery>

    <cfquery name=
"queryGetClientIDValue"
                   datasource=
"#Application.databaseDatasource#"
                   username=
"#Application.databaseUsername#"
                   password=
"#Application.databasePassword#"
                   maxrows=
"1">
            select * from Clients order by int_ClientID desc
    </cfquery>

    <cfset session.int_ClientID = queryGetClientIDValue.int_ClientID>
</cfif>

<!--- Include the header of the site --->
<cfinclude template="site_header.cfm">

cartItems.cfm
<!--- Get All The Items That This Client Has In His Shopping Cart Basket --->
<cfquery name="queryGetMyCartItems"
              datasource=
"#Application.databaseDatasource#"
              username=
"#Application.databaseUsername#"
              password=
"#Application.databasePassword#">
    select Products.*, WebCart.int_Quantity from WebCart, Products
    where WebCart.int_ProductID = Products.int_ProductID
    and WebCart.int_ClientID = #session.int_ClientID#
</cfquery>

<!--- Display The Items That I Have In My Cart --->
<cfif queryGetMyCartItems.recordcount gt 0>
    Total Items In My Cart: <cfoutput>#queryGetMyCartItems.recordcount#</cfoutput><br>

    <!--- Create A Temporary Value To Display The Subtotal Amount --->
    <cfset subtotal = 0>
    <cfoutput query=
"queryGetMyCartItems">
        <cfset subtotal = subtotal
+ (queryGetMyCartItems.int_Quantity * queryGetMyCartItems.flt_productprice)>
    </cfoutput>
    Subtotal: <cfoutput>#DollarFormat(subtotal)#</cfoutput><br>
    [<a href="viewCart.cfm">view cart</a>] [<a href="checkout.cfm">checkout</a>]
<cfelse>
    You have no items in the cart
</cfif>

Checkout.cfm
<!--- Display a simple form to allow the client to add their personal details --->
<form action="checkout2.cfm" method="post">
<table border="1">
    <tr>
        <td>
Your Name:</td>
        <td>
<input type="text" name="txt_ClientName"></td>
    </tr>
    <tr>
        <td>
Your Email:</td>
        <td>
<input type="text" name="txt_ClientEmail"></td>
    </tr>
    <tr>
        <td>
Username:</td>
        <td>
<input type="text" name="txt_Username"></td>
    </tr>
    <tr>
        <td>
Password:</td>
        <td>
<input type="password" name="txt_Password"></td>
    </tr>
    <tr>
        <td></td>
        <td>
<input type="submit" value="checkout"></td>
    </tr>
</table>

</form>

Checkout2.cfm
<!--- This will add the details from the client (entered in checkout.cfm) to the database--->
<cfquery name="queryAddClientDetailsToDatabase"
               datasource=
"#Application.databaseDatasource#"
               username=
"#Application.databaseUsername#"
               password=
"#Application.databasePassword#">
          update Clients set txt_ClientName = '#txt_ClientName#', txt_ClientEmail = '#txt_ClientEmail#',
          txt_Username = '#txt_Username#', txt_Password = '#txt_Password#', bit_Active = 1
          where int_ClientID = #session.int_ClientID#
</cfquery>

<cfquery name="queryGetViewCartItems"
              datasource=
"#Application.databaseDatasource#"
              username=
"#Application.databaseUsername#"
              password=
"#Application.databasePassword#">
          select Products.*, WebCart.* from WebCart, Products
          where WebCart.int_ProductID = Products.int_ProductID
          and WebCart.int_ClientID = #session.int_ClientID#
</cfquery>

<cfoutput query="queryGetViewCartItems">
     <cfquery name="queryAddProductPurchaseToFinal"
                    datasource=
"#Application.databaseDatasource#"                     username="#Application.databaseUsername#"
                    password=
"#Application.databasePassword#">
               insert into PurchasedProducts(int_ClientID, int_ProductID, int_Quantity)
               values(#session.int_ClientID#, #int_ProductID#, #int_Quantity#)
     </cfquery>
     <cfquery name="queryDeleteCartItems"
                    datasource=
"#Application.databaseDatasource#"
                    username=
"#Application.databaseUsername#"
                    password=
"#Application.databasePassword#">
               delete from WebCart where int_CartID = #int_CartID#
     </cfquery>
</cfoutput>

Thank you, your order was received successfully!<br>
[<a href="index.cfm">back to the home page</a>]

Index.cfm
<!--- Get All The Product Categories And Display Them On The Home Page --->
<cfquery name="queryGetAllCategories"
              datasource=
"#Application.databaseDatasource#"
              username=
"#Application.databaseUsername#"
              password=
"#Application.databasePassword#">
          select * from Categories where bit_Active = 1 order by txt_Category
</cfquery>

<ul>
<cfoutput query="queryGetAllCategories">
   <li><a href="listProducts.cfm?int_CategoryID=#int_CategoryID#">#txt_Category#</a></li>
</cfoutput>
</ul>

<!--- Now Include The File That Keeps Count Of My Items In The Shopping Cart --->
<cfinclude template="cartItems.cfm">

listProducts.cfm
<!--- get all the products for a specified category --->
<cfquery name="queryGetProductsInCategory"
              datasource=
"#Application.databaseDatasource#"
              username=
"#Application.databaseUsername#"
              password=
"#Application.databasePassword#">
          select * from Products where Products.int_CategoryID = #int_CategoryID#
</cfquery>

<!--- Now go through the results returned by the query and output the data --->
<cfoutput query="queryGetProductsInCategory">
<table border="1">
    <tr>
        <td>
#txt_ProductTitle#</td>
    </tr>
    <tr>
        <td>
#txt_ProductDescription#</td>
    </tr>
    <tr>
        <td>
#DollarFormat(flt_ProductPrice)# [<a href="addItem.cfm?int_ProductID=#int_ProductID#">add to cart</a>]</td>
    </tr>
</table>
<hr>
</cfoutput>

OnRequestEnd.cfm
<!--- Include the footer of the site --->
<cfinclude template="site_footer.cfm">

Site_Footer.cfm
<!--- here you can put any type of footer you want to display, think of this as your site-wrap ---><

About This Tutorial
Author: Pamela Gonzalez
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5,CFMX
Total Views: 164,483
Submission Date: July 20, 2004
Last Update Date: June 05, 2009
All Tutorials By This Autor: 1
Discuss This Tutorial
  • it's easy to understand would tell me if there is any way to download this program...thanks

  • this was a great tutorial but i am an amature programmer and was wondering about the application varriables. What do these ones do and how/where do they go and work. If you can quickly and easily explain this let me know @ cuda_man_73@zachbarker.com Thanks

  • why you guys are congratulating her is beyond me. she never responds to your questions by emails or on this board. Why she won $3,000 is completely beyond me. Have you seen her on this site again? nope she is gone

  • The quantity changes could be a little more improved. This allows one change at a time, but what about changing 10 items all at once?

  • Hi, congrats on winning the 3000 award. From now I know how to develope ecommerce in my web page. Thank you for sharing your knowledge with us. = )

  • Many congratulations on winning the $3000 piece of kit :) Have fun! I will definately be giving this a tutorial a try :D

  • I've been coding CF for a couple/three years now and haven't taken the time to learn how to code my own cart. But after reading this tutorial I plan to begin working on it right away. Thanks!

  • thank for this info because now i build e-commerce, so this the example to make my web thank ruben

  • Im tryring to create my first on-line shop and am struggling with this tutorial, is there a chance of having a download of a ready made shop, linking to an access db to see it working?????

  • nice tutorial .. i use mysql rather than sql server, but converting to that shouldnt be a problem. One thought ... you didnt mention what the clients password and username is used for ? i guess it's for them to login, so that entering their details isnt required again .. But i take it that the int_CFID & txt_CFTOKEN fields would change after they login ? or the fields would be updated from the moment they access the site ?

Advertisement

Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.