Trivia Quiz Database

In the previous chapter we made our first use of server-side scripting in the trivia quiz. In this chapter we build on that and use server-side script to access a database that contains all the questions and answers for our quiz, data that previously we kept client-side in arrays. Arrays are fine up to a point, but can you imagine how big our pages would be if we had 1000 or more questions in the quiz? Also, as the code starts to become unmanageable, trying to add or edit questions involves modifying many lines of code. Additionally, if the information is in a database, it makes it possible for people without JavaScript skills to add, delete, and modify questions. We could even write a web page that provides that functionality wrapped up in an easy-to-use interface, so users would not even need to understand databases.

Creating the TriviaQuestions Database

We'll be using Microsoft Access again for our database. This time we are connecting using OLE DB and specifying the file location, rather than creating an ODBC data source and specifying that.

Before we create the database, let's look at the table structure. The database will be nice and simple, just two tables. The first table is the Question table shown Figure 17-42.

Figure 17-42

This table contains all the questions and answers for our quiz. It'll consist of four fields: QuestionId, Questionhtml, QuestionType, and Answer. The QuestionId is an AutoNumber field and will allow us to uniquely identify a question, which will be useful when we look up the answer. Questionhtml contains the text of the question, such as "What is Homer Simpson's favorite food?"

Some questions require a text answer typed into a text box. Others, such as Homer's favorite food question, are multiple-choice questions. The field QuestionType will contain a number indicating the type of question, such as plain text or multiple-choice, that the record deals with.

Finally in the Answer field, we store a letter for the correct multiple-choice answer, or a regular expression, which should match the correct text-based answer.

If the question is a multiple-choice question, we'll include each of the options given to the user in a second table, the QuestionOptions table shown in Figure 17-43.

Figure 17-43

Each answer option for a multiple-choice question has its own record in the QuestionOptions table. For example, if in the Question table we have "What is Homer's favorite food?" records in the QuestionOptions table might include doughnuts, apples, fresh salad, and so on.

We can see that the first field in this table is the QuestionId. The value in this field for a particular record will be the same as the value in the QuestionId field of the question record in the Question table that this option is a possible answer for.

We've used the OptionOrder field of the QuestionOptions table to specify the order for the options for any particular question. Finally, the text displayed to the user for each option is given in the AnswerOption field.

The big question is, "Why not have just one table and put all the options in there?"

We could define extra fields in the Question table, for example an Option1 field, Option2 field, and so on, and then store all the possible multiple-choice options in these fields. However, there are problems with this. First, text-based questions don't have options, so the extra fields would be wasted for these questions. Second, how many option fields should we define? Three? Four? Five?

If, for some strange reason, we decide later that we want to have some questions with eight options, we have to go back to the database and add extra fields, which for most of the time are not used.

By including the options as rows in another table, we help reduce redundant fields and we can choose to have as many or few options for each question as we like. After all, options are just rows or records in the QuestionOptions table. We'll also see later that having a separate table for the options makes the programming a little easier. We can create the options in the page by just looping through a recordset from the QuestionOptions table until we hit Eof, rather than having to find some way of guessing how many fields are used and how many are redundant.

Having looked at the table structure, let's start creating the database and its tables.

Our first task is to open up Access. At the initial start up, select the Blank Access database option and click OK. (See Figure 17-44.)

Figure 17-44

In the next dialog box, shown in Figure 17-45, we're given the option to name and save the database. Call it TriviaQuestions.mdb. I've saved mine in the C:\Temp directory, but any directory is fine. Remember where you save the database, because its location will be used in the web pages later. Click Create.

Figure 17-45

This takes us to the main database view, shown in Figure 17-46, with the Tables tab selected under Objects.

Figure 17-46

We'll create both of our tables by double-clicking the Create table in Design view option.

Create the Question table first. The fields are shown in Figure 17-47, along with suggested field sizes, which we change under the General tab.

Figure 17-47

Make the QuestionId field a primary key by clicking the field; then click the primary key icon in the toolbar (the yellow key icon). Finally, save the table and close it.

Now create the QuestionOptions table in the same way. Its fields are shown in Figure 17-48.

Figure 17-48

This time we'll make two fields into a primary key. Click the gray box to the left of the QuestionId field; then while pressing the Ctrl key, click the gray box next to the OptionOrder field; both rows should be blocked in black. Now click the primary key icon in the toolbar and both fields should become part of a primary key, each with a key icon next to it.

Why do we need to make both fields part of the primary key? Why not just the QuestionId field?

We'll be storing a number of options for each question, so the same QuestionId will appear in a number of records. Primary keys must be unique, so the QuestionId field on its own can't be a primary key. However, QuestionId and OptionOrder combined do provide the uniqueness we need to define a primary key.

Save the table, and then close it.

Our next task is to define the relationships between the two tables.

Select the Relationships option from the Tools menu. Because it's the first time we've opened the diagram we'll be given the chance to add our two tables to the diagram. Add both tables; then click the Close button. (See Figure 17-49.)

Figure 17-49

