MYSQL - 在循环浏览周结束日期时显示数据 - MYSQL- Displaying data while looping through week ending dates

在过去的一个月里，我一直在创建一个基准系统，允许用户添加类别和基准项目，在特定年份内每周保留数据。我在创建一个 mysql 语句时遇到问题，该语句显示每个基准项及其一年内所有周的结果，即使该周没有数据或一年没有数据。以下是我一直在测试的查询和表列。我正在使用代码点火器，并计划以底部映射的格式显示结果。对于这个冗长的描述，我深表歉意，但我试图尽可能清楚。任何帮助将不胜感激。

tblbenchmarkitem
=================
itemID | itemDescription | itemTarget | ItemFreq | FKcategoryID
tblbenchmarkData
================
dataID | FKitemID | resultDate | result | dateAdded | dateModified
tblcategories
===============
categoryID | categoryName | parentID | FKdeptID
tblcalendardates  - stores 52 weeks for year Saturday to Friday
================
id | year | startDate | endDate

这些是我在模型中的 mysql 语句。我将在函数参数中为年份设置默认值。

public function get_data() {
    $dates = $this->db->query("SELECT endDate FROM tblcalendardates WHERE year = '2014' ORDER BY endDate");
    $dates = $dates->result();
    foreach ($dates as $date ) {
        //$query = "SELECT tblcalendardates.enddate, tblbenchmarkitems.itemDescription, tblbenchmarkitems.itemTarget, tblbenchmarkitems.itemFrequency, tblbenchmarkdata.resultDate, IFNULL(tblbenchmarkdata.result,0) AS result, (SELECT strCategoryName FROM tblcategories WHERE tblbenchmarkitems.FKcategoryID = tblcategories.categoryID) AS category FROM tblbenchmarkdata LEFT JOIN tblbenchmarkitems ON tblbenchmarkdata.FKitemID = tblbenchmarkitems.itemID JOIN tblcalendardates WHERE DATE(tblbenchmarkdata.resultDate) BETWEEN (SELECT MIN(DATE(tblcalendardates.startdate)) FROM tblcalendardates WHERE tblcalendardates.year = " . date('Y') . ") AND (SELECT MAX(DATE(tblcalendardates.enddate)) FROM tblcalendardates WHERE tblcalendardates.year = " . date('Y') . ") GROUP BY category ORDER BY tblcalendardates.enddate";
        $query = 'SELECT c.*, i.itemDescription, i.itemTarget, IFNULL(d.result, 0) as result,"' . $date ->endDate . '" FROM tblcategories AS c JOIN tblbenchmarkitems AS i ON i.FKcategoryID = c.categoryID JOIN tblbenchmarkdata as d ON i.itemID = d.FKitemID JOIN tblcalendardates WHERE DATE(d.resultDate) BETWEEN (SELECT MIN(DATE(tblcalendardates.startdate)) FROM tblcalendardates WHERE tblcalendardates.year = "' . date('Y') . '") AND (SELECT MAX(DATE(tblcalendardates.enddate)) FROM tblcalendardates WHERE tblcalendardates.year = "' . date('Y') . '") GROUP BY strCategoryName';
        $query = $this->db->query($query);
    return $query->result();
    }
}

在我看来，这是

<table class="table table-hover data-list">
    <thead>
        <tr>
          <th class="th-set-width1">Benchmark Item</th>
          <th class="th-set-width2">Target</th>
        <?php foreach( $weeks as $week ): ?>
          <th class="th-set-width2"><?php echo date('m-d-Y', strtotime($week->enddate)); ?></th>
        <?php endforeach; ?>
        </tr>
    </thead>
    <tbody>
        <?php foreach( $entries as $entry ) : ?>
        <tr>
          <td><?php echo $entry->itemDescription; ?></td>
          <td><?php echo $entry->itemTarget; ?></td>
          <?php for ($i = 0; $i < 52; $i++) : ?>
            <?php if ($entry->result != NULL || $entry->result > 0): ?>
              <td><?php echo $entry->result; ?></td>
            <?php else: ?>
              <td>0</td>
            <?php endif; ?>
          <?php endfor; ?>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>

这就是我想在表中显示数据的方式。

-----------------------------------------------------------------------------------
categoryName
====================================================================================
itemDescription |  itemTarget | 01-03-14 | 01-10-14 | 01-17-14 | 01-24-14 | 01-31-14
=====================================================================================
No. of Visits   |    12.00    |   NULL   |   NULL   |   15.00  |    NULL  |   20.00 
No. of Calls    |    17.00    |   12.00  |   NULL   |   17.00  |    22.00 |   NULL

我对代码进行了一些更改，几乎产生了我正在寻找的响应。现在唯一的问题是我有两个项目正在显示，每个项目一个结果。

我的模型：

public function get_data() {
    $items = $this->db->query('SELECT * FROM tblbenchmarkitems');
    $items = $items->result();
    foreach ( $items as $item ) {
        $query = 'SELECT c.endDate, IFNULL(d.result,0) as result FROM tblbenchmarkdata AS d RIGHT JOIN tblcalendardates as c ON (DATE(d.resultDate) = c.endDate) JOIN tblbenchmarkitems as i  WHERE c.year = "2014" AND i.itemID = "' . $item->itemID .'" GROUP BY c.endDate';
        $query = $this->db->query($query);
        $data[] = array(
            'itemID'            => $item->itemID,
            'itemDescription'   => $item->itemDescription,
            'itemTarget'        => $item->itemTarget,
            'dates'             => $query->result()
        );
    }
    return $data;
}

我的观点：

