I first got started in ColdFusion at my first full time web position. I created a craigslist post basically saying I would work any company that would have me (lol) and sure enough, 1 month later I was working as a web developer. When I started my boss told me that they used ColdFusion and I would have to learn it. Previous to this I had some limited PHP knowledge but no real development chops…that changed real quick. Soon after learning the basic structure of the language, I was handling my own projects and really learning all that ColdFusion had to offer.

We offered a CMS that could be customized based off the customers site/requirements and this really helped me learn how to read existing code and learn to update it. The most common task we did was generating reports, so I became very familiar with exporting pdf’s and excel documents on the fly.

Fast forward 3 years and I have become the developer I am today. I run a successful web development business specializing in rapid development using ColdFusion. I have created my own custom CMS that I implement on most of my sites and with the new features available in ColdFusion 9, this really has become a great piece of software. I owe a lot to ColdFusion and any chance that I get, I tell people about it.

1. The difference between a server-side language (such as CFML), a server-side platform (such as Adobe ColdFusion Server), and a server-side development framework (such as CFWheels).
2. The inherent statelessness of server-side applications, and how some platforms work around this statelessness using tricks such as Sessions.
3. The pros and cons of at least two server-side platforms or languages.

I got a lot of nice remarks on my post, so I figured I would share it:

There are many differences between a server-side language, like ColdFusion, a server-side platform, like Adobe ColdFusion Server and a server-side framework, like CfWheels. The server-side language is the language the server processes and what you will be using when creating your pages. Server-side languages need a server to process the language and that is what is called the server-side platform. When you use the server-side language of ColdFusion, your server-side platform will be Adobe’s ColdFusion Server. This is what you install on your web server to run your pages. Most languages have what are called Frameworks. What these essentially are is a group of pages that someone else created, that you can use in your source to make things easier. Certain things are already done for you and you essentially just have to plug in your data and the framework displays/updates the data with prewritten code.

I have used Coldfusion (version 5-9) for the past 4 years and couldn’t be happier. The rapid development times alone should be reason enough to check this language out. With a huge library of tags built into the language, you can create the code to do many advanced functions in just a few lines. Now the main complaint the ColdFusion community (which is huge, helpful and always growing) hears from the PHP Community is the price of ColdFusion. Now the server costs are more than what people are expecting to pay, which is $0 but you have to think of that initial cost as an investment in the language. There are many posts proving this (Link – which shows even more great reasons to use ColdFusion) and as you will see, the time you save learning the language, developing the app, and then the time saved on maintenance and support, shows that ColdFusion is actually the cheapest language to use based on that criteria. Also, not to forget but there is a FREE, OPEN SOURCE version of ColdFusion used all over the web called Railo. The only difference between Railo and the Adobe version are a few tags but Railo is constantly improving the language and new tags are added all the time.

Now to be fair, I have used PHP in the past and actually wrote a few websites with it back before I learned ColdFusion and it wasn’t a terrible language. Server setup was pretty simple and changing the configuration file was also pretty easy. There are also a TON of sites written in PHP so usually if I Googled an error, somewhere, someone would have the answer but my main complaint when using PHP was the amount of code I had to write to achieve a certain function. To get a decent PHP page took long hours and tons of lines of code and really made me dislike the language more and more. Also PHP is very OOP and I was never fond of that type of programming. I would reccomend learning PHP first before any other languages though. If taught correctly, it can be a great introduction to server-side language and development but people should explore the other options.

The statelessness of server-side applications can be hard thing to grasp at first but as you begin to develop applications, you will learn real quick. Essentially, all pages that you create that use a server-side language do not contain the data until they are requested. Unlike a regular HTML page that contains all of the data, regardless of any user conditions or page variables, the dynamic page just sits there, waiting to be requested and process the code. The main issue with this is that there is no way to save the page for a particular person, without having to create for each user and with sites with hundreds of members, this would just not be practical. A way around this is using Session Variables and/or Cookies. An easy example of this is a login form. When you login to most websites, you do not have to login again until you close the browser or physically log out of the system. This is done via Session Variables. Session Variables are a sort of global varial that has ties into the browses, so they can be cleared whenever the user closes their browser. When you login somewhere, a Session Variable is created and is stored in the server’s memory to remember you. This way, you can have one dynamic page but have millions of different kinds of data show
up.

What do you think?

First up, I had to make a .html product link for each product.  Here is how I accomplished that:

