我有一个表,它在一个表中显示月份(一月到十二月),如下所示。
该表从数据库中获取值,不固定。
<table width="100%" style="font-size:9px">
<thead>
<tr>
<th colspan="14" style="text-align:center">Year: <?PHP echo $year?></th>
</tr>
<tr >
<th>Jan</th>
<th>Feb</th>
<th>Mar</th>
<th>Apr</th>
<th>May</th>
<th>Jun</th>
<th>Jul</th>
<th>Aug</th>
<th>Sep</th>
<th>Oct</th>
<th>Nov</th>
<th>Dec</th>
<th>Total</th>
</tr></thead><tbody>
<tr >
<?PHP
for ($i=1; $i<=12; $i++) {
$yearstart = $year."-".$i."-01";
$yearend = $year."-".$i."-31";
$sql="SELECT SUM(total) as totalsales_$i from salesorder where user_id=7 and created BETWEEN '$yearstart' AND '$yearend' ";
$res = $db->sql_query($sql);
$row = $db->sql_fetchrow($res);
$total = $total + $row['totalsales_'.$i];
?>
<td style="width: 96px; text-align:right">
<?PHP if ($row['totalsales_'.$i] !='' )
echo "$".number_format($row['totalsales_'.$i],2);
else
echo "<font color=#c2c2c2>$0.00</font>";?>
</td>
<?PHP //for ?>
<td style="text-align:right"><?PHP echo "$".number_format($total,2) ;?></td>
</tr>
</tbody></table>
OUTPUT:
Jan feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
$0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0
...
...
但是我需要的是如何垂直显示表格,如下所示
2011 2012 2013 ...
Jan $0.00 $0.00 $0.00 $0.00
feb $0.00 $0.00 $0.00 $0.00
Mar $0.00 $0.00 $0.00 $0.00
Apr $0.00 $0.00 $0.00 $0.00
May $0.00 $0.00 $0.00 $0.00
Jun $0.00 $0.00 $0.00 $0.00
Jul $0.00 $0.00 $0.00 $0.00
Aug $0.00 $0.00 $0.00 $0.00
Sep $0.00 $0.00 $0.00 $0.00
Oct $0.00 $0.00 $0.00 $0.00
Nov $0.00 $0.00 $0.00 $0.00
Dec $0.00 $0.00 $0.00 $0.00
Total $0.00 $0.00 $0.00 $0.00
我尝试了几种不同的方法,但没有运气。我希望我能在这里展示一些我所尝试的代码,但是我真的没有任何东西可以展示。
有人知道如何垂直显示这个表吗?
您可以首先从数据库中获取数据,并以可访问的方式将其存储到数组中,如:
$array[$year][$monthOrTotal] = $value;
然后你可以遍历这个数组,在你喜欢的任何方向输出你的表:
$cols = array('2011', '2012', '2013');
$rows = explode(',', 'Jan,feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Total');
echo '<table>', '<tr>', '<th></th>';
foreach($cols as $col)
{
echo '<th>', $col, '</th>';
}
echo '</tr>';
foreach($rows as $row)
{
echo '<tr>', '<th>', $row, '</th>';
foreach($cols as $col)
{
echo '<td>', $array[$col][$row], '</td>';
}
echo '</tr>';
}
echo '</table>';
(未测试代码警告)
这样做,您已经分离了从存储中获取数据的部分和它的输出。这通常是可取的,因为您不会将输出逻辑问题与获取数据混为一谈。
首先,你可能想要停止执行12n查询,并一次获得所有信息和MySQL的聚合机制的力量:
SELECT
YEAR(created) as `Y`,
MONTH(created) AS `M`,
SUM(total) AS `SUM`
FROM
salesorder
WHERE
user_id = 7
GROUP BY
YEAR(created),
MONTH(created)
WITH ROLLUP
将得到如下结果集:
Y | M | SUM
2011 | 10 | 20.00
2011 | 11 | 10.00
2011 | 12 | 5.00
2011 | NULL | 35.00 // M == NULL -> sum for this year
2012 | 1 | 7.00
2012 | 2 | 7.00
2012 | NULL | 14.00
NULL | NULL | 49.00 // M & Y == NULL -> sum for whole resultset
因此,您将在每行中有单个月份的sum,如果M
列为空则为每年的sum,并且在最后一行中有整个结果的sum(如果您不需要它,您可以丢弃它)
现在您必须创建其他数组,以便在向表中添加行时更容易迭代。我建议它看起来像这样:
$tableRows = array(
0 => array( // 0 will be for sum from each year
2011 => 0,
2012 => 0
),
1 => array( // January
2011 => 0
...
);
我不知道你使用什么类与数据库交互,所以让我假设有一些方法允许从resultset
获取关联数组$years = array();
while($dbRow = $res->fetchAssoc()){
$y = intval($dbRow['Y']);
if($y){
$m = intval($dbRow['M']);
$tableRow[$m][$y] = $dbRow['SUM'];
}
}
$allYears = array_keys($tableRows[0]); // get all years we have
最后你可以在你的表中显示它:
<table>
<thead>
<tr>
<th>Month</th>
<?php foreach($allYears as $year): ?>
<th><?=$year?></th>
<?php endforeach; ?>
</tr>
<thead>
<tfoot>
<? foreach($tableRows as $month => $years): ?>
<tr>
<td><?=$month?></td>
<?php foreach($allYears as $y): ?>
<td><?=$years[$y]?></td>
<?php endforeach;
if(!$month): ?>
</tr>
</tfoot>
<tbody>
<?php else: ?>
</tr>
<?php endif;
endforeach; ?>
</tbody>
</table>
需要更正的是在第一列显示月份名称和Total
而不是数字