PR

PHP で簡単 WEB BI サイト構築

2024.11.26

データをリアルタイムで見える化するBIツールは、会社の状態を迅速かつ正確に把握することができ、的確でタイムリーな経営判断には必要不可欠なものです。

しかし、市販のBIツールは大抵は高額で、導入にはかなりの抵抗があると思います。

しかし、データベースにデータが蓄積できているのであれば PHP の簡単なプログラムを作成することで手軽に WEB BIサイト を作ることができます。

本記事ではPHPを使って WEB BIサイトを構築する方法 をご紹介します。PHP環境構築からプログラム作成の詳細まで丁寧に解説しますので、興味を持たれた方は、是非挑戦してみて下さい。

本記事の前提

冒頭のご紹介でも触れましたが、本記事は以下の環境が整っていることが前提となっています。

  1. データベースに各種データが保管され適宜更新されている
  2. サーバ上でWebサーバが動いている
  3. WebサーバでPHPが動作する

もしここまでの環境が構築できていない場合は、以下の記事などを参考にビジネスデータをデータベースに蓄積する仕組みを構築してみて下さい。

無料 SQL Server Express を Excel と連携させて活用する
yomikakiexcel.com
脱Excel DX by Excel のススメ【コンセプト編】
yomikakiexcel.com
無料ツールで Excel BI システムを構築する方法を詳細解説！
yomikakiexcel.com

PHPのPDO環境構築

PHPには PDO(PHP Data Object) という便利な機能があり、様々なデータベースに対応したPDOのオブジェクトを作成した後は統一したプログラムの記述でデータベースの操作が可能となっています。

そこでまず、PHPでPDOを使えるように環境構築をします。

SQL Server を使う方は次の項に従ってMicrosoft のサイトからドライバを入手して下さい。

MySQL, PostgreSQL, SQLite のドライバはPHPに標準で組み込まれていますので、これらのデータベースをお使いの方は次の項はスキップして php.ini の設定 に進んで下さい。

Microsoft SQL Server のPDO Driver を入手する

まず、以下のMicrosoftの公式サイトから、PHP用のSQL Serverドライバーをダウンロードします。

Microsoft SQL Server 用 Drivers for PHP をダウンロードする - PHP drivers for SQL Server
learn.microsoft.com
使用する PDO Driver を見つける

ダウンロードした ZIPファイル には Driver のファイルが複数入っています。この中から使用している PHP のバージョンに一致したものを選ぶ必要があります。

適切なドライバを選択するにはPHPの情報を把握する必要があります。そのために、PHPの情報一覧を出力します。

以下の内容の PHP のファイルをブラウザで閲覧できるフォルダに配置します。例えば phpinfo.php という名前で保存してみて下さい。

<?php
phpinfo();
?>

このファイルにブラウザでアクセスすると、図のように PHP の情報の一覧が表示されます。

その中から図のように バージョン、Architecture 、 Thread Safety の3つの項目を確認します。

バージョン は 最初の2ケタ を読み取ります。

Architecture は X86 か X64 になっているはずです。

Thread Safety は enabled か disabled になっているはずです。

この3つの情報をメモしておいて下さい。

得られた情報から図のようにして適切なドライバを選択します。

このファイルを PHP のインストールフォルダの下の ext というフォルダに保管します。これでPDOドライバの配置は完了です。

php.ini の設定

PHPの設定ファイルである php.ini に PDOドライバーの設定を行います。

  1. PHPの設定ファイル（通常は php.ini）を開きます。
  2. 図のようにPDOドライバの設定部分を修正します
  1. 設定が完了したら、Webサーバを再起動します。（Windows は 「サービス」 から 「World Wide Web 発行サービス」 を右クリックして「再起動」）

PDO Driver の設定確認

設定が正しく行われているか確認するために、再び phpinfo.php のページを開いてみて下さい。

図のように「PDO」セクションにデータベースに対応したPDOドライバが表示されていれば、設定が正しく行われています。

PHPからのアクセス権限設定

続いて、PHPからデータベースにアクセスするための権限設定を行います。

ここでは SQL Server では次の設定をします。他のデータベースでは参照ユーザーの作成に進みます。

SQL Server 認証有効化設定

  1. SQL Server Management Studio（SSMS）を開き、データベースに接続します。
  2. SQL Serverのプロパティから「セキュリティ」タブを選び、「SQL ServerおよびWindows認証モード」に設定します。
  3. 設定を保存して、SQL Serverを再起動します。

PHPからの参照ユーザーの作成