With the Relationships diagram now open and our two tables displayed, click on the QuestionId field in the Question table and, holding the mouse button down, drag over to the QuestionId field in the QuestionOptions table; then let go. The Edit Relationships dialog box should appear as shown in Figure 17-50. Make sure you check the Enforce Referential Integrity box. Doing so will prevent question option records for which there are no questions from appearing in the QuestionOptions table.

Figure 17-50

Finally, click the Create button to create the new relationship. Save the diagram and close it to return to the table objects view.

All that's left is to put some questions in the database.

Open the Question table in datasheet view by double-clicking the Question table and enter the questions as shown in Figure 17-51. The questions are identical to those in the questions[] array that we used in previous versions of the trivia quiz. Be careful entering the answers. If they are even one character off, the user's answers will be wrong.

Figure 17-51

Finally, add the data for the QuestionOptions table, as shown in Figure 17-52.

Figure 17-52

That completes the database's creation and population with questions. Now it's time to look at the changes to the trivia quiz's web pages.

Changes to the Trivia Quiz Logic

Although most of the logic for the trivia quiz has stayed the same, the screen flow and methods of passing data will be changed.

The frameset structure will remain the same as it has been since Chapter 7, but new pages that load into the fraQuizPage frame will be added. Let's look at what happens when a user first clicks to start a quiz and answers the first question:

Let's look at each stage in the diagram. First the user browses to the TriviaQuiz.htm page on our website. In the QuizPage.asp page loaded into the fraQuizPage frame, the user selects the options for the quiz, such as how many questions to answer and in what time frame, from the drop-down lists. Then the user clicks the Start Quiz button, which starts a timer by calling a function in the Globalfunctions.htm page in the top frame. Next, the form containing the quiz options is submitted to the NoteUserSelections.asp page for processing.

The NoteUserSelections.asp page simply resets any session variables holding quiz data, such as the number of questions asked, and then notes the user's quiz choices posted by the form in TriviaQuiz.htm. Finally, it redirects the user to the AskQuestion.asp page, so in fact the user never actually gets to see the NoteUserSelections.asp page.

The AskQuestion.asp page gets a question from the database, and then writes this out, server-side, to the page. This page is then sent to the user and includes a form in which either radio buttons or a text box enable the user to enter an answer. The form includes a hidden text box containing the QuestionId.

When the Answer Question button is clicked, the form is posted to the page CheckAnswer.asp. Using the QuestionId posted in the hidden text box, the server-side JavaScript in CheckAnswer.asp looks up the answer for that question and compares it to the answer posted in the form. If they match, an image is displayed telling the user that the answer was correct, otherwise an image letting the user know the answer was wrong is written to the page.

Session variables are used to hold the number of questions to be asked and the total number of questions already asked. These variables are updated as each question is asked and checked within the AskQuestion.asp and CheckAnswer.asp pages.

After a question's answer has been checked in the CheckAnswer.asp page, if there are still more questions to ask, a button is written to the page by server-side JavaScript. The button, when clicked by the user, fires client-side JavaScript, which loads the AskQuestion.asp page and the whole process of asking questions and checking answers starts again.

If, however, all the questions have been asked, a summary of how the user did is created by server-side JavaScript in the page FinalResults.asp, which is included into CheckAnswer.asp, and is written out to the page, along with buttons allowing the user to restart the quiz and reset the statistics.

All this time, the timer that was started at the beginning of the quiz by the function in Global_Functions.htm has been running. If at any time during the quiz, the time limit runs out, the quiz page is replaced by another page, OutOfTime.asp, which again includes FinalResults.asp that will write the results to the page.

As we can see, there is quite a lot to be done. However, now that we have an idea of how things will work with the latest version of the quiz, let's start making the changes and creating the new pages, looking at each page in turn.

Changes to QuizPage.asp

We have a couple of small changes to make to the QuizPage.asp page that begins the quiz.

Previously, when the quiz was started by the user using the cmdStartQuiz_onclick() function, we called a client-side JavaScript function resetQuiz() in the GlobalFunctions.htm page. In this function, we reset various global client-side variables. However, in this version of the quiz we are moving virtually all of our global client-side variables to the server-side and storing them as session variables.

Because of this, we no longer need to reset client-side variables in the GlobalFunctions.htm page so the resetQuiz() function can be deleted from there, as we'll do shortly.

Our cmdStartQuiz_onclick() function only needs to call the startTimer() function in Global_Functions.htm to start a timer going if the user has selected a time limit, and then submit the quiz start values, that is, the number of questions to answer, to an ASP page that will process them server-side.

Change cmdStartQuiz_onclick() function inside the page's header as follows:

<!DOCTYPE html PUBLIC "-//W3C//DTD html 4.01 Transitional//EN"
"http://www.w3.org/tr/html4/loose.dtd">
<html>
<head>
<META HTTP-EQUIV="expires" CONTENT="Mon, 1 Jan 1980 00:00:00 GMT">
<script language=JavaScript type="text/javascript">
function cmdStartQuiz_onclick()
{
   var timeLimit =
document.frmQuiz.cboTimeLimit.options[document.frmQuiz.cboTimeLimit.selectedIndex]
value
   window.top.fraTopFrame.fraGlobalFunctions.startTimer(timeLimit);
   document.frmQuiz.submit();
}
</script>
</head>

