日にちごとのデータを集計したい

Question

サイトの登録数、レビュー投稿数、ポイント交換申請数の日にちごとの集計を取りたいのですが、うまくいかないのでご教授お願いします。

プルダウンメニューで、見たい月を選び、その月の稼働情報を表示させたいです。
投稿レビュー数はreview、登録情報はlogin、ポイント申請数はgt_grantというデータベースからそれぞれ呼び出したいと思っています。
日付はそれぞれdatetimeで登録してあります。

まずはレビュー数を呼び出そうとしているのですが、10月のレビュー数全部の数は呼び出せても、それをカレンダーの日にちと結びつけて日にちごとに集計する部分で、どう結びつければいいのかわかりません。

=================カレンダー==================

$yea=$_REQUEST["year"];//年
$mon=$_REQUEST["month"];//月

$year=sprintf("%04d-",$yea);
$month=sprintf("%02d",$mon);
$date_y_m=$year.$month;//曖昧検索

$data[]="";
$message[]="";

if ($year!="" || $month!=""){

$today_year = (int)$year;//文字列を数字に変換
$today_month = (int)$month;

if(checkdate($today_month, 1, $today_year)){//日付が正しいかチェック

//カレンダー開始日（指定された年月の最初の日のYYYY-MM-DD形式)
$startYmd = sprintf('%04d-%02d-%02d', $today_year, $today_month, 1);

//カレンダー開始タイムスタンプ
$start = strtotime($startYmd);

//これに24時間ずつ加算し、1ヶ月分（月が同じ範囲）の日付の一覧を作成
for ($t = $start; date('m', $t) == date('m', $start); $t += 60 * 60 * 24) {

$data[] = $t;//1~31 チェックデートでfalseが出たら処理を打ち止め

}

}else{
$message="存在しない日付です。やり直してください";
}//if(checkdate($mon, 1, $y))

}else{

$message="年月日を選択して、表示ボタンを押してください";

}//if ($y!="" || $mon!="")

=============レビューが書かれた日にち呼び出し==========

$count_rev_date = [];
$record_rev_sql="SELECT * FROM review WHERE rev_date LIKE '$date_y_m%'";
$record_rev_stt=$dbh->prepare($record_rev_sql);
$record_rev_stt->execute();

while($record_rev_result=$record_rev_stt->fetch()){

$count_rev_date[] = $record_rev_result["rev_date"];
}
//rev_dateに日付が入っています。

================集計カレンダー表示===================

<div class="admin_right">
<?php
if(isset($date)){
?>
<div class="running_info">

<p><?php print($today_year); ?>年<?php print($today_month); ?>の稼働情報</p>
<ul class="running_ul_index">
<li>日付</li>
<li>仮登録</li>
<li>登録</li>
<li>投稿レビュー数</li>
<li>カード申請数</li>
</ul>
<?php

foreach($data as $d_value){

$review_date=date("Y年m月d日" , $d_value);


?>

<ul class="running_ul">
<li>
<?php print ($review_date); ?>
</li>
<li>

</li>
<li>

</li>
<li>

</li>
<li>
<!--ここにレビュー数-->
</li>
</ul>

<?php
}//foreach($data as $d_value)
?>

</div><!-- running_info -->

<?php
}//if(isset($date))
?>

</div><!-- admin_right -->

まだまだ途中の段階で中途半端な記述でわかりづらく申し訳ありません。
カレンダーの日にちと、その月のレビューを書いた日付をどう結びつければいいのかわからなくなってしまいました...。
よろしくお願いいたします。

Answer 1

カレンダーと結びつける一つの案としてです。テーブル内容は想像して作成しています。

   //日毎にレビュー数を集計
    $count_rev_date = [];
    $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";
    $record_rev_stt=$dbh->prepare($record_rev_sql);
    $record_rev_stt->execute();

    while($record_rev_result=$record_rev_stt->fetch()){
    //ハッシュ配列に保存
        $count_rev_date[$record_rev_result["rev_date"]] = $record_rev_result["cnt"]
    }
  //rev_dateに日付が入っています。

カレンダーの作成する際に日付を使って一緒に処理する

    // $i 1~31のイメージ
    if (array_key_exists($data[$i], $count_rev_date))
    {
    　　　//レビューが有った日
    　　　$cnt=$count_rev_date[$data[$i]];
    }else{
    　　　//レビューが無かった日
    }

Comments

[KiyoshiMotoki]

セレクト句の rev_date と count(*) の間のカンマが抜けていますよ。

また、
> 日付はそれぞれdatetimeで登録してあります

とのことですので、 group by rev_date だと時分秒まで考慮した集計を行なってしまう可能性がありますよ。

