I have three tables in my database for questions, answers and viewed:
Questions:
---------------------------------
| ID | category | title |
---------------------------------
| 1 | 1 | What is this? |
---------------------------------
| 2 | 1 | Who is this? |
---------------------------------
And so on: category is a category id from a different table, but thats not important.
Then I have answers:
-------------------------------------------
| ID | question | answer | user | date |
-------------------------------------------
| 1 | 1 | Blue | 25 | 1.1.15 |
-------------------------------------------
| 2 | 1 | Red | 18 | 2.2.15 |
-------------------------------------------
| 3 | 2 | Brad | 25 | 1.1.15 |
-------------------------------------------
Where question is the id from questions table and user is user_id
Then I have viewed:
-----------------------------------
| ID | category | question | user |
-----------------------------------
| 1 | 1 | 1 | 25 |
-----------------------------------
| 2 | 1 | 1 | 18 |
-----------------------------------
| 3 | 1 | 2 | 25 |
-----------------------------------
This will keep track of which questions users have seen, so they don't see the same question twice. And the point is that a user can give their answer that another user have given, so that won't be stored in answers table.
I have tried with this MYSQL-code:
SELECT Q.ID, Q.category, Q.title, A.ID, A.answer
FROM questions Q, answers A
WHERE
NOT EXISTS (
SELECT *
FROM category_answered S
WHERE Q.ID = S.question AND S.user = '$user' AND S.category = '$category'
)
AND
CASE
WHEN Q.category = '$category' THEN Q.ID = A.question
ELSE Q.category = '$category'
END
ORDER BY RAND() LIMIT 1
This list everything except the question that matches. So ORDER BY RAND() LIMIT 1
is not working.
Any help will be gladly appreciated