The first line of our new cmdStartQuiz_onclick() function gets the value of the time limit selected by the user from the drop-down list element named cboTimeLimit. Remember it's the option that was selected by the user whose value we need. We find out which option is selected using the select control's selectedIndex property, the value of which we use to access the selected option in the options array.

In the second line we call the startTimer() function in the GlobalFunctions.htm page, passing the time limit that the user selected as a parameter.

Finally we submit the frmQuiz form to the server. In this case our <form> tag's ACTION attribute is NoteUserSelections.asp and it's this page that will deal with the quiz startup values.

We've completed the changes to QuizPage.asp, so we can save it and close it.

Changes to GlobalFunctions.htm

While we're thinking about the changes to QuizPage.asp, we may as well make the changes to the GlobalFunctions.htm page. It is mostly a matter of deleting the majority of the functions, with a few exceptions. We remove all the code defining the questions, along with the answercorrect(), getquestion(), and resetquiz() functions. We add two new functions, stopTimer() and startTimer(), and make a small change to the UpdateTimeLeft() function. Finally, we remove the getCookieValue() function and leave the setCookie() function unchanged. Here is the new GlobalFunctions.htm page in full:

<!DOCTYPE html PUBLIC "-//W3C//DTD html 4.01 Transitional//EN"
"http://www.w3.org/tr/html4/loose.dtd">
<html>
<head>
<script language=JavaScript type="text/javascript">
var timeLeft =-1;
var quizTimerId = 0;
function stopTimer()
{
   window.clearInterval(quizTimerId)
}
function startTimer(timeLimit)
{
   timeLeft = timeLimit;
   if (timeLimit == -1)
   {
      window.status = "No Time Limit";
   }
   else
   {
      quizTimerId = window.setInterval("updateTimeLeft()",1000);
   }
}
function updateTimeLeft()
{
   timeLeft--;
   if (timeLeft == 0)
   {
      alert("Time's Up");
      window.top.fraQuizPage.location.href = "OutOfTime.asp";
   }
   else
   {
      var minutes = Math.floor(timeLeft / 60);
      var seconds = timeLeft - (60 * minutes);
      if (minutes < 10)
      {
         minutes = "0" + minutes;
      }
      if (seconds < 10)
      {
         seconds = "0" + seconds;
      }
      window.status = "Time left is " + minutes + ":" + seconds;
   }
}
function setCookie(cookieName,cookieValue, cookiePath, cookieExpires)
{
   cookieValue = escape(cookieValue);
   if (cookieExpires == "")
   {
      var nowDate = new Date();
      nowDate.setMonth(nowDate.getMonth() + 3);
      cookieExpires = nowDate.toGMTString();
   }
   if (cookiePath != "")
   {
      cookiePath = ";Path=" + cookiePath;
   }
   document.cookie = cookieName + "=" + cookieValue +
      ";expires=" + cookieExpires + cookiePath;
}
</script>
</html>

Let's look at the two new functions, stopTimer() and startTimer(); the names give away what they actually do.

The function stopTimer() uses the clearInterval() method of the window object to stop the firing of the quiz timer every second. This function is called when the time limit is up or when the user has completed the quiz. It is called by a new page that we'll be creating shortly, which deals with the end of the quiz.

The function startTimer() is based on code in the resetQuiz() function, which has now been removed, so you can cut and paste it from there. The function's only parameter is the time limit, in seconds, that the user selected to complete the quiz. The code checks to see what this time limit is. If it's -1, which indicates that no time limit has been selected, we write "No Time Limit" to the status bar. If a time limit has been selected, we start the timer going by using the window object's setInterval() method so that every second the updateTimer() function is called.

Our next change is to the upDateTimeLeft() function and is just a one-line change. When the time limit expires, we redirect the user to the page OutOfTime.asp, where server-side script will create a summary of the user's results and display a button allowing the user to restart the quiz. We'll be creating OutOfTime.asp shortly. Recall that, previously, we redirected the user to AskQuestion.htm, and the results-displaying functionality was bound up in the getQuestion() function in GlobalFunctions.htm, which was called from there.

Save the GlobalFunctions.htm page and close it. We have no more changes to make to it.

Changes to NoteUserSelections.asp

The final change as far as beginning the quiz is to the page NoteUserSelections.asp, which we created in Chapter 15.

<%@ language = JavaScript%>
<%
   Session("NoQsToAsk") = parseInt(Request.Form("cboNoQuestions"));
   Session("TimeLimit") = parseInt(Request.Form("cboTimeLimit"));
   Session("totalQuestionsAsked") = 0;
   Session("AskedQuestions") = "0";
   Session("numberOfQuestionsCorrect") = 0;
   Response.Redirect("AskQuestion.asp");
%>

All the data previously held client-side in the GlobalFunctions.htm page, such as the number of questions to ask, how many have been asked, how many the user has gotten right and so on, are initialized and stored server-side in this page. We've used session variables, stored in the Session object, to keep track of these essential quiz variables. With the changes made to NoteUserSelections.asp file we can now close it.

