Need help

[AN-net]

hey guys, well see im trying to make it so for each question it will show all answers for that question but for some reason it is carrying over the questions from the last question and add those answers together.

see attachment for exmaple

heres my code:

PHP Code:

        $fquestions= $DB_site->query("SELECT question,question_id FROM ".TABLE_PREFIX."quiz_questions WHERE quiz_id='".$_REQUEST['q']."' AND active='1'");
        while($question= $DB_site->fetch_array($fquestions))
        {
            $fanswers= $DB_site->query("SELECT answer,correct FROM ".TABLE_PREFIX."quiz_answers WHERE quiz_id='".$_REQUEST['q']."' AND question_id='".$question[question_id]."' AND active='1'");
            while($answer= $DB_site->fetch_array($fanswers))
            {
                exec_switch_bg();
                eval('$ansbits .= "' . fetch_template('quiz_correctansbits') . '";');
            }
            $DB_site->free_result($fanswers);
            $userans= $DB_site->query_first("SELECT quiz_answers.correct AS useranscorrect,quiz_answers.answer AS userans FROM ".TABLE_PREFIX."quiz_useranswers LEFT JOIN quiz_answers ON (quiz_useranswers.answer_id=quiz_answers.answer_id) WHERE quiz_useranswers.quiz_id='".$_REQUEST['q']."' AND quiz_useranswers.question_id='".$question['question_id']."'");
            eval('$questionbits .= "' . fetch_template('quiz_statquestionbits') . '";');
        }
        eval('print_output("' . fetch_template('quiz_qbstatistics') . '");'); 


[AN-net]

anyone?

[Velocd]

First off, never put a DB query (you have two, which is even worse) into a while loop of another DB query.

Bad news, as it could call hundreds of queries thus slowing your system down.

If you've tried LEFT JOIN'ing tables and that doesn't prove successful, there is usually a way to make arrays containing the data you want from each query, and then you can make one main loop and iterate through the arrays and sort the data.

Hard to explain so I'll try to give an example.

But first, how is this setup? Looks like you have a table for questions, a table for correct answers, and a table for user answers.

How about combining the first two tables, questions and correct answers? Have the fields in one table.

[AN-net]

it does make sense to left join the question and answer table

[Velocd]

Although, unless I'm interpreting it wrong, it makes more sense to combine quiz_answers and quiz_questions into one table.

[AN-net]

that might get a little confusing for me though and it would make the table very large.