Multiple SELECT queries and looping through results in PHP and mySQL

Question

I'm trying to learn best practices with multiple SQL queries in PHP and mySQL. Not sure if I should have two separate queries or if I should join (or UNION?) my queries into one.

Can I do the following or is there another way to accomplish the same thing?

It's important to note that my first query is pulling a list of questions and answers for a quiz. The second query is pulling a list of score profiles that I'll be using to assign to users based on their quiz score. The only relationship between these two tables is that both use the same foreign key linked to an ID in my "quiz" table. I wouldn't need to JOIN these tables side by side, but I think I would probably need to UNION them so my second query results appear after the first.

Now if I should union my queries into one, I have no idea how I would loop through the query results to create my array. When I loop through each result, I would need some conditional logic that does something with my first query results and something different with my second query results. How would I write those conditions?

Here is what I'm doing now that seems to work fine as two separate queries...

...query the database for the first time to get my quiz questions and answers:

$result_quiz = mysql_query("
    SELECT quiz_question.question_text, quiz_question.id, quiz_answer.answer_text, quiz_answer.points
    FROM quiz
    JOIN quiz_question ON (quiz.id = quiz_question.quiz_id)
    JOIN quiz_answer ON (quiz_question.id = quiz_answer.quiz_question_id)
    WHERE quiz.id = $id;
");

...then based on the above query, build my array of questions and answers:

$quiz = array();
while ($row = mysql_fetch_assoc($result_quiz)) {
    if (!isset($quiz['questions']['q'.$row['id'].''])) {
        $quiz['questions']['q'.$row['id'].''] = array(
            'question' => $row['question_text'],
            'answers' => array()
        );
    }
    $quiz['questions']['q'.$row['id'].'']['answers'][] = array('answer' => $row['answer_text'],'points' => $row['points']);
}

...then query the database for the second time to get a list of score profiles:

$result_profile = mysql_query("
    SELECT quiz_profile.name, quiz_profile.description, quiz_profile.min_points, quiz_profile.max_points
    FROM quiz
    JOIN quiz_profile ON (quiz.id = quiz_profile.quiz_id)
    WHERE quiz.id = $id;
");

...then loop through my second query so I can append the score profiles to my array:

while ($row = mysql_fetch_assoc($result_profile)) {
    $quiz['profiles'][] = array('name' => $row['name'],'description' => $row['description'],'min_points' => $row['min_points'],'max_points' => $row['max_points']);
}

Is this the best approach?

Or should I combine my queries into one using UNION?

If so, how can I write my conditions to know what results I'm looping through?

Thanks SO much!

user359040 · Accepted Answer · 2012-01-12 17:22:12Z

1

I suggest sticking with the current approach, unless there is a serious problem with performance - combining the two queries might improve total throughput (slightly), but would significantly increase the complexity of the code required to display it.

answered Jan 12, 2012 at 17:22

user359040

Sign up to request clarification or add additional context in comments.

Comments

Eugen Rieck · Accepted Answer · 2012-01-12 20:21:58Z

1

I recommend to take a step back at the design of your app, not the code itself: If you go down the UNION road, you have to sacrifice all isolation between your questions-code and your scoreprofiles-code.

I say, this is not a good thing: What if one day the score profiles are in some way recalculated or trabsformed between db and user? Is it intuitive, that for that you have to touch the function getting the questions from the db?

In PHP, there is a rule of thumb, which (as all rules of thumb) is not 100% perfect, but quite a start: It should be possible to have all code relating to one field of functionality be located in one file, the includes and requires mirroring the dependencies.

answered Jan 12, 2012 at 20:21

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Collectives™ on Stack Overflow

Multiple SELECT queries and looping through results in PHP and mySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related