Once the global quiz data has been stored, we redirect, using Response.Redirect(), to the AskQuestion.asp page, which we'll look at next.

Asking a Question: Changing AskQuestion.htm

Previously, questions were posed in the AskQuestion.htm page. Because it's an .htm page, we're not able to do any server-side processing inside it. Now that we need to access the questions from a server-side database, we need to change the AskQuestion page's file extension to .asp so that our web server will read through it and process any server-side script before sending it to the client.

Having resaved the page as AskQuestion.asp, let's look at the changes we need to make to the page.

<%@ language = JavaScript%>
<!DOCTYPE html PUBLIC "-//W3C//DTD html 4.01 Transitional//EN"
"http://www.w3.org/tr/html4/loose.dtd">
<html>
<head>
<META HTTP-EQUIV="Expires" CONTENT="1 Jan 1980">
</head>
<body background="bluewashqs.jpg">
<table align=center border="2" width="70%">
<tr>
<td bgcolor=RoyalBlue>
<form name="QuestionForm" method=post action="CheckAnswer.asp">
<%
   Session("totalQuestionsAsked")= parseInt(Session("totalQuestionsAsked")) + 1;

   var questionId;
   var mySQL;
   var adoConnection = Server.CreateObject("ADODB.Connection")
   adoConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data" +
      " Source=C:\\Temp\\TriviaQuestions.mdb")
   mySQL = "select top 1 QuestionId, Questionhtml, QuestionType from Question ";

   mySQL = mySQL + " where QuestionId NOT IN (" +
      Session("AskedQuestions") + ")";
   var adoRecordset = adoConnection.Execute(mySQL);
   questionId = adoRecordset("QuestionId").Value;
   Session("AskedQuestions") = Session("AskedQuestions") + "," + questionId;
   if (adoRecordset("QuestionType").Value == 1)
   {
      mySQL = "select OptionOrder, AnswerOption from QuestionOptions ";
      mySQL = mySQL + " where QuestionId = " + questionId;
      mySQL = mySQL + " order by OptionOrder"
      var adoOptionsRecordset = adoConnection.Execute(mySQL);
   }
   Response.Write("<h4>Question " + Session("totalQuestionsAsked") + "</h4>");
   Response.Write("<input type=hidden name=txtQuestionId" +
      " value=" + adoRecordset("QuestionId").Value + ">");
   // Check QuestionType field - if 2 then text based question
   if (adoRecordset("QuestionType").Value == 2)
   {
      Response.Write("<P>" + adoRecordset("Questionhtml").Value + "</P>");
      Response.Write("<P><input type=text name=txtAnswer");
      Response.Write(" maxlength=100 size=35></P>");
   }
   else
   {
      Response.Write("<P>" + adoRecordset("Questionhtml").Value + "</P>");
      while (adoOptionsRecordset.Eof == false)
      {
         Response.Write("<input type=radio name=radQuestionChoice");
         if (adoOptionsRecordset("OptionOrder").Value == 1)
         {
            Response.Write(" checked");
         }
         Response.Write(" value=" + adoOptionsRecordset("OptionOrder").Value);
         Response.Write(">" + adoOptionsRecordset("AnswerOption"));
         Response.Write("<br>");
         adoOptionsRecordset.MoveNext();
      }
      adoOptionsRecordset.Close()
      adoOptionsRecordset = null;
   }
   adoRecordset.Close();
   adoRecordset = null;
   adoConnection.Close();
   adoConnection = null;
%>
<br>
<input type="submit" value="Answer Question" id=submit1 name=submit1>
</form>
</td>
</tr>
</table>
</body>
</html>

As we can see, it's pretty much a whole new page bearing little resemblance to its previous client-side-only incarnation. However, we'll see that much of its logic and code is actually borrowed from the now defunct client-side getQuestion() function that we just deleted from the GlobalFunctions.htm page.

In the previous incarnation of the page, the body of the page held a form, inside which the getQuestion() function was called, which wrote the trivia quiz question to the page. In the new page, the body still contains a form, but the form contains a block of server-side script that accesses our database and writes the quiz question to the page.

We add two attributes to the <form> definition: method, to which we assign the value post, and action, to which we assign the value CheckAnswer.asp. Thus, when the form is submitted, it will be posted to CheckAnswer.asp, which will check the user's answer to the question. We will look at this in more detail in the next section.

<form name="QuestionForm" method=post action="CheckAnswer.asp">

Now let's look at the server-side script block contained within the form.

As the following lines show, we start the script block by incrementing the Session variable totalQuestionsAsked, which is used to keep track of the number of questions that the user has answered.

<%
   Session("totalQuestionsAsked") =
      parseInt(Session("totalQuestionsAsked")) + 1;

Next, after declaring two variables, we create an ADO Connection object, which will be used to connect to our TriviaQuestions database. We then open a connection using the Open() method of the Connection object.

   var questionId;
   var mySQL;
   var adoConnection = Server.CreateObject("ADODB.Connection")
   adoConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
      "Data Source=C:\\Temp\\TriviaQuestions.mdb")

