Query in a question bank that cannot return hits

Question

I have a bank of questions organized as follows:

questions:

    ID | pergunta            | a    | b     | c    | d      | e     | correta
    1  | Qual meu nome?      | joao | pedro | jose | mateus | lucas | pedro
    2  | Qual a minha idade? | 15   | 18    | 20   | 22     | 25    | 18

When the user accesses your profile page, a random question from this database should appear to him. However, questions he has already hit in the past should not appear again.

For this, I tried to create a new table that related the questions to the user, as in the example below:

cad_questoes:

id_tabela | id_questao | id_usuario | acerto
1         | 1          | 2          | sim
2         | 2          | 2          | nao
3         | 1          | 1          | sim

To ask the question to the database that will be shown to the user, I use the following command in PHP/Mysql:

$busca_questoes = "SELECT * FROM cad_questoes 
                    INNER JOIN questoes ON cad_questoes.id_questao = questoes.ID 
                    WHERE acerto='nao' ORDER BY rand() LIMIT 1";

However, for this command to work in practice, I would have to update the cad_questoes database manually to notify that there are questions that have not been answered, but I would like this to be done automatically.

My question is: How to request a question from the database that the user has not answered it and how to do this check?

I hope you have made yourself clear, I am willing to provide any information that contributes to the question.

Answer 1

One way to do it would be searching the database all questions were answered by any user except the one in question, in the following query I used DISTINCT not to return repeated questions.

$buscaQuestoes = "SELECT DISTINCT c.* FROM cad_questoes as cq
        INNER JOIN questoes as q ON cq.id_questao = q.ID 
        WHERE acerto = 'nao' 
        WHERE cq.id_usuario <> '$idUsuario'"; 

Another possible method is to add a column cad_questoes.respondida to identify if the question has already been answered, it would also be necessary to initialize a line for each question when creating a new user.