Generating Random Results ColdFusion Help

Currently, your quiz application retrieves and displays all questions entered in the database. If you had 50 questions, all 50 cold be displayed to the user on the home. cfm template. In this section, you will learn how to display one randomly generated question from all the questions available in the database.

In the preceding section, you learned how to limit the recordset returned in the qui zJesul ts. cfm template bg using the WHEREclause. In this section, you will also limit the recordset returned in the home. cfm template-but this time the record returned will be randomly generated.

First, you need to add a WHERE clause to the query in your home. cfm template. The format should be as follows:

WHERE quest;on_;d – randomly_generated_number

how do you go about getting a randomly generated number? Cold Fusion has a function that lets you generate random numbers. It is called the RandRange function. The RandRange function takes two values:

RandRange(number1, number2)

The randomly generated number will fall between the two values entered for numberl and number2. So if number 1 is 10 and number2 is 300 then the randomly generated number would have to fall somewhere between 10 and 300. In this example the number returned could not be 8.

For the quiz application you will{enerate a random quest; on_; d. If you look at the quest; ons table you will notice that the quest; on_; d values fall between 1 and 10. You could code the WHEREclause by incorporating these numbers:

WHERE quest;on_;d – #RandRange(l, 10)#

This would work, unless you added another question to the quest; ons table or deleted one from it (in which case the query will not return any data when quest; vn_; d is 10). Any new questions you add to the table will be excluded because their quest; on_; d values would be higher than 10 (the limit you placed on the randomly generated number).

So, to accommodate the possibility of any number of questions in the table you need to first find out how many records are in the table at any given time. To do this you can query the database for a total count and then use that count in your existing query. This means you will have two queries in your home. cfm template

In the new query you will use a SQL function called Count to get the count. The Count function takes one value: the field that you want to be counted. You must then give this count a name so that ColdFusion can reference the results:

<CFQUERY NAME-‘get_count’ DATASOURCE-‘qu;z’ DBTYPE=’ODBC’)
SELECT count(quest;on_;d) as total_questions
FROM questions
</CFQUERY)

This query returns only one value: the count of the quest; on_; d field. Because the question_; d field is the primary key field, it can never be blank. So, the count of this field will always be equal to the count of records in the database. The as keyword places the value retrieved by the database in a variable named tota ‘_questions so that subsequently, the value can be referred to as get_count. tot a ‘_questions.

Now that you have the count of records in the table, you can 1 back to your original query and generate a random quest; on_; d value like’ the for wing:

<CFQUERY NAME-‘get_quest;on’ DATASOURCE-‘quiz’ DBTYPE=’ODBC’)
SELECT questi on_ id,
question,
answ.erl,
answer2,
answer3,
answer4
FROM questions
WHERE question_id – #RandRange(~, get_count.total_quest;ons)#
</CFQUERY>

You will notice that the total count has been referenced as get_count. questions in the RandRange function. When referencing data in a query from another query you should always precede the variable with the name of the referenced query. In this case get_count is the name of the query that returned the total_questions variable. Make the changes indicated in Listing 3.4 to your home. cfm template.

<!DOCTYPE HTML PUBLIC ‘-//W3C//DTD HTML 4.0 Transitional//EN’>
<CFQUERY NAME-‘get_count’ DATASOURCE*’quiz’ DBTYPE=’OOBC’>
SELECT count(question_id) as total_questions
FROM questions
</CFQUERY>
<CFQUERY NAME=’get_question’ DATASOURCE-‘quiz’ DBTYPE=’ODBC’>
SELECT question_id,
question,
answerl,
• answer2,
answer3,
answer4
FROM questions
WHERE question_id • #RandRange(l, get_count.total_questions)#
</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>#today#</I>
</CFOUTPUT>
</DIY>
I.
<!— The following is code that dynamically generates each quiz question —>
<CFOUTPUT QUERY-‘get_question’>
<FORM ACTION-‘quiz_results.cfm’ METHOD-‘post’>
<P><HR><B>’our Question is:</8>
<BR><I>#question#</I><P>
<I’lPUT TYPE= hidden” NAME-“today” VALUE=’#today#’>
c”JJT T’TDE= hidden NAME=”question_id
,~_lE= =~~es:ion_id= >
<INPUT TYPE=’radio” NAME-“user answer
\!LU~= =answerl~ > #answerl#CBR>
<I”lPUT TYPE-‘ radio” NAME-‘user _answer”
V~LUE= :answer2# > #answer2#<BR>
<INPUT TYPE= radio’ NAME=”user _answer’
VALUE=’~answer3#’> #answer3#<BR>
<INPUT TYPE=” radio , NAME=’user_answer’
VALUE-‘#answer4#’> #answer4#<BR>
<P><INPUT TYPE=”Submit” VALUE-“Score Question!”>
<INPUT TYPE-‘Button” VALUE-“Get New Question!’
ONCLICK-‘location,hrefm’home.cfm’ ‘>
</FORM>
</CFOUTPUT>
</BOOY>
</HTML>

Now view the changes in your browser (see Figure 3.11). If you receive an error please make sure that your code matches the preceding code.

FIGURE 3.11

FIGURE 3.11

Here’s a look at what you did

1. Determined how many records existed in the questions table.
2. Modified your query to return a random record.
3. Removed the references to the current row (because only one record is returned).
4. Added a form button using JavaScript in order to refresh the page and get a new quiz question. (This is needed just in case a user gets two of the same quiz questions in-a row.)

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

Share the Story

Back to Top
Share This