I want to display all sections from sections table for each classroom of classroom table with php mysql

Question

I have 2 tables:

Classrooms (id, name) with 13 records.

Sections (id, name, class_id) with 2 records for each classroom.

I want to display all sections from sections table for each classroom of classrooms table with php mysql.

I have 2 queries, one for each table with full join

SELECT * FROM classrooms
JOIN sections ON sections.class_id=classrooms.id
GROUP BY(sections.class_id)

SELECT * FROM sections
JOIN classrooms ON classrooms.id=sections.class_id 

my code is the following:

 <?php foreach($classrooms as $classe): ?>
    <div><?php echo $classe['name']; ?></div>
    <?php foreach($sections as $section): ?>
       <?php if($section['class_id'] == $classe['id']) : ?>
           <div class="">
               <div><?php echo $section['name']; ?></div>
           </div>
       <?php endif ?> 
   <?php endforeach ?>

The problem it display the 2 sections for the first classroom and all the other classrooms are empty.

Thanks

Answer 1

Edit Edit: Okay, your query shouldnt return the query itself, but the items inside the database (as mentioned in your 2nd comment). Maybe check the database function itself and what you get out of it. If you get an array, you can loop through it. Your queries themselves (as stated in your 4th comment) look fine. But they dont seem to return the right values as said before. Maybe share the database function?

Answer 2

I found the solution. The problem was in sql query, so after many reaserchs, I have changed it (the code) to the following:

   $sql = "SELECT ac.`classe_name`, `section_name`, `classe_id` FROM `allclasses` ac JOIN `sections` sc ON  sc.`classe_id`=ac.id";

   $result = $db->query($sql);

   $classes = $result->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

And in the view :

foreach($classes as $classe => $sections)

echo $classe;

foreach($sections as $section)

echo $section['section_name']

endforeach

endforeach