//make it 2 dim in case you change your order for ( $i = 0; $i < $totalRows; ++$i ) { $results[$i] = mysql_fetch_array($result); //print_r($results[$i]);
//make it 2 dim in case you change your order for ( $i = 0; $i < $totalRows; ++$i ) { $results[$i] = mysql_fetch_array($result); //print_r($results[$i]);
$results = array(); $is_re = array(); $totalRows = mysql_num_rows($result);
$totalFields = mysql_num_fields($result);
// echo $totalRows."<br />";
// echo $totalFields;
//make it 2 dim in case you change your order
for ( $i = 0; $i < $totalRows; ++$i )
{
$results[$i] = mysql_fetch_array($result);
//print_r($results[$i]);
for ( $k = 1; $k < 15; ++$k)
$is_re[$i][$k] = 1;
}
for ( $i = 0; $i < $totalRows; ++$i )
{
for ( $k = 1; $k < 15; ++$k)
{
if($is_re[$i][$k] == 0)
continue;
for ( $j = $i + 1; $j <= $totalRows; ++$j )
{
$is_re_tmp = TRUE; // 比当前列靠前的列都要相同,才认为此列是相同的
for( $m = 1; $m <= $k; ++$m)
{
if ((strtolower(trim($results[$i][$m])) != strtolower(trim($results[$j][$m]))))
$is_re_tmp = FALSE;
}
if ($is_re_tmp)
{
++$is_re[$i][$k];
$is_re[$j][$k] = 0;
}
else
{
break;
}
}
}
}
for ( $i = 0; $i < $totalRows; ++$i )
{
$row = $results[$i];
//print_r($row);
echo '<tr class="cells" onmouseover="this.className=\'cells2\'" onmouseout="this.className=\'cells\'">';
echo '<td><center><font size=2>'.$row['0'].'</font></center></td>'; for ( $k = 1; $k < 15; ++$k)
{
if ($is_re[$i][$k] > 0)
{
if($k < 1)
echo '<td rowspan="'.$is_re[$i][$k].'"><center><font size=2>'.$row[$k].'</font></center></td>';
if($k == 1)
{
if($row[$k] == '0')
echo '<td rowspan="'.$is_re[$i][$k].'"><center><font size=2> N/A </font></center></td>';
else
echo '<td rowspan="'.$is_re[$i][$k].'"><center><font size=2>'.$row[$k].'</font></center></td>';
}
}
}
}
array(
'1' => 1.2,
'second' => 1.2,
),
array(
'1' => 2.2,
'second' => 4.2,
),
);$totals = array();
foreach($array as $value) {
foreach($value as $k => $v) {
$totals[$k] += $v;
}
}
用array_map + array_sum 也可以,不过效率差不多就算了
Array
(
[0] => 8
[num] => 8
[1] => 咨询服务费
[project] => 咨询服务费
[2] =>
[project_category] =>
[3] => 66020210
[code] => 66020210
[4] => 35.5
[item_real] => 35.5
[5] => 34.7
[item_budget] => 34.7
[6] => 38.0
[item_current_predict] => 38.0
[7] => 32.0
[item1] => 32.0
[8] => 32.0
[item2] => 32.0
[9] =>
[item3] =>
[10] =>
[item4] =>
[11] =>
[item5] =>
[12] =>
[item6] =>
[13] => 32.0
[tuisuan] => 32.0
[14] => 0
[tuisuan_status] => 0
[15] => 0
[current_status] => 0
[16] =>
[comment] =>
)
Array
(
[0] => 9
[num] => 9
[1] => 国内费用小计
[project] => 国内费用小计
[2] =>
[project_category] =>
[3] =>
[code] =>
[4] => 38.6
[item_real] => 38.6
[5] => 39.0
[item_budget] => 39.0
[6] => 41.5
[item_current_predict] => 41.5
[7] => 36.3
[item1] => 36.3
[8] => 34.1
[item2] => 34.1
[9] =>
[item3] =>
[10] =>
[item4] =>
[11] =>
[item5] =>
[12] =>
[item6] =>
[13] => 35.2
[tuisuan] => 35.2
[14] => 0
[tuisuan_status] => 0
[15] => 0
[current_status] => 0
[16] =>
[comment] =>
)
...这样的形式,我怎么能找到指定列然后进行累加?
你可以手工指定
// 指定参与的key
$total_keys = array(4, 5, 6, 7);$totals = array();
foreach($array as $value) {
foreach($value as $k => $v) {
if(in_array($k, $total_keys) {
$totals[$k] += $v;
}
}
}
$total_keys = array(4, 5, 6, 7);$totals = array();
foreach($array as $value) {
foreach($value as $k => $v) {
if(in_array($k, $total_keys) {
$totals[$k] += $v;
}
}
}
后面$a,$b,$c的个数是写的时候就已知的未知的话还是要写循环,还不如楼上的
把$array[行号a][字段b] 转换成 $array[字段b][行号a] ,这个不难做的然后array_sum($array[你要统计的b项])其实如果不考虑按行输出的话,我习惯都是把字段名作为第一维的key,行号作为第二维的key
查询出的数组插入显示数组的最后,这样循环输出后就有合计了
for ($i=0;$i<a;$i++){
for ($j=0;$j<b;$j++){
$arr[$j][$i]=$db[$i][$j];
}}//如果不是数字key可以用foreach做循环$newArray = array();
foreach ($arr as $k=>$v){
//这里可以加入判断哪个字段不需要或者不适合总计的用continue跳过,或者让这个$newArray[$k] = '';
$newArray[$k] = array_sum($v);
}
//$newArray就是最后总计的一行
这是常见的 $re = [行号][字段] 的方式但是结合mysql_field_name 函数
$results[mysql_field_name][$i] 就是 $re= [字段][行号] 的方式啦在sql查询的时候就应该想好哪种方式组数的数组更适合自己后面的程序需要,避免把简单问题复杂化
(当然你这个程序需要按行输出,还是需要前者的)
Array
(
[0] => 8
[num] => 8
[1] => 咨询服务费
[project] => 咨询服务费
[2] =>
[project_category] =>
[3] => 66020210
[code] => 66020210
[4] => 35.5
[item_real] => 35.5
[5] => 34.7
[item_budget] => 34.7
[6] => 38.0
[item_current_predict] => 38.0
[7] => 32.0
[item1] => 32.0
[8] => 32.0
[item2] => 32.0
[9] =>
[item3] =>
[10] =>
[item4] =>
[11] =>
[item5] =>
[12] =>
[item6] =>
[13] => 32.0
[tuisuan] => 32.0
[14] => 0
[tuisuan_status] => 0
[15] => 0
[current_status] => 0
[16] =>
[comment] =>
)格式输出代码如下:<?php
$results = array(); $is_re = array(); $totalRows = mysql_num_rows($result);
$totalFields = mysql_num_fields($result);
// echo $totalRows."<br />";
// echo $totalFields;
//make it 2 dim in case you change your order
for ( $i = 0; $i < $totalRows; ++$i )
{
$results[$i] = mysql_fetch_array($result);
//print_r($results[$i]);
for ( $k = 1; $k < 15; ++$k)
$is_re[$i][$k] = 1;
}
for ( $i = 0; $i < $totalRows; ++$i )
{
for ( $k = 1; $k < 15; ++$k)
{
if($is_re[$i][$k] == 0)
continue;
for ( $j = $i + 1; $j <= $totalRows; ++$j )
{
$is_re_tmp = TRUE; // 比当前列靠前的列都要相同,才认为此列是相同的
for( $m = 1; $m <= $k; ++$m)
{
if ((strtolower(trim($results[$i][$m])) != strtolower(trim($results[$j][$m]))))
$is_re_tmp = FALSE;
}
if ($is_re_tmp)
{
++$is_re[$i][$k];
$is_re[$j][$k] = 0;
}
else
{
break;
}
}
}
}
for ( $i = 0; $i < $totalRows; ++$i )
{
$row = $results[$i];
//print_r($row);
echo '<tr class="cells" onmouseover="this.className=\'cells2\'" onmouseout="this.className=\'cells\'">';
echo '<td><center><font size=2>'.$row['0'].'</font></center></td>'; for ( $k = 1; $k < 15; ++$k)
{
if ($is_re[$i][$k] > 0)
{
if($k < 1)
echo '<td rowspan="'.$is_re[$i][$k].'"><center><font size=2>'.$row[$k].'</font></center></td>';
if($k == 1)
{
if($row[$k] == '0')
echo '<td rowspan="'.$is_re[$i][$k].'"><center><font size=2> N/A </font></center></td>';
else
echo '<td rowspan="'.$is_re[$i][$k].'"><center><font size=2>'.$row[$k].'</font></center></td>';
}
}
}
}
`id` int(10) NOT NULL auto_increment,
`num` int(4) NOT NULL,
`project` varchar(20) NOT NULL,
`project_category` varchar(20) NOT NULL,
`code` varchar(50) default NULL,
`item_real` decimal(18,1) default NULL,
`item_budget` decimal(18,1) default NULL,
`item_current_predict` decimal(18,1) default NULL,
`item_current_real` decimal(18,1) default NULL,
`item1` decimal(18,1) default NULL,
`item2` decimal(18,1) default NULL,
`item3` decimal(18,1) default NULL,
`item4` decimal(18,1) default NULL,
`item5` decimal(18,1) default NULL,
`item6` decimal(18,1) default NULL,
`department` varchar(20) NOT NULL,
`comment` varchar(50) default NULL,
`tuisuan` decimal(18,1) default NULL,
`tuisuan_status` tinyint(4) NOT NULL,
`current_status` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8INSERT INTO `budgetcontrol` VALUES (196, 1, '差旅费(国内)', '', '6602060201', 12.5, 29.8, 42.2, 21.9, 4.5, 21.9, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 13.2, 0, 0);
INSERT INTO `budgetcontrol` VALUES (197, 2, '业务招待费', '', '6602060301', NULL, 2.0, 2.0, NULL, 0.0, NULL, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 0.0, 0, 0);
INSERT INTO `budgetcontrol` VALUES (198, 3, '办公费', '', '66020604', NULL, 1.0, 1.0, NULL, 0.0, NULL, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 0.0, 0, 0);
INSERT INTO `budgetcontrol` VALUES (199, 4, '修理服务费', '', NULL, NULL, 0.0, NULL, NULL, 0.0, NULL, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 0.0, 0, 0);
INSERT INTO `budgetcontrol` VALUES (200, 5, '低耗品', '', NULL, NULL, 0.3, NULL, NULL, 0.0, NULL, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 0.0, 0, 0);
INSERT INTO `budgetcontrol` VALUES (201, 6, '租赁费', '办公用房', NULL, NULL, 7.1, NULL, NULL, 0.0, NULL, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 0.0, 0, 0);
INSERT INTO `budgetcontrol` VALUES (202, 7, '租赁费', '汽车', NULL, NULL, 8.0, NULL, NULL, 0.0, NULL, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 0.0, 0, 0);
INSERT INTO `budgetcontrol` VALUES (203, 8, '租赁费', '员工房租', NULL, NULL, 5.0, 5.0, 16.8, 0.0, 16.8, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 8.4, 1, 1);
INSERT INTO `budgetcontrol` VALUES (204, 9, '租赁费', '小计', NULL, NULL, 20.1, 5.0, NULL, 0.0, NULL, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 0.0, 0, 0);
INSERT INTO `budgetcontrol` VALUES (205, 10, '可控费用合计', '', NULL, 12.5, 53.2, 50.2, 38.7, 4.5, 38.7, NULL, NULL, NULL, NULL, 'dd_wh', NULL, 21.6, 0, 0);我现在结果是手工输入,希望其中decimal的类型能通过累加实现图中结果。
header('Content-Type: text/html;cherset=utf-8');
if (!$link = mysql_connect('localhost', 'root', '')) {
echo 'Could not connect to mysql';
exit;
}
mysql_query('SET NAMES utf8');
if (!mysql_select_db('test', $link)) {
echo 'Could not select database';
exit;
}// 只取前8条有数据的记录
$sql = 'SELECT * FROM budgetcontrol limit 8';
$result = mysql_query($sql, $link);if (!$result) {
echo "DB Error, could not query the database\n";
echo 'MySQL Error: ' . mysql_error();
exit;
}// 初始化统计和小计的数组
$total_p = array();
$total_c = array();
// 记录上一个项目的数据
$last_project = array();// 记录显示的列表
$list = array();// 初始化统计和小计需要统计的字段
while($row = mysql_fetch_field($result)) {
// 当然你可以手工写,无所谓
if($row->type == 'real') {
$total_p[$row->name] = null;
$total_c[$row->name] = null;
} else {
$total_p[$row->name] = '';
$total_c[$row->name] = '';
}
// 为了显示方便
$list[0][$row->name] = $row->name;
}$total_p['project'] = '合计';
$total_c['project_category'] = '小计';$curr_total_c = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
if(empty($last_project)) {
$last_project = $row;
} else {
if($last_project['project'] == $row['project']) {
// 初次记录
if(!$curr_total_c['project']) {
$curr_total_c['project'] = $row['project'];
$curr_total_c = total_run($curr_total_c, $last_project);
}
// 记录小计
$curr_total_c = total_run($curr_total_c, $row);
} else {
// 添加到数组
if(count(array_diff_assoc($total_c, $curr_total_c)) > 0) {
$list[] = $curr_total_c;
$curr_total_c = $total_c;
}
// 重置上一记录
$last_project = $row;
}
}
// 总计
$total_p = total_run($total_p, $row);
// 记录内容
$list[] = $row;
}// 最后判断一次是否有小计
if(count(array_diff_assoc($total_c, $curr_total_c)) > 0) {
$list[] = $curr_total_c;
}// 记录总计
$list[] = $total_p;
echo '<table>';
foreach($list as $line => $row) {
echo '<tr>';
foreach($row as $k => $v) {
if($k == 'num') $v = $v ? $v : $line - 1;
echo '<td>', $v, '</td>';
}
echo '</tr>';
}echo '</table>';function total_run($total, $row) {
foreach($row as $k => $v) {
if($total[$k] === null) {
$total[$k] = $v;
} else if(is_numeric($total[$k])) {
$total[$k] += $v;
}
}
return $total;
}mysql_free_result($result);
你好,测试部分可用。但是实际情况可能更复杂。格式不固定,更多的累加项。
是否进行多次select limit处理?
如图:
在你数组循环的时候就处理下不知道你有没有用模板,如果用了的话,在主程序里循环处理好,再在模板里直接调用
如果程序和html在一起的话,在html循环的地方处理下问题应该不是很大吧
// 初始化统计和小计的数组
$total_p = array();
$total_c = array();
$totcal_q = array();
$total_t =array();
// 记录上一个项目的数据
$last_project = array();
$last_localcation = array();// 记录显示的列表
$list = array();// 初始化统计和小计需要统计的字段
while($row = mysql_fetch_field($result)) {
// 当然你可以手工写,无所谓
if($row->type == 'real') {
$total_p[$row->name] = null;
$total_c[$row->name] = null;
$total_q[$row->name] = null;
$total_t[$row->name] = null;
} else {
$total_p[$row->name] = '';
$total_c[$row->name] = '';
$total_q[$row->name] = '';
$total_t[$row->name] = '';
}
// 为了显示方便
$list[0][$row->name] = $row->name;
}$total_p['project'] = '国内费用小计';
$total_q['project'] = '海外费用小计';
$total_c['project_category'] = '小计';
$total_t['localcation'] = '总合计'$curr_total_c = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
if(empty($last_localcation)) {
$last_localcation = $row;
} else {
if($last_localcation['localcation'] == $row['localcation']) {
// 初次记录
if(!$curr_total_c['localcation']) {
$curr_total_c['localcation'] = $row['localcation'];
$curr_total_c = total_run($curr_total_c, $last_project);
}
// 记录小计
$curr_total_c = total_run($curr_total_c, $row);
} else {
// 添加到数组
if(count(array_diff_assoc($total_c, $curr_total_c)) > 0) {
$list[] = $curr_total_c;
$curr_total_c = $total_c;
}
// 重置上一记录
$last_project = $row;
}
}
// 总计
$total_p = total_run($total_p, $row);
// 记录内容
$list[] = $row;
}// 最后判断一次是否有小计
if(count(array_diff_assoc($total_c, $curr_total_c)) > 0) {
$list[] = $curr_total_c;
}// 记录总计
$list[] = $total_p;
echo '<table>';
foreach($list as $line => $row) {
echo '<tr>';
foreach($row as $k => $v) {
if($k == 'num') $v = $v ? $v : $line - 1;
echo '<td>', $v, '</td>';
}
echo '</tr>';
}echo '</table>';function total_run($total, $row) {
foreach($row as $k => $v) {
if($total[$k] === null) {
$total[$k] = $v;
} else if(is_numeric($total[$k])) {
$total[$k] += $v;
}
}
return $total;
}mysql_free_result($result);
项目 项目分类 g1code 月份 费用 项目费用发生地 可控与否