Querying a Database ColdFusion Help

Remember that in the preceding chapter you created a quiz with two questions. The user’s answers as well as the correct answers were then passed through form fields. However any user could look at the source of the home. cfm file and view the correct answers by looking at the hidden form fields. One way to solve that problem is to store all the quiz data in a database and retrieve it when needed as opposed to passing it through URL or form parameters. In this way the quiz information is not stored as code in your templates, which would make it accessible to the general public. The quiz. mdb file provided on the CD has all the pertinent quiz information stored for you (see Figure 3.2

Now you just need to know how to access that information. Cold Fusion makes it easy to retrieve information from a database using a basic CFQUERY tag and SQL. If you are unfamiliar with SQL this chapter is a good place to start learning the basics. SQL is a language that is used to communicate a variety of commands to a database. Using SQL you can do such things as retrieve data update data delete data and append to existing data in a database.

Using CFQUERY and Basic SQL

In this section you will use the CFQUERY tag and a basic SQL statement to retrieve all of the quiz information from the quiz database. You will no longer have to hard-code the quiz information in the template. A CFQUERY tag has both a beginning and ending tag. Between the two tags you define your SQL statement. Look at the following example:

<CFQUERY NAME-‘myquery’ OATASOURCE-‘name_of_datasource’ OBTYPE-‘OOBC’)
SELECT field_namel, field_name2, field_name3
FROM table_name
</CFQUERY>

Notice that the CFQUERY tag takes several attributes. The most commonly used attributes are described in Table 3.1

TAB l E 3. 1:

TAB l E 3. 1:

Now/look at the SQL query in the preceding example ..This SQL query is a basic SELECT query the most common type of query. It is used to retrieve data from a database. Although we will be using only a SELECT query in this chapter you might find it useful to know what other types of queries are available. You ‘will be using them in other parts of this book. The various query types are described in the following list:

SELECT Used to search a database and retrieve information
DELETE Used to delete records from a database
UPDATE Used to change or modify information in a database
INSERT Used to append additional records to a database

Now you can code the CFQUERY tag and SQL SELECT statement for your home. cfm file The CFQUERY tag should LOOK like the following:

<CFQUERY NAME~’get_question DATASOURCE=”quiz” DBTYPE=’ODBC’>

When coding a SQL SELECTstatement, you must first select the information that you would like to retrieve. First determine what information you need to display to the user. When the user views the home. cfm template, they should see each question with four possible answers. Your database table quest; ons has the following field names that would give you this information:

• question
• answer 1
• answer 2
• answer 3
• answer 4

It is also useful to retrieve the question_ id field because in the next section you will be passing that information to the quiz_resul ts. cfm template so that each question can be easily identified. Therefore the first part of your SQL statement should look like this:

SELECT question_id, question, answerl, answer2. answer3. answer4

In a SELECT statement, you begin with the word SELECT and then follow it with the names of the fields that you wish to retrieve. The fields should be in a commadelimited list.

Now you need to tell the database which table to find the information in. The quiz database has only one table but it is possible for a database to have many tables, so you need to specify the table to which you are referring. Because the information you need is in the questions table your next SQL command should look like:

<CFQUERY NAME-‘get_question’ DATASOURCE-‘quiz’ DBTYPE-‘ODBC’)
SELEcr question_id.
question.
answerl.
answer2.
answer3.
answer4
FROM questions
</CFQUERY>

The results that will be returned from this query are called a record set. Add this code to the top of your home. cfm template.

You cannot test the results of your query yet because you have not added any code that would output the results into the home. cfm template. In the next section you will learn how to output the results of your query

Outputting Query Results

Now that you have queried the quest; ons table, you need to output the results in your home. cfm file. You will do this using the CFOUTPUT tag. When outputting results from a query, the name of the query being referenced needs to be added to the CFOUTPUT tag by using the QUERY attribute. Your beginning CFOUTPUT tag should look like the following

<CFOUTPUT QUERY·’get_question’>

The CFOUTPUT tag is then followed by HTML and CFML code. This code is used to display and format each record that is returned. ColdFusion processes this code for each record returned by the query. If the query produced 10 records, then the code inside the CFOUTPUT tag would be processed 10 times

When ColdFusion finishes processing all the records returned it continues on to the rest of the code in your template

