PHPとMysqlを使用した集計表の作り方

質問

--------------媒体--------------
CREATE TABLE IF NOT EXISTS `media` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `media` (`id`, `name`) VALUES
(1, 'yahoo'),
(2, '楽天'),
(3, '電話'),
(4, 'メール'),
(5, 'その他');

--------------店舗--------------
CREATE TABLE IF NOT EXISTS `shop` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `shop` (`id`, `name`) VALUES
(1, '新宿'),
(2, '池袋'),
(3, '渋谷'),
(4, '博多'),
(5, '梅田');

--------------来客数--------------
CREATE TABLE IF NOT EXISTS `raikyakusuu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`shop_id` int(2) DEFAULT NULL,
`media_id` int(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

INSERT INTO `raikyakusuu` (`id`, `date`, `shop_id`, `media_id`) VALUES
(1, '2013-11-01', 1, 1),
(2, '2013-11-01', 1, 1),
(3, '2013-11-01', 1, 2),
(4, '2013-11-01', 4, 4),
(5, '2013-11-01', 1, 2),
(6, '2013-11-02', 3, 1),
(8, '2013-11-02', 3, 4),
(9, '2013-11-02', 2, 2),
(10, '2013-11-02', 2, 4),
(11, '2013-11-03', 1, 4),
(12, '2013-11-03', 3, 1),
(13, '2013-11-05', 4, 3),
(14, '2013-11-05', 1, 3),
(15, '2013-11-06', 2, 2),
(16, '2013-11-06', 3, 3),
(17, '2013-11-06', 1, 5);

select
DATE_FORMAT(r.`date`,'%d日') as DayTime,
count(*) as cnt,
s.`name` as s_name,
m.`name` as m_name
from
raikyakusuu r
LEFT JOIN shop s ON s.`id` = r.`shop_id`
LEFT JOIN media m ON m.`id` = r.`media_id`
WHERE
DATE_FORMAT(r.`date`, '%Y-%m') = '2013-11'
GROUP BY DayTime, m.`name`

上記のセレクト文で取れた結果をPHPにて

新宿
媒体 |01日|02日|03日|04日|05日|06日|07日|08日|・・・
yahoo | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
楽天 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
電話 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
メール | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
その他 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |

池袋
媒体 |01日|02日|03日|04日|05日|06日|07日|08日|・・・
yahoo | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
楽天 | 0 | 1 | 0 | 0 | 0 | 1 | 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 |

渋谷
媒体 |01日|02日|03日|04日|05日|06日|07日|08日|・・・
yahoo | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
楽天 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
電話 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
メール | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
その他 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

といったようにtableにて表示を考えておりますが、
うまくだせずに困っております。

条件といたしましては、
結果で取れていない媒体、日付も表示させ、cntには0と表示させたいです。

お分かりの方がいらっしゃいましたらご教示お願いいたします。

端折らせていただいている部分も御座いますが、宜しくお願いいたします。

回答No.3

agunuz · ベストアンサー

>$d1 = $row['D1'];
>$d2 = $row['D2'];

のように扱うと面倒です。「配列のまま」扱うべきです。

（例）
<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8", 'root', '');

// SQL文の準備（年月によって日付・列数が変わる）
$sql = "select media.name as m_name,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-01' then 1 else 0 end) as D01,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-02' then 1 else 0 end) as D02,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-03' then 1 else 0 end) as D03,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-04' then 1 else 0 end) as D04,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-05' then 1 else 0 end) as D05,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-06' then 1 else 0 end) as D06,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-07' then 1 else 0 end) as D07,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-08' then 1 else 0 end) as D08,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-09' then 1 else 0 end) as D09,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-10' then 1 else 0 end) as D10,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-11' then 1 else 0 end) as D11,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-12' then 1 else 0 end) as D12,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-13' then 1 else 0 end) as D13,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-14' then 1 else 0 end) as D14,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-15' then 1 else 0 end) as D15,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-16' then 1 else 0 end) as D16,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-17' then 1 else 0 end) as D17,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-18' then 1 else 0 end) as D18,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-19' then 1 else 0 end) as D19,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-20' then 1 else 0 end) as D20,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-21' then 1 else 0 end) as D21,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-22' then 1 else 0 end) as D22,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-23' then 1 else 0 end) as D23,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-24' then 1 else 0 end) as D24,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-25' then 1 else 0 end) as D25,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-26' then 1 else 0 end) as D26,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-27' then 1 else 0 end) as D27,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-28' then 1 else 0 end) as D28,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-29' then 1 else 0 end) as D29,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-30' then 1 else 0 end) as D30
from media
left join raikyakusuu on media.id = raikyakusuu.media_id
where raikyakusuu.shop_id = ?
group by media.name";

$st1 = $pdo->query('select * from shop order by id'); // 店舗名を取得するSQL実行
$st2 = $pdo->prepare($sql); // データ取得の準備

while ($row = $st1->fetch(PDO::FETCH_ASSOC)) {
print $row['name'] . '<br>'; // 店舗名
$st2->execute(array($row['id'])); // 店舗IDでデータ取得
$sw = 0;
while ($item = $st2->fetch(PDO::FETCH_ASSOC)) {
if ($sw++ === 0) { // ヘッダ出力
print implode(',', array_keys($item)) . '<br>';
}
// 明細出力
print implode(',', $item) . '<br>';

}
print '<hr>';
}

お礼

お忙しい中有難う御座います。

上記の内容にてできました。
本当に有難う御座います。

回答No.2

agunuz

ANo.1です

ANo.1で提示したSQL文だと、raikyakusuuデータが（当該年月以外でいいので）存在しないといけませんね（デバッグ中にデータを弄りすぎてしまった）。

お礼

申し訳ありません。
PHPでループをしながら、質問に記載のある
新宿
媒体 |01日|02日|03日|04日|05日|06日|07日|08日|・・・
yahoo | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
楽天 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
電話 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
メール | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
その他 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |

池袋
媒体 |01日|02日|03日|04日|05日|06日|07日|08日|・・・
yahoo | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
楽天 | 0 | 1 | 0 | 0 | 0 | 1 | 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 |

渋谷
媒体 |01日|02日|03日|04日|05日|06日|07日|08日|・・・
yahoo | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
楽天 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
電話 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
メール | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
その他 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

のように出したいのですが、それもうまくいかないため、こちらも併せてご教示いただけないでしょうか。
大変申し訳ありませんが、宜しくお願いいたします。

補足

有難う御座います。
ご教示いただいきました、SQL文にて意図している内容が取れました。

ただこの場合ですと、
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-01' then 1 else 0 end) as D01,
の部分は月ごとに変更は可能かと思うのですが、
結果をwhileで回した場合、

while($row = $query) {
$d1 = $row['D1'];
$d2 = $row['D2'];
・
・
・
}
月ごとに末日をとることが難しくないでしょうか。
11月は30日まで$row['D30'];
12月は31日まで$row['D31'];

回答No.1

agunuz

>結果で取れていない媒体、日付も表示させ、cntには0と表示させたいです。

この条件があるので、

select shop.name as s_name, media.name as m_name,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-01' then 1 else 0 end) as D01,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-02' then 1 else 0 end) as D02,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-03' then 1 else 0 end) as D03,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-04' then 1 else 0 end) as D04,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-05' then 1 else 0 end) as D05,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-06' then 1 else 0 end) as D06,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-07' then 1 else 0 end) as D07,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-08' then 1 else 0 end) as D08,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-09' then 1 else 0 end) as D09,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-10' then 1 else 0 end) as D10,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-11' then 1 else 0 end) as D11,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-12' then 1 else 0 end) as D12,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-13' then 1 else 0 end) as D13,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-14' then 1 else 0 end) as D14,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-15' then 1 else 0 end) as D15,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-16' then 1 else 0 end) as D16,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-17' then 1 else 0 end) as D17,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-18' then 1 else 0 end) as D18,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-19' then 1 else 0 end) as D19,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-20' then 1 else 0 end) as D20,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-21' then 1 else 0 end) as D21,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-22' then 1 else 0 end) as D22,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-23' then 1 else 0 end) as D23,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-24' then 1 else 0 end) as D24,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-25' then 1 else 0 end) as D25,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-26' then 1 else 0 end) as D26,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-27' then 1 else 0 end) as D27,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-28' then 1 else 0 end) as D28,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-29' then 1 else 0 end) as D29,
sum(case DATE_FORMAT(raikyakusuu.date,'%Y-%m-%d') when '2013-11-30' then 1 else 0 end) as D30
from raikyakusuu
right join shop on raikyakusuu.shop_id = shop.id
right join media on raikyakusuu.media_id = media.id
group by shop.name, media.name
order by shop.id, media.id

のように書くしかありません。phpで動的に生成するのであれば1日から月末日までを取得するのは難しくないと思います。