Quiz Script

a440guy thanks for asking.  It would be nice if you can use your code.  

    *The questions can be one, five, ten or whatever; that depends on the admin.  The admin may have only one question this week, or ten questions next week.  
   
    *The questions will be multiple choice and a radio buttons.  

    *The user should not be able to move backward once they submit, so they are not allowed to go back and change their answers.  

    *Users can skip around and answer questions, but all questions must be answered before submitting.  

    *Quitting and then coming back the next day to finish it the questionnaire is not necessary, but if it can be done it is a good option.

    *Users can not answer one question more than once.  may be a cookie or an IP Address track might help.  (prefer IP Address)

    *Once the user clicks submit, provide the user a result of user's score in percentage, and a result of what other people answered in percentage (This web site is a good example: http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=3&lngWId=9 )

The problem I am running into is once the user submit, I could not get the questions to insert into the database b/c I believe it is my loop doing some kind of wired things.  

"*Users can not answer one question more than once.  may be a cookie or an IP Address track might help.  (prefer IP Address)"
don't use IP address, as it won't work for modem users...they get IP dynamicaly once they connect to the provider...

AtlGT,

Unfortunately, I couldn't look at the planetsourcecode web site; the server was not responding.  I'll try later.  In the meantime, why don't you continue with your functional  description of the quiz application.  And, I have a few more questions for you:

How many questions should appear on each page? All of them?

The admin will be creating the questions.  Who will be programming them?

How will you identify your users?  Will they be required to log in to the application or the web site?  (Cookie tracking will not prevent a user from answering the questionnaire a second time from a different computer.)

I will leave the reporting of scores up to you, as I don't have enough information to know what to write.

Also, and this is important, please provide a description of the tables and relationships that you want to use.  explain what data is contained in each field and what it is for.

