Question database that doesn’t repeat questions that were answered correctly by user

I have a question table organised as shown bellow:

    ID | question            | a    | b     | c    | d      | e     | correct_item
    1  | What's my name?     | joao | pedro | jose | mateus | lucas | b
    2  | How old am I?       | 15   | 18    | 20   | 22     | 25    | d
    3  | Where are you from? | Paris| Berlin| Tokyo| Nairobi| Rio   | d

When the user access his profile page, there is a modal button that selects one random question from the database.

$busca_questoes = "SELECT * FROM questoes ORDER BY rand() LIMIT 1";
$result_questoes = mysqli_query($conexao, $busca_questoes);
$questoes = mysqli_fetch_array($result_questoes);

What i want to know is:

1. How to sort questions randomly, but, before showing the question to the user, I want to check if the user has already answered correctly this question and, if so, to select a new one;

First, I tried to build a second table to integrate users database with questions database. Code bellow:

id_table | id_question | id_user | correct
1        | 1           | 2       | yes
2        | 2           | 2       | no
3        | 1           | 1       | yes

But the problem that I find with this strategy is that it is necessary to correlate each question with the user almost manually. I could do this when the user sign up into the website, but then what should I do when new questions are added?

Now I don’t know what to do to solve this problem. Please let me know if more information is needed.

Answer

As commented by Shadow, you want to store the questions that were already asked to each user, regardless of the fact that they gave the correct answer or not. You don’t need to store the questions that they did not yet answered.

Then, you can use the following query to pull out a random question that was not yet asked to a given user:

SELECT * 
FROM questions q
WHERE NOT EXISTS (
    SELECT 1
    FROM user_questions uq
    WHERE uq.id_user = :id_user AND uq.id_question = q.id_question
)
ORDER BY rand() LIMIT 1

Where parameter :id_user is the id of the current user.

For this to work, you need a large number of questions, because once a user has seen all possible questions, the above query will return an empty result set. Optionaly, you can store the date when they were asked each question in the user_questions table, and add a filter in the subquery to exclude only questions that have been seen recently.

SELECT * 
FROM questions q
WHERE NOT EXISTS (
    SELECT 1
    FROM user_questions uq
    WHERE 
        uq.id_user = :id_user 
        AND uq.id_question = q.id_question
        AND uq.date_asked > NOW() - INTERVAL 30 DAY
)
ORDER BY rand() LIMIT 1