把多个表的值到数组 [英] Putting multiple table values into array

问题描述

所以我通过表中的所有值，它通过IDS连接尝试循环。这里涉及4桌，而我试图让所有从每个根据第一表的id的值。这里是code我有这么远。
（P.S我使用codeIgniter的数据库法）

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.

感谢您的帮助！

推荐答案

在我看来，你的code做一些替代品，你可能不希望它。至少与行：

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;

如果您有兴趣使用（presenting）由实体（表）分开的数据，那么我劝你做出的唯一改变是改变那些两条线（上图）于以下内容：

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

但是，我注意到，您包括每个孩子的实体，导致我的父ID来断定你想使用/ present数据在某种程度上反映关系的层次结构。如果是这样的话，我建议你做一些这样的：

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.

编辑：

这是你会如何遍历/解压缩从阵列中的信息。这code将在视图文件。

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

  }
?>

这篇关于把多个表的值到数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！