本番環境の構築

実際にシステムで使用するDBを作成する。
前提としてプロジェクト型演習 #1 (raspberrypi DB操作) 実装済み、更に Apache2 をインストール済みののRaspberry Pi を利用しているものとして話を進める。
今回は、本番用DBを作成し、Web画面で確認するところまでやってみる。

DB構築

ログイン

面倒なので、以前作成したユーザ, DBを使い回す。

ユーザでログイン

pi@raspberry:~ $ sudo mysql -upi -praspberry

DBへ入る

MariaDB[(none)]> use sample_db
Database changed

各種テーブルの作成

※下記SQL文をそのままコピペでおけ。詳しくは #1 を参照。

スコアテーブルの作成

CREATE TABLE `score` (
  `num` int(32) NOT NULL AUTO_INCREMENT,
  `id` int(4) NOT NULL,
  `score` int(8) NOT NULL,
  `great` int(4) NOT NULL,
  `good` int(4) NOT NULL,
  `bad` int(4) NOT NULL,
  `datetime` DATETIME NOT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

楽曲テーブルの作成

CREATE TABLE `music` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(512) NOT NULL,
  `max` int(8) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

楽曲テーブルにデータを挿入

INSERT INTO `music` (`name`, `max`) VALUES
("ピタゴラスイッチ", 1000),
("きらきら星", 1000);

スコアテーブルにデータを挿入

INSERT INTO `score` (`id`, `score`, `great`, `good`, `bad`, `datetime`) VALUES
(1, 305, 25, 51, 24, '2021-06-01 21:45:31'),
(1, 700, 50, 40, 10, '2021-06-02 21:45:31'),
(2, 625, 40, 45, 15, '2021-06-03 21:45:31'),
(1, 125, 5, 15, 80, '2021-06-03 21:45:31');

WEB画面 (PHP)

TOPページ

今後修正予定だが、雑に作っておく。
以下の全ファイルを/var/www/html/内に配置。index.phpは前回作成したものを修正する。

pi@raspberry:~ $ cd /var/www/html
pi@raspberry:/var/www/html $ nano index.php

TOP

index.php

<?php
    $title = "認知症予防システム";
    require('./header.php');
    $page = "home";
?>
    <h1 class="mx-auto" style="width: 200px; font-family: ヒラギノ丸ゴ ProN W4;">スコア表</h1>
    <table class="table">
        <thead class="table-light" style="background-color: #008b8b;">
            <th>日付</th>
            <th>曲名</th>
            <th>スコア</th>
            <th>GREAT</th>
            <th>GOOD</th>
            <th>BAD</th>
            <th>%</th>
        </thead>
<?php
    ini_set('display_errors', "On");
    //phpinfo();
    include "./pdo_connect.php";
    $sql = "SELECT * FROM score JOIN music WHERE score.id = music.id ORDER BY score.datetime DESC";
    $stmt = $pdo -> query($sql);
    foreach($stmt as $row){
?>
        <tbody id = "<?=$row['num']?>">
            <td><?=date('Y年m月d日 H:i',strtotime($row['datetime']))?></td>
            <td><?=$row['name']?></td>
            <td><?=$row['score']?></td>
            <td><?=$row['great']?></td>
            <td><?=$row['good']?></td>
            <td><?=$row['bad']?></td>
            <td><?=($row['score'] / $row['max']) * 100?></td>
        </tbody>
<?php
    }
?>
    </table>
<?php
    require('./footer.php');
?>

ヘッダーファイル

pi@raspberry:/var/www/html $ nano header.php

ヘッダーファイル

header.php

<!DOCTYPE html>
<html lang='ja'>
<head>
    <meta charset='utf-8' />
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
    <title><?=$title?></title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/fullcalendar@5.5.0/main.min.css">
    <script src="https://cdn.jsdelivr.net/npm/fullcalendar@5.5.0/main.min.js"></script>
</head>
<body>
<nav class="navbar navbar-dark mb-5" style="background-color: #008080;">
  <a class="navbar-brand" href="./index.php">認知症予防システム</a>
  <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNavAltMarkup" aria-controls="navbarNavAltMarkup" aria-expanded="false" aria-label="Toggle navigation">
    <span class="navbar-toggler-icon"></span>
  </button>
  <div class="collapse navbar-collapse" id="navbarNavAltMarkup">
    <div class="navbar-nav">
      <a id="home" class="nav-item nav-link" href="./index.php">Home <span class="sr-only">(current)</span></a>
      <a id="calendar" class="nav-item nav-link" href="./calendar.php">Calendar</a>
      <a id="graph" class="nav-item nav-link" href="./graph.php">Graph</a>
    </div>
  </div>
</nav>
<div class="container w-80">

フッターファイル

pi@raspberry:/var/www/html $ nano footer.php

フッターファイル

footer.php

</div>
</body>
</html>
<script>
const div = document.getElementById("<?=$page?>");
div.classList.add('active');
</script>

カレンダー

pi@raspberry:/var/www/html $ nano calendar.php

カレンダー

calendar.php

<?php
    $title = "カレンダー";
    require('./header.php');
    $page = "calendar";
?>
<div id='calendar-box'></div>
<?php
    require('./footer.php');
?>
<script>
document.addEventListener('DOMContentLoaded', function() {
    var calendarEl = document.getElementById('calendar-box');
    var calendar = new FullCalendar.Calendar(calendarEl, {
        initialView: 'dayGridMonth',
        events: [
<?php
include "./pdo_connect.php";
$sql = "SELECT * FROM score JOIN music WHERE score.id = music.id";
$stmt = $pdo -> query($sql);
foreach($stmt as $row){
?>
                {
                    id: '<?=$row['num']?>',
                    title: '<?=$row['name']." ".$row['score']?>',
                    start: '<?=date('Y-m-d',strtotime($row['datetime']))?>',
                    url: './index.php#<?=$row['num']?>'
                },
<?php
}
?>

        ],
        // 日本語化
        locale: 'ja',
        buttonText: {
            prev:     '<',
            next:     '>',
            prevYear: '<<',
            nextYear: '>>',
            today:    '今日',
            month:    '月',
            week:     '週',
            day:      '日',
            list:     '一覧'
        },
    });
    calendar.render();
});
</script>

グラフ

pi@raspberry:/var/www/html $ nano graph.php

グラフ

graph.php

<?php
    $title = "グラフ";
    require('./header.php');
    $page = "graph";
    $labels = [];
    $datetime_list = [];
    for ($i = 6; $i >= 0; $i--) {
        $m = (int)date("m", strtotime("-"."$i"." day"));
        $d = (int)date("d", strtotime("-"."$i"." day"));
        $labels[] = $m."月".$d."日";
        $datetime_list[] = date("Y-m-d", strtotime("-"."$i"." day"));
    }
    $start = date("Y-m-d", strtotime("-6 day"));
    $start .= " 00:00:00";
    $end = date("Y-m-d", strtotime("-0 day"));
    $end .= " 99:99:99";

    include "./pdo_connect.php";
    $sql = "SELECT * FROM score JOIN music WHERE score.id = music.id AND score.datetime BETWEEN '$start' AND '$end' ORDER BY score.datetime ASC";
    $stmt = $pdo -> query($sql);
    $cnt = 0;
    $max_list = [0, 0, 0, 0, 0, 0, 0];
    $min_list = [200, 200, 200, 200, 200, 200, 200];
    foreach($stmt as $row){
        for ($i = $cnt; $i < 7; $i++) {
            if ($datetime_list[$i]." 00:00:00" <= $row['datetime'] && $row['datetime'] <= $datetime_list[$i]." 99:99:99") {
                if ($max_list[$cnt] < ($row['score'] / $row['max']) * 100) {
                    $max_list[$cnt] = ($row['score'] / $row['max']) * 100;
                }
                if ($min_list[$cnt] > ($row['score'] / $row['max']) * 100) {
                    $min_list[$cnt] = ($row['score'] / $row['max']) * 100;
                }
                break;
            } else {
                $cnt ++;
            }
        }
    }
    for ($i = 0; $i < 7; $i++) {
        if ($min_list[$i] == 200) {
            $min_list[$i] = 0;
        }
    }
?>
<h1>折れ線グラフ</h1>
  <canvas id="myLineChart"></canvas>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.2/Chart.bundle.js"></script>

  <script>
  var ctx = document.getElementById("myLineChart");
  var myLineChart = new Chart(ctx, {
    type: 'line',
    data: {
        labels: ['<?=$labels[0]?>', '<?=$labels[1]?>', '<?=$labels[2]?>', '<?=$labels[3]?>', '<?=$labels[4]?>', '<?=$labels[5]?>', '<?=$labels[6]?>'],
      datasets: [
        {
          label: '最高得点率(%）',
          data: [<?=$max_list[0]?>, <?=$max_list[1]?>, <?=$max_list[2]?>, <?=$max_list[3]?>, <?=$max_list[4]?>, <?=$max_list[5]?>, <?=$max_list[6]?>],
          borderColor: "rgba(255,0,0,1)",
          backgroundColor: "rgba(0,0,0,0)"
        },
        {
          label: '最低得点率(%）',
          data: [<?=$min_list[0]?>, <?=$min_list[1]?>, <?=$min_list[2]?>, <?=$min_list[3]?>, <?=$min_list[4]?>, <?=$min_list[5]?>, <?=$min_list[6]?>],
          borderColor: "rgba(0,0,255,1)",
          backgroundColor: "rgba(0,0,0,0)"
        }
      ],
    },
    options: {
      title: {
        display: true,
        text: '得点率（<?=$labels[0]?> ~ <?=$labels[6]?>）'
      },
      scales: {
        yAxes: [{
          ticks: {
            suggestedMax: 100,
            suggestedMin: 0,
            stepSize: 10,
            callback: function(value, index, values){
              return  value +  '%'
            }
          }
        }]
      },
    }
  });
  </script>
<?php
    require('./footer.php');
?>

DB接続ファイル

pi@raspberry:/var/www/html $ nano pdo_connect.php

DB接続ファイル

pdo_connect.php

<?php
//データベースの接続と選択
$hostdbname = 'mysql:host=localhost;dbname=sample_db;charset=UTF8';
$username = "pi";
$password = "raspberry";
$pdo = new PDO($hostdbname, $username, $password);
$pdo -> setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
?>

実行

TOPページの確認


カレンダーページの確認


グラフページの確認

おそらくこんな感じでできるはず、、、
※この画像はPC上にDockerで環境構築したものを使用している為、実際とは異なる。

今後

TOPページの装飾
検索機能やランキング、ソートなんかの実装（余力あれば）