The reason why I wanted an IP Address is that I made a discussion board and I use an IP address to keep track of the users, so an IP Address is more effective than cookies b/c as you know cookies are easy to delete.  

   * All the questions should appear on one page.
   * when ever the admin wants to create a question, the admin will be asked who many questions he wants ask and based on that on the next page    'x' amount of questions will be created.  The admin also will have a radio box next to each multiple choice to check the correct answer or any other method to identify the correct answer.
   * The users are required to log in (which I have already made).  
   * The database will have 7 tables (I got the table from macromedia exchange a while ago, but I could not find it now)
       table 1 is called  answer.  table will have AnswerID and answer2choice(this is where the correct answer stored)
       table 2 is called choice.  table will have ChoiceID, choice_letter(choice is letters.  Eg. A, B, C...), Choice_text(the question itself) and question2choice
       table 3 is called info.  table will have infoID and alpha
       table 4 is called question.  table will have questionID, question and type(question Title)
       table 5 is called results.  table will have questionID, A, B, C, D, E, F, G, H, I, J and TotalVote  (the reason why I made A, B,..., J is that I could not find a way to create a dynamic multiple choice
       table 6 is called type. table will have TypeID, type(question Title) and online(it is a check box.  if checked,  available online)
       table 7 is called Users.  table will have UserID(this is were i store the IP Address) and QuizID(each quiz has an ID)

       the relationships are: answer has one to one with choice and question
                                             choice has one to many with question
                                             question has one to many with type
                                             type has one to one with results  

Hope what I wrote makes sense

Thanks for the info.  I can't spend time on it right now, but I'll study it and get back to you.

All right thanks.  I'll be waiting for your response.  In the meantime, I'll work on mine hopping it will work.

I should add:

The primary key of:           is:
------------------------           --------------------------
QUIZ                                 (QuizID)
QUESTION                         (QuestionID)
QUIZ_QUESTION               (QuizID,QuestionID)
CHOICE                             (QuestionID,ChoiceSequence)
USER                                 (UserID)
QUIZ_USER                       (QuizID,UserID)
ANSWER                            (QuizID,UserID,QuestionID)

Thanks that sound great.  I'll change my database and see how it is going to come out.  

AtlGT,

How's it going?  I'm sorry I haven't had time to work on this.  But, I will try to do some work soon.  What, if any have you been able to do?  I don't want to repeat any work you've already done.

OK, here is a script that demonstrates the basic technique.  It displays the quiz, submits the answers, and calculates a score.  You will have to put some data (questions, choices) into the tables before it will do anything, of course.  Things left for you to do:

Write scripts that enable someone to create quizzes.
Write scripts that enable someone to produce various reports.

Let me know what you think of this.  Does it meet your needs?

<!--- showQuiz.cfm --->

<cfset dsn="QUIZ">

<cfparam name="form.action" default="selectQuiz">
<cfset userid = cgi.remote_addr>
<cfset today = now()>

<cfif form.action eq "selectQuiz">
      <!--- Get quizzes that haven't been answered by this user. --->
      <!--- You can also get this by joining QUIZ to QUIZ_USER instead of ANSWER --->
      <cfquery name="quizzes" datasource="#dsn#">
            select      qz.quizID, qz.quizTitle
            from      quiz qz left outer join answer a on qz.quizID = a.QuizID
            where      a.quizID is null
            and      #CreateODBCDate(today)# >= qz.quizOpenDate
            and      #CreateODBCDate(today)# < qz.quizCloseDate
      </cfquery>
      
      <h1>Quizzes</h1>
      <hr>
      <cfif quizzes.recordcount eq 0>
            <h3>There are no new quizzes</h3>
            <P>
            <cfabort>
      </cfif>
      
      <form name="getQuiz" method="post">
            Please select a quiz from the list below.
            <P>
            <select name="selectedQuiz">
            <cfoutput query="quizzes">
                  <option value="#quizID#">#quizTitle#</option>
            </cfoutput>
            </select>
            <P>
            <input type="submit" name="submit" value="Get Quiz">
            <input type="hidden" name="action" value="showQuiz">
      </form>

<cfelseif form.action eq "showQuiz">
      <cfquery name="quizInfo" datasource="#dsn#">
            select      quizTitle, quizText
            from      quiz
            where      quizID = #form.selectedQuiz#
      </cfquery>
      
      <cfquery name="getQuiz" datasource="#dsn#">
            select      qq.questionSequence, q.questionID, q.questionText, c.choiceSequence, c.choiceText
            from      quiz qz, quiz_question qq, question q, choice c
            where      qz.quizID = qq.quizID
            and            qq.questionID = q.questionID
            and            q.questionID = c.questionID
            order by qq.questionSequence, c.choiceSequence
      </cfquery>
      
      <cfoutput>
      <h1>#quizInfo.quizTitle#</h1>
      <P>
      #quizInfo.quizText#
      </p>
      </cfoutput>
      <hr>
      
      <form name="quiz" method="post">
            <table>
            <cfoutput query="getQuiz" group="questionSequence">
                  <tr>
                        <td width="30"><B>#questionSequence#.</B></td>
                        <td colspan="3"><B>#questionText#</B></td>
                  </tr>
                  <cfoutput>
                  <tr>
                  <td width="30">&nbsp;</td>
                  <td><input type="radio" name="Q_#questionID#" value="#choiceSequence#">
                  <td width="25">#choiceSequence#.</td>
                  <td>#choiceText#</td>
                  </tr>
                  </cfoutput>
            </cfoutput>
            </table>
            <hr>
            
            <cfoutput>
            <input type="submit" value="Submit">
            <input type="hidden" name="action" value="saveQuiz">
            <input type="hidden" name="quizID" value="#selectedQuiz#">
            </cfoutput>
      </form>
      

<cfelseif form.action eq "saveQuiz">
      <!--- get correct answers --->
      <cfquery name="answers" datasource="#dsn#">
            select      q.questionID, q.correctAnswer
            from      quiz_question qq, question q
            where      qq.quizID = #quizID#
            and            qq.questionID = q.questionID
      </cfquery>
      
      <!--- insert answers into database --->
      <cfloop index="f" list="#fieldnames#">
            <cfif reFind("^Q_", f)>
                  <cfset questionID = ListLast(f, "_")>
                  <cfset selection = evaluate(f)>
                  
                  <cfquery  datasource="#dsn#">
                        insert into answer (userID, quizID, questionID, choiceSequence)
                        values ('#userid#', #quizID#, #questionID#, '#selection#')
                  </cfquery>
                  
            </cfif>
      </cfloop>
      
      <!--- calculate user's score --->
      <cfquery name="answersheet" datasource="#dsn#">
            select      count(*) as numCorrect
            from      quiz_question qq, question q, answer a
            where      qq.quizID = #quizID#
            and      qq.questionID = q.questionID
            and      q.questionID = a.questionID
            and      a.quizID = #quizID#
            and      q.correctAnswer = a.choiceSequence
      </cfquery>
      
      <cfset totalQuestions = answers.recordcount>
      <cfset percentCorrect = answersheet.numCorrect / totalQuestions * 100>
      
      <h3>Your answers have been saved</h3>
      <P>
      <cfoutput>
      <h2>Score: #numberformat(percentCorrect)#% (#answersheet.numCorrect# out of #totalQuestions#)</h2>
      </cfoutput>
      
      <cfif percentCorrect eq 100>
            <h1>Congratulations!</h1>
      </cfif>
      
      <!--- store the score in the database so we don't have to calculate it every time --->
      <cfquery name="saveScore" datasource="#dsn#">
            insert into quiz_user (quizID, userID, score, quizdate)
            values (#quizID#, '#userid#', #percentCorrect#, #CreateODBCDateTime(now())#)
      </cfquery>
            
<cfelse>
      <h2>Invalid action</h2>
      
</cfif>

Oh, one more thing.  To prevent users from pressing their browser's back button and resubmitting, you could

1.  Put primary keys on the answer and quiz_user tables to that subsequent attempts to submit the page will fail.  This is a brute force method and not elegant.

Better:
2.  Put a check at the very top of the 'action eq "saveQuiz"' section:

<!--- check for existing answers --->
<cfquery name="check" datasource="#dsn#">
      select      qu.score
      from      quiz qz, quiz_user qu
      where      qz.quizID = qu.QuizID
      and      qu.quizID = #form.quizID#
      and      qu.userID = '#userID#'
</cfquery>
      
<cfif check.recordcount neq 0>
      <h2>You cannot submit answers more than once.</h2>
      <cfabort>
</cfif>

  a440guy thanks for your post and also thank you for taking time from you busy schedule to help me out.  
I have done a few things.  I have already created that enable someone to create quizzes, but the only thing I need is a scripts that enable someone to produce various reports.   I could post my quiz here but it is a few pages.  If there is any way zip it send you my code it would be nice so you can look at it.  Let me know.

Sounds good.  Did you use the tables that I outlined?  You can upload a zipped file of your code on the Macromedia ColdFusion forum.  Go to http://www.macromedia.com and create an account for yourself.  then go to the ColdFusion forum and select the "CFML General Discussion" (it's at http://webforums.macromedia.com/coldfusion/categories.cfm?catid=3).  Click on "new topic" near the top of the page and use the same Message title as this one ("Quiz Script").  Post your question and at the bottom, there is a place to type the filename to upload.  I go by the same handle on that forum.

I don't think I would comfortable posting all my script in public.  If email is okay, email me with a subject "Quiz Script" at ebbsa@yahoo.com
Thanks

if a440guy is still around please replay to my email please.  Thank you