<cfset prodlink = getproducts.name>
<cfset prodlink = replacelist(trim(prodlink)," ,-","_,_")>
<cfset prodlink = prodlink & "_" & getproducts.id & ".html">

I first set my prodLink variable to the current name of the product being displayed. I then remove any spaces in the name (for those products with long names) and insert underscores to start building the link. I then append in the id of the product (I will be using this to pull in the info later on) and then give it the .html extension.

Now lets jump over to the ISAPI part and show you what I did.

RewriteEngine On
RewriteRule ^[^?/]*_(\d+)\.html? store_info.cfm?ID=$1 [NC,L]

*For those of you unfamiliar with ISAPI, I will explain what all this means.*

First, I set the RewriteEngine to on (for security reasons, it is globally turned off) and then I started to work on the rule to grab any file with an html extension with a trailing underscore. That is what this part accomplishes “RewriteRule ^[^?/]*_(\d+)\.html?”

The second part is where I want ISAPI to redirect if the first part matches something. I need to send it to the page that displays the info, along with the ID of the product. This is how the store is functioning now. Sending an ID over a URL string to the info page, which then displays the corresponding information. So although I am now going to an html page, it is actually still hitting my ColdFusion page in the exact same way, just done as an html page instead of displaying the store_info.cfm?id= in the URL.

Now if you are reading this and saying “seems pretty simple and straight forward”. Well you are right, the solution I came up with is simple but it took me over 3 hours to get to this. I kept getting 404 errors when testing my new .html pages because it wasn’t able to redirect to the ColdFusion page. The problem wasn’t with my code, or ISAPI but the mappings on my server that ColdFusion setup on install. A blog post that I found while googling ISAPI and Coldfusion on IIS7 had my answer. The blog post (Link) stated that “The problem lies in handler mappings configuration. To resolve it, any handler mappings for *.cfm extension should be mapped to 1\jrun_iis6_wildcard.dll executable (instead of jrun_iis6.dll).”

So I looked in my Handler Mappings in IIS7 for the site I was working on and sure enough, it was pointing to the jrun_iis6.dl. A quick edit, site restart, and bam, everything worked like a charm.

The updated mapping

Now the blog post tells you to replace all of them by dropping in a config file but IIS7 has a built in way to edit these. Seeing this site doesn’t use any of the other ColdFusion extensions beyond .cfm, I only changed that one.

So thats it….3 hours of googling, testing and bugging people for help comes down to a few paragraphs and a few simple steps on how to fix my issue. Now my client has ColdFusion pages displayed with a html extension.

<start rant>

For those of you who don’t know, there are people who are called ‘Freelance developers/designers/etc’ and essentially these people have no steady client base and really just do one off jobs.  This is not a bad thing and many people make a great living doing this.  This post is not against them, its against people who assume since I work on the web for myself, I am simply a freelancer.  Well I am not.  I run a business from top to bottom, janitor to ceo, pushing papers to pushing code and it really irriates me when people think I just sit at my computer and type code all day.  It is so much more involved then that.

I would love to see some of these people do what I do on a daily/weekly/monthly basis.  Not trying to say I am the shit and better then everyone else but I don’t know many 25 year old business owners who support themselves 100% on their own.  This is me.  I am a business owner and the only full time employee.  That means, anytime Blackfish Development does anything, anything at all, I am responsible for that.  I am not looking for a metal or a crown to wear around (which would be sweet) just the respect that I deserve for doing what most people only dream of doing.

So if you want to know what its like to run a business and be 100% responsible 150% of the time.  Go to your work and tell everyone to take the next week off, janitor to ceo, and you do all of the jobs.

#getOnMyLevel

</end rant>

edit:  People think I have something against freelancers or being called a freelancer is a bad thing. It is not.  I have no issue with freelancers and it is not below me in any way.  I just am more then a freelancer. I have over 90 clients who I handle development work, consulting work and I also run hosting for over 60 of those clients.

I received an email yesterday from a client, stating that her email is messing up and that they think someone “stole” an address.  I immediately checked the site to see if it was hacked or anyway altered and came up with nothing.  So I called the customer and got the first hand details, “I keep getting emails from someone named Don O’treply and there is no one here with that name, I think we are getting hacked”.  I paused for a second on the phone and asked if she could forward me an email and I would look into it and get back to them.  As soon as that email hit my inbox I couldn’t believe my eyes Don O’treply = donotreply@xxxxx.com .  Apparently this person was receiving emails from their site that had a contact us form on it, with the from address pointing to Mr Don O’treply.  After forwarding the email around the office and composing myself, I called her back, explained the situation, and told her everything was perfectly fine and that I fixed it so she will not receive them anymore.

