データベース分析の基礎固め！月別売上集計をPHPとMySQLでスマートに実装

2024-06-30

PHPとMySQLを使って月と年のみでデータを取得する方法

必要なもの
データベースの設定
SQLクエリ
PHPスクリプト
クエリのテスト

必要なもの

このチュートリアルを完了するには、以下のものが必要です。

Webサーバー (Apache、Nginxなど)
MySQLデータベース
PHP

データベースの設定

まず、MySQLデータベースに接続して、データを取得するテーブルを作成する必要があります。この例では、orders という名前のテーブルを使用し、order_id、customer_id、order_date、order_amount というフィールドがあると仮定します。

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  order_date DATE,
  order_amount DECIMAL(10,2)
);

SQLクエリ

以下のSQLクエリを使用して、特定の月と年の注文を取得します。

SELECT
  MONTH(order_date) AS order_month,
  YEAR(order_date) AS order_year,
  SUM(order_amount) AS total_sales
FROM orders
WHERE MONTH(order_date) = ? AND YEAR(order_date) = ?
GROUP BY MONTH(order_date), YEAR(order_date)
ORDER BY order_year, order_month;

このクエリは、以下のことを行います。

order_date フィールドから月と年を抽出します。
指定された月と年の注文のみを選択します。
注文金額を月と年ごとに合計します。
結果を年と月の昇順に並べ替えます。

PHPスクリプト

以下のPHPスクリプトを使用して、SQLクエリを実行し、結果をブラウザに表示します。

<?php

// データベース接続
$dbhost = "localhost";
$dbuser = "username";
$dbpass = "password";
$dbname = "database_name";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

// 対象年月を取得
$selected_month = $_GET['month'];
$selected_year = $_GET['year'];

// SQLクエリを実行
$sql = "SELECT
  MONTH(order_date) AS order_month,
  YEAR(order_date) AS order_year,
  SUM(order_amount) AS total_sales
FROM orders
WHERE MONTH(order_date) = ? AND YEAR(order_date) = ?
GROUP BY MONTH(order_date), YEAR(order_date)
ORDER BY order_year, order_month;";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $selected_month, $selected_year);
$stmt->execute();
$result = $stmt->get_result();

// 結果を出力
echo "<h1>" . $selected_year . "年" . $selected_month . "月の売上</h1>";
echo "<table border=1>";
echo "<tr><th>月</th><th>年</th><th>売上</th></tr>";
while ($row = $result->fetch_assoc()) {
  echo "<tr>";
  echo "<td>" . $row["order_month"] . "</td>";
  echo "<td>" . $row["order_year"] . "</td>";
  echo "<td>" . $row["total_sales"] . "</td>";
  echo "</tr>";
}
echo "</table>";

$conn->close();

?>

データベースに接続します。
GETパラメータから対象年月を取得します。
SQLクエリを準備し、パラメータをバインドします。
クエリを実行し、結果を取得します。
結果をHTMLテーブル形式で出力します。

クエリ

上記のスクリプトを実行するには、ブラウザのアドレスバーに以下のURLを入力する必要があります。

http://localhost/script.php?month=1&year=2023

この例では、1月の2023年のデータが表示されます。月は1から12、年は任意の値に置き

CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  order_date DATE,
  order_amount DECIMAL(10,2)
);

<?php

// データベース接続
$dbhost = "localhost";
$dbuser = "username";
$dbpass = "password";
$dbname = "database_name";

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

// 対象年月を取得
$selected_month = $_GET['month'];
$selected_year = $_GET['year'];

// SQLクエリを実行
$sql = "SELECT
  MONTH(order_date) AS order_month,
  YEAR(order_date) AS order_year,
  SUM(order_amount) AS total_sales
FROM orders
WHERE MONTH(order_date) = ? AND YEAR(order_date) = ?
GROUP BY MONTH(order_date), YEAR(order_date)
ORDER BY order_year, order_month;";

$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $selected_month, $selected_year);
$stmt->execute();
$result = $stmt->get_result();

// 結果を出力
echo "<h1>" . $selected_year . "年" . $selected_month . "月の売上</h1>";
echo "<table border=1>";
echo "<tr><th>月</th><th>年</th><th>売上</th></tr>";
while ($row = $result->fetch_assoc()) {
  echo "<tr>";
  echo "<td>" . $row["order_month"] . "</td>";
  echo "<td>" . $row["order_year"] . "</td>";
  echo "<td>" . $row["total_sales"] . "</td>";
  echo "</tr>";
}
echo "</table>";

$conn->close();

?>

クエリの実行

http://localhost/script.php?month=1&year=2023

解説

データベースに接続します ($conn = new mysqli(...))
GETパラメータから対象年月を取得します ($selected_month = $_GET['month']; $selected_year = $_GET['year'];)
SQLクエリを準備し、パラメータをバインドします ($stmt = $conn->prepare($sql); $stmt->bind_param("ii", $selected_month, $selected_year);)
クエリを実行し、結果を取得します ($stmt->execute(); $result = $stmt->get_result();)
結果をHTMLテーブル形式で出力します (echo "<table>...</table>";)
データベース接続を閉じます ($conn->close();)

注意事項

このコードはあくまで例であり、実際の状況に合わせて変更する必要があります。
データベースへの接続情報などは、ご自身の環境に合わせて変更してください。
セキュリティ対策として、ユーザー入力データは必ずエスケープ処理を行ってください。

PHPとMySQLで月と年のみでデータを取得するその他の方法

MONTH() と YEAR() 関数を使用したサブクエリ

この方法は、サブクエリを使用して、MONTH() と YEAR() 関数を直接使用するものです。

SELECT
  y.year,
  m.month,
  SUM(order_amount) AS total_sales
FROM orders o
JOIN (
  SELECT DISTINCT YEAR(order_date) AS year
  FROM orders
) AS y
ON o.YEAR(order_date) = y.year
JOIN (
  SELECT DISTINCT MONTH(order_date) AS month
  FROM orders
) AS m
ON o.MONTH(order_date) = m.month
WHERE y.year = ? AND m.month = ?
GROUP BY y.year, m.month
ORDER BY y.year, m.month;

このクエリは、前述のクエリとほぼ同じ結果を返しますが、より冗長で読みづらくなっています。

集計関数を使用した GROUP BY

この方法は、GROUP BY 句と集計関数を使用して、月と年の売上を直接グループ化します。

SELECT
  YEAR(order_date) AS order_year,
  MONTH(order_date) AS order_month,
  SUM(order_amount) AS total_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
HAVING YEAR(order_date) = ? AND MONTH(order_date) = ?
ORDER BY order_year, order_month;