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と表示させたいです。
お分かりの方がいらっしゃいましたらご教示お願いいたします。
端折らせていただいている部分も御座いますが、宜しくお願いいたします。