<table class="table table-hover data-list">
  <thead>
    <tr>
      <th class="th-set-width1">Benchmark Item</th>
      <th class="th-set-width2">Target</th>
    <?php foreach( $weeks as $week ): ?>
       <th class="th-set-width2"><?php echo date('m-d-Y', strtotime($week->enddate)); ?></th>
    <?php endforeach; ?>
    </tr>
  </thead>
  <tbody>
  <?php for( $n = 0; $n < count($entries); $n++ ) : ?>
    <tr>
      <td><?php echo $entries[$n]['itemDescription']; ?></td>
      <td><?php echo $entries[$n]['itemTarget']; ?></td>
      <?php for( $i = 0; $i < count($entries[$n]['dates']);  $i++) :?>
      <td><?php echo $entries[$n]['dates'][$i]->result; ?></td>
      <?php endfor; ?>
    </tr>
  <?php endfor; ?>
  </tbody>
</table>

查询结果中的表格式 - 问题是记录 1 和记录 2 同时显示两个结果。记录 1 的结果记录在 11/7/14，记录 2 的结果记录在 7/11/14：

+-----------------------------------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
|Benchmark Item                           | Target |01-03-2014|01-10-2014|01-17-2014|01-24-2014|01-31-2014|02-07-2014|02-14-2014|02-21-2014|02-28-2014|03-07-2014|03-14-2014|03-21-2014|03-28-2014|04-04-2014|04-11-2014|04-18-2014|04-25-2014|05-02-2014|05-09-2014|05-16-2014|05-23-2014|05-30-2014|06-06-2014|06-13-2014|06-20-2014|06-27-2014|07-04-2014|07-11-2014|07-18-2014|07-25-2014|08-01-2014|08-08-2014|08-15-2014|08-22-2014|08-29-2014|09-05-2014|09-12-2014|09-19-2014|09-26-2014|10-03-2014|10-10-2014|10-17-2014|10-24-2014|10-31-2014|11-07-2014|11-14-2014|11-21-2014|11-28-2014|12-05-2014|12-12-2014|12-19-2014|12-26-2014|
+-----------------------------------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
|Total No. of Calls Conducted               17.00     0.00        0.00        0.00      0.00        0.00       0.00     0.00        0.00        0.00        0.00        0.00        0.00      0.00      0.00       0.00     0.00        0.00        0.00        0.00        0.00        0.00      0.00     0.00    0.00     0.00        0.00        0.00        **2.00**        0.00        0.00      0.00     0.00    0.00     0.00        0.00        0.00        0.00        0.00        0.00      0.00        0.00     0.00         0.00       0.00   **12.00**     0.00        0.00        0.00     0.00      0.00      0.00       0.00   |
+-----------------------------------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
|Total No. of Random Visits                 29.00     0.00        0.00        0.00      0.00        0.00       0.00     0.00        0.00        0.00        0.00        0.00        0.00      0.00      0.00       0.00     0.00        0.00        0.00        0.00        0.00        0.00      0.00     0.00    0.00     0.00        0.00        0.00        **2.00**        0.00        0.00      0.00     0.00    0.00     0.00        0.00        0.00        0.00        0.00        0.00      0.00        0.00     0.00         0.00       0.00   **12.00**     0.00        0.00        0.00     0.00      0.00      0.00       0.00   |
+-----------------------------------------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+

已解决

在模型中，我更改了查询，删除了对 tblbenchmarkitems 表的连接，并删除了在 where 语句中指定 itemID 的需要。我将 FKitemID 字段添加到输出中，以帮助确保结果对应于正在评估的特定项目（即，如果项目 ID 为 1，并且数组中提供的结果属于 ID 为 1 的项目，则仅显示 ID 为 1 的数据。

模型中获取基准数据的函数。

public function get_data() {
    $items = $this->db->query('SELECT * FROM tblbenchmarkitems');
    $items = $items->result();
    foreach ( $items as $item ) {
        $query = 'SELECT c.endDate as weekending, IFNULL(d.result,0) as result, d.FKitemID FROM tblbenchmarkdata AS d RIGHT JOIN tblcalendardates as c ON (DATE(d.resultDate) = c.endDate) WHERE c.year = "2014" GROUP BY weekending';
        $query = $this->db->query($query);
        $data[] = array(
            'itemID'            => $item->itemID,
            'itemDescription'   => $item->itemDescription,
            'itemTarget'        => $item->itemTarget,
            'dates'             => $query->result()
        );
    }
    return $data;
}

观点：

<table class="table table-hover data-list">
  <thead>
     <tr>
       <th class="th-set-width1">Benchmark Item</th>
       <th class="th-set-width2">Target</th>
    <?php foreach( $weeks as $week ): ?>
        <th class="th-set-width2"><?php echo date('m-d-Y', strtotime($week->enddate)); ?></th>
    <?php endforeach; ?>
     </tr>
  </thead>
  <tbody>
  <?php for( $n = 0; $n < count($entries); $n++ ) : ?>
    <tr>
      <td><?php echo $entries[$n]['itemDescription']; ?></td>
      <td><?php echo $entries[$n]['itemTarget']; ?></td>
     <?php for( $i = 0; $i < count($entries[$n]['dates']);  $i++) :?>
       <?php if ( $entries[$n]['itemID'] == $entries[$n]['dates'][$i]->FKitemID ) : ?>
       <td><?php echo $entries[$n]['dates'][$i]->result; ?></td>
       <?php else: ?>
       <td>0.00</td>
       <?php endif; ?>
     <?php endfor; ?>
   </tr>
  <?php endfor; ?>
  </tbody>
</table>

最后，在视图中，我在第二个 for 循环（在几周内输出数据的循环）之后添加了一个 if 语句。它检查是否有带有正在输出的项目ID的结果，如果有，则仅在它对应的行和列中显示它。如果不是，则显示"0.00"。