Optimizing multiple queries in MySQL and PHP

Question

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:

+------------------------------+-------------------+
| 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:

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;
    }
}

Optimize what queries? You didn't attach any. You can't get multi dimensional results from MySQL. — Lukasz Kujawa, Mar 02 '13 at 22:37

score 1 · Accepted Answer · edited May 23 '17 at 11:57

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

To get the desired array structure in PHP will require:

Separate queries/result-sets (as shown in the post): 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.

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.

¹ Related to re-normalizing the data, YMMV:

So, if I understood, I get results like: `(course_info), (section_info), (resource_info)` for each query row and "filter" the data with PHP. Thats it? — Giovanne Afonso, Mar 02 '13 at 23:15
@GiovanneAfonso For #2, where "filter" means "re-normalize", then yes. If you need *every* record, see Travis G's answer, but if you *only* need some (i.e. using a WHERE) then a JOIN, as shown in MIIB's answer, is the way to go. Note that they return distinctly different results and so must be re-normalized differently. — , Mar 02 '13 at 23:17

score 1 · Answer 2 · edited Mar 02 '13 at 23:18

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

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.

score 1 · Answer 3 · answered Mar 02 '13 at 22:45

1

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
)

answered Mar 02 '13 at 22:45

Travis G

1,534
1
13
18

I have a lot of another different columns in each table (didn't put there to make it simple to read). So I would get a different "column count" for each table. Can I still use something like this? – Giovanne Afonso Mar 02 '13 at 23:20

score 0 · Answer 4 · answered Mar 02 '13 at 23:57

        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
                                )

                        )

                )

        )

)

Optimizing multiple queries in MySQL and PHP

Questions

Details

What I did:

4 Answers4