I need some help thinking through the best process do this...
basically, I have a pool of a few hundred exam questions of which I pick 35 to generate an exam. This in itself would be pretty easy:
SELECT * FROM question_pool ORDER BY rand() LIMIT 35
but its a little more complicated. There are 10 sections of the exam, each with its set of pooled questions. Some sections pull 6 questions from the pool, others only 2 or 3. This two is still petty simple, I just use several queries, like:
SELECT * FROM question_pool WHERE section = 1 ORDER BY rand() LIMIT 6
and repeat that changing the section id and the limit number.
(I'm physically making 10 different queries - I know I could just make one really long SQL statement too -- this seems simpler for now.)
So here's where it gets REALLY complicated.
These are technically "practice" exams which can be taken over and over again... each time, the test questions will be changed BUT, since I'm tracking what questions have already been asked and if the user got them right or wrong, I want to weight the questions that end up on the exam to show something like:
50% new questions not asked yet
35% questions incorrectly answered before
15% question previously answered correctly.
Thoughts about the best way to do this?