Creating a Login System
Now that you can save a user's name and favorite color in the database, you can create a login page so that existing users can enter only their name to retrieve their favorite color.
Here's a simple form with one entry field:
<FORM Action="get_color.cfm" method="post">
<P><INPUT type="text" name="name"></P>
<INPUT type="submit" value="Submit">
In a browser, the above code looks like Figure 3.
This form sends a name to the sample "get_color.cfm" file. That file asks the database which color is associated with the user's name and prints it on the page. Suppose Reginald signs in and already has red listed in the database as his favorite color.
<CFQUERY name="qget_color" datasource="FC">
SELECT color FROM users
WHERE name = '#name#'
<CFIF qget_color.RecordCount EQ 0 >
<P>NO RECORDS FOUND</P>
<FONT color=#color#>#name#'s favorite color is #color#.</FONT>
The SQL statement in the CFQUERY tag asks: "From the table users, give me the color where the user's name is Reginald." Next, a CFIF tag tests to see if the query returned any results. Because the name "Reginald" returns a row from the database, the CFIF tag fails, and the code executes the CFELSE tag, which contains a CFOUPUT tag which substitutes "Reginald" for the #name# variable and "red" for the #color# variable and sends the resulting HTML back to the browser. The code has access to the values because the CFOUTPUT tag specifies a query name (the qget_color query), so the value sent from the database for "color" is readily available to be displayed in between the CFOUTPUT tags.
You can test the streamlined, new and improved, database-driven Favorite Color Recovery System here
The Next Step
You've probably seen enough to continue building this application on your own. Your mission, should you choose to accept it, is to set up a page that deletes users and a page that updates a users color. The following SQL code should help.
The SQL statement for deleting records:
DELETE FROM users
WHERE name = #name#
The SQL statement for updating the color in a record:
SET color = #color#
WHERE name = #name#
Most applications aren't complete without some reporting. In this section, you'll create a reporting page that displays the contents of the Favorite Color Recovery System sample application. To do that, you need to create a query that selects all the records from the database, and then add some CFML to display the results in an HTML table.
The SQL is simple:
<CFQUERY name=" qget_everything" datasource="FC">
SELECT * FROM users
Again, the asterisk in the SQL means "all columns."
This query is slightly different from the other queries in this article because, rather than one record, it returns a series of records, known as an array. An array is basically a list of items, for example:
You can use the CFOUTPUT tag to process each item in an array returned from a database. For each record in the database returned, ColdFusion runs the code within the CFOUTPUT tag. This iterative process is called looping.
<CFQUERY name="qget_everything" datasource="FC">
SELECT * FROM users
The preceding code displays every record in the database showing each users name and color.
Another form of reporting is visual. For example, you can display a bar graph of the entries in the database. To get the rows that have blue as the value in the column "color," we can use the SQL:
SELECT * FROM users WHERE color = blue
If you run a query for each color, you can display colored images that are as many pixels wide as the number of rows returned for each query. The images in Figure 4
represent the number of users who have blue, green, red, or none listed as their favorite color.
|Figure 4: The images represent the number of users who have blue, green, red or none listed as their favorite color.
<CFQUERY name="qblue" datasource="FC">
SELECT * FROM users WHERE color = 'blue'
<CFQUERY name="qred" datasource="FC">
SELECT * FROM users WHERE color = 'red'
<CFQUERY name="qgreen" datasource="FC">
SELECT * FROM users WHERE color = 'green'
<CFQUERY name="qnone" datasource="FC">
SELECT * FROM users WHERE color = 'none'
<P><IMG src="blue.gif" height="15"
<P><IMG src="green.gif" height="15"
<P><IMG src="red.gif" height="15"
<P><IMG src="black.gif" height="15"
The number of records for each color will determine the size of each image, resulting in a bar graph.
Because you are referring to several queries, you don't want to specify a query in the CFOUTPUT tag itself. You can refer to the RecordCount of each query specifically. See the bar graph page in action here.
ColdFusion is a powerful Web application programming language. Development time is often speedy and debugging is relatively simple. ColdFusion is a great gateway language for those who are new to the world of programming.