Mysql，PHPで一年分（１２ヶ月）の登録データを日別・カテゴリ別で動的に取得した

仮定

CREATE TABLE categories (
   `name` VARCHAR(20) PRIMARY KEY
);

CREATE TABLE users (
    `user_id` SERIAL PRIMARY KEY,
    `category_name` VARCHAR(20),
    `created` DATETIME,
    FOREIGN KEY (category_name) REFERENCES categories(name)
        ON UPDATE CASCADE
);

categories.nameにはMySqlの特殊文字が入らないとする．
（入りかねないときは，処理にプリペアドステートメントを挿入する）

条件

1.取得月数が12から値が変わっても大丈夫なようにする
2.categoriesも動的に取得

取得データイメージ

4月テーブル

date	count_categoryName1	count_categoryName2	count_categoryName3
4/18	12	25	5
4/17	4	21	11
4/16	28	12	13
...	...	...	...

３月テーブル

date	count_categoryName1	count_categoryName2	count_categoryName3
3/31	21	12	2
3/30	32	11	2
3/29	18	12	13
...	...	...	...

処理

動的にsql文を生成する．
$categoriesにあらかじめ対象のcategoryをselectしておく．

<?php
$categories = $pdo->query("SELECT name FROM categories WHERE name IN (<your select>));
// SQL文の生成
$baseSQL="SELECT DATE_FORMAT(u.created, '%m/%d') AS date";
foreach($categories as $category){
    $baseSQL .= ",SUM(CASE u.category_name WHEN {$category['name']} THEN 1 ELSE 0 END) AS count_category_{$category['name']}";
}

$allMonthlyData = [];
$month_size = 12;
for($between_min = 0; $between_min > -$month_size; $between_min--) {
    $between_max = $between_min + 1;
    $SQL = $baseSQL . " FROM users AS u WHERE u.created BETWEEN DATE_FORMAT( ADDDATE( CURDATE() , INTERVAL {$between_min} MONTH) , '%Y-%m-01' ) AND DATE_FORMAT( ADDDATE( CURDATE() , INTERVAL {$between_max} MONTH) , '%Y-%m-01' ) GROUP BY DATE_FORMAT(u.created, '%Y-%m-%d') ORDER BY u.created DESC;";
}

テスト

テストデータ入力

# categoriesデータ
INSERT INTO categories (name) VALUES ('CATEGORY1'), ('CATEGORY2');

# 適当にusersデータ
INSERT INTO users (category_name, created) VALUES('CATEGORY1',ADDTIME(CONCAT_WS(' ','2019-01-01' + INTERVAL RAND() * 90 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401)))));
INSERT INTO users (category_name, created) VALUES('CATEGORY2',ADDTIME(CONCAT_WS(' ','2019-01-01' + INTERVAL RAND() * 90 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401)))));

出力されたsql実行結果（途中まで）