MySQL Forums
Forum List  »  PHP

How to prevent a record from being affected by the ORDER BY RAND() function
Posted by: Muluh Godson
Date: June 19, 2019 06:25AM

I am building an online exam site using php and mysql. It's just for MCQ type questions.
Here is my database structure:
1) QUESTIONS ( Exam_ID, Questions_ID, Questions_Des)

2) OPTIONS ( Question_ID, Option, Options_ID, Atype)

1) TRACK (User_ID, Exam_ID, Question_ID)

The table TRACK keeps a record of which questions have been answered already by which students.

The field Atype in the OPTIONS table indicates if the answer option is STATIC or null.

I want the questions randomized for each student without repetition and I achieved that with:

SELECT * FROM questions WHERE question_ID NOT IN (SELECT question_ID FROM track where User_ID = '<user_id>') ORDER BY RAND() LIMIT 1

This works perfectly.

And I also randomize the various answer options so that no two students have the same answer pattern for a question. The code i used is:

SELECT * FROM options WHERE question_ID = '<question_ID>' ORDER BY RAND()

A problem arises when a question is of this form:
Question: which of the following are even numbers?
A) 1
B) 3
C) 7
D) None of the above.

When this randomizes, option D could now be option A which makes no sense since there aren't any answers above it. That is why I added another field to the options table known as Atype which indicates if the answer is static or not.

How can I then query the options such that any answer option which is not STATIC won't be affected by the ORDER BY RAND() function so that when it randomizes option A, B and C will randomize but not Option D which has been marked as static. Hope it is clearer now.

Options: ReplyQuote

Written By
How to prevent a record from being affected by the ORDER BY RAND() function
June 19, 2019 06:25AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.