Some people amaze me…especially the ones that are high in companies, but delete emails before reading them if they do not recognize the from address ( or read the subject for that matter ).

Good Stuff…

Any feedback would be great!!

The first part I wanted to just get a list of referrers and how many times they were used. This post will be the next part of that and how I grabbed the exact searched keywords and made a report on a few things about them. First thing I did was made 3 lists depending on what domain the referrer was from ( google, msn, and yahoo ) by grabbing the url string and then breaking it down into what I wanted:

<cfquery datasource="#db#" name="getCat">
	SELECT     id, referer
	FROM         orders
</cfquery>
<cfquery datasource="#db#" name="getInfo">
	SELECT     id, referer
	FROM         orders
	WHERE     (referer LIKE '%google%') OR
		      (referer LIKE '%search.msn%') OR
		      (referer LIKE '%search.yahoo.com%') AND (NOT (referer IS NULL))
</cfquery>
<!--- CREATE LISTS FOR KEYWORDS --->
<cfset googlist= ''>
<cfset msnlist= ''>
<cfset yahoolist= ''>
<cfoutput query="getInfo">
	<cfif referer contains 'google'>
		<cfset googlist = listappend(googlist,'#getinfo.referer#',',')>
	</cfif>
	<cfif referer contains 'msn'>
		<cfset msnlist = listappend(msnlist,'#getinfo.referer#',',')>
	</cfif>
	<cfif referer contains 'yahoo'>
		<cfset yahoolist = listappend(yahoolist,'#getinfo.referer#',',')>
	</cfif>
</cfoutput>

Now that I have my lists, I needed to break it down:

