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

Question

Using PHP and MySQL, how can I convert the below table:

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

to this table:

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

I have three tables (as follows):

• employee table has eid, ename

• failure table has fid, failure

• frequency table has qid, frequency, eid, fid

For the first table, I joined it using PHP and MySQL like shown in the below code:

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

The second table is where I am having problem because its horizontal and each row has two fields from two different tables. (i.e. failure and frequency).

Answer 1

The following code prepares your table as a 2d array. You can display it however you wish:

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

For html output, a simple approach may be as follows:

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

Output is as follows:

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

This is your final table:

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