Note that here we are using OLE DB rather than ODBC to connect to the database. This means we don't need to create an ODBC data source as we did in previous examples. In practice, an ODBC data source would be fine. I just want to demonstrate the OLE DB way. If your database is in a directory other than C:\Temp, change the last line shown to point to the relevant directory. Note that we use two back slashes rather than one when specifying the directory path. Remember that in strings, a single back slash is a special character, an escape character, which allows unprintable characters to be embedded in a string. So to tell JavaScript that the escape character is in fact an actual back slash, we need to use the first back slash as an escape character and the second to indicate that the special character being inserted is a back slash.

In the next two lines we create the SQL we want to execute against the database; then we use the Connection object's Execute() method to run it.

   mySQL = "select top 1 QuestionId, Questionhtml, QuestionType from Question ";

   mySQL = mySQL + " where QuestionId NOT IN (" +
      Session("AskedQuestions") + ")";
   var adoRecordset = adoConnection.Execute(mySQL);

The SQL statement itself simply returns a question from the database. We want only one question, so we've used the top SQL statement, which tells the database we just want the top, or first, record from the recordset. Note that this means that we get the questions in the same order every time we run the quiz, unlike the random order of the client-side version. However, once you've completed the chapter you might want to go back and write code that gets a random question.

To avoid getting the same question twice, we use a where clause and the SQL NOT IN statement. The NOT IN operator simply compares the values of the QuestionId field in the Question table to a list of values and picks only records that don't have a QuestionId in that list.

select top 1 QuestionId, Questionhtml, QuestionType from Question ";
where QuestionId NOT IN (1,2,3)

The preceding code will not return any records whose QuestionId is 1, 2, or 3.

Each time we ask a question we make a note of it in the session variable AskedQuestions, and this variable provides the list for the NOT IN statement.

When the SQL is run against the database, the record returned by our select query is returned inside a Recordset object whose value we store in the adoRecordset variable.

The next two lines of code retrieve the QuestionId from the recordset and update the session variable AskedQuestions.

   questionId = adoRecordset("QuestionId").Value;
   Session("AskedQuestions") = Session("AskedQuestions") + "," + questionId;

If the question returned by our SQL query is a multiple-choice question, that is, the QuestionType field has value of 1, we need to get the multiple-choice options for this question from the QuestionOptions table. We do this in the next lines.

   if (adoRecordset("QuestionType").Value == 1)
   {
      mySQL = "select OptionOrder, AnswerOption from QuestionOptions ";
      mySQL = mySQL + " where QuestionId = " + questionId;
      mySQL = mySQL + " order by OptionOrder"
      var adoOptionsRecordset = adoConnection.Execute(mySQL);
   }

As you can see, we create another SQL select query, which gets all the possible options for a multiple-choice question, and executes those against the database, this time storing the returned recordset in the variable adoOptionsRecordset.

Now we've got all the information we need from the database; it's time to write our question to the page.

We start by writing the question number, which is contained in totalQuestionsAsked session variable. Recall that this was updated at the very start of the server-side script block. We also write out a hidden form element with the QuestionId in it. This will be used by the CheckAnswer.asp page when the form is posted to it to look up the actual answer to the question, so that the user's answer can be checked against it.

   Response.Write("<h4>Question " + Session("totalQuestionsAsked") + "</h4>");
   Response.Write("<input type=hidden name=txtQuestionId" +
      " value=" + adoRecordset("QuestionId").Value + ">");

We next need to write out the question to the page. There are two types of questions: ones requiring a text-based answer via a text box and ones in which the user selects an answer from a list of possible answers. These can be distinguished by the value for the QuestionType field. We deal with text box questions first.

   if (adoRecordset("QuestionType").Value == 2)
   {
      Response.Write("<P>" + adoRecordset("Questionhtml").Value + "</P>");
      Response.Write("<P><input type=text name=txtAnswer");
      Response.Write("maxlength=100 size=35></P>");
   }

If the QuestionType field is 2, we know it is a text box answer. We write the question itself to the page based on the Questionhtml field in the recordset, and then we write the text box.