In the preceding chapter you hard-coded two questions in the home. cfm template. In this section you need to provide CFML and HTML code for only one question. This code will be placed inside the CFOUTPUT tags for your get_quest; on query. ColdFusion will then loop through this code and display as many questions as have been returned by your query. Modify your home. cfm file to reflect the changes in Listing 3.2

<!DOCTYPE HTML PUBLIC ‘-//W3C//DTD HTML 4.0 Transitional//EN’>
<CFQUERY NAME-‘get_quest;on’ DATASOURCE-‘qu;z’ DBTYPE-‘OOBC’>
SELECT ques~;on_id,
question,
answerl,
answer2
answer3,
answer4
FROM qll.s~ions
</CFQUERY>
<HTML>
<HEAD>
<TITLE>My Home Page</TITLE>
</HEAD>
<BODY BGCOLOR-‘#FFFFFF’>
<DIV ALIGN-‘cente~’>
<HR><B>welcome to My Home Page!</B><HR>
<B>Today’s date is:</B><BR>
<CFOUTPUT>
<!—
The follOMring variable, ‘today’, displays the current
date in the format: Tuesday, September 1~, 1998
Nowe) returns the current timest~mp (da~. & time)
dddd – full day of week
mmmm – full name of month
d – day of month
yyyy – year in four digits
—>

<CFSET today w DateFormat(NowO, ‘dddd, mmmm d, yyyy’»
<I>#tcday#</I>
</CFOUTPUT>
</OIV>
<!— The following is code that dynamicall} generates each quiz
question —>
<CFOUTPUT QUERY~”get_question”>
<FORM ACTION=’quiz3esults.cf’ll” METHOO=”post”>
<P><HR><8>Question ###currentrow# i5:</8>
<BR><I>#question#<jI><P>
<INPUT TYPE=”hidden’ NAMEc’today” VALUE=”#today#”>
<INPUT TYPE-‘hidden’ NAME=”question_id”
VALUE-‘#question_id#”>
<INPUT TYPE-‘radio’ NAMEa”user_answer’
VALUEz’#answerl#’> #answerl#<BR>
<INPUT TYPEc’radio” NAME-“u5er_answer”
VALUE-‘#answer2#”> #answer2#<BR>
<INPUT TYPE-“radio” NAMEw” user_answer ,
VALUE-“#answer3#’> #answer3#<BR>
<INPUT TYPEc’radio” NAMEs” user_answer’
VALUE-“#answer4#”> #answer4#<8R>
<P><INPUT TYPEs’Submit’
VALUE-‘Score Question #currentrow#!”>
</FORM>
<jCFOUTPUT>
</body>
</HTML>

After you save your changes you can view the results by opening the following

You will notice that all 10 questions and their corresponding answers from the database table questions have been displayed. Imagine how much code you would have had to write if you hard-coded all 10 questions yourself

You also made several other significant changes:

• By using the system variable current row you were able to number all the questions returned. The variable current row is a system variable generated by Cold Fusion for each record of a particular query. It is used to keep track of which record count ColdFusion is currently processing. It is not related to the primary key field in your database.

• Instead of hard-coding the answers for each question, you were able to dynamically generate them by outputting the answer fields from the query results.
• You passed the question d field as a hidden field to the next template. In the next template you can use it to query the database and compare the user’s answer to the correct answer.

Now that you have created a dynamically generated quiz in your home. cfm template you must also slightly modify your quiz_resu 1ts . cfm template to accommodate these changes.

Retrieving a Limited Recordset

In the last section you learned how to retrieve all recor is from a database using a CFQUERY tag and SQL. What ifyou want to retrieve only one record? Then you must limit the recordset returned by your query. You will do that in this section by adding a WHERE clause to your SQL statement. This WHERE clause will help to narrow the record set returned by your query by making the results conditional upon some statement.

You previously coded the home. cfm template so that it would pass the following parameters t.o.the quiz_resul ts. cfm template:

today The date that the quiz was taken.
question_ id . The ID for the question that the user responded to.
user_answer The answer chosen by the user for the above question_ d.

This is one less parameter than you passed in the preceding chapter. You are no longer passing the correct_answer parameter because you can now pull that from the database. In the quiz_resul ts. cfm template you will how retrieve only the information related to the question that the user chose to answer instead of retrieving all possible information from the database. This is where the question_; d comes into play.

To pull only the data related to one record from the database, you need to add a WHERE clause to your SQL statement. The basic format is written as the following:

<CFQUERY NAME=’myquery’ DATASOURCE=’name_of_datasource’ DBTYPE=’ODBC’)
SELECT field_namel,
f;eld_name2,
fioeid_name3
FROM table_name
WHERE f;eld_namel – ‘some_value’
</CFQUERY>

In your quiz_results . cfm template you will need to get the following fields from· the database:

question You need to display the question chosen by the user but because you did – not pass this value through a form parameter you need to get it from the database.

correct_answer After the correct answer is returned from the database, you can compare it to the answer chosen by the user to see whether rt was correct

When you code your SQL statement, you want to retrieve only the record that contains the question chosen by the user. The chosen record can be identified by using the question_ i d parameter that you passed through a hidden form field. Because question_ i d is a Cold Fusion variable, you need to surround it with signs and it will be evaluated when Cold fusion processes the query.

Your entire query should look like this:
<CFQUERYNAME-‘get_answer’ DATASOURCE=’quiz’ DBTYPE-‘ODBC’)
SELECTques’ ion. correct_answer
FROMquestions
WHEREquestion_id a #question_id#
</CFQUERY>

The results of this query will be limited to all records that have e field questi on_ i d equal to the ColdFusion vanable questi on_ i d. For example. if the user chose a question with the questi on_ i d of 4,

when ColdFusion processes the query e ERE clause that is sent to the database will be WHERE question_ i d. In your template you will also need to output the results of this query. You have already written the code to compare the user_answer to the correct_answer so very little modification to this file is necessary. Please make the changes indicated in Listing 3.3 to your quiz_results. cfm template

<!OOCTYPEHTMLPUBLIC ‘-//W3C//DTD HTML4.0 Transitional//EN’>
<HTML>
<HEAD>
<TITLE>Quiz Results</TITLE>
</HEAD>
<BODY BGCOLOR=’#FFFFFF’>
<CFIF NOT ISDEFINEDC’Form.user_answer’) OR
NOT ISDEFINEDC’Form.question_id’) OR
NOT ISDEFINED(‘Form.today’»
<DIV ALIGN=’~enter’>
<HR><B>Welcome!</B><HR>
<I><A HREF=’home.cfm’>Please take my quiz!</A></I>
</DIY>
<CFABORT>
</CFIF>
<CFQUERY NAME=’get_answer’ DATASOURCE=’quiz’ DBTYPE=’OOBC’>
SELECT question, correct_answer
FROM questions
WHERE question_id fForm.question_idf
</CFQUERY>
<DIV ALIGN=’ center’>
<HR><B>Qu;z Results!</B><HR>
</DI<YB>>Tha~k ,y.ou for taking my short qu;z!</B><P>
<CFOUTPUT QUERY-‘get_answer’>
<CFIF Form.user_answer IS correct_answer>
<B><FONT COLOR-‘ffOO8000’>Congratulations!!!
You are correct!</FONT></B><P>
<CFELSE>
<B><FONT COLOR-‘ffFFOOOO’>
I’m sorry, but that is the wrong answer.
Please try again.</FONT></B><P>
</CFIF>
<B>The question was:</B> fquestion#<BR>
<CFIF Form.user_answer IS NOT correct_answer>
<B>Your answer was:</B> #Form.user_answer#<BR>
<CFELSE>
<B>The correct answer ;s:</B> #correct_answer#<BR>
</CFIF>
<B>You took the quiz on:</B> #Form.today#<P>
</CFOUTPUT>
<DIV ALIGN=’center’>
<I><A HREF-‘home.cfm’>Take the quiz again?</A></I>
</DIY>
</BODY>
</HTML>

FIGURE 3.10

FIGURE 3.10

You will notice that not much seems to have changed on the surface. But in reality you have made the following signific.ant changes in the way your quiz application works:

• You are no longer passing the correct answer through a hidden form field or the URL so the user cannot cheat on the quiz.
• You are pulling all your quiz information from the database.
• You have reduced the amount of code needed to create the application.
• You can add as many questions to the database table as you wish, yet you do not need to make changes to your Cold Fusion templates for your application to work.

You have learned the basics of querying a database table by using the CFQUERY tag and SQL. In the next sections you will add some more functionality to your quiz application by learning to do the following

• Display a randomly generated question
• Create SQL statements based on user input

Posted on November 14, 2015 in Getting Data From a Database

Share the Story

Back to Top
Share This