使用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表开斋节,ename
故障表 has fid,故障
频率表有qid, frequency, eid, fid
<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>
第二个表是我有问题的地方,因为它是水平的,每一行有两个来自两个不同表的字段。(即故障和频率)
下面的代码将表准备为2d数组。你可以随意显示:
$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
)
)
这是你的最终表格:
failure khalil yuzri arthur ashley
battery failure 2 3 0 1
windows failure 0 0 3 4
virus attack 3 2 3 1
试试这个:
<?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>