在MySQL和PHP中优化多个查询

Questions

How should I do the query(ies) to get this results?

我应该如何执行查询以获得此结果?
Should I use a different structure for database tables?

我应该对数据库表使用不同的结构吗?

Details

I want to get results from 3 tables:

我想从3个表中得到结果:

+------------------------------+-------------------+
| courses                      | id                | <-------+
|                              | name              |         |
|                              |                   |         |
+------------------------------+-------------------+         |
| sections                     | id                | <-------|----------+
|                              | course_id         | <- FK(courses.id)  |
|                              | name              |                    |
+------------------------------+-------------------|                    |
| resources                    | id                |                    |
|                              | section_id        | <- FK(sections.id)-+
|                              | name              |
+------------------------------+-------------------+

I want to store results in a PHP Array like this:

我想将结果存储在这样的PHP数组中:

Array
(
    [courses] => Array
        (
            [id] => 1
            [name] => course 1
            [sections] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [course_id] => 1
                            [name] => course 1 section 1
                            [resources] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 1
                                            [section_id] => 1
                                            [name] => resource 1
                                        )

                                )

                        )

                )

        )

)

EDIT

编辑

What I did:

$cources = DB::query(Database::SELECT,
'select * from courses')->execute($db,false)[0];  // Get all courses as array

foreach($courses as &$course) {
    $sections = DB::query(Database::SELECT,
    'select * from sections where course_id = '.$courses['id']);

    $course['sections'] = $sections;

    foreach($course['sections'] as &&section) {
        $resources = DB::query(...); // Get array of resources
        $section['resources'] = $resources;
    }
}

4 个解决方案

#1

The database structure is normalized - this is correct and should not be changed.

数据库结构是规范化的——这是正确的，不应该更改。

However, SQL returns de-normalized or "flattened" data for an N+ join: only a set of homogenous records can be returned in a single result-set. (Some databases, like SQL Server, allow returning structure by supporting XML generation.)

但是，SQL返回N+连接的非规范化或“扁平”数据:在一个结果集中只能返回一组同质记录。(有些数据库，如SQL Server，通过支持XML生成，允许返回结构。)

To get the desired array structure in PHP will require:

要在PHP中获得所需的数组结构，需要:

Separate queries/result-sets (as shown in the post): ick!

单独查询/结果集(如文章所示):ick!

There will about one query/object. While the theoretical bounds might be similar, the practical implementation will be much less efficient and the overhead will be much more than for single query. Remember that every query incurs (at the very least) a round-trip penalty - as such, this is not scalable although it will likely work just fine for smaller sets of data or for "time insensitive" operations.

将会有一个查询/对象。虽然理论上的界限可能类似，但实际实现的效率要低得多，而且开销要比单个查询大得多。请记住，每个查询都会带来(至少)往返代价——因此，这是不可伸缩的，尽管它可能只适用于较小的数据集或“时间不敏感”操作。
Re-normalize the resulting structure:

可以生成的结构:

This is very trivial to do with support of a "Group By" operation, as found in C#/LINQ. I am not sure how this would be approached [easily] in PHP¹. This isn't perfect either, but assuming that hashing is used for the grouping, this should be able to scale fairly well - it will definitely be better than #1.

这对于支持c# /LINQ中的“Group By”操作非常简单。我不确定这在PHP1中是如何实现的。这也不是完美的，但是假设散列用于分组，这应该能够很好地伸缩—它肯定比#1更好。

Instead of the above, consider writing the query in such a way that the "flat" result can be used within the current problem/scope, if possible. That is, analyze how the array is to be used - then write the queries around that problem. This is often a better approach that can scale very well.

如果可能的话，可以考虑以这样一种方式编写查询，以便在当前的问题/范围内使用“flat”结果。也就是说，分析如何使用数组——然后围绕这个问题编写查询。这通常是一种可以很好地扩展的更好的方法。

¹ Related to re-normalizing the data, YMMV:

1 .与数据重新正常化相关，YMMV:

SQL result to PHP multidimensional array
SQL结果到PHP多维数组。
PHP array to multidimensional array
PHP数组到多维数组。
Group a multidimensional array by a particular value?
用特定的值分组多维数组?

#2

You can try something like this

你可以试试这个

SELECT * FROM (
select c.id, c.name from courses c
union
select s.id, r.name,s.course_ID from sections s
union
select r.id, r.name,r.section_ID from resources r
)

#3

You cant get multi dimensional result from mysql. The query for getting the elements should be like this:

你不能从mysql得到多维的结果。获取元素的查询应该如下所示:

select courses.id as coursesId,courses.name as coursesName,sections.id as sectionsId,sections.name as sectionsName,resources.id as resourcesId, resources.name as resourcesName
from courses
left join sections on courses.id=sections.course_id
left join resources on sections.id=resources.section_id;

But ofcourse it will not give you the array as you like.

但是它当然不会像你想的那样给你数组。

#4

        if you are familiar with php then you can use this code i am writing only 2nd level you can write same way with third label
    $final=array();
           $c=-1;
           $cid=false;
           $cname=false;
           $query = "SELECT c.*,s.*,r.* FROM  courses AS c LEFT JOIN sections AS s ON c.id=s.course_id LEFT JOIN resources AS r ON r.section_id =s.id";
           $result=mysql_query($query, $this->con) or die(mysql_error());
           while($row=  mysql_fetch_array($result)){
               if($cid!=$row[2]){
                   $final['cources'][++$c]['id']=$cid=$row[0];
                   $final['cources'][$c]['name']=$cname=$row[1];
                   $s=-1;


               }
               $final['cources'][$c]['sections'][++$s]['id']=$row[2];
               $final['cources'][$c]['sections'][$s]['course_id']=$row[3];
              $final['cources'][$c]['sections'][$s]['name']=$row[4];
           }
           echo "<pre>";
           print_r($final);
           echo "</pre>";

//Outpur
Array
(
    [cources] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [name] => c1
                    [sections] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 1
                                    [course_id] => 1
                                    [name] => s1-1
                                )

                            [1] => Array
                                (
                                    [id] => 1
                                    [course_id] => 1
                                    [name] => s1-1
                                )

                        )

                )

            [1] => Array
                (
                    [id] => 2
                    [name] => c2
                    [sections] => Array
                        (
                            [0] => Array
                                (
                                    [id] => 2
                                    [course_id] => 2
                                    [name] => s1-2
                                )

                        )

                )

        )

)

#1