ColdFusion

The great thing about being a "Freak" and working with a "Geek" is not having to concern yourself with the nitty-gritty details of coding a dynamic site. The bad thing is that you will get involved with the nitty gritty details whether you like it or not.

In my previous article (MXDJ Vol. 1, issue 4), I walked you through my "epiphany." I had redesigned my site, basked in the accolades of my students and colleagues, and then discovered to my chagrin that I had screwed up - big time. I had a tutorial area that was a classic case of a freak focusing on the design and not wondering, "Can anybody use this page?" There were a few dozen tutorials available but they were impossible to find.

The entrance page simply laid them out and said to the visitor, "You have a brain. Figure it out." This is a huge error and once I "figured it out," I wandered down the hall from my office to that of my colleague James Cullin, the ideal ubergeek, and described my error. I also added the fact that, in my humble opinion, the solution was to get dynamic and could he help. Rather than point fingers or laugh up his sleeve at me, James did something that is so typical of James - he pulled out a notepad and said, "Let's get to work." We quickly sketched out a broad plan of attack and I left James alone.

This article describes how James, in typical geek fashion, attacked the problem. It shows how he worked from concept to code and offers you a few ideas as to how you can approach the task of building a database and then "hook" it into MySQL through ColdFusion MX. We started the task with good old-fashioned pragmatism.

Once we understood the scope of the project, our first question was, "How does it work?" It is all well and good, in typical freak fashion, to say, "Here's what I want the page to look like." It is also all well and good for the geek to say, "Here's what I want the data to look like." What both are overlooking is a fundamental question: How does the data get into the design? The first step for both of us was a rather intense discussion around that very question.

From a design point of view, there were a couple of approaches to solving this issue. The first was to simply flow the images and text into the page from the database. This seemed to be the ideal solution. I write the tutorial, do a few screen shots, toss it all into a database, and somehow the page, thanks to James, is magically constructed on the server and shot into the browser. As James and I fully explored this idea, it became evident it was the worst possible solution. This was due to the way the page is designed.

The page is constructed from a series of div tags (see Image I). All of the words and images are placed in a <div> with the ID of "content". The major elements in this area are a headline, body text, subheads, images, and captions. As the freak, I controlled the placement of all of these elements, and there was no way to control the placement of the images and captions if they were to be inserted into the <div> on the server without a lot of unnecessary extra work on both our parts. Plan A, the most obvious solution, was discarded.

The solution we eventually arrived at is based upon how I work with Community MX. Having written for them for a couple of years, I always found the process to be rather smooth. All articles are contained in a folder, named using an article number generated by a database, and all of the images, pages, and uploads sit in that folder. This is a rather tidy solution to the problem at hand. Everything is in one place, making development and maintenance extremely easy.

We quickly decided this model met our needs. James saw how easy this would make his life and, rather than an article number, he suggested we use the date the article was created for the folder name. My first reaction was a bit negative. I foresaw a potential situation where two or three tutorials could be posted on the same day. James' response was essentially, "That's my problem. Worry about something more important." The solution, as you will see, had more to do with me than anything else.

What was important to me was how the user would locate a tutorial. I felt it important that the user be able locate it by either viewing all of the tutorials in a category or through a keyword search. I toyed with a couple of ideas, but the design of the page presented me with the most logical solution. On the right side of the page is a <div> that contains link information based upon the subject of the page. I decided to have the main content area welcome the user to the page and to use the link area for the search. When the user selects a subject or does a keyword search, the results flow into the "Content <div>". From there the user can select the tutorial and go to work.

This is an invaluable step because it gave James a clear idea of how the user would access the information and the functionality involved. It also gave both of us a roadmap to follow. The concept, in the form of a Dreamweaver MX 2004 page, was sent to James with the following question: "Is this doable?" This is an important question because it puts the freak and the geek on common ground. The geek can understand my thinking, and I learn (rather quickly) whether my idea is too ambitious. This is the best time to discover this because changes can easily be made. Discover it in the middle or at the end of the process and you are essentially relegated to starting all over again (see Image II).

Building the 'Tutorial Engine'
The first step was to build the MySQL database tables that reflect the architecture we had agreed to. James discovered that this job, based upon our discussions, was a relatively simple undertaking because it only needed one table, as shown in Image III.

You will note the naming conventions are fairly intuitive. James' explanation for this should have several of you nodding and saying, "Been there, brother."

