--------------媒体--------------
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ベストアンサー
- 回答日時:
>$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
- 回答日時:
ANo.1です
ANo.1で提示したSQL文だと、raikyakusuuデータが(当該年月以外でいいので)存在しないといけませんね(デバッグ中にデータを弄りすぎてしまった)。
この回答への補足
有難う御座います。
ご教示いただいきました、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'];
申し訳ありません。
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 |
のように出したいのですが、それもうまくいかないため、こちらも併せてご教示いただけないでしょうか。
大変申し訳ありませんが、宜しくお願いいたします。
No.1
- 回答日時:
>結果で取れていない媒体、日付も表示させ、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日から月末日までを取得するのは難しくないと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL my_itemsテーブルのIDにAUTO_INCREMENT を追加ができるかで 1 2023/01/03 09:09
- MySQL `picture` varchar(255) のコマンドで間違いないでしょうか? 1 2022/11/21 04:08
- MySQL エラー 1068 (42000): 複数の主キーが定義されていますエラー 2 2022/11/17 04:36
- MySQL SHOW CREATE TABLE posts;これって何ですか? 3 2022/08/28 22:57
- MySQL テーブル作成です。どこかのスペルが間違っているか記号など スペースかな? 1 2022/10/01 05:08
- PHP php エラー 2 2022/10/23 16:43
- MySQL あと、MySQLの文字コードはutf8 気になりますね 1 2022/12/01 07:22
- MySQL MYSQL エラー 2 2022/10/18 11:37
- PHP php テーブルが作成できない 1 2022/11/17 23:41
- MySQL php テーブルを作れない 2 2022/11/17 18:22
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
PHPとMysqlを使用した集計表の...
-
日替わりメッセージを表示させ...
-
DB登録に関する日付の認識
-
SQLローダーCSV取込で、囲み文...
-
単一グループのグループ関数で...
-
AccessのSQL文で1件のみヒット...
-
select文の実行結果に空白行を...
-
2つの列が同じ値の行を取得するSQL
-
テーブルの最後(最新)のレコー...
-
count関数の値をwhere句で使用...
-
where句中のtrim関数について
-
Oracleで「文字が無効です」の...
-
SQLです下記の問合せを行うクエ...
-
並べ替えについて
-
SELECT FOR UPDATE で該当レコ...
-
<SQL>重複しているデータの場合...
-
レコードの登録順がおかしい
-
SQLです。下記の問合せを行うク...
-
割合(パーセント)を求めるには?
-
複数のテーブルから値を合計出...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
PHP+MySQLで月ごとの数量を表...
-
【PL/SQL】DATE型の時刻の表示...
-
PHPとMysqlを使用した集計表の...
-
データ更新用のphpの記述でデー...
-
PHPでMySQL内のデータの合計値...
-
postgresのカレンダーについて
-
EXCELマクロのSQL文での引用符...
-
日替わりメッセージを表示させ...
-
bash内のaws cli cloudwatch Lo...
-
SQLローダーCSV取込で、囲み文...
-
単一グループのグループ関数で...
-
select文の実行結果に空白行を...
-
テーブルの最後(最新)のレコー...
-
count関数の値をwhere句で使用...
-
【PL/SQL】FROM区に変数を使う方法
-
SELECT FOR UPDATE で該当レコ...
-
2つの列が同じ値の行を取得するSQL
-
レコードの登録順がおかしい
-
<SQL>重複しているデータの場合...
-
AccessのSQL文で1件のみヒット...
おすすめ情報