Need help in creating quiz in php

[subhomoy]

Hello every body. Currently I am creating a quiz section but I'm facing some problem.

 

The thing i want is that the questions which the user has played before should not come again.

 

So here is my quiz table.

+-------------+-------------------------------------+------+-----+---------+----------------+
| Field       | Type                                | Null | Key | Default | Extra          |
+-------------+-------------------------------------+------+-----+---------+----------------+
| qid         | int(11)                             | NO   | PRI | NULL    | auto_increment |
| questions   | varchar(255)                        | NO   | UNI | NULL    |                |
| correct_ans | varchar(50)                         | NO   |     | NULL    |                |
| option1     | varchar(50)                         | NO   |     | NULL    |                |
| option2     | varchar(50)                         | NO   |     | NULL    |                |
| option3     | varchar(50)                         | NO   |     | NULL    |                |
| category    | enum('Maths','Bollywood','Special') | NO   |     | NULL    |                |
+-------------+-------------------------------------+------+-----+---------+----------------+

Then I have created another tablle where I stored their user id and the questions Iid they have played. Here is the table.

+----------------+---------+------+-----+---------+-------+
| Field          | Type    | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+-------+
| mid            | int(11) | NO   | PRI | NULL    |       |
| quiz_questions | text    | NO   |     | NULL    |       |
+----------------+---------+------+-----+---------+-------+

In the above table, the ids are stored in this pattern     1,5,10,6,7,11,15,29,2   ------------------> Tis are the quiz id.

 

I'm using this query but to bring the uinque questions but still the same questions are comming..

$q = $this->database()->prepare("SELECT quiz_questions FROM quiz_played WHERE mid = ?");
        $q->bindParam(1, $mid,PDO::PARAM_INT);
        $q->execute();
        $row = $q->fetch(PDO::FETCH_ASSOC);       
        $q->closeCursor();
        $q1 = $this->database()->prepare("SELECT * FROM quiz WHERE qid NOT IN (?) ORDER BY RAND()");
        $q1->bindParam(1, $genre,PDO::PARAM_STR);
        $q1->bindParam(1, $row['quiz_questions'],  PDO::PARAM_STR);
        $q1->execute();
        return  $q1->fetch(PDO::FETCH_ASSOC);

Any help will be really appreciated.

[Barand]

No, no, no.

 

Don't store delimited lists (especially lists of keys) in a database table.

 

The table should be

 

mid INT,

qid INT,

PRIMARY KEY (mid,qid)

 

You then use LEFT join between the two table to find those not answered.

SELECT question 
FROM quiz q
LEFT JOIN othertable o ON q.qid = o.qid AND o.mid = ?
WHERE o.qid IS NULL