SQLのデータをグラフ化する方法

Google、Amazonおよび楽天の広告/アフィリエイトを使用しています。

SQLからchart.jsでグラフ化 PC/デジモノ
2023.10.19

前回、ESP32で収集した気温などのデータをデータベース(mariadb)に登録する方法を紹介しました。今回はこのデータをウェブブラウザから簡単に確認できるようなシステムを構築する方法を紹介します。

HTMLとPHPとJavaScriptを使って最低限の実装で実現します。

概要

本記事ではHTML,PHP,JavaScriptを使用します。

  • PHPでSQLのデータを抜き出す
  • JavaScriptでChart.jsを使ってグラフ化
  • HTMLでWEBブラウザ上に表示する

という流れです。わかりやすくするために、HTMLなどの装飾はほぼ省いてあります。とにかくSQLのデータをChart.jsに流し込む、ということに絞ります。

動作環境

同一サーバー上にSQLサーバー(mariadb)とWEBサーバー(nginx+PHP)が稼働しているものとします。

環境構築やデータの登録方法は以前の記事を参照してください。

RaspberryPiにNGINX＋MySQL(MariaDB)+PHPの環境を構築する
RaspberryPiにNGINXとMariaDBをインストールする方法を紹介します。 SQLはたくさんのデータを管理するのに便利です。IOT機器で収集したデータを保管するため、また、純粋にSQLを勉強するための環境としてRaspberryPiにインストールしてみてはいかがでしょうか。
tsuzureya.net
2024.04.09
マイコンで取得したデータをSQLサーバーに登録する方法
マイコンなどで収集したデータをSQLサーバー(MySQL/MariaDB)に登録する方法を紹介します。
tsuzureya.net
2024.04.09

データベースの構成

データベースは以下のような構成になっているものとします。

ユーザー:hoge
パスワード:hoge_password
データベース名:DB_HOGE
テーブル名:TB_HOGE
カラム1:ID int型
カラム2:YDT datetime型
カラム3:TEMP float型

(参考)上記構成を構築するクエリーは以下です。

$sudo mysql
>CREATE DATABASE DB_HOGE;
>USE DB_HOGE;
>CREATE TABLE TB_HOGE
 (ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,YDT DATETIME,TEMP FLOAT);
>CREATE USER hoge@localhost IDENTIFIED BY 'hoge_password';
>GRANT SELECT,INSERT ON DB_HOGE.TB_HOGE TO hoge@localhost;
>exit

コード

HTML,PHP,JavaScriptの各コードは以下のようにします。

HTML

$emacs index.html
<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="utf-8">
  </head>

  <body>
    <canvas id="myChart"></canvas>

    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.10.6/moment.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chartjs-adapter-moment@1.0.0"></script>
    <script src="graph.js"></script>

    <?php include('./graph.php'); ?>
    <script>DrawChart( <?php getCatSum(); ?>);</script>
  </body>
</html>

PHP

$emacs graph.php
<?php

function connectDB(){
  $dsn= 'mysql:host=localhost;dbname=DB_HOGE';
  $user = 'hoge';
  $pass = 'hoge_password';
  try{
    $pdo = new PDO($dsn,$user,$pass);
    return $pdo;
  }
  catch(PDOException $e){
  print_r ("データベース接続失敗");
  }
}

function getCatSum(){
  $pdo = connectDB();
  $sql = "select YDT,TEMP from TB_HOGE limit 500";
  $sth = $pdo -> prepare($sql);
  $sth -> execute();

  $time="";
  $value="";

  foreach($sth as $row){
    $time=$time."'".$row[0]."'".',';
    $value=$value.$row[1].',';
  }

  $time=trim($time,',');
  $value=trim($value,',');

  $result="\"".$time."*".$value."\"";
  printf($result);
  $pdo=null;
}
?>

JavaScript

$emacs graph.js
function DrawChart(data){

  dataArray=data.split('*');
  time = dataArray[0].split(',');
  value = dataArray[1].split(',');

  var ctx = document.getElementById('myChart');
  var myChart = new Chart(ctx, {
    type: 'line',
    data:{
      labels: time,
      datasets:[{
        label:'気温',
        pointRadius : 1,
        lineTension: 0.2,
        data: value
      }]
    },
    options:{
      plugins:{
        legend:{ display : false }
      },
      scales:{
        x:{
          type: 'time',
          time:{
            parser: 'YYYY-MM-DD HH-mm-ss',
            unit:'hour',
            displayFormats:{ hour : 'MM/DD HH:mm'}
          },
          ticks:{ stepSize : 12 }
        },
        y:{
          max:40,
          min:0,
          ticks:{ stepSize : 5 }
        }
      }
    }
  });
}

解説

HTML

まずHTML内の以下の記述はChart.jsを使うためのおまじないです。ちなみに、現時点でのChart.jsのバージョンは4.4です。

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.10.6/moment.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chartjs-adapter-moment@1.0.0"></script>
<script>DrawChart( <?php getSQLData(); ?>);</script>

PHPの関数getSQLData()で取得したデータをJavaScriptの関数DrawChartに渡す、という処理をしています。

PHP

PHPではPDOを使ってSQLのデータを取得しています。この部分は定番の方法です。

JavaScriptに渡すためのデータは以下の部分で生成しています。

foreach($sth as $row){
  $time=$time."'".$row[0]."'".',';
  $value=$value.$row[1].',';
}
~~~
$result="\"".$time."*".$value."\"";

この処理で以下のような形式になっています。

時間1,時間2,時間3,…,時間n*気温1,気温2,気温3,…,気温n

データの区切りは,(カンマ)、カラムの区切りは*(アスタリスク)として1つの文字列にまとめています。泥臭い方法ですが。

この方法でなくても、配列をそれぞれ渡してもよいですし、JSON形式にまとめたDataSetで渡す方法もあります。CSV脳が作るとこうなるという例です。

JavaScript

PHPからのデータを以下の手順で配列に格納しています。

dataArray=data.split('*');
time = dataArray[0].split(',');
value = dataArray[1].split(',');

これより下の部分はChart.jsの描画部分です。

var ctx = document.getElementById('myChart');
var myChart = new Chart(ctx, {
~~~~~
 });
}

注意事項

chart.jsの表示例

今回の中ではChart.jsが一番の曲者かなと思います。オプションの設定が{}や[]のオンパレードで複雑なんですよね。しかも、ウェブにはv2系、v3系、v4系などの情報が入り乱れていて、微妙に書き方が違うせいで私を含めて初心者には難しいです。なお、今回のコードは現時点最新のv4.4での動作を確認しています。

また、画面サイズ(ウィンドウサイズ)によって表示が自動で調整されるので、ticksの間隔などが設定どおりにならない場合も多いです。(書き方を間違えたのか自動調整されたのか分かりにくい)

このあたりは自分でいろいろいじってみるしかないと思います。

まとめ

  1. マイコンでデータ取得
  2. SQLにデータ蓄積
  3. WEBブラウザでグラフを確認

このようなシステムができれば収集したデータの使い出がぐっと上がりますよね。すでにAmbientで同じようなことはできるのですが、自前のサーバーであればデータ数やチャンネル数の制約など気にせずに使うことができますね！

コメント