CODEWITHSUNDEEP
phpmysql
Staysee
Staysee

Reputation: 1907

php foreach in foreach in foreach

This is a hypothetical question. If I have 3 arrays from 3 separate sql db queries that all relate to another. For example...

//db
schools
id | school_name

classes
id | class_name | school_id

students
id | student_name | class_id

And I want to display everything in a huge list like this...

//php
foreach(schools as school){

    echo '<h1>' . $school->school_name . '<h1>';

    foreach(classes as class){

        if($class->school_id == $school->id){

            echo '<h2>' . $class->class_name . '<h2>';

            foreach(students as student){

                if($student->class_id == $class->id){

                    echo '<h3>' . $student->student_name . '<h3>';

                }
            }
        }
    }
}

I have to make 3 database calls. Is there a way to grab all this information in a single db query? Like maybe an array in an array in an array and then somehow loop through? Or is this the best way to do this?

Upvotes: 1

Views: 457

Answers (5)

CXJ
CXJ

Reputation: 4467

<?php

try {
    $pdo = new PDO("mysql:host=127.0.0.1;dbname=school", "username");
} catch (PDOException $e) {
    echo "PDO Connection failed: " . $e->getMessage();
    exit(1);
}

$sql = <<<SQL

    SELECT schools.school_name, classes.class_name, students.student_name
    FROM
        schools INNER JOIN classes ON (schools.id = classes.school_id)
        INNER JOIN students ON (classes.id = students.class_id)
    ORDER BY 1, 2;

SQL;

$result = $pdo->query($sql);
if ($result == false) {
    die("query failed?!");
}

$school = "";
$class = "";
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    if ($school != $row['school_name']) {
        $school = $row['school_name'];
        echo "\nSchool:  $school\n\n";
    }
    if ($class != $row['class_name']) {
        $class = $row['class_name'];
        echo "   Class:  $class\n\n";
        echo "      Student list:\n";
    }
    echo "         {$row['student_name']}\n";
}

Upvotes: 0

CXJ
CXJ

Reputation: 4467

You could do it all in one SQL query that might look something like:

SELECT schools.schoolname, classes.class_name, students.student_name
FROM
    schools INNER JOIN classes ON (schools.id = classes.school_id)
    INNER JOIN students ON (classes.id = students.class_id)
ORDER BY 1, 2;

Then you could walk the result set in one loop, but you'd probably want to add some logic to only display the school name and class name once for each time it changes.

Upvotes: 0

Erik Lukiman
Erik Lukiman

Reputation: 337

$res = mysql_query('SELECT school_name, class_name, student_name, sc.id AS scid, c.id AS cid, st.id AS stid FROM schools sc LEFT JOIN classes c ON (sc.id = c.school_id) LEFT JOIN students st ON (c.id = st.class_id) ');  
$arr = array();
while ($v = mysql_fetch_assoc($res)) {
 $arr[$v['school_name']][$v['class_name']][$v['stid']] = $v['student_name'];
}

print_r($arr);

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116190

You can join those table, to get one big array with flattened data. When looping through this data, you can check if the id of the previous record still matches the id of the current record. If not, you can output a new header. It is important, though, that the resultset is properly sorted for this.

SELECT
  s.id AS school_id,
  s.school_name,
  c.id AS class_id,
  c.class_name,
  st.id AS student_id,
  st.student_name
FROM
  schools s
  INNER JOIN classes c ON c.school_id = s.id
  INNER JOIN students st ON st.class_id = c.id
ORDER BY
  s.id,
  c.id,
  st.id

If you have it all in a flattened structure, you can even make it into a nested array structure again something like this:

foreach ($resultset as $row)
{
    $schools[$row->school_id]->school_name = 
        $row->school_name;
    $schools[$row->school_id]->classes[$row->class_id]->class_name = 
        $row->class_name;
    $schools[$row->school_id]->classes[$row->class_id]->students[$row->student_id]->student_name =
        $row->student_name;
}
var_dump($schools);

After that, you can still use the nested for loops to process the array, but it will be in a more efficient way, since the data is already sorted out: classes are already added to the school they belong to, and student are already added to the right class.

Upvotes: 1

exussum
exussum

Reputation: 18578

You can do a join which will allow you to have 1 for each. Are you wanting everything or any sort of filter ?

Upvotes: 1

Related Questions