The CF_Sitewatch example application

This application demonstrates the ease in which you can create a web based database application. Users could search a list of Coldfusion powered sites as well as add their own sites to the database.

The application no longer runs live on this website but you can examine and understand the main features and download the template files.

The home page

The home page has three main features:

A count of the sites listed in the directory, the latest sites that have been added and a form that allows you to submit a site.

Count the sites

Counting the sites is a really simple query:

<CFQUERY datasource="#dsn#" name="Sitewatch1">
		Select * 
    	from Sitewatch
</cfquery>

Which just selects the total number of rows in the Sitewatch table. I then create a variable to use later on in the code by sending .Recordcount to the query set:

<CFSET Sites=#Sitewatch1.Recordcount#>

There is a better way of running this query. I am asking the SQL engine to return the whole set of records with this query - where actually I just need to return the number of records. This is not very eficcient and with a large database this could slow down the application. In this example though, for simplicity I've just grabbed that set and asked Coldfusion to count it for me.

Now in the table cell where I want the site count to appear I just insert the following code which gets Coldfusion to return the record count stored in the Sites variable:

<CFOUTPUT>#Sites#</cfoutput>

List of recent sites

The next query is used to generate the list of recently logged sites, but the query reveals there is slightly more going on behind the scenes than the user may realise:

<CFQUERY datasource="#dsn#" name="Sitewatcher" 
		maxrows=4>
 Select * 
 from Sitewatch
 WHERE Moderated Like 'Yes'
 ORDER BY SiteID DESC
</cfquery>

First of all I ask Coldfusion to only return to 4 rows. Again this could have been done in the SQL statement, but it is complicatated enough as it is. I am already restricting the records returned by checking if the row has a moderated field. Realistically I dont want everyone on the web to be able to add sites to the application - so when they do I have built a moderating system which allows me to control what is being displayed. Once moderated the site gets a Moderated Yes flag and so is displayed on the site. Finally the list is ordered so that the most recent records are listed.

Now I have the record set the rest is simple, I open a table tag and create a header row, then loop through the records using the following code to generate a table row for each record in the query:

<CFOUTPUT query="Sitewatcher">
 <tr>
 <td bgcolor="##a7d4da">
 <A href="#SiteURL#" target="_new">
 <font color="black">#SiteTitle#</FONT>
 </a>
 </td>
 </tr>
 </cfoutput>