I have one table that stores data related to math problems, and
another table that stores users’ answers to those problems. To make
this very simple, we’ll say that the math table has two numbers that
get subtracted, and the answers table has a field that can either be
“pos” or “neg”
Okay so if we do the math, we know that the correct answer to record 1
(2 -5) is “neg”, and the correct answer to record 2 (8-3) is “pos”.
What I’d like to do is get the user’s answers to these questions, and
determine what % they get correct. I really want to do this in as few
queries as possible though.
Right now I think the easiest way is to just change the table a bit so
it keeps the correct answer. Then I can do two queries:
SELECT COUNT(*) FROM answers WHERE user_id=5;
SELECT COUNT(SELECT answer FROM answers, problems WHERE
answers.answer=problems.answer AND answers.user_id=5));
The first one gets the number of total questions answered, the second
one gets the number of ones correctly answered. The first query - the
second query gives me incorrect answers.
Also I’m not entirely sure that the syntax for #2 is valid, I think it
First of all, is there a fast way that I can do this without having to
create another field representing the answer? I’m kind of thinking
not…I’d be performing these queries a lot, makes sense just to keep
the answer in the db so I can index it, rather than calculating it
every time. Secondly, can I do this with AR? Don’t know if it can