"Every table I create starts with 'tbl_'", said James. "I wish I could say I had this good sense right from the start but I can't. It is only after your 9th or 10th 'all-nighter' where you meet a deadline by the skin of your teeth that you realize there just has to be a better way. When it is 3:00 a.m., and you started work at 8:00 a.m., you will discover that you either develop a distinct naming pattern or just live with the confusion."

This logical naming convention is used throughout. For example, the primary key in the database uses "pk_" in the name. Columns use "col_" and so on. This not only helps James build the database using clearly labeled elements but also it gives anybody else maintaining the database a very clear idea of what's what.

When geeks and freaks work together for as long as James and I have, they tend to become sensitive to each other's quirks. For example, the DataType for the article titles, "col_title" is text. By doing this James allows me the flexibility to get a bit wordy with the titles. This DataType supports from 1 to 65,535 characters or about 8,000 words without truncating the title. I am not to sure whether this was shrewd planning or self defense because, according to James, "I can sleep easy knowing there is no way in hell Tom will ever have his title truncated."

The rest of the database design follows a similar plan. Using the category drop-down list from the concept shown in Image I, James created a category column that plans for product names of more than eight words. There is a column for the abstracts I will write and it, again, allows up to 8,000 words. The Key Words column allows for the search. The interesting aspect of that name is the use of the upper case lettering in the name. "As my naming convention evolved," said James, "I found it confusing to have multiple underscores. I established that if a column needs multiple words to describe it, such as Key Words, I will capitalize the first letter of each word."

James also decided to categorize each article by the date the article was posted - col_DatePosted - and to set the datatype to "datetime". Finally, the entire Tutorial Engine resets on putting each article and its related images in a unique folder. This is the purpose of "col_FolderName" and the "safest" datatype for it was text.

Building the Admin Page
Next James and I discussed how I would actually get the content into the database. There were two agendas here. The first was it had to be easy for me to use - I didn't need was a complicated process. The information was to be clearly presented and the data required was to be simple to input. I was also looking down the road and, if others were to post tutorials, the process had to be intuitive and not require a few hours of training. From the freak point of view, the design imperative was functionality and usability rather than award-winning design. The site's users will never see it, so design consistency was unnecessary.

James and I discussed a couple of ideas and settled on the design shown in Image IV. It is a simple design whose sole focus is data collection. Each field is clearly labeled and there is no ambiguity. The big surprise for me was the inclusion of space for 10 keywords. When we first discussed this feature, in typical freak fashion, I mentioned I would most likely add the keyword search "based on a couple of words." In typical geek fashion, that was translated into a "specific number of words." James knows me and I suspect he did it just to be sure I couldn't fill it. With the admin page designed, James went to work.

Coding the Admin Page
James is quite methodical and precise in his approach to coding. In fact, he rarely uses Dreamweaver's Design View preferring, instead, to work where he feels most comfortable: in the Code View. This is quite understandable for someone who, as he puts it, "cut his teeth on HomeSite". This methodical approach is quite visible in how he coded the admin page. James believes that each and every form element needs to have an intuitive name attribute. "This is critical prep work," says James, "in advance of writing the ColdFusion MX script that actually writes to the database."

In the XHTML example in Image V, it is important to note how the <form> tag action attribute is directed to the CFM script named "AddArticle.cfm". James also used the same naming process for the <input> and <select> tags as he did when he set up the database. The names actually mean something. This tends to eliminate confusion at a later date. This approach is also evident in the code for the description and keyword fields, where each of the ten keywords is given a unique attribute (see Image VI).

The final bit was something I was looking forward to seeing. There are any number of ways of approaching the Month, Day, and Year drop-down menus and there are as many approaches to this as there are geeks in this business (see Image VII).

First James used the names of the months for the drop-down list instead of the number that the MySQL datatype stores. The number is found in the value attribute for each of the option tags. " I set up the code this way," he said, "in anticipation of writing to the database which stores the date using the 'datetime' datatype." The other interesting approach was the dates drop-down. "Lines 86 to 90 show that I am lazy," he said. "I don't like typing. When faced with a situation of needing to create 31 <option> tags to cover the 31 days of a month, I used a cfloop to do it for me." As a teacher, I especially appreciated this approach because, like James, I believe in letting the software do the work.

Coding the Data Flow
The Create button submits the form data to a file named "AddArticle.cfm". This ColdFusion script has two purposes:

1. To write the information collected to the database.
2. To provide me (Tom) with clear instructions regarding the folder name. This is a fancy way of saying, "Make the naming process idiot-proof."

