php - 如何将连接表的垂直列表转换为水平列表？

Question

使用 PHP 和 MySQL，如何转换下表:

id  employee     failures          frequency
---------------------------------------------
1   khalil      battery failure     2
2   khalil      windows failure     0
3   khalil      virus attack        3
4   yuzri       battery failure     3
5   yuzri       windows failure     0
6   yuzri       virus attack        2
7   arthur      battery failure     0
8   arthur      windows failure     3
9   arthur      virus attack        3
10  ashley      battery failure     1
11  ashley      windows failure     4
12  ashley      virus attack        1

到这个表:

failure           khalil  yuzri  arthur  ashley 
------------------------------------------------    
battery failure     2       3       0      1
windows failure     0       0       3      4
virus attack        3       2       3      1  

我有三个表(如下):

• employee表有eid,ename

• 失败表有fid，失败

• 频率表有qid,frequency,eid,fid

对于第一个表，我使用 PHP 和 MySQL 加入它，如下面的代码所示:

<table align="center" cellpadding="1" cellspacing="1" bordercolor="#000000" border="1">
  <tr align="center" bgcolor="#FFD700">
    <td align="center"><strong>ID</strong></td>
    <td align="center"><strong>EMPLOYEE</strong></td>
    <td align="center"><strong>FAILURES</strong></td>
    <td align="center"><strong>FREQUENCY</strong></td>
    <td align="center"><strong>DEPARTMENT</strong></td>
  </tr>
  <?php
  $sql="SELECT * FROM employees INNER JOIN frequency ON employees.eid=frequency.eid INNER JOIN f_types ON frequency.fid=f_types.fid ORDER BY frequency.qid";
  $result=mysql_query($sql);
  while($row = mysql_fetch_array($result)){
  ?>
  <tr align="center">
    <td align="center"><div><?php echo $row['qid']; ?></div></td>
    <td align="center"><div><?php echo $row['ename']; ?></div></td>
    <td align="center"><div><?php echo $row['failure']; ?></div></td>
    <td align="center"><div><?php echo $row['frequency']; ?></div></td>
    <td align="center"><div><?php echo $row['dept']; ?></div></td>
  </tr>
  <?php } ?>
</table>

第二个表是我遇到问题的地方，因为它是水平的，每一行都有来自两个不同表的两个字段。 (即失败和频率)。

Answer 1

以下代码将您的表格准备为二维数组。您可以根据需要显示它:

$output=array();

$output["title"]["title"]="failure";

// Table first row (headers)
$result=mysql_query("SELECT eid, ename FROM emplyee");
while ($row=mysql_fetch_array($result))
  $output["title"][$row["eid"]]=$row["ename"];

// Table first column (headers)
$result=mysql_query("SELECT fid, failure FROM failure");
while ($row=mysql_fetch_array($result))
  $output[$row["fid"]]["title"]=$row["failure"];

$result=mysql_query("SELECT eid, fid, frequency FROM frequency");
while ($row=mysql_fetch_array($result))
  $output[$row["fid"]][$row["eid"]]=$row["frequency"];

echo "<pre>";
print_r($output);

对于html输出，一个简单的做法可能如下:

<table align="center" cellpadding="1" cellspacing="1" bordercolor="#000000" border="1">
<?php foreach ($output as $rows) { ?>
    <tr align="center">
    <?php foreach ($rows as $vals) { ?>
        <td align="center"><?php echo $vals;?></td>
    <?php } ?>
    </tr>
<?php } ?>
</table>

输出如下:

Array
(
    [title] => Array
        (
            [title] => failure
            [1] => khalil
            [2] => yuzri
            [3] => arthur
            [4] => ashley
        )
    [1] => Array
        (
            [title] => battery failure
            [1] => 2
            [2] => 3
            [3] => 0
            [4] => 1
        )
    [2] => Array
        (
            [title] => windows failure
            [1] => 0
            [2] => 0
            [3] => 3
            [4] => 4
        )
    [3] => Array
        (
            [title] => virus attack
            [1] => 3
            [2] => 2
            [3] => 3
            [4] => 1
        )
)

这是您的决赛 table :

failure           khalil    yuzri   arthur  ashley
battery failure   2         3       0       1
windows failure   0         0       3       4
virus attack      3         2       3       1