Creating Dynamic SQL ColdFusion Help

In this section, you will add functionality to your quiz application that will enable the user to retake a quiz question if they gave the incorrect response. Here is the process that will occur

1. A user goes to the home. cfm page and answers a quiz question.
2.On the quiz_resul ts. cfm page, they are informed that their answer was incorrect.
3. A link is provided back to the same quiz question that they just answered so they can try again.
4. The process starts again until the user gets the correct answer.

You have already finished the tasks required for steps 1and 2 to be completed. Step 3 requires that you add a link in the quiz_resul ts. cfm template back to the home. cfm template. This link would pass the quest; on_; d parameter. Then in step 4, you need to modify the home. cfm template to determine if a new question should be displayed, or if the question that was answered incorrectly needs to be displayed. Step 3 is easy. You just need to modify the “Please try again” text in your quiz_ resul ts. cfm template. Add the link in Listing 3.5 around this text.

<A HREF-·home.cfm?question_id-#questio~_id#·>Please try again,(/A>

This link will pass the current quest; on_; d back to the home. cfm template.

The link will not retrieve the same question until you modify your home. cfm template. In home. cfm you need to determine if the quest; on_; d is being passed to it. Remember that in Chapter 2 you used the IsDef; ned function to determine whether a variable existed. You will now use the IsDef; ned function in the home. cfm template to determine whether the quest; on_; d has been passed to it. Modify your home. cfm file to reflect the changes indicated in Listing 3.6

<!OOCTYPE HTML PUBLIC ‘-//W3C//DTD HTML 4.0 Transitional//EN’>
G tz __ x
<CFIF NOT lSOEFINEDC’question_id’»
<CFQUERY NAME-‘get_count’ DATASOURCE-‘quiz’ DBTYPE-‘OOBC’>
SELECT countCquestion_id) as total_questions
FROM questions
</CFQUERY>
</CFIF>
<CFQUERY NAME-‘get_question’ DATASOURCE-‘quiz’ DBTYPE-‘OOBC’>
SELECT question_id.
question.
answer1.
answer2.
answer3,
answer4
FROM questions
WHERE question_i~ –
.<CFIF lSOEFINEDC’question_id’»fquestion_idl
<CFELSE>tRandRange C1, get_count. total_questions) I
</CFIF>
</CFQUERY>
<HTML>
<HEAD>
<TITLE>My Home Page</TITLE>
</HEAD>
<BODY BGCOLOR-“FFFFFF’>
<DIV ALIGN-‘center’>
<HR><B>Welcome to My Home Page!</B><HR>
<B>Today’s date is:</B><BR>
<CFOUTPUT>
<!—
The following variable. ‘today’. displays the current
date in the format: Tuesday. September 15. 1998
Now() returns the current timestamp (date & time)
dddd – full day of week
mmmm – full name of month
d – day of month
yyyy – year in four digits
—>
<CFSET today – DateFormat(Now(). “‘dddd. mmmm d. yyyy’»
<I>4ttoday'</I>
</CFOUTPUT>
</DIY>
<!— The following is code that dynamically generates each quiz
question —>
<CFOUTPUT QUERY-‘get_question’>
<FORM ACTION-‘quiz_results,cfm’ METHOD-‘post’>
<P><HR><B>Your Question is:</B>
<BR><I>’questionl</I><P>
<INPUT TYPE-‘hidden’ NAME·’ today , VALUE-“today”>
<INPUT TYPE-‘hidden’ NAME-‘question_id’
VALUE-‘Iquestion_id,’>
<INPUT TYPE-“‘radio’ NAME-‘user_answer’
VAtUE-“answerl”> ‘answerl'<BR>
<INPUT TYPE~’radio’ NAME-‘user_answer’
VALUE-“answer2”> ‘answer2′<BR>
<INPUT TYPE-‘radio’ NAME-‘ user_answer ,
VALUE-“answer3,’> ‘answer3′<BR>
<INPUT TYPE-‘radio’ NAME-‘user_answer’
VALUE-“answer4″> ‘answer4l<BR>
<P><INPUT TYPE-‘Submit’ VALUE-‘Score Question!’>
<INPUT TYPE-‘Button’ VALUE-‘Get New Question!’
ONCLICK-‘location.href-‘~.cfm”>
.</FORM>
</CFOUTPUT>
</BOOY>
-</HTML>

First you told ColdFusion that if the question_; d variable does not exist to run the get count query. If the question_; d variable exists you would not need to run this query because you are not trying to retrieve a random question. You want ColdFusion to display the same question that the user answered incorrectly.

Then, in your second query you told ColdFusion that if the question_; d variable exists, only that particular question_; d should be returned. The WHERE clause sent to the database would look like the following

WHERE question_id – #question_id#

Otherwise, ColdFusion should get a randomly generated question.

The code you just used is called dynamic SQL. It is dynamic because the WHERE clause will vary depending on whether the conditions specified in your CFIF statement are met.

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

Share the Story

Back to Top
Share This