多个SELECT查询并循环遍历PHP和mySQL中的结果

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.

我正在尝试在PHP和mySQL中学习多个SQL查询的最佳实践。不确定我是否应该有两个单独的查询,或者我是否应该将(或UNION?)我的查询加入其中。

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.

重要的是要注意我的第一个查询是提取测验的问题和答案列表。第二个查询是拉出我将用于根据他们的测验分数分配给用户的分数配置文件列表。这两个表之间的唯一关系是两个都使用链接到我的“测验”表中的ID的相同外键。我不需要并排加入这些表,但我想我可能需要UNION它们,所以我的第二个查询结果出现在第一个之后。

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?

或者我应该使用UNION将我的查询合并到一个?

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

如果是这样,我怎样才能写出条件来了解我正在循环的结果?

Thanks SO much!

非常感谢!

2 个解决方案

#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.

我建议坚持使用当前的方法,除非性能存在严重问题 - 将两个查询组合起来可能会略微提高总吞吐量,但会显着增加显示它所需的代码的复杂性。

#2

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.

我建议您退一步设计您的应用程序,而不是代码本身:如果您沿着UNION路走下去,您必须牺牲所有问题代码和您的scoreprofiles代码之间的隔离。

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?

我说,这不是一件好事:如果有一天得分配置文件以某种方式重新计算或在数据库和用户之间进行转换怎么办?是否直观,为此你必须触摸从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.

在PHP中,有一条经验法则,(根据所有经验法则)并非100%完美,而是一个很好的开始:应该可以将与一个功能领域相关的所有代码都放在一个文件中,包括并要求镜像依赖项。