php foreach in foreach in foreach

Question

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?

why don't you use an activeRecord.. maybe create relations with InnoDB and fetch them with PDO; or start using some of MVC frameworks which support ActiveRecords, relations, etc. like: Yii, CodeIgniter, Kohana, Symphony — user1386320
– user1386320, Commented Jul 5, 2013 at 18:20

exussum · Accepted Answer · 2013-07-05 18:21:07Z

1

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

answered Jul 5, 2013 at 18:21

exussum

18.6k8 gold badges35 silver badges67 bronze badges

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

Comments

GolezTrol · Accepted Answer · 2013-07-05 18:29:28Z

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.

CXJ · Accepted Answer · 2013-07-05 18:56:43Z

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

Is this better practice than doing a foreach in foreach in foreach? If so, why?
It's likely more efficient and faster. I also find it easier to read.

Erik Lukiman · Accepted Answer · 2013-07-05 18:33:23Z

0

$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);

answered Jul 5, 2013 at 18:33

Erik Lukiman

3371 silver badge4 bronze badges

Comments

CXJ · Accepted Answer · 2013-07-05 18:41:57Z

0

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.

edited Jul 5, 2013 at 18:41

answered Jul 5, 2013 at 18:23

CXJ

4,5073 gold badges38 silver badges65 bronze badges

2 Comments

Staysee Over a year ago

I have used joins a little bit. What would the logic look like to display the school name once per change? I am new to php.

CXJ Over a year ago

See my other answer. It's a quick, ugly hack, but it should show you what you need to know.

Collectives™ on Stack Overflow

php foreach in foreach in foreach

5 Answers 5

Comments

Comments

2 Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

2 Comments

Related