[A.Ichi]

ご指摘の通り間違っております。修正いたしました。有難うございます。

[A.Ichi]

追伸、ハッシュキーと同じ値にする様に、to_char(rev_date,’YYYYMMDD’)で編集してください。

[KiyoshiMotoki]

度々の指摘で恐縮ですが、、

rev_date::date は、MySQL ではサポートされていない構文です。
MySQL の場合は DATE(rev_date) とするのが一般的かと。

[A.Ichi]

$startYmd = sprintf('%04d-%02d-%02d'...　されているので::dateで大丈夫みたいですね。

[A.Ichi]

重ね重ね有難うございます。to_charでも無いのでdate_formatにて修正いたしました。

[KiyoshiMotoki]

b

[puuko]

大変遅くなってしまい申し訳ありませんでした。
どうもありがとうございました！
カレンダーの作成する際に日付を使って一緒に処理する部分の記述の場所が間違っていたのか、うまく表示することができませんでしたが、集計ではすべて呼び出すことができ
表示する際には
$rev_result = $count_rev_date[date('Y-m-d', $d_value)];
で思い通り出すことができました。
select部分の記述がわかりやすく、とても参考になりました！
どうもありがとうございました！

Answer 2

以下のコードは投稿レビュー数の集計のみですが、登録情報およびポイント申請数も同様の操作で取得できます。

=============レビューが書かれた日にち呼び出し==========

$date_y_m = '2016-10'; // 実際はプルダウンで選択した年月

$record_rev_sql = "
        SELECT DATE_FORMAT(rev_date, '%d') rev_day, COUNT(*) count
        FROM review
        WHERE rev_date >= :target
          AND rev_date < DATE_ADD(:target, INTERVAL 1 MONTH)
        GROUP BY rev_day
        ORDER BY rev_day ASC
";
$record_rev_stt = $dbh->prepare($record_rev_sql);
$record_rev_stt->bindValue(':target', $date_y_m . '-01', PDO::PARAM_STR);
$record_rev_stt->execute();

$count_rev_date = [];
while(($record_rev_result = $record_rev_stt->fetch(PDO::FETCH_ASSOC)) !== false){
    $count_rev_date[(int) $record_rev_result['rev_day']] = (int) $record_rev_result['count'];
}

// var_dump($count_rev_date);

変数 $count_rev_date がどのような構造の配列になっているかは、var_dump するなどしてご自分で確認してください。

ただし、この方法では投稿レビューが１つもない日の投稿数は取得できません。
どういうことかと言うと、例えば 10月19日、20日、21日の投稿レビュー数がそれぞれ 10、0、20 だったとすると、
変数 $count_rev_date の要素は

19 => 10
20 => 0
21 => 20

とはならず、

19 => 10
21 => 20

となってしまいます。

この点については、「集計カレンダー表示」の方を工夫して対応するのがよいでしょう。

Comments

[puuko]

大変遅くなってしまい申し訳ありませんでした。
丁寧に教えて頂いてありがとうございました！

foreach($data as $d => $d_value){

$review_date=date("Y年m月d日" , $d_value);

$interim_result = $count_interim_date[date('j', $d_value)];

$regist_result = $count_regist_date[date('j', $d_value)];

$grant_result = $count_grant_date[date('j', $d_value)];

$result = $count_rev_date[date('j', $d_value)];

?>

<ul class="running_ul">
<li>
<?php print ($review_date); ?>
</li>
<li>
<?php print ((int)$interim_result); ?>
</li>
<li>
<?php print ((int)$regist_result); ?>
</li>

<li>
<?php print ((int)$result); ?>
</li>

<li>
<?php print ((int)$grant_result); ?>
</li>
</ul>

<?php
}//foreach($data as $d_value)

?>

この表示の部分でうまく表示されず、悩みましたが、どのような構造になっているか確認してみてくださいとあったので、よく確認してみたらdateの”j”の部分を”d”にしていたことに
気付きました。
全て表示することができました。
どうもありがとうございます！

[KiyoshiMotoki]

Good job

Answer 3

下記のSQLを作成すると日付毎の集計ができます。
・BETWEENで月の始めと終わりを指定する。
・日付けをDATEFORMAT関数でyyyyMMdd形式に変換する。
・日付毎でGROUP BYする。
・SUM関数でレビュー数を集計する。

上記の結果をPHPでカレンダーに表示すればできますね。

Comments

[puuko]

大変遅くなってしまい申し訳ありませんでした。
教えていただいてありがとうございました！
いろいろ試しながら勉強していきたいと思います！
ありがとうございます。