データベースを参照するためのユーザーを作成します。セキュリティを考慮して必要な範囲だけ参照できるユーザーにします。
ここでは SQL Server での設定方法を紹介します。

  1. SSMSで、接続したいデータベースに移動します。
  2. 「セキュリティ」 > 「ログイン」を右クリックして「新しいログイン」を選びます。
  3. ユーザー名、パスワードを設定し、必要なデータベースの参照権限を付与します。

PHP PDO で データベース に接続する基本コード

環境設定が整ったら、いよいよPHPからデータベースに接続してみましょう。以下のコードを使って接続します。

なお、冒頭の変数でサーバ名、データベース名、ユーザ名、パスワード はご自分の環境に合わせて設定して下さい。

<?php

$dsnPrefix = "sqlsrv";                   // DSN Prefix
$serverName = "localhost\sqlexpress";    // サーバ名
$database = "sampleDB";                  // データベース名
$username = "foobaa";                    // ユーザ名
$password = "mySecretPW";                // パスワード

try {
    $pdo = new PDO($dsnPrefix . ":server=$serverName;Database=$database", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "接続に成功しました！";
} catch (PDOException $e) {
    echo "接続に失敗しました: " . $e->getMessage();
}
?>

上記コードを実行し、「接続に成功しました！」と表示されれば、接続設定が正しく行われています。

SQL クエリを Web のテーブルに出力

PDOの環境構築ができたら後は比較的少ないコードでデータベースからデータを取得するプログラムの作成が可能です。

例としてデータを表形式で出力するコードを紹介します。

このコードでは、your_table_name というテーブルから全てのデータを取得し、HTMLテーブルとして表示します。

<?php
// Data Base 接続情報
$dsnPrefix = "sqlsrv";                   // DSN Prefix
$serverName = "localhost\sqlexpress";    // サーバ名
$database = "sampleDB";                  // データベース名
$username = "foobaa";                    // ユーザ名
$password = "mySecretPW";                // パスワード

// データベース接続
$pdo = new PDO("$dsnPrefix:server=$serverName;Database=$database", $username, $password);

// SQL クエリの実行
$sql = "SELECT ID, Name, Age FROM your_table_name";
$stmt = $pdo->query($sql);

// 結果をテーブル形式で出力
echo "<table border='1'>";
echo "<tr><th>ID</th><th>Name</th><th>Age</th></tr>";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr>";
    echo "<td>" . $row['ID'] . "</td>";
    echo "<td>" . $row['Name'] . "</td>";
    echo "<td>" . $row['Age'] . "</td>";
    echo "</tr>";
}
echo "</table>";
?>

テーブル作成、グラフ作成を関数化

ここまでできたら後は応用です。まず以下のプログラムを外部読み込み用のファイルとして保存して下さい。ここでは sqlib.php として保存します。

executeSQLQuery() 関数

引数として SQLクエリ を受け取り、クエリ結果を配列で返します。

displayTable() 関数

executeSQLQuery()関数の実行結果を受け取って表形式で出力します。

displayChart() 関数

executeSQLQuery()関数の実行結果を受け取って Chart.js ライブラリを使ってグラフを出力します。

3番目の引数はユニークなグラフのIDを適当に設定します。（例えば “Chart1”, “Chart2” など）

<?php
// Data Base 接続情報
$dsnPrefix = "sqlsrv";                   // DSN Prefix
$serverName = "localhost\sqlexpress";    // サーバ名
$database = "sampleDB";                  // データベース名
$username = "foobaa";                    // ユーザ名
$password = "mySecretPW";                // パスワード

/**
 * SQLクエリを実行して結果を返す関数
 * @param string $query SQLクエリ
 * @return array [列名の配列, データの配列]
 */
function executeSQLQuery($query)
{
    global $dsnPrefix, $serverName, $database, $username, $password;

    try {
        // PDOインスタンスの作成
        $pdo = new PDO("$dsnPrefix:server=$serverName;Database=$database", $username, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // クエリ実行
        $stmt = $pdo->prepare($query);
        $stmt->execute();

        // クエリ結果を配列に格納
        $data = [];
        $columns = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            if (empty($columns)) {
                $columns = array_keys($row); // 列名を取得
            }
            $data[] = $row;
        }
        return [$columns, $data];
    } catch (PDOException $e) {
        die("接続に失敗しました: " . $e->getMessage());
    }
}

/**
 * 表を表示する関数
 * @param array $columns 列名の配列
 * @param array $data データの配列
 */
function displayTable($columns, $data)
{
    echo '<div class="table-responsive">';
    echo '<table class="table table-striped table-bordered">';
    echo '<thead><tr>';
    foreach ($columns as $col) {
        echo '<th>' . htmlspecialchars($col) . '</th>';
    }
    echo '</tr></thead><tbody>';
    foreach ($data as $row) {
        echo '<tr>';
        foreach ($row as $key => $value) {
            echo '<td>' . (is_numeric($value) ? number_format($value) : htmlspecialchars($value)) . '</td>';
        }
        echo '</tr>';
    }
    echo '</tbody></table></div>';
}

