Jump to content
Nigel12

MySql- VB.NET Stop questions repeating

Recommended Posts

Hi, I have created a quiz and the questions get read from a MySql database table. I am now wanting to stop it repeating questions, when I first started this I thougt it would be easy just create a column called Duplicate and everytime a question got pulled I would mark it in the databse. So it would only read questions that had a "0" in the DuplicateCol and after reading the question it would put a "1" in place of the "0" That would work, the problem being multiple people are using it so if a 1 is there they wont all get asked the question.

Anyway that idea is a fail now, which is a shame because everything else worked. Im hoping someone can show me another way on stopping duplicates. when I created the quiz I created a registration, and login page which have there own table I then created another table for the questions with multiple columns ie             "   id, Question, Answer1, Answer2, Answer3, CorrectAnswer, DuplicateCol   "  I had a number on my form which is a random number that lets say is "6" will then pull the question from column id 6

Query = "select Question,Answer1,Answer2,Answer3,CorrectAnswer,DuplicateCol from Questions where DuplicateCol= '0' AND id='" + RandomN.Text + "'"

Anyway im for the time being lost until someone can give me ideas please, The registration, login table looks like this

id, Serial, Email, UserName, Location, UserScore, Activated,

I was thinking of adding q1, q2, q3, q4, q5, etc etc and then somehow if a question got pulled in my questions table with id "6" then put a "1" in the column but I think it might be to hard to try and cross reference 2 tables.

Any Ideas ? :)

 

Share this post


Link to post
Share on other sites

Are you not storing the users' answers anywhere?

Write a record to the "user_response" table when a user answers a question. Then you have a record of which questions each user has answered.

Only select questions for a user where there is no record in the response table for that question.

SELECT  Question
      , Answer1
      , Answer2
      , Answer3
      , CorrectAnswer
FROM question q 
       LEFT JOIN
     user_response r ON q.id = r.q_id
WHERE r.q_id IS NULL
ORDER BY RAND()
LIMIT 1  
+-----------+                              +-----------+
|  user     |                              | question  |
+-----------+                              +-----------+
| user_id   |--+                      +----| id        |
| name      |  |                      |    | question  |
+-----------+  |                      |    | etc       |
               |                      |    +-----------+
               |                      |
               |   +---------------+  |
               |   | user_response |  |
               |   +---------------+  |
               |   | q_id          |>-+
               +--<| user_id       |
                   | test_date     |
                   | answer        |
                   +---------------+

 

Edited by Barand

Share this post


Link to post
Share on other sites

Hi Barand, No I had not stored the users response I simply had the question marked ie label with the word Correct & label with Incorrect as soon as the user selected either A, B, C the question was instanly scored. I then uploaded there score to the database

 

If correctResult.Text = "Answer1" Then
                    AnswerCorrect.Text = CStr(CDbl(AnswerCorrect.Text) + 1)

Anyway Im now looking at what you have given me regarding the left join. I have not done something like this before so I will come back after I have played. :) spk soon

Share this post


Link to post
Share on other sites
4 minutes ago, Nigel12 said:

I then uploaded there score to the database

Don't store totals in a database, store the individual records that make up that total. That way you have an audit trail. With the responses, you can get info on which questions a user got right or wrong, which is lot more useful that just knowing how many. When you want the scores, just count the correct responses.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.