how to write mysql statement

Question

I made a quiz game system (mysql+php).there are two tables:

question table:

qid            question        options      answer  level
23              1+1=?          1^2^3^4        2       1
24             10*10=?       1010^100^1000    100     1 
.....
3212          9999*32342/765   ...           ...      4 (more large the level is more difficult the question is)  

...more that ten thousand records...

user table:

userID       username        used(this field store qid someone has answered that from question table )
1            mary           23,56 (means she had answered qid=23 qid=56 question from question table)
2            mike           1,4,6,2123,567,2341,678,431234,45125
3            jack           3,23,24,.....
...

..many users....

When a player logs in and starts to play the game, the system would select 4 questions from the question table that are level=1 and 4 question that level=2 ...4 question that level=3 ....level=4. All these question had not be used before according to the used from user table.

how to write the mysql statement?

Answer 1

With your current schema, you will need at least two queries. The first will return the used value to PHP. PHP will then need to use it in a second query:

$q   = mysql_query( 'select used from user_table where id=1' ) 
            or die( mysql_error() );
$row = mysql_result_array( $q );
$ids = $row[ 0 ];
$q   = mysql_query( 
    "SELECT * FROM (
       select * from question_table where level = 1 and qid not in ($ids) ORDER BY (RAND()) limit 4
    )
    UNION
    SELECT * FROM (
       select * from question_table where level = 2 and qid not in ($ids) ORDER BY (RAND()) limit 4
    )
    UNION
    SELECT * FROM (
       select * from question_table where level = 3 and qid not in ($ids) ORDER BY (RAND()) limit 4
    )
    UNION
    SELECT * FROM (
       select * from question_table where level = 4 and qid not in ($ids) ORDER BY (RAND()) limit 4
    )" 
) or die(mysql_error());

Answer 2

The easiest (and probably most efficient way) is to do it in 2 queries.

1. SELECT used FROM user WHERE userID = 'mike'
2. SELECT * FROM question WHERE level = 1 qid NOT IN (result from query 1) ORDER BY RAND() LIMIT 4

Then repeat query 2 for each level.