teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップMySQLに関する質問日にちごとのデータを集計したい

編集履歴

回答編集履歴

6

修正

2016/10/21 00:51

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -3,7 +3,7 @@
3
3
  ```php
4
4
  //日毎にレビュー数を集計
5
5
  $count_rev_date = [];
6
- $record_rev_sql="SELECT date_format(rev_date,'%Y-%m-%d') rev_date, count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by 1";
6
+ $record_rev_sql="SELECT date_format(rev_date,'%Y-%m-%d') as rev_date, count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by 1";
7
7
  $record_rev_stt=$dbh->prepare($record_rev_sql);
8
8
  $record_rev_stt->execute();
9
9
 

5

修正

2016/10/21 00:51

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -3,7 +3,7 @@
3
3
  ```php
4
4
  //日毎にレビュー数を集計
5
5
  $count_rev_date = [];
6
- $record_rev_sql="SELECT date_format(rev_date,'%Y-%m-%d'), count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by 1";
6
+ $record_rev_sql="SELECT date_format(rev_date,'%Y-%m-%d') rev_date, count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by 1";
7
7
  $record_rev_stt=$dbh->prepare($record_rev_sql);
8
8
  $record_rev_stt->execute();
9
9
 

4

変更

2016/10/21 00:49

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -3,7 +3,7 @@
3
3
  ```php
4
4
  //日毎にレビュー数を集計
5
5
  $count_rev_date = [];
6
- $record_rev_sql="SELECT rev_date::date, count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by 1";
6
+ $record_rev_sql="SELECT date_format(rev_date,'%Y-%m-%d'), count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by 1";
7
7
  $record_rev_stt=$dbh->prepare($record_rev_sql);
8
8
  $record_rev_stt->execute();
9
9
 

3

変更

2016/10/21 00:45

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
File without changes

2

変更

2016/10/21 00:26

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -3,7 +3,7 @@
3
3
  ```php
4
4
  //日毎にレビュー数を集計
5
5
  $count_rev_date = [];
6
- $record_rev_sql="SELECT rev_date count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by rev_date";
6
+ $record_rev_sql="SELECT rev_date::date, count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by 1";
7
7
  $record_rev_stt=$dbh->prepare($record_rev_sql);
8
8
  $record_rev_stt->execute();
9
9
 

1

修正

2016/10/21 00:26

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -1,14 +1,15 @@
1
- カレンダーと結びつける一つの案としてです。
1
+ カレンダーと結びつける一つの案としてです。テーブル内容は想像して作成しています。
2
2
 
3
3
  ```php
4
+ //日毎にレビュー数を集計
4
5
  $count_rev_date = [];
5
- $record_rev_sql="SELECT * FROM review WHERE rev_date LIKE '$date_y_m%'";
6
+ $record_rev_sql="SELECT rev_date count(*) as cnt FROM review WHERE rev_date LIKE '$date_y_m%' group by rev_date";
6
7
  $record_rev_stt=$dbh->prepare($record_rev_sql);
7
8
  $record_rev_stt->execute();
8
9
 
9
10
  while($record_rev_result=$record_rev_stt->fetch()){
10
11
  //ハッシュ配列に保存
11
- $count_rev_date[$record_rev_result["rev_date"]] = 1; //reviewテーブルにカウントが有れば1の代わりにセット
12
+ $count_rev_date[$record_rev_result["rev_date"]] = $record_rev_result["cnt"]
12
13
  }
13
14
  //rev_dateに日付が入っています。
14
15
  ```
@@ -19,9 +20,9 @@
19
20
  // $i 1~31のイメージ
20
21
  if (array_key_exists($data[$i], $count_rev_date))
21
22
  {
22
- //レビューが有った日
23
+ 　　　//レビューが有った日
23
- $cnt=$count_rev_date[$data[$i]];
24
+ 　　　$cnt=$count_rev_date[$data[$i]];
24
25
  }else{
25
- //レビューが無かった日
26
+ 　　　//レビューが無かった日
26
27
  }
27
28
  ```