来自多个MySQL表的单个HTML表。

I've been struggling for a while with this one; I'll try to explain it here as simply as possible.

我已经为这个挣扎了一段时间;我在这里尽量简单地解释一下。

Consider this MySQL table:

考虑一下这个MySQL表:

+----------+-----------+---------+--------+
|status_id |session_id |pilot_id |present |
+----------+-----------+---------+--------+
|1         |61         |901      |1       |
|2         |63         |901      |1       |
|3         |62         |901      |0       |
|4         |62         |902      |1       |
|5         |63         |903      |1       |
+----------+-----------+---------+--------+

Both session_id and pilot_id are foreign keys making reference to a primary key in another table. The same pilot_id can be associated with different session_id, but every pilot_id-session_id combination is unique.

session_id和pilot_id都是外键，用于引用另一个表中的主键。相同的pilot_id可以与不同的session_id相关联，但是每一个pilot_id-session_id组合都是独一无二的。

I need to make an HTML table (in PHP) that would display the data like this:

我需要制作一个HTML表格(PHP)，显示如下数据:

+----------+---------+---------+---------+
|          |61       |62       |63       |
+----------+---------+---------+---------+
|901       |X        |         |X        |
|902       |         |X        |         |
|903       |         |         |X        |
+----------+---------+---------+---------+

Hence, rows are pilot_id and columns are session_id. When a pilot_id-session_id combination has a present value of 1, the corresponding cell should be checked. (ie. when the row-combination is zero or the combination does not exist in the MySQL table, nothing should appear in the HTML table)

因此，行是pilot_id，列是session_id。当pilot_id-session_id组合的现值为1时，应该检查相应的单元格。(即。当行组合为零时，或者在MySQL表中不存在组合时，HTML表中不应该出现任何东西。

Phew.

唷。

Any ideas?

什么好主意吗?

Thanks!

谢谢!

I've tried the answer proposed by erisco, but I'm quite confused. (the comment field is much too small for my explanation, hence this update to my question).

我试过erisco提出的答案，但是我很困惑。(对于我的解释来说，注释字段太小了，因此更新了我的问题)。

This is the actual data I am working with:

这是我正在研究的实际数据:

+----------+-----------+---------+--------+
|status_id |session_id |pilot_id |present |
+----------+-----------+---------+--------+
|7         |65         |33       |1       |
|8         |66         |33       |1       |
|9         |65         |17       |0       |
|10        |66         |16       |1       |
+----------+-----------+---------+--------+

I use $rows = mysqli_fetch_array($result);. I have confirmed the query is returning the right data.

我使用$rows = mysqli_fetch_array($result);我已经确认了查询正在返回正确的数据。

However, when I use the answer proposed by ericso, I am getting seemingly arbitrary data. Here's the generated HTML table:

然而，当我使用ericso给出的答案时，我得到的数据似乎是随意的。下面是生成的HTML表:

+----------+---------+---------+---------+---------+
|          |1        |3        |6        |7        |
+----------+---------+---------+---------+---------+
|1         |X        |         |         |         |
|3         |         |         |         |         |
|6         |         |         |         |         |
|7         |         |         |         |         |
+----------+---------+---------+---------+---------+

Furthermore the 'X' position stays the same irrelevantly of present values.

此外，“X”的位置与当前值不相关。

Any ideas why this is happening?

有什么想法吗?

Thanks!

谢谢!

2 个解决方案

#1

Luckily you only need one query. Presuming $rows is the format of your data withdrawn from the database:

幸运的是，您只需要一个查询。假设$row是您从数据库中提取数据的格式:

<?php

$rows = array(
  array(
    'status_id' => 1,
    'session_id' => 61,
    'pilot_id' => 901,
    'present' => 1,
  ),
  array(
    'status_id' => 2,
    'session_id' => 63,
    'pilot_id' => 901,
    'present' => 1,
  ),
  array(
    'status_id' => 3,
    'session_id' => 62,
    'pilot_id' => 901,
    'present' => 0,
  ),
  array(
    'status_id' => 4,
    'session_id' => 62,
    'pilot_id' => 902,
    'present' => 1,
  ),
  array(
    'status_id' => 5,
    'session_id' => 63,
    'pilot_id' => 903,
    'present' => 1,
  )
);

$session_ids = array();
$pilot_ids = array();
$crosses = array();

foreach ($rows as $row) {
  $session_ids[$row['session_id']] = $row['session_id'];
  $pilot_ids[$row['pilot_id']] = $row['pilot_id'];
  if ($row['present'] == 1) {
    $cross_index = $row['session_id'].'.'.$row['pilot_id'];
    $crosses[$cross_index] = $cross_index;
  }
}

sort($session_ids);
sort($pilot_ids);

?>

<table>
  <tr>
    <th></th>
  <?php foreach ($session_ids as $sess_id): ?>
    <th><?php echo $sess_id; ?></th>
  <?php endforeach; ?>
  </tr>
  <?php foreach ($pilot_ids as $pilot_id): ?>
  <tr>
    <th><?php echo $pilot_id; ?></th>
    <?php foreach ($session_ids as $sess_id): ?>
    <?php if (isset($crosses[$sess_id.'.'.$pilot_id])): ?>
    <td>X</td>
    <?php else: ?>
    <td></td>
    <?php endif; ?>
    <?php endforeach; ?>
  </tr>
  <?php endforeach; ?>
</table>

#2

You can use algorithm like this:

你可以使用如下算法:

$sql = "SELECT DISTINCT session_id AS sid FROM pilot_session ORDER BY 1 ASC";
$rs = mysql_query($sql, $conn);
$sessions = array();
while(false !== ($r = mysql_fetch_array($rs))){
    $sessions[] = $r['sid'];
}

$sql = "SELECT DISTINCT pilot_id AS pid FROM pilot_session ORDER BY 1 ASC";
$rs = mysql_query($sql, $conn);
$pilots = array();
while(false !== ($r = mysql_fetch_array($rs))){
    $pilots[] = $r['pid'];
}

$pilot_presence = array();
$sql = "SELECT session_id, pilot_id, present FROM pilot_session";
$rs = mysql_query($sql, $conn);
while(false !== ($r = mysql_fetch_array($rs))){
    $s_presence[$r['pilot_id']][$r['session_id']] = $r['present'];
}

echo "<table><tr><td>&nbsp</td>";
foreach($sessions as $s){
    echo "<td>$s</td>";
}
echo "</tr>";
foreach($pilots as $p){
    echo "<tr><td>$p</td>";
    foreach($sessions as $s){
        $tp = '';
        if(isset($s_presence[$p][$s])){
            if($s_presence[$p][$s] == '1'){
                $tp = 'X';
            }
        }
        echo "<td>".$tp."</td>";
    };
    echo "</tr>";
}
echo "</table>";

#1