MySql- VB.NET Stop questions repeating

[Nigel12]

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 ?

 

[Barand]

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 November 30, 2019 by Barand

[Nigel12]

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

[Barand]

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.