/**
 * グラフを表示する関数
 * @param array $columns 列名の配列
 * @param array $data データの配列
 * @param string $chartId グラフのID
 */
function displayChart($columns, $data, $chartId)
{
    // カラーパレット
    $colorPalette = [
        'rgba(255, 99, 132, 0.2)', // 赤
        'rgba(54, 162, 235, 0.2)', // 青
        'rgba(255, 206, 86, 0.2)', // 黄
        'rgba(75, 192, 192, 0.2)', // 緑
        'rgba(153, 102, 255, 0.2)' // 紫
    ];

    $borderPalette = [
        'rgba(255, 99, 132, 1)', // 赤
        'rgba(54, 162, 235, 1)', // 青
        'rgba(255, 206, 86, 1)', // 黄
        'rgba(75, 192, 192, 1)', // 緑
        'rgba(153, 102, 255, 1)' // 紫
    ];

    echo '<div class="my-4"><canvas id="' . htmlspecialchars($chartId) . '"></canvas></div>';
    echo '<script>';
    echo 'const labels_' . $chartId . ' = ' . json_encode(array_column($data, $columns[0])) . ';';
    echo 'const datasets_' . $chartId . ' = [';
    for ($i = 1; $i < count($columns); $i++) {
        echo '{';
        echo 'label: "' . $columns[$i] . '",';
        echo 'data: ' . json_encode(array_column($data, $columns[$i])) . ',';
        echo 'backgroundColor: ' . json_encode($colorPalette) . ' [' . $i . '],';
        echo 'borderColor: ' . json_encode($borderPalette) . ' [' . $i . '],';
        echo 'borderWidth: 1';
        echo '}' . ($i < count($columns) - 1 ? ',' : '');
    }
    echo '];';
    echo 'const ctx_' . $chartId . ' = document.getElementById("' . $chartId . '").getContext("2d");';
    echo 'const myChart_' . $chartId . ' = new Chart(ctx_' . $chartId . ', {';
    echo 'type: "bar",';
    echo 'data: { labels: labels_' . $chartId . ', datasets: datasets_' . $chartId . ' },';
    echo 'options: { responsive: true, scales: { y: { beginAtZero: true } } }';
    echo '});';
    echo '</script>';
}

?>

WEB BI サイトを作成する

いよいよ WEB BI サイトを作成します。先ほど作成したライブラリの関数を使って、実質的にSQLクエリを渡すだけで表やグラフの作成が可能です。

以下の例では Bootstrap5 を使ってレスポンシブデザインに対応したBIサイトを表示します。

SQLクエリはご自分で工夫してみて下さい。これを使えば、いちいち SQL Server Management Studio などのデータベース接続ツールを使わなくても
WEBサイトにアクセスするだけでデータベースの最新の情報を確認することができます。

<?php
require 'sqllib.php';

// SQLクエリ文字列を使用して実行
$query = "SELECT category, value1, value2 FROM your_table_name";
[$columns, $data] = executeSQLQuery($query);

// 2つ目のクエリの例
$query2 = "SELECT another_category, another_value1, another_value2 FROM another_table";
[$columns2, $data2] = executeSQLQuery($query2);

?>
<!DOCTYPE html>
<html lang="ja">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>WEB BI テストサイト</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>

<body>
    <div class="container my-4">
        <h1 class="mb-4">WEB BI テストサイト</h1>

        <div class="row">
            <div class="col-lg-6 mb-4">
                <h2>クエリ1　表出力</h2>
                <?php displayTable($columns1, $data1); // テーブル表示 
                ?>
            </div>
            <div class="col-lg-6 mb-4">
                <h2>クエリ2　グラフ出力</h2>
                <?php displayChart($columns2, $data2, 'chart1'); // グラフ表示 (ID = chart1) 
                ?>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>

</html>

まとめ

この記事では、PHP の PDO を使って簡単なWEB BIツールを作成する方法をご紹介しました。

PHPからデータベースに接続し、データを 表 や グラフ で表示することで、オリジナルのBIツールを構築できます。

一つ作成できれば、思い付くクエリの数だけ簡単に BI サイト を構築できますので、これがあれば高額はBIツール無しでもかなりのことができるのではないでしょうか？

データ分析や業務報告など、さまざまな場面で活用できるので、是非WEB BIサイトの構築に挑戦してみてください。

タイトルとURLをコピーしました