We next come to multiple-choice questions, which are a bit more complex. If it is a multiple-choice type of question, that is, if the QuestionType field is 1, we need to first write out the question using the Questionhtml field in the recordset.

   else
   {
      Response.Write("<P>" + adoRecordset("Questionhtml").Value + "</P>");

Then, using the records in the adoOptionsRecordset, we need to write out each of the possible options with a radio button enabling the user to select what he or she thinks is the right answer. We don't know how many options there might be, so we simply loop through the adoOptionsRecordset recordset with a while loop, using MoveNext() to go to the next record or option.

      while (adoOptionsRecordset.Eof == false)
      {
         Response.Write("<input type=radio name=radQuestionChoice");
         if (adoOptionsRecordset("OptionOrder").Value == 1)
         {
            Response.Write(" checked");
         }
         Response.Write(" value=" + adoOptionsRecordset("OptionOrder").Value);
         Response.Write(">" + adoOptionsRecordset("AnswerOption"));
         Response.Write("<br>");
         adoOptionsRecordset.MoveNext();
      }

Inside the while loop, we use Response.Write() to write each radio button, represented by <input type=radio>, to the page. If it's the first radio button, that is, if the OptionOrder field returned by the recordset is 1, we add checked to the HTML for the radio button thereby ensuring that the first radio button is selected by default when the page is loaded in the user's browser.

Next we complete the radio button's HTML by adding the value attribute to its definition, with the value being the OptionOrder field from the recordset, which will provide the answer when the form is submitted. Finally we add some text to be displayed after the radio button, which indicates what answer the radio button signifies. For example, for "What is Homer Simpson's favorite food?" we write out "Fresh Salad" for the first radio button, "Doughnuts" for the second, and so on.

Finally, in the while loop, we add an HTML <br> tag so that each radio button goes on a separate line. Also, we move to the next record in the adoOptionsRecordset. Forget this and the loop will never end because it will stay on the same record and never actually reach the end of file (Eof).

When we've finished looping the options recordset, we make sure we close it and set the variable to null to allow the object to be released from memory.

      adoOptionsRecordset.Close()
      adoOptionsRecordset = null;
   }

Finally we close the adoRecordset and also the Connection object and set them to null.

   adoRecordset.Close();
   adoRecordset = null;
   adoConnection.Close();
   adoConnection = null;
%>

This ends the server-side script block. At the bottom of the form we have the Answer Question button, which submits the answer for checking to CheckAnswer.asp. We'll create this page next.

<input type="submit" value="Answer Question" id=submit1 name=submit1>

We've completed the changes to AskQuestion.asp so save and close it in your editor.

Checking the Answer: CheckAnswer.asp

CheckAnswer.asp is a completely new page that needs to be created. As its name suggests, it checks the answer submitted by the user in the AskQuestion.asp page. It retrieves the answer given in that page; then it opens a connection to the database and retrieves the actual answer we have stored there. If they match, the user got the question right and we write an <img> tag to the page that displays an image saying the user got the answer correct. If the answers don't match, we write a different <img> tag to the page and display an image letting the user know the answer is wrong. You can either use the images in the code download available on the Wrox website or create them yourself using a paint package.

Let's create the page, and then we'll look at how it works.

<%@ language = JavaScript%>
<% Response.Buffer = "True" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD html 4.01 Transitional//EN"
"http://www.w3.org/tr/html4/loose.dtd">
<html>
<head>
</head>
<body background="bluewash.jpg">
<%
   var answer;
   var mySQL;
   mySQL = "select QuestionType, Answer from Question ";
   mySQL = mySQL + " where QuestionId = " + Request.Form("txtQuestionId");
   var adoRecordset = Server.CreateObject("ADODB.Recordset");
   adoRecordset.Open(mySQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data" +
                " Source=C:\\Temp\\TriviaQuestions.mdb");
   if (adoRecordset("QuestionType").Value == 2)
   {
      answer = new String(Request.Form("txtAnswer"));
   }
   else
   {
      answer = new String(Request.Form("radQuestionChoice"));
   }
   var answerRegExp = new RegExp(adoRecordset("Answer").Value,"i");
    
   adoRecordset.Close();
   adoRecordset = null;
    
   if (answer.search(answerRegExp) != -1)
   {
      Session("numberOfQuestionsCorrect") =
            parseInt(Session("numberOfQuestionsCorrect")) + 1;
%>
      <center><img src="Correct.gif"></center>
<%
   }
   else
   {
%>
      <center><img src="Incorrect.gif"></center>
<%
   }
   if (Session("totalQuestionsAsked") != Session("NoQsToAsk"))
   {
%>
      <form name="quizForm" action="" method="post">
      <input type="button" value="Next Question" name=butNext
         onclick="window.location.replace('AskQuestion.asp')">
      </form>
<%
   }
   else
   {
%>
      <!-- #include file="FinalResults.asp" -->
<%
   }
%>
</body>
</html>

Once you have finished typing in the code, save it as CheckAnswer.asp.

The page starts with the usual declaration that the server-side code is being written in JavaScript.

<%@ language = JavaScript%>

Note also the use of the following line:

<% Response.Buffer = True %>

This sets the Response object's Buffer property to True. We need to do this because this page makes use of cookies (in the file FinalResults.asp, which is included into this page with a #include directive) that must be set in the header of the page. Buffering means that the server composes the whole page before it is sent to the user's browser. In ASP 3.0, buffering is set by default to True, but in ASP 2.0 it is set to False. If the page isn't buffered, the header would already be sent by the time the server was reading the code that sets the cookies and we would encounter errors. So, to make sure that our code will work on all Microsoft servers, we include this line.

Then, within the body of the page, we have a block of server-side script. The first task of this script is to open the database, look in the Question table, and get the correct answer to the question just asked. We know the QuestionId of the current question because that is passed inside the form just submitted.

