Cold Fusion Tutorial: Part I

Cold Fusion Database Integration

Now that you know the basics of what you need to create a database integrated website it is time to put everything together.
Create a Database named Friends, and a table within it named Friends. Create the following Fields and Datatypes:

FirstName - Text
LastName - Text
Age - Number
FriendID - AutoNumber
Sex - Text
FavMovie - Text
FavColor - Text
Car - Text
Comments - Memo

Then fill in the appropriate values for a number of people. Upload it to your server and tell your Server Admin to create a DSN named "Friends" for it using Microsoft Access Drivers.

Now we have the database, lets make a website for it. First lets make a page that lets people search the database.

Figure 7. Search.htm
<form method = "post" action = "results.cfm">  
<h1>Search For Friends</h1>  
<select name = "Sex">  
<option value = "male">Male  
<option value = "female">Female  
</select>  
<input type = "Submit" value = "Submit">  
</form>

We have our simple search page, now we need the results page:

Figure 8. Results.cfm
<CFQUERY DATASOURCE = "Friends" NAME = "MAIN">  
SELECT FirstName, LastName, Age, FriendID  
FROM Friends  
Where Sex LIKE '#sex#'  
</CFQUERY>  


</CFOUTPUT>  
</table>  
</div>

Now lets examine the above. As you can see to perform a Cold Fusion Query you just fill in the CFQUERY tags with your SQL statement and you're good to go. Any part of the SQL statement can be substituted with a variable passed from a previous page as I did with the sex variable. Then notice the CFOUTPUT tags have a query argument, this tells it that it is drawing info from a query and also sometimes you will use multiple queries on one page, this tells it which query to use.

Then you'll notice I only had to include one table row between the CFOUTPUT tags, this is because Cold Fusion will duplicate whatever is in the CFOUTPUT tags once for every row returned by the query.
Finally notice what is in the More Info column. This is a link to another page that also passes the FriendID as an argument. Lets take a look at that page now.

Figure 9. info.cfm
<CFQUERY DATASOURCE = "Friends" NAME = "INFO">  
SELECT *  
FROM Friends  
WHERE FriendID = '#ID#'  
</CFQUERY>  


#ParagraphFormat(Comments)#  
</cfoutput>  
</div>

Now lets examine the above. In the Select statement I use an * because I want to select all of the fields. Then in the where statement I am using the ID variable I passed in the URL. This time for the output I do not put it in a table since each ID is unique and I know only one row will be returned. Then I list the variables as before with one exception. For the comment variable I include the ParagraphFormat function. What this function does is recognize the 2 blank lines between paragraphs and inserts a <p> tag there so that it is displayed correctly on the page.

Conclusion

You should now be able to create a fully database integrated Cold Fusion website. Stay tuned for my next article which will cover advanced intermediate coding techniques and more advanced SQL.