<cfloop query="getcat">
	<cfset keys = "">
	<cfset name = rereplace(getcat.referer, 'http://www.','','ALL')>
	<cfset name = rereplace(name, 'http://','','ALL')>
	<cfset name = rereplace(name, '.com','','ALL')>
	<cfset name = rereplace(name, '.net','','ALL')>
	<cfset name = ReReplaceNoCase(name, '/.*','','all') />
	<cfif name neq ''>
	<!--- GOOGLE --->
		<cfif getcat.referer contains "http://www.google.com">
			<cfset skiprow = 0>
			<cfset tmplist = rereplace(getcat.referer, 'http://www.google.com','','ALL')>
			<cfif tmplist contains "?q=">
				<cfset tmplist = rereplacenocase(tmplist,'^.*?q=','')>
			<cfelseif  tmplist contains "?q=">
				<cfset tmplist = rereplacenocase(tmplist,'^.*&q=','')>
			<cfelse>
				<cfset skiprow = 1>
			</cfif>
			<cfset tmplist = rereplacenocase(tmplist,'&.*$','')>
			<cfset tmplist = replacenocase(tmplist,'+',' ','all')>
			<cfif skiprow is 0><cfset keys = urldecode(tmplist)></cfif>
		</cfif>
	<!--- MSN --->
		<cfif getcat.referer contains "http://search.msn.com" or getcat.referer contains "http://search.live.com">
			<cfset skiprow = 0>
			<cfset tmplist2 = rereplace(getcat.referer, 'http://search.msn.com','','ALL')>
			<cfset tmplist2 = rereplace(getcat.referer, 'http://search.live.com','','ALL')>
			<cfif tmplist2 contains "?q=">
				<cfset tmplist2 = rereplacenocase(tmplist2,'^.*?q=','')>
			<cfelseif  tmplist2 contains "?q=">
				<cfset tmplist2 = rereplacenocase(tmplist2,'^.*&q=','')>
			<cfelse>
				<cfset skiprow = 1>
			</cfif>
			<cfset tmplist2 = rereplacenocase(tmplist2,'&.*$','')>
			<cfset tmplist2 = replacenocase(tmplist2,'+',' ','all')>
			<cfif skiprow is 0><cfset keys = urldecode(tmplist2)></cfif>
		</cfif>
	<!--- YAHOOOOOO --->
		<cfif getcat.referer contains "http://search.yahoo.com">
			<cfset skiprow = 0>
			<cfset tmplist3 = rereplace(getcat.referer, 'http://search.yahoo.com','','ALL')>
			<cfif tmplist3 contains "?p=">
				<cfset tmplist3 = rereplacenocase(tmplist3,'^.*?p=','')>
			<cfelseif  tmplist3 contains "?p=">
				<cfset tmplist3 = rereplacenocase(tmplist3,'^.*&p=','')>
			<cfelse>
				<cfset skiprow = 1>
			</cfif>
			<cfset tmplist3 = rereplacenocase(tmplist3,'&.*$','')>
			<cfset tmplist3 = replacenocase(tmplist3,'+',' ','all')>
			<cfif skiprow is 0><cfset keys = urldecode(tmplist3)></cfif>
		</cfif>
		<cfquery datasource="#db#" name="insert">
			Insert Into Referers
			VALUES
			('#name#', #id#, '#keys#')
		</cfquery>
	</cfif>
</cfloop>

Now let me break this down and explain what I did:

<cfloop query="getcat">
	<cfset keys = "">
	<cfset name = rereplace(getcat.referer, 'http://www.','','ALL')>
	<cfset name = rereplace(name, 'http://','','ALL')>
	<cfset name = rereplace(name, '.com','','ALL')>
	<cfset name = rereplace(name, '.net','','ALL')>
	<cfset name = ReReplaceNoCase(name, '/.*','','all') />

This piece of code grabs the referrer and strips out everything i don’t want ( this was part of the original post but just moved to suit my final needs )

Next I started grabbing the keywords out of the url string based on each search engines unique url string. I’ll show google’s and then explain:

<cfif getcat.referer contains "http://www.google.com">
	<cfset skiprow = 0>
	<cfset tmplist = rereplace(getcat.referer, 'http://www.google.com','','ALL')>
	<cfif tmplist contains "?q=">
		<cfset tmplist = rereplacenocase(tmplist,'^.*?q=','')>
	<cfelse>
		<cfset skiprow = 1>
	</cfif>
	<cfset tmplist = rereplacenocase(tmplist,'&.*$','')>
	<cfset tmplist = replacenocase(tmplist,'+',' ','all')>
	<cfif skiprow is 0><cfset keys = urldecode(tmplist)></cfif>
</cfif>

First few lines are pretty easy to explain, if the original record from the table, not the stripped down version, contains “http://www.google.com” start editing the string. First thing I did was delete the “http://www.google.com” and then grab everything after “?q=”. This is what google uses when it starts it search query ( ie keywords searched ). So now we have something resembling this: some+example+keywords&btnG=Google+Search&aq=f&oq=

This is a great start and how i needed to grab the keywords and bag the rest. Google ends its keywrods with a ‘&’. So now I set up a RegEx to grab everything before the & and then replace the remaining “+” with just spaces, thus leaving: some example keywords.

You might be noticing the skiprow variable that I have set up. That is so incase the referrer contains “http://www.google.com” but after everything is stripped out there is nothing left, it skips that record and loops back around. Once it is done looping over itself, I have it inserting into a temporary table with the name of the referrer, order id it came from, and all the keys that were in that order.

<cfif skiprow is 0><cfset keys = urldecode(tmplist3)></cfif>
<cfquery datasource="#db#" name="insert">
	Insert Into Referers
	VALUES
	('#name#', #id#, '#keys#')
</cfquery>

This was a pretty extensive report but after it was all said and done I had learned a whole bunch about regular expression and how great they can be when you know what you want to do. I am happy to take any suggestions on trimming this up or doing it a smarter way.

Exporting things to excel can be a pretty useful feature and is extremely to set up. What I do is make a param called excel:

<cfparam name = "excel" default = "">

Then I set up my url string to trigger the excel function:

<cfif excel eq 1>
<cfcontent type="application/msexcel">
<cfheader name="content-disposition" value="filename=report.xls">
 
<table width="100%" cellpadding="0" cellspacing="2">
	<cfoutput>
		<tr>
			<td><strong <cfif sortby eq 'Name'>style="color:##FF0000"</cfif>>Name</strong></td>
			<td align="right"><strong <cfif sortby eq 'Date'>style="color:##FF0000"</cfif>>Date</strong></td>
		</tr>
	</cfoutput>
	<tr><td colspan="2"><img src="images/blackLine.gif" width="100%" height="1" alt="" border="0"></td></tr>
	<cfoutput query="getinfo">
	<tr bgcolor="#IIf(currentrow mod 2, DE('ffffcf'),DE('ffffff'))#">
		<td>#name#</td>
		<td align="right">#dateformat(date, 'mm/dd/yyyy')#</td>
	</tr>
	<tr><td colspan="2"><img src="images/blackLine.gif" width="100%" height="1" alt="" border="0"></td></tr>
	</cfoutput>
</table>
 
</cfif>

Basically you just want to set up what you want to export and format it so everything will look ok in excel.

Then add a link to trigger the export:

<a href="index.cfm?page=#filename#&excel=1<cfif isdefined("form.fromdate") and form.fromdate neq "">&<fromdate=#lsdateformat(form.fromdate,'mm/dd/yyyy')#</cfif><cfif isdefined("form.todate") and form.todate neq "">&todate=#lsdateformat(form.todate,'mm/dd/yyyy')#</cfif>&sortby=#sortby#" target="_blank">Open in Excel</a>

As you can see, I passed a few variables along because this is used on a report page where the user can search by date and also order the columns. Its very easy to set up and customers seem to love it.

Here is the scenario:

I recently set up a site to start grabbing where its visitors are coming from and then store that info in their order, if they order anything.  After waiting a few days to get some data, it was time to create their report.  What they wanted was a list of how many times a certain url was grabbed, the name of that domain, and the total number of sales from the domain as well.  I knew I had to some how strip out all the excess around the domain (http://www.google.com/searchstringfromgoogle) and I wanted to try my hand at regular expressions to accomplish this.

First thing I was did, was made my query and outputted the results.  Was a jumbled mess but was a start.  I then formatted that list to show the domain up to the .com/ on one line, and then the rest on the next.  This was so I could see what I was doing. Next thing I did was started the formatting process, first thing that went was the http://www.

<cfloop query="getcat">
	<cfset name = rereplace(getcat.referer, 'http://www.','','ALL')>

Next went the domains with the ‘www’:

	<cfset name = rereplace(getcat.referer, 'http://','','ALL')>

Next I got rid of the ‘.com’ or the ‘.net’:

	<cfset name = rereplace(name, '.com','','ALL')>
        <cfset name = rereplace(name, '.net','','ALL')>

Finally it was the tricky part that took me quite some time googling and searching other blogs. I had to find something that would delete everything after the ‘/’ where the .com or .net used to be. Here is the final solution:

	<cfset name = ReReplaceNoCase(name, '/.*','','all') />

This worked great and did exactly what I needed. After this, I dumped all of the data into a temporary table so I could generate my report:

<cfif name neq ''>
	<cfquery datasource="#db#" name="insert">
		Insert Into Referers
		VALUES
		('#name#', #id#)
	</cfquery>
</cfif>
</cfloop>

The next and final step was to make a dynamic select box and then print up the results based on what was selected:

<cfquery datasource="#db#" name="getRef">
SELECT     Referers.Name as Name, COUNT(Referers.Name) AS NameCnt, SUM(order_items.price) AS Total
FROM         Referers INNER JOIN
orders ON Referers.OrderID = orders.id INNER JOIN
order_items ON orders.id = order_items.id
GROUP BY Referers.Name, Referers.Name
</cfquery>
<select name="refname">
<cfoutput>
<cfloop query="getref">
<option value="#name#" <cfif name eq form.refname>selected</cfif>>#UCase(Left(name,1))##mid(name,2,len(name)-1)#</option>
</cfloop>
</cfoutput>
</select><br>
</td>
</tr>
<tr>
<td colspan="3">
<cfquery datasource="#db#" name="getInfo">
SELECT     Referers.Name as Name, COUNT(Referers.Name) AS NameCnt, SUM(order_items.price) AS Total
FROM         Referers INNER JOIN
orders ON Referers.OrderID = orders.id INNER JOIN
order_items ON orders.id = order_items.id
GROUP BY Referers.Name, Referers.Name
HAVING      (Referers.Name = '#form.refname#')
</cfquery>
<cfoutput query="getInfo">			
<br><table border="1" bordercolor="000000" cellpadding="3" cellspacing="0" align="center" rules="rows">
<tr>
<td width="180"><b>Referer Name</b></td>
<td width="180">#name#</td>
</tr>
<tr>
<td width="180"><b>Order Totals</b></td>
<td width="180">#lscurrencyformat(Total)#</td>
</tr>
<tr>
<td width="180"><b>Number of Referers</b></td>
<td width="180">#numberformat(NameCnt)#</td>
</tr>			
</table>
</td>
</tr>
</cfoutput>

I learned a lot about Regular Expressions and how they are used. They are extremely useful if they are applied in the correct manner.

Next post will be the next report I am doing with this info:
Keyword statistics on Google, MSN, and Yahoo referrals.