Putting multiple table values into array

Question

So I'm trying to loop through all the values of tables, which connect by ids. There are 4 tables involved, and I am trying to get all the values from each according the id of the first table. Here is the code I have so far. (P.S I'm using CodeIgniter's database method)

    $course = $this->db->query("SELECT * FROM courses");

    // Put Courses into array
    foreach ($course->result() as $row)
    {
        $courses['id'] = $row->id;
        $courses['course_name'] = $row->course_name;

        // Put Topics into array
        $topic = $this->db->query("SELECT * FROM course_topics WHERE course_id = 5");
        foreach ($topic->result() as $row)
        {
            $topics[] = array(
                                'id'            => $row->id,
                                'course_id'     => $row->course_id,
                                'topic_name'    => $row->topic_name,
                                'order'         => $row->order
                                );

            // Put badges into array
            $badge = $this->db->query("SELECT * FROM course_topic_badges WHERE topic_id = ".$row->id);
            foreach ($badge->result() as $row)
            {
                $badges[] = array(
                                    'id'            => $row->id,
                                    'topic_id'      => $row->topic_id,
                                    'badge_name'    => $row->badge_name
                                    );

                // Put dotpoints into array
                $dotpoint = $this->db->query("SELECT * FROM course_topic_dotpoints WHERE badge_id = ".$row->id);
                foreach ($dotpoint->result() as $row)
                {
                    $dotpoints[] = array(
                                        'id'            => $row->id,
                                        'badge_id'      => $row->badge_id,
                                        'dotpoint'      => $row->dotpoint,
                                        'viddler_video_id' => $row->viddler_video_id,
                                        'viddler_openurl' => $row->viddler_openurl,
                                        'order'         => $row->order
                                        );                  
                }
            }
        }
    }

I've been fiddling around for a while so there may be some lines that aren't done in the best fashion :)

So basically I need to iterate through the array later, but I'm just not sure of the best method of actually filling it to begin with.

Thanks for the help!!

Answer 1

It seems to me that your code is doing some replacement that you probably do not want it to. At least with the lines:

$courses['id'] = $row->id;
$courses['course_name'] = $row->course_name;

If you are interested in using (presenting) the data separated by entity (table) then the only alteration I advise you to make is change those two lines (above) to the following:

$courses[] = array('id'=>$row->id, 'course_name'=>$row->course_name);

BUT, I noticed that you are including the parent id in each of the children entities which leads me to conclude that you want to use/present the data in a way to reflect the relational hierarchy. If that is the case I recommend you doing something like:

$query = $this->db->query('SELECT * FROM courses');
$courses = $query->result_array();

// Put Courses into array
foreach ($courses as &$c)
{   
  // Put Topics into array
  $query = $this->db->query('SELECT id, topic_name, `order` FROM course_topics WHERE course_id = '.$c['id']);
  $topics = $query->result_array();
  $c['topics'] = $topics;
  foreach ($topics as $ti=>$t)
  {   
    $query = $this->db->query('SELECT id, badge_name FROM course_topic_badges WHERE topic_id = '.$t['id']);
    $badges = $query->result_array();
    $c['topics'][$ti]['badges'] = $badges;
    foreach ($badges as $bi=>$b)
    {   
      $query = $this->db->query('SELECT id, dotpoint, viddler_video_id, viddler_openurl, `order` FROM course_topic_dotpoints WHERE badge_id = '.$b['id']);
      $dotpoints = $query->result_array();
      $c['topics'][$ti]['badges'][$bi]['dotpoints'] = $dotpoints;
    }   
  }   
}

This builds a rather large associated array, but it is a good data structure if you are really going to need all that data and the relational hierarchy is important to you. Certainly it is best to reduce what you build to only what you will use.

EDIT:

This is how you might iterate/extract the information from the array. This code would be in the view file.

<h2>Courses:</h2>
<?php
  foreach($courses as $c) 
  {
    echo '<h3>'.$c['course_name'].' ('.$c['id'].")</h3>\n";
    echo '<ul>';
    foreach($c['topics'] as $t) 
    {   
      echo '<li>'.$t['topic_name'].' ('.$t['id'].")</li>\n";
      echo '<ul>';
      foreach($t['badges'] as $b) 
      {   
        echo '<li>'.$b['badge_name'].' ('.$b['id'].")</li>\n";
        echo '<ul>';
        foreach($b['dotpoints'] as $dp)
        {   
          echo '<li>'.$dp['dotpoint'].' viddler: '.$dp['viddler_video_id'].' viddler_url: '.$dp['viddler_openurl']."</li><br/>";
        }
        echo '</ul>';
      }     
      echo '</ul>';
    }
    echo '</ul>';

  }
?>