How will I convert a vertical list of a joined table to a horizontal list?

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

possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — reaanb, Jul 09 '15 at 08:15
I have improved the formatting of the table data and code blocks in the question, removed *thanks note*, *signature* because they are not required. I have also removed the language and database name from title because they are not required due to presence of tags. — Harry, Jul 09 '15 at 08:49
@Strawberry yup, if there's a simple way to loop it. Since I'm just a newbie. — Kjames Omar, Jul 23 '15 at 07:47

geoidesic · Answer 1 · 2015-07-11T09:45:54.760

Try this:

<?php
  $sql="SELECT employees.eid as eid, employees.ename as ename, f_types.fid as fid, f_types.failure as failure, frequency.qid as qid, frequency.frequency as frequency 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);
  $data = array();
  while($row = mysql_fetch_array($result)){
    $data[$result['failure']][$result['ename']] = $result['frequency'];
  }
  ?>
<table>
  <tr>
    <th>failure</th>
    <?php
      $sql="SELECT DISTINCT employees.ename FROM employees";
      $result=mysql_query($sql);
      while($ename = mysql_fetch_array($result)){ ?>
    <th><?php echo $ename['ename']?></th>
    <?php } ?>
  </tr>
    <?php 
      $failure = '';
      $count = 0;
      foreach($data as $key=>$names { 
        if ($failure != $key) {
          $failure = $key;
          echo '<tr><td>'.$failure.'<td>';
        }
        foreach ($names as $name => $freq) {
          echo '<td>'.$freq.'</td>';
        }
        echo '</tr>';

      } ?>
</table>

score 0 · Accepted Answer · answered Jul 11 '15 at 09:50

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

your code works thanks. But I also need to put a bgcolor on the (first row headers of the table). Inserting the bgcolor property of the table in tr or td results to all rows inheriting the bgcolor property. Using your code, how do I change the bgcolor of the (first row headers of the table) only. Thanks. — Kjames Omar, Jul 23 '15 at 07:42

How will I convert a vertical list of a joined table to a horizontal list?

2 Answers2