Help with MySQL comparing two fields

julifos

I'm a newbie in PHP and MySQL and this is my first post in this forum.

I have two tables, one for users, the other for questions (it's kind of a trivia game). I don't want to display to the user the same question twice, so I store the "id" of already answered questions in a field of the user's record (as a string). Ie:

--> table "questions"
id | more_stuff
1 | blah, blah, blah
2 | blah, blah, blah
...

--> table "users"
nick | Already_answered_questions
John | ,4,17,548,72,36,41,9045,210,
...

So, I would like, if possible, create a sentence which would, in plain english:

SELECT * FROM questions WHOSE ("," + id + ",") is not in contents of (Already_answered_questions FROM users WHOSE nick = "John")

If such one-liner isn't possible, what do you think would be the fast way to do the task?

Actually, my only thoughts are retrieving all the questions' id, the field Already_answered_questions, and loop through. But I will have many users, many already-answered-questions and many questions to loop-thru, so this approach seems slow and very intensive.

Many thanks in advance for any comments!

Lars_Berg

You should start with redesigning your tables. Storing multiple values in a columns just leads to troublesome and inefficient queries. When your tables increase in size the queries will become slower. It would be good if you read some articles about databae normalization.

Add another table with two columns, one referencing the primary key for the question and the other referencing the primary key of the user table. Store one row in this table for each question answered by a user.

To get all questions not answered by a user

select * 
  from questions
 where not exists
      (select * 
         from answeredQuestions
       where questions.questionId = answeredQuestions
           and userid in (select id from users where nick = 'John'))

(Using improvised names)

julifos

Thanks. So, you say I need a new table containing:

[FONT="Courier New"]user_id | answered_question_id

1 | 547
1 | 12
37 | 6054
4255 | 2713
...[/FONT]

I'm pretty new to this MySQL stuff, but potentially this table could be, for example, 10,000 users * 5,000 answered questions = 50 millions of rows long??? Or I'm missing something here? --> maybe MySQL is so powerful and me a simple ignorant 😕

Lars_Berg

If you have proper indexes, 50 million rows is not a problem for a DBMS.

julifos

I guessed it was ignorance ;-)

Thanks, I'll take a look to this way!