<%
   var answer;
   var mySQL;
   mySQL = "select QuestionType, Answer from Question ";
   mySQL = mySQL + " where QuestionId = " + Request.Form("txtQuestionId");
   var adoRecordset = Server.CreateObject("ADODB.Recordset");
   adoRecordset.Open(mySQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data" +
      " Source=C:\\temp\\TriviaQuestions.mdb")

We build the SQL needed to obtain the answer from the database. In our query we select two fields, the QuestionType and the answer to the question. Our where statement makes sure that we get the answer for the right question, the QuestionId being contained in a hidden text field passed along with the form data.

We don't create a Connection object because we need to connect to the database only once. Instead we just use a Recordset object to connect to the database and open the recordset with the SQL we just created in mySQL.

We now need to retrieve the answer that the user gave for the question. This answer was posted to the CheckAnswer.asp page from the form in the AskQuestion.asp page. However, the name of the control containing the answer depends on the type of question, text or multiple choice, that was asked. A text answer will have been posted from a txtAnswer text box; a multiple-choice answer is contained in the value of the radQuestionChoice radio button.

If the answer was a text answer, that is, the QuestionType field of the Question table had the value 2, the answer is extracted from the txtAnswer text box and placed in the variable called answer.

   if (adoRecordset("QuestionType").Value == 2)
   {
      answer = String(Request.Form("txtAnswer"));
   }

If the QuestionType field does not have the value 2, that is, the question is a multiple-choice question, the answer is extracted from the radQuestionChoice radio button, and again placed in the variable answer.

   else
   {
      answer = String(Request.Form("radQuestionChoice"));
   }

We now have the answer that the user gave stored in the variable answer. Let's get the correct answer from the Recordset object. Remember that the answer we put in the database is actually a regular expression, so we use the answer in the database as the constructor for a new RegExp object.

   var answerRegExp = new RegExp(adoRecordset("Answer").Value,"i");

We've finished with our Recordset so let's close it now and remove the reference to the Recordset object, allowing the system to reclaim the memory used.

   adoRecordset.Close();
   adoRecordset = null;

We now need to check the answer that the user gave, which we've stored in variable answer, against the correct answer that we retrieved from the database and used to create a RegExp object. The method of checking the answer has not changed from when it was client-side in the answerCorrect() function in the GlobalFunctions.htm page.

In the case of multiple-choice answers, the regular expression is a simple 1, 2, 3, or 4 for the option number that was correct. For text-based answers, it's a full regular expression.

In the condition of the following if statement, we search the answer variable containing the answer given by the user to see if it matches the correct answer given in answerRegExp. If it does, we write an image to the page to tell the user the answer was right and we also update a Session variable keeping track of how many questions have been answered correctly so far. If the answers don't match, we use the else statement to write a different image to the page to tell the user the answer was wrong.

   if (answer.search(answerRegExp) != -1)
   {
      Session("numberOfQuestionsCorrect") =
         parseInt(Session("numberOfQuestionsCorrect")) + 1;
%>
      <center><img src="Correct.gif"></center>
<%
   }
   else
   {
%>
      <center><img src="Incorrect.gif"></center>
<%
   }

The final part of our server-side script checks whether the number of questions that have been asked is the same as the total number of questions the user opted to answer at the beginning of the quiz. If more questions need to be asked, we write a button to the page that will take the user to the AskQuestion.asp page. If there are no more questions to ask, the quiz is over so we write the results table that lists how well the user did on the quiz this time and what the user's average score is. The code for this table is not contained in this page, but instead is inside the file FinalResults.asp, which we include inside this page using the #include directive we saw in the previous chapter.

Why not just include it directly in this page? Well, we'll be reusing the FinalResults.asp page in another page, so to save repetition we create the results code just once, then #include it into the different pages.

   if (Session("totalQuestionsAsked") != Session("NoQsToAsk"))
   {
%>
      <form name="quizForm" action="" method="post">
      <input type="button" value="Next Question" name=butNext
         onclick="window.location.replace('AskQuestion.asp')">
      </form>
<%
   }
   else
   {
%>
      <!-- #include file="FinalResults.asp" -->
<%
   }
%>

We'll look at the FinalResults.asp included file next.

Displaying a Summary of Results

The server-side code and HTML that displays a summary of results at the end of the quiz is contained inside a file called FinalResults.asp. We never actually load this file as a page on its own, but instead include it, using the #include directive, into the CheckAnswer.asp page, which we discussed previously, and the OutOfTime.asp page we'll be creating shortly.

<form name="quizForm" action="" method="post">
<script language=JavaScript type="text/javascript">
   window.top.fraTopFrame.fraGlobalFunctions.stopTimer();
