Forum Moderators: open

Message Too Old, No Replies

MySql Query query

Tough for me, easy for you?

         

grooble

8:01 am on Apr 17, 2009 (gmt 0)

10+ Year Member



Hi all,
I've got a database feeding a webapp that does an online multiple choice test.
the two tables of interest here are:

questions2 : (id, name, image, category, difficulty)
tests : (id, user, test_id, qn_index, choice1, choice2, choice3, choice4, correct, given)

Each row of questions2 is one choice of a four-choice multiple-choice question. A typical row might look like:
(17, cat, cat.gif, animal, easy)
a query selects four by topic and some Java makes each into a Question object. An array of four Question objects becomes one multiple choice qn. A list of these is the test.

The tests table stores results.
A typical row might look like this:
(3724, 37, 15, 3, 234, 434, 12, 45, 2,1)
(unique id, user-from a different tbl, this user's 15th test, this is qn3 of that test, ,the four choices of this qn,,the correct index 0-3, user's answer 0-3)

This query gives me a list of choice indices by user and test:
SELECT choice1, choice2, choice3, choice4
FROM tests
WHERE ((user=37) AND (test_id=15));

say...
123 234 23 54
543 56 2 65
etc...
how do I go from question index 123 to question
cat
for the above criteria, I want something that returns:
cat dog sheep mouse
teacher doctor fireman baker
etc..

I also want to avoid hittng the DB multiple times because of the overhead.

Any ideas?