0

Hi guys.

I wanted to create a quiz for my website. I have found some code from a website called (getcode.org) (prev world of webcraft).

The quiz now works as it should and I'm very happy it does. There is one problem I have adapited it to the needs of my website. The website is an learning aid for people like myself who suffer from Dyscaculia and its foucing on Fractions. I have added in 80 question into the database and at the moment the PHP/SQL code is selecting all of the question from the database.

I only want each quiz to contain 20 questions per quiz.

    $singleSQL = mysql_query("SELECT * FROM questions WHERE id='$question' LIMIT 1");
        while($row = mysql_fetch_array($singleSQL)){
            $id = $row['id'];
            $thisQuestion = $row['question'];
            $type = $row['type'];
            $question_id = $row['question_id'];
            $q = '<h2>'.$thisQuestion.'</h2>';
            $sql2 = mysql_query("SELECT * FROM answers WHERE question_id='$question' ORDER BY rand()");
            while($row2 = mysql_fetch_array($sql2)){
                $answer = $row2['answer'];
                $correct = $row2['correct'];
                $answers .= '<label style="cursor:pointer;"><input type="radio" name="rads" value="'.$correct.'">'.$answer.'</label> 
                <input type="hidden" id="qid" value="'.$id.'" name="qid"><br /><br />
                ';

Is there anyway, how I can just state in the SQL code to only select 20 questions from the "questions" table and to have them in a random order so each quiz will contain 20 different questions each time a user takes the quiz?

Here is the specific line of code that is the concern

This is taking the questions from the questions table

$singleSQL = mysql_query("SELECT * FROM questions WHERE id='$question' LIMIT 1");

And this is the code that is taking the answers from the answers table and putting the answers in a ramdom order each time the questions are displayed

$sql2 = mysql_query("SELECT * FROM answers WHERE question_id='$question' ORDER BY rand()");

Any help would be much appreciated

2
Contributors
1
Reply
19
Views
3 Years
Discussion Span
Last Post by hericles
0

You can do a couple of things. Frstly, you can generate the random number in your code and use it in your query. Get the MAX(id) from the table as a SELECT and use it to generate a random number between 0 and MAX(id).
Pass that random ID back to the database in your select question and select answers queries.

Or, you can use something like this:

SELECT CEIL( RAND() * MAX(id))

This will return a random number between 0 and MAX(id) as well but now you have the difficulty of working that into an efficient query that generates the random ID and returns the row. This maybe tricky and RAND() can be a performance inhibitor so I would go with the much simpler, in code approach.
But that is just me.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.