A major issue to be dealt with was the keywords. When the button is clicked the keywords simply won't work. They are a collected as a series of 10 variables. The database is set up to store them as a single comma delimited string. James' first step was to merge them into one string using the code shown (see Image VIII).

The first line of the code establishes a global variable named "KeyWords". Knowing that I would be hard pressed to come up with 10 words for certain tutorials, there was a 100% probability there were going to be blank fields when I clicked the "Create" button. Line 3 is a rather elegant solution to this issue.

The <cfif> tag inspects the first field to see if it is blank. James also knows that if there is a way to screw it up, I will find it. For example, my "accidental" use of the spacebar has resulted in some pretty legendary stories amongst our faculty. This is why he added the ColdFusion "trim" function to strip out any "accidental" spaces to the right or the left of the keyword. If the field is blank, there will be no value in the field. This explains the neq operator. It is the way one expresses "not equal to" in ColdFusion MX and, in this case, if there is a word, then the value is not equal to null (the empty quotation) and the next line of code is executed. If it does equal null, then the code skips to the next CFIF statement and repeats the process.

The next line - <cfset KeyWords = "#KeyWords##form.kw1#," - tosses the word into the string that is being assembled and the XHTML form variable being used is #form.kw1#. The "#" signs enclose each of the two variables and the comma at the end is used because the keywords in the database will be separated by commas. This whole process repeats itself nine times until the string is assembled in the database.

With keywords out of the way, James next turned his attention to the date. This too is rather interesting because the form kicks out three values - Month, Date, Year - while there is only one column for the date in the database. The code used to assemble this single date element is:

<cfset DatePosted = CreateDate(form.YearNumber,form.MonthNumber,form.DayNumber)>

The ColdFusion MX function CreateDate is used to create the data string for the "DatePosted" variable. This function requires three arguments for Year, Month, and Day, which must have numeric values. This wasn't terribly difficult to accomplish because James did just that when he coded the drop-down menu earlier.

The final issue was making the folder-naming process idiot-proof. The name is set using this code:

<cfset FolderName =
"#form.category##form.DayNumber##form.MonthNumber##form.YearNumber#">

"I created a variable named FolderName and, to make it unique, I assemble the FolderName using the category of the article Tom chooses and the three date elements." says James. "My assumption here is that Tom won't post two Dreamweaver articles on the same day." I am glad I asked. This is important information for me to be aware of.

From the Page into the Database
With the data assembled, the time had arrived to move it into the database and, according to James, "ColdFusion MX makes inserting a record into a database table incredibly easy." The code that accomplished this task is shown in Image IX.

Lines 39 to 42 are the opening <cfquery> tag. Each ColdFusion query has a name and James' protocol is to make the name as verbose and intuitive as possible. "I learned that lesson the hard way," he told me. "When a file is due at 9:00 a.m. and it is 3:00 a.m., the last thing you need is to be wondering which query does what. If the name is verbose and obvious, even the most sleep-deprived coder can figure it out."

The datasource is how ColdFusion MX maps to the database. In this case, our ISP has configured our ColdFusion MX server so that each site has a datasource that maps to a corresponding MySQL account. To access that account, we need a username and password which are detailed in the <CFQUERY> tag.

Lines 44 and 45 are not ColdFusion code. Those lines are pure SQL, which is a database manipulation language. The first line may, at first glance appear to be a bit convoluted:

INSERT into tbl_articles (col_title, col_category, col_description, col_KeyWords,
col_datePosted, col_FolderName)

In fact it is simply saying where to insert the values from the Add Articles.cfm page into the database. The locations are contained in the brackets. Now that we know where the values are to be placed we also have to answer the question, "What values?" The next line of code handles this:

VALUES ('#form.title#','#form.category# ',
#form.description#','#KeyWords#','#DatePosted#','#FolderName#')

What is absolutely critical in this code line is that the order of the items in the VALUES statement precisely matches the order of their counterparts in the INSERT statement. As well, the "title", "category", and "description" come from the form created in the Admin page, which explains why they are prefaced with "form". One item, "KeyWords", doesn't use this convention because its value was established in line 1 of this page's code.

You may also have noticed the variables are surrounded by single quotes. This is because the information is going into the database as text.

Another inconsistency you may have noted is the variable being inserted in the "col_FolderName" column of the database. It is named "FolderName". This variable name was also set earlier in the page.

Now that we have the data I input sitting in the database, how does it appear on the page? That, ladies and gentlemen, is a whole other story in the Freaks and Geeks saga.