多次元配列から作った表の重複行をまとめたい

質問

データベースからある期限（14年３月21日から3月30日）で取得した下記の多次元配列から日付に該当するデータがあればセルに値を入れて表を作ったのですが、

<?php
$cal=array(
array("type" => 19, "name" => "山田", "hour" => 5, "day" => 2014-03-21),
array("type" => 19, "name" => "山田", "hour" => 8, "day" => 2014-03-24),
array("type" => 19, "name" => "山田", "hour" => 8, "day" => 2014-03-26),
array("type" => 19, "name" => "田中", "hour" => 8, "day" => 2014-03-23),
array("type" => 19, "name" => "田中", "hour" => 8, "day" => 2014-03-26),
array("type" => 19, "name" => "田中", "hour" => 3, "day" => 2014-03-28),
array("type" => 20, "name" => "大田", "hour" => 3, "day" => 2014-03-21),
array("type" => 20, "name" => "大田", "hour" => 5, "day" => 2014-03-27),
array("type" => 20, "name" => "田中", "hour" => 3, "day" => 2014-03-24),
array("type" => 20, "name" => "田中", "hour" => 4, "day" => 2014-03-27),
array("type" => 0, "name" => "高橋", "hour" => 1, "day" => 2014-03-27),
);
?>

<?php foreach ( $cal as $val ) : ?>
<tr>
<td><?php echo $val['type']; ?></td>
<td><?php echo $val['name']; ?></td>
<?php for ( $i = 21; $i <= 30; $i ++ ) : ?>
<td>
<?php
if ( $day == $i ) {
echo $val['hour'];
}
?>
</td>
<?php endfor; ?>
</tr>
<?php endforeach; ?>

同じIDと名前の行が複数あり、同じIDと名前の行をひとつにしたいのです（画像参照）がわからず質問させていただきました。よろしくお願い致します。

回答No.1

yambejp · ベストアンサー

ちょっと微妙なかんじですが、sql側で集計したものを表示した方がはやくないですか？

//元データ
create table tbl(type int,name varchar(20),hour int,day date);
insert into tbl
values(19,"山田",5,"2014-03-21")
,(19,"山田",8,"2014-03-24")
,(19,"山田",8,"2014-03-26")
,(19,"田中",8,"2014-03-23")
,(19,"田中",8,"2014-03-26")
,(19,"田中",3,"2014-03-28")
,(20,"大田",3,"2014-03-21")
,(20,"大田",5,"2014-03-27")
,(20,"田中",3,"2014-03-24")
,(20,"田中",4,"2014-03-27")
,(0,"高橋",1,"2014-03-27");

//集計
select type,name
,sum(hour*(day='2014-03-21')) as '0321'
,sum(hour*(day='2014-03-22')) as '0322'
,sum(hour*(day='2014-03-23')) as '0323'
,sum(hour*(day='2014-03-24')) as '0324'
,sum(hour*(day='2014-03-25')) as '0325'
,sum(hour*(day='2014-03-26')) as '0326'
,sum(hour*(day='2014-03-27')) as '0327'
,sum(hour*(day='2014-03-28')) as '0328'
from tbl
group by type,name;

お礼

selet文でここまで集計できることがわかりませんでした。
教えていただきありがとうございました！