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!!

ghbarratt · Accepted Answer · 2012-03-11 20:24:01Z

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

  }
?>

edited Mar 11, 2012 at 20:24

answered Mar 10, 2012 at 2:16

ghbarratt

11.7k4 gold badges43 silver badges41 bronze badges

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

7 Comments

Sneaksta Over a year ago

Amazing! That is very helpful and shows me a much better way of doing things. What would be the best way to iterate through this array?

ghbarratt Over a year ago

Glad you liked it. You can iterate the result data in a fashion similar to how you built it, with nested loops.

ghbarratt Over a year ago

My recent concern with my answer was that you might only want to present the different data entities (tables) within their own presentation (like listing the badges separate from the topics). If that were the case, then it actually would make more sense to build separate arrays for each as you did, but when I saw that you were including the parent id in each I thought that you had an interest in preserving the relations so I decided to build the array to handle that. I am making a tiny edit to make note of this.

Sneaksta Over a year ago

I'm sorry but as I only mostly understand what you have done, I am not sure how to iterate the array properly. What would be the best way?

ghbarratt Over a year ago

Okay, I added some code showing how you can iterate the array.

|

Collectives™ on Stack Overflow

Putting multiple table values into array

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related