php - 在php中的mysql语句中连接4个表

Question

我不知道如何很好地解释它，但我想要的是我想在我的主题表中显示讲师姓名和类(class)标题以及主题的日期和时间，首先我可以显示主题及其讲师姓名和类(class)标题，但是当我显示它的日期和时间时。它不符合我想要的输出..

这是我的示例代码:

//subjectClass.php
public function subjects(){
            global $db;

    $sql = "
    SELECT s.*
         , i.first_name
         , i.mid_name
         , i.last_name
         , c.course_title
         , d.sub_day
         , d.start_time
         , d.end_time 
      FROM subjects_tbl s
      LEFT 
      JOIN instructors_tbl i
        ON i.instructor_id = s.instructor_id 
      LEFT 
      JOIN courses_tbl c
        ON c.course_id = s.course_id
      LEFT 
      JOIN subject_day_tbl d
        ON d.subject_id = s.subject_id;
      ";

            $query = $db->query($sql);
            if($query->num_rows > 0){
                while($row = $query->fetch_assoc()){
                    $list[] = $row;
                }
            }else{
                $list = NULL;
            }

            return $list;
        }
//subjects.php
//include 'subjectsClass.php';
<table class="table table-condensed table-bordered">
        <thead>
          <tr>
            <th>Subject Code</th>
            <th>Subject Title</th>
            <th>Unit</th>
            <th>Section</th>         
            <th>Course</th>
            <th>Instructor</th>
            <th>Day/Time</th>
            <th></th>
          </tr>
        </thead>
        <tbody>
          <?php
            $subjectsClass = new subjectsClass;
            $subjects = $subjectsClass->subjects();

            foreach ($subjects as $key => $value) {

          ?>
          <tr>
            <td><?php echo $value['subject_code']; ?></td>
            <td><?php echo $value['subject_title']; ?></td>
            <td><?php echo $value['unit']; ?></td>
            <td><?php echo $value['section']; ?></td>
            <td><?php echo $value['course_title']; ?></td>
            <td><?php echo $value['first_name'] . " " . $value['mid_name'] . ". " . $value['last_name'] ; ?></td>
            <td>
              <?php echo $value['sub_day'] . " [" . $value['start_time'] . " - " . $value['end_time'] . "]<br />"; ?>
            </td>
            <td><a href="#">Edit</a> | <a href="#">Delete</a></td>
          </tr>
          <?php
              }

          ?>
        </tbody>

这是输出:

Subject Code Subject Title                  Unit Section Course Instructor              Day/Time
ITE 131      Security Issues and Principles    3 IT-R    BSIT   Darwin Paradela. Galudo Monday    [07:30:00 - 09:00:00]
ITE 131      Security Issues and Principles    3 IT-R    BSIT   Darwin Paradela. Galudo Wednesday [08:30:00 - 10:00:00]
ITE 050      Database Management System 2      3 IT-R    BSIT   Ronnie Pitt. Cambangay  Tuesday   [07:00:00 - 08:30:00]
ITE 050      Database Management System 2      3 IT-R    BSIT   Ronnie Pitt. Cambangay  Thursday  [07:00:00 - 08:30:00]

我想要的输出是这样的:

+--------------+--------------------------------+------+---------+--------+-------------------------+---------------------------------+
| Subject Code | Subject Title                  | Unit | Section | Course | Instructor              | Day/Time                        |
+--------------+--------------------------------+------+---------+--------+-------------------------+---------------------------------+
| ITE 131      | Security Issues and Principles |    3 | IT-R    | BSIT   | Darwin Paradela. Galudo | Monday    [07:30:00 - 09:00:00] |
|              |                                |      |         |        |                         | Wednesday [08:30:00 - 10:00:00] |
+--------------+--------------------------------+------+---------+--------+-------------------------+---------------------------------+
| ITE 050      | Database Management System 2   |    3 | IT-R    | BSIT   | Ronnie Pitt. Cambangay  | Tuesday   [07:00:00 - 08:30:00] |
|              |                                |      |         |        |                         | Thursday  [07:00:00 - 08:30:00] |
+--------------+--------------------------------+------+---------+--------+-------------------------+---------------------------------+

我的 table :主题_tbl 类(class)表 讲师_tbl 主题日表

Answer 1

希望能帮到你。

您的问题似乎是 subject_day_tbl 的左连接。它是一对多关系(即，subject_day_tbl 中可以有许多记录，而 subject_tbl 中的每条记录)，当您离开加入一对多关系时，您将获得“一”侧的副本“多”方的每一行。在单个查询中执行此操作的唯一方法是使用子查询或组语句来连接数据库引擎中的行...但这非常糟糕，因为您将显示与数据模型混合在一起。

虽然一般来说，为了性能，人们应该避免向数据库发出太多查询，premature optimization is the root of all evil 。首先，尝试编写干净、易于理解的代码，然后在遇到问题时查找瓶颈。

在这种情况下，第二个查询当然是最好的。我就是这样做的:

//subjectClass.php
protected function subject_days($subject_id)
{
   // I don't know what type of object $db is it looks like ezSQL,
   // but you get the idea
   global $db;
   $sql = "SELECT sub_day, start_time, end_time
           FROM subject_day_tbl
           WHERE subject_id = %s";
   $query = $db->query($db->prepare($sql, $subject_id));
   return ($query->num_rows > 0) ? $stmt->fetch_assoc() : array();
}

public function subjects()
{
    global $db;
    $sql = "SELECT s.*
              , i.first_name
              , i.mid_name
              , i.last_name
              , c.course_title
              , d.sub_day
              , d.start_time
              , d.end_time 
            FROM subjects_tbl s
            LEFT JOIN instructors_tbl i
                   ON i.instructor_id = s.instructor_id 
            LEFT JOIN courses_tbl c
                   ON c.course_id = s.course_id
            ";
    $list = array();
    $query = $db->query($sql);
    if($query->num_rows > 0){
        while($row = $query->fetch_assoc()){
            $row['course_days'] = $this->subject_days($row['subject_id']);
            $list[] = $row;
        }
    }
    return empty($list) ? NULL : $list;
}

// subjects.php
$subjectsClass = new subjectsClass;
$subjects = $subjectsClass->subjects();
foreach ($subjects as $key => $value) {
    ?><tr>
        <td><?php echo $value['subject_code']; ?></td>
        <td><?php echo $value['subject_title']; ?></td>
        <td><?php echo $value['unit']; ?></td>
        <td><?php echo $value['section']; ?></td>
        <td><?php echo $value['course_title']; ?></td>
        <td><?php echo $value['first_name'] . " "
                     . $value['mid_name'] . ". "
                     . $value['last_name'] ; ?></td>
        <td><?php foreach($value['course_days'] as $day) {
            echo $value['sub_day'] . " [" . $value['start_time'] . " - " . $value['end_time'] . "]<br />";
        }?></td>
        <td><a href="#">Edit</a> | <a href="#">Delete</a></td>
    </tr><?php
}

另外，不要成为一个学究，但你真的应该在之前转义你的输出回显它，例如使用 htmlspecialchars。