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