CODEWITHSUNDEEP
phpmysql
schmelding
schmelding

Reputation: 55

How do I query for random rows AND track what has been queried (that's the important part)

I already know how to do a random mysql/php query, but how do I track what has already been queried a row isn't pulled again. Use case: A quiz that has 10 questions. The questions need to be in random order. How can I track that questions (rows) 2,5,6,8,9 have already been answered, but questions 1,3,4,7,10 remain. Here's what I have so far:

$current_test = $_SESSION['testid'];
// v v v query for this TEST
$tests = mysql_query("SELECT * FROM the_tests WHERE the_tests.testid=$current_test");
$test = mysql_fetch_array($tests);
// ^ ^ ^ query for this TEST


// v v v query for the QUESTIONS from this Test
// Generate
if ($test['randomize']==1){
    $offset_result = mysql_query("SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM the_questions WHERE the_questions.test_id_q=$current_test");//SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM the_questions WHERE `qid` NOT IN (1,5,6) AND the_questions.test_id_q=1
    $offset_row = mysql_fetch_object($offset_result);
    $offset = $offset_row->offset;
    $questions = mysql_query("SELECT * FROM the_questions WHERE the_questions.test_id_q=$current_test LIMIT $offset, 1 " );
}else{
    $questions = mysql_query("SELECT * FROM the_questions WHERE the_questions.test_id_q=$current_test");
}
$totalQuestions = mysql_query("SELECT * FROM the_questions WHERE the_questions.test_id_q=$current_test");
$totalQs = mysql_num_rows($totalQuestions);
$testQ = mysql_fetch_array($questions);

Thanks in advance!

Upvotes: 1

Views: 106

Answers (2)

schmelding
schmelding

Reputation: 55

Here's what I came up with:

Assuming $_SESSION['qList'] is an array that looks like this:

$_SESSION['qList'] = array(1,2,3,4,5,6,7,8,9,10);

When the user submits a question, there is a <input type="hidden" value="5"...> where the value is the current question ID. $eliminateQ below is this question ID:

$_SESSION['qList'] = array_diff($_SESSION['qList'],array($eliminateQ));
$_SESSION['qList'] = array_values($_SESSION['qList']);
$questions = mysql_query("SELECT * FROM tan_questions WHERE test_id_q=$current_test AND qid IN (".implode(',',$_SESSION[qList]).") ORDER BY RAND() LIMIT 1");

This results in an updated qList array without ID number 5:

$_SESSION['qList'] = array(1,2,3,4,6,7,8,9,10);

And the whole thing goes on until no questions are left. There is an if() statement in that instance forwarding the user on to the results page. Viola!

Thanks, @pixeline, for your insight. I took your NOT IN and reversed it to IN. :)

Upvotes: 1

pixeline
pixeline

Reputation: 17974

You're looking to complement that SQL WHERE clause with NOT IN (5,23,45);

So basically keep an array with each random item ID value and feed it to your SQL clause. 

mysql_query("SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM the_questions WHERE the_questions.test_id_q=$current_test AND the_questions.id NOT IN (".implode(',',$excluded).")");

Now, this raises another question: the $excluded array. Should it be a temporary exclusion or should it be permanent? If it should not be permanent, then store it in a session (ex: $_SESSION['excluded'] ) If the user comes 3 days later, he will probably be able to re-answer that question.

If that has to be permanent, then store the fact that that question has been answered in a column dedicated to that, or, simply check if there is an answer ?

mysql_query("SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM the_questions WHERE the_questions.test_id_q=$current_test AND the_questions.answer ==''");

Upvotes: 1

Related Questions