PHP Trivia Game with Databases

How could we use the user_questions table to ensure that the user never got the same question twice? We’d need to store information on what question the users answered, and a join table (user <-> question) is a great way to do that!

Any time the user answered a question correctly, along with updating their score, let’s add the fact that they correctly answered this question already into the database. We can do this with a mapping from users to questions in the user_questions table. Let’s pretend that Jane (user id 5) correctly answered the question “What is the largest desert?” (question id 18). Then, we’d store that information in the join table:

insert into user_questions (user_id, question_id) values (5, 18);

That will help us know later that Jane answered this question correctly. But how can we make use of that later? Without necessarily needing a join, we could ask: What questions did Jane (id 5) answer?

select question_id from user_questions where user_id = 5;

This will return a list of question ids that Jane has already answered. Then, when we want a new question for Jane, we’d need to check that any new question we got was not in this list! We could just randomly pull questions and check that the id is not in the list, but that might take a long time! Perhaps we want the database to help us out. We can ask for all the questions with id not in a list:

select * from questions where id not in (2, 6, 16, 18);

That still would mean we’d need to formulate a list, which we could do in PHP. We would need to be very careful that we’re secure and avoiding SQL injections; but, we got this list of ids from the database and the user never entered them, so we should be okay to use string interpolation here with the list (remember implode()? If not, check it out in the PHP manual!)

However, we could potentially let SQL help us out tremendously and use nested queries. That is, we could build the list using a select SQL query inside the actual query; i.e., combining the queries! That is:

select * from questions where id not in (select question_id from user_questions where user_id = 5);