</script>
<font color=darkslateblue face="Comic Sans MS">
<P><img src="quizcomplete.gif"></P>
You got <%=Session("numberOfQuestionsCorrect")%>
questions correct out of <%= Session("NoQsToAsk") %>
<br><br>Your trivia rating is
<%
   var numberOfQuestionsCorrect = parseInt(Session("numberOfQuestionsCorrect"));

   var numberOfQuestionsAsked = parseInt(Session("NoQsToAsk"));
   switch(Math.round(((numberOfQuestionsCorrect / numberOfQuestionsAsked)
      * 10)))
   {
      case 0:
      case 1:
      case 2:
      case 3:
         Response.Write("Beyond embarrasing");
         break;
      case 4:
      case 5:
      case 6:
      case 7:
         Response.Write("Average");
         break;
      default:
         Response.Write("Excellent");
   }
   var previousNoCorrect = Math.floor(Request.Cookies("previousNoCorrect"));
   var previousNoAsked = Math.floor(Request.Cookies("previousNoAsked"));
   var currentAvgScore = Math.round(numberOfQuestionsCorrect /
      numberOfQuestionsAsked * 100);
   Response.Write("<br>The percentage you've " +
      " answered correctly in this quiz is " + currentAvgScore + "%");
   if (previousNoAsked == 0)
   {
      previousNoCorrect = 0;
   }
   previousNoCorrect = previousNoCorrect + numberOfQuestionsCorrect;
   previousNoAsked = previousNoAsked + numberOfQuestionsAsked;
   currentAvgScore = Math.round(previousNoCorrect / previousNoAsked * 100);
   var nowDate = new Date();
   nowDate.setMonth(nowDate.getMonth() + 3);
   cookieExpires = nowDate.toGMTString();
   cookieExpires = cookieExpires.slice(5,cookieExpires.length - 4);
   Response.Cookies("previousNoAsked") = previousNoAsked;
   Response.Cookies("previousNoAsked").Expires = cookieExpires;
   Response.Cookies("previousNoCorrect") =  previousNoCorrect;
   Response.Cookies("previousNoCorrect").Expires = cookieExpires;
   Response.Cookies("AverageScore") = currentAvgScore;
   Response.Cookies("AverageScore").Expires = cookieExpires;
   Response.Write("<br>This brings your average todate to " +
      currentAvgScore + "%");
%>
<P>
<input type=button value='Reset Stats' name=buttonReset
   onclick="window.top.fraTopFrame.fraGlobalFunctions
      .setCookie('previousNoAsked', 0,'','Mon, 1 Jan 1970')" >
<P>
<input type=button value='Restart Quiz' name=buttonRestart
   onclick="window.location.replace('quizpage.asp')" >
</form>

<%@ language = JavaScript %>

<script language="javascript" type="text/javascript">
   window.top.fraTopFrame.fraGlobalFunctions.stopTimer();
</script>

You got <%=Session("numberOfQuestionsCorrect")%>
questions correct out of <%= Session("NoQsToAsk") %>

<br><br>Your trivia rating is
<%
   var numberOfQuestionsCorrect = parseInt(Session("numberOfQuestionsCorrect"));

   var numberOfQuestionsAsked = parseInt(Session("NoQsToAsk"));
   switch(Math.round(((numberOfQuestionsCorrect / numberOfQuestionsAsked)
      * 10)))
   {
      case 0:
      case 1:
      case 2:
      case 3:
         Response.Write("Beyond embarrasing");
         break;
      case 4:
      case 5:
      case 6:
      case 7:
         Response.Write("Average");
         break;
      default:
         Response.Write("Excellent");
   }

   var previousNoCorrect = Math.floor(Request.Cookies("previousNoCorrect"));
   var previousNoAsked = Math.floor(Request.Cookies("previousNoAsked"));
   var currentAvgScore = Math.round(numberOfQuestionsCorrect /
      numberOfQuestionsAsked * 100);
   Response.Write("<br>The percentage you've " +
      " answered correctly in this quiz is " + currentAvgScore + "%");
   if (previousNoAsked == 0)
   {
      previousNoCorrect = 0;
   }
   previousNoCorrect = previousNoCorrect + numberOfQuestionsCorrect;
   previousNoAsked = previousNoAsked + numberOfQuestionsAsked;
   currentAvgScore = Math.round(previousNoCorrect / previousNoAsked * 100);
   var nowDate = new Date();
   nowDate.setMonth(nowDate.getMonth() + 3);
   cookieExpires = nowDate.toGMTString();
   cookieExpires = cookieExpires.slice(5,cookieExpires.length - 4);
   Response.Cookies("previousNoAsked") = previousNoAsked;
   Response.Cookies("previousNoAsked").Expires = cookieExpires;
   Response.Cookies("previousNoCorrect") =  previousNoCorrect;
   Response.Cookies("previousNoCorrect").Expires = cookieExpires;
   Response.Cookies("AverageScore") = currentAvgScore;
   Response.Cookies("AverageScore").Expires = cookieExpires;
   Response.Write("<br>This brings your average todate to " +
      currentAvgScore + "%");
%>

<P>
<input type=button value='Reset Stats' name=buttonReset
   onclick="window.top.fraTopFrame.fraGlobalFunctions" +
      ".setCookie('previousNoAsked', 0,'','Mon, 1 Jan 1970')" >
<P>
<input type=button value='Restart Quiz' name=buttonRestart
   onclick="window.location.replace('quizpage.asp')" >

<%@ language = JavaScript%>
<% Response.Buffer = True %>
<html>
<body background="bluewash.jpg">
<!-- #include file="FinalResults.asp" -->
</body>
</html>