CODEWITHSUNDEEP
mysqlsqlmoodle
Akhilesh
Akhilesh

Reputation: 1313

Show random questions from a table without repetition

I am using moodle Multitrack test for developing a career assessment test. I want to modify the plugin to show random questions, one question at a time. So if there are a total of 10 questions then it should show first a random question and after it is saved then show a random question from the remaining 9 questions and so on. Questions are saved in a table called 'magtest_question' whose fields are:

id(bigint),
magtestid(bigint),
questiontext(longtext),
questiontextformat(mediumint),
sortorder(bigint).

The questions are sorted based on the 'sortorder' column. So I tried changing the query to sort randomly.

SELECT * FROM {magtest_question} WHERE magtestid=? ORDER BY RAND()

But it will sometimes show the same question again. I want to avoid the questions which are attempted. How can I achieve this using sql query. Please help.

Upvotes: 0

Views: 124

Answers (2)

Russell England
Russell England

Reputation: 10221

You need to exclude the questions that have already been answered

Something like

$sql = "SELECT q.*
        FROM {magtest_question} q
        WHERE q.magtestid = :magtestid
        AND NOT EXIST (SELECT a.id
                       FROM {magtest_answer} a
                       WHERE a.questionid = q.id
                       AND a.userid = :userid)
        ORDER BY RAND()";

$params = array('magtestid' => $magtest->id, 'userid' => $USER->id);

$questions = $DB->get_records_sql($sql, $params, $currentpage * $magtest->pagesize, $magtest->pagesize);

Upvotes: 0

Shadow
Shadow

Reputation: 34285

Query the ids of all 10 questions in a random order, store the ids in session in an array in the order returned by the query and then retrieve the questions one-by-one. This way you only issue an order by rand() query once and the questions will not be repeated.

Upvotes: 1

Related Questions