20201026のMySQLに関する記事は16件です。

投稿日：2020-10-26T22:43:46+09:00

mysqlをdockerで起動しておきたい時のdocker-compose.yml

(1) docker-compose.ymlを作成する

docker-compose.yml

version: "3.8"
services:
  db:
    image: mysql:5.7                   # mysqlのバージョンを指定する
    container_name: docker_mysql
    ports:
      - "3307:3306"                    # DBを起動するポートを指定する
    environment:
      - MYSQL_USER=root
      - MYSQL_PASSWORD=
      - MYSQL_ROOT_PASSWORD=
      - MYSQL_ALLOW_EMPTY_PASSWORD=yes # パスワード無しを許可する
    volumes:
      - mysql-db-data:/var/lib/mysql   # データの永続化
    tty: true                          # コンテナを起動し続ける
volumes:
  mysql-db-data:
    driver: local

(2) mysqlを起動する docker-compose up -d

(3) mysqlに接続する mysql -u root -h 127.0.0.1 -P 3307

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T22:39:12+09:00

Youtuberが配信・動画投稿したらメールにて通知するプログラムを作ってみた

はじめに

私は結構Youtubeを見るようになり、好きなYoutuberから動画投稿の通知があれば気になって視聴していました。
しかし、ある日Youtubeからのメール通知がなくなっていたことに気づきました。

Youtubeがメール通知機能を2020年8月13日で終了

下記の通達通り、メール通知機能が終わってしまっていました...
https://support.google.com/youtube/thread/63268863?hl=ja

登録チャンネルの新しいアップロード動画に関するメール通知の変更

登録しているチャンネルの新しいアップロード、ライブ配信、プレミア公開に関する通知をメールで受け取る機能は、2020 年 8 月 13 日で終了します。この機能を設定している場合はご注意ください。モバイルの YouTube アプリまたはパソコンの Chrome ブラウザ経由の通知をオンにしている場合は、引き続き通知が届きます。

個人的にはメールから開いて動画を視聴することが多かったので、その機能が廃止されるとなると嫌になってしまいますので独自にメールで通知するプログラムを作成しました。

このプログラムでできること

1つのプログラムにつき、1人のYoutuberの配信,動画投稿されたらプログラムに書かれてあるメールアドレス宛に動画URLが送信されるようになる。

crontabを使って定期的に投稿されたかチャンネルの投稿動画を確認する。

運用環境
環境
OS:CentOS7
プログラム言語：PHP7
データベース：MySQL
定期実行：crontab
サーバ実行環境：さくらVPS(安価なレンタルサーバでPHP7,cron,MySQLの機能が使える環境でもOKです。)
必要ツール：Youtube API
必要な情報：自分の持っているメールアドレス1〜2つ(現在も使用可能であること)
Youtube APIの登録・APIキーの取得

このプログラムを動かすためには、チャンネルの情報を取得する必要があるため、Youtube APIのAPIキーの取得が必須です。
以下の記事を参考にしてYoutube APIの登録を行いました。
https://blog.codecamp.jp/programming-api-youtube

最終的に、以下のようにAPIキーがコピーできる画面になりますので、赤枠の部分の情報をメモしてください。

YoutubeのチャンネルページにあるURLを取得する

例としてHikakinTVさんのチャンネルIDを取得します。
チャンネルページにアクセスして、URLにある赤枠の部分の情報をメモしてください。

MySQLでテーブル作成

MySQLを使い、現状の新しい動画があるか値を保存するデータベースとテーブルを作成します。
例として、CentOS7の端末上で操作しています。
データベース作成
mysql> CREATE DATABASE NEW_VIDEO_DATA;
mysql> use NEW_VIDEO_DATA
テーブル作成
mysql> create table new_video_check(videoid varchar(200) NOT NULL PRIMARY KEY, title varchar(200));
テーブル中身
mysql> describe new_video_check;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| videoid | varchar(200) | NO   | PRI | NULL    |       |
| title   | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
プログラム

PHPで作成したプログラムは以下の通りです。
コメントの部分の通りにメモした情報を記載してください。
newvideocheck.php
<?php

class NewVideoCheck {

    private function apikey() {
        /* メモしたAPIキーを記載 */
        $apikey = "XXXXXXXXXXXXXXXXX";
        return $apikey;
    }

    private function mail_send($videoid) {
        /* 送信元メールアドレスを記載(送信元,送信先のメールアドレスは同じでも良い) */
        $from = "hogehoge@yahoo.co.jp";

        mb_language("Japanese");
        mb_internal_encoding("UTF-8");

        /* 送信先メールアドレスを記載(送信元,送信先のメールアドレスは同じでも良い) */
        $to      = 'hogehoge@gmail.com';
        $subject = '新しい動画・配信されたよ！';
        $message = 'URL https://www.youtube.com/watch?v=' . $videoid;
        $headers = "From: ".$from."\r\n";
        /* メール送信 */
        mb_send_mail($to, $subject, $message, $headers);
    }


    public function mysql_connect() {
        /* Mysql接続(サーバのIPアドレス、ユーザ情報、パスワードを記載してDBにアクセス) */
        $IPADDR = isset($_SERVER['SERVER_ADDR'])?$_SERVER['SERVER_ADDR']:gethostbyname(gethostname());
        if($IPADDR == 'YYY.YYY.YYY.YYY'){
            $link = mysqli_connect('YYY.YYY.YYY.YYY','root','XXXXXXXX');
        }
        if(!$link){
            die("エラー：DBに接続できません");
        }

        /* テーブル選択 */
        $db_selected = mysqli_select_db($link, 'NEW_VIDEO_DATA');
        if (!$db_selected){
            die("エラー：テーブルに接続できません");
        }

        return $link;
    }

    public function video_info($link) {
        /* channelIdにはメモしたチャンネルIDを記載する */
        $view_info_url = "https://www.googleapis.com/youtube/v3/search?part=snippet&channelId=UCZf__ehlCEBPop-_sldpBUQ&maxResults=1&order=date&type=video";
        /* YoutubeAPIキーを取得しURLに連結させる */    
        $view_info_url .= "&key=".$this->apikey();

        /* Youtube APIを使い動画の情報取得 */
        $json = file_get_contents($view_info_url);
        $json = mb_convert_encoding($json, 'UTF8', 'ASCII,JIS,UTF-8,EUC-JP,SJIS-WIN');
        $arr = json_decode($json, true);
        $videoid = $arr["items"][0]["id"]["videoId"];
        $title = $arr["items"][0]["snippet"]["title"];
        $videoinfo[0] = $videoid;
        $videoinfo[1] = $title;

        /* テーブル内にある動画IDと比較し、違っていたらメールを送信 */
        $select_sql = "select * from new_video_check limit 1";
        $select = mysqli_query($link, $select_sql);
        $tbl = mysqli_fetch_array($select);
        $new_videoid = $tbl[0];
        if($videoid != $new_videoid) {
            $this->mail_send($videoid);
        }

        return $videoinfo; 
    }

    public function video_register($link, $videoinfo) {

        $delete_sql = "delete from new_video_check";
        $delete = mysqli_query($link, $delete_sql);

        $register_sql = "insert into new_video_check values('".$videoinfo[0]."', '".$videoinfo[1]."')";
        $register = mysqli_query($link, $register_sql);
    }
}

// Mysqlのデータベースへアクセスする
$newvideocheck = new NewVideoCheck();
$link = $newvideocheck->mysql_connect();

// チャンネル内の最新動画を調べる
$videoinfo = $newvideocheck->video_info($link);

// チャンネル内で最新動画あげたら最新動画を登録する
$newvideocheck->video_register($link, $videoinfo);

?>
cronを使って定期実行させる

作成したプログラムをサーバにアップロードさせてcronに定期実行させるようにします。
私の場合は1時間ごとにプログラムを動かすよう定期実行させています。
crontab
# 動画更新されたらメールで通知
00 */1 * * * /bin/php /tmp/batch/newvideocheck.php
動画投稿されたらメールが届くのを確認

以下の通りにHikakinTVのチャンネル内で動画投稿されたらメールで通知されました。
画像はGmailで確認しています。

問題点

1つのプログラムにつき1チャンネルの投稿を確認するので、複数のチャンネルでメール通知したいときはその分のデータベースとプログラムとcronへの記載が必要となる。

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T20:22:04+09:00

HTML・PHP・MySQLだけで作る間違い探しゲーム【⑥ランキングページを作る】

6. ランキング表示ページ

<< 前の記事【⑤結果表示ページを作る】

5. 結果表示ページ
 5-1. 無効なアクセスの拒否
 5-2. 回答時間の算出
 5-3. 各変数への格納
 5-4. 保存する回答時間とカウント数の上限設定
 5-5. 正解／不正解による表示メッセージの分岐
 5-6. ランキングへの登録
 5-6-1. ランキングテーブルの構成
 5-6-2. ランキングへの登録

最後に、回答時間ランキングを表示するページを実装します。
ranking.php
<?php

// 難易度がPOSTされている場合は変数に格納
if (isset($_POST['show_method'])) {
    $select = $_POST['show_method'];
} else {
    $select = 'all';
}

// db接続
require_once('db_connect.php');

// 選択された表示形式によってSQL文を分岐
$sql = 'SELECT * FROM rankings ';
switch ($select) {
    case 'difficult':
        $sql .= "WHERE difficulty = '難しい（漢字）' ORDER BY time LIMIT 10";
        break;
    case 'easy':
        $sql .= "WHERE difficulty = '易しい（絵文字）' ORDER BY time LIMIT 10";
        break;
    case 'all':
        $sql .= 'ORDER BY time LIMIT 10';
        break;
}
$stmt = $dbh->query($sql);
$players = $stmt->fetchAll();

?>
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <link rel="stylesheet" href="style.css">
        <title>間違い探し ランキング</title>
    </head>
    <body>
        <h1>回答時間ランキング</h1>
        <form method="POST">
            <select name="show_method" size="1">
                <option value="all" <?php if ($select === 'all') echo 'selected' ?>>全て</option>
                <option value="easy" <?php if ($select === 'easy') echo 'selected' ?>>易しい</option>
                <option value="difficult" <?php if ($select === 'difficult') echo 'selected' ?>>難しい</option>
            </select>
            <input type="submit" value="表示">
        </form>
        <div class="table">
            <table class="s-tbl">
                <thead>
                    <tr>
                        <th>順位</th>
                        <th>名前</th>
                        <th>難易度</th>
                        <th>回答時間</th>
                        <th>リセット回数</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($players as $key => $player): ?>
                    <tr>
                        <td><?= ++$key; ?></td>
                        <td><?= $player['name']; ?></td>
                        <td><?= $player['difficulty']; ?></td>
                        <td><?= $player['time']; ?></td>
                        <td><?= $player['reset']; ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
        <p>
            <button type="button" onclick="location.href='start.php'">スタートページへ</button>
        </p>
    </body>
</html>
6-1. 表示切り替え機能
ranking.php
// 難易度がPOSTされている場合は変数に格納
if (isset($_POST['show_method'])) {
    $select = $_POST['show_method'];
} else {
    $select = 'all';
}

// db接続
require_once('db_connect.php');

// 選択された表示形式によってSQL文を分岐
$sql = 'SELECT * FROM rankings ';
switch ($select) {
    case 'difficult':
        $sql .= "WHERE difficulty = '難しい（漢字）' ORDER BY time LIMIT 10";
        break;
    case 'easy':
        $sql .= "WHERE difficulty = '易しい（絵文字）' ORDER BY time LIMIT 10";
        break;
    case 'all':
        $sql .= 'ORDER BY time LIMIT 10';
        break;
}
$stmt = $dbh->query($sql);
$players = $stmt->fetchAll();
後述する<select>タグによって難易度を「全て」「易しい」「難しい」の選択肢の中から選べるようになっています。

if-else文によって選択されていた場合はそのvalueの値を、選択されていない場合（=初回訪問時）にはallを$selectに格納させています。

そして、DB接続設定ファイルを呼び出し、$select内の値によってSQL文が変更されるようにswitch構文を用いています。

どの選択肢においてもSELECT * FROM rankingsは共通しているので、一旦$sqlにこの文を格納してから、条件分岐に応じたSQL文を結合代入演算子.=によって追加しています。

PHPマニュアル「PHP: 文字列演算子 - Manual」

その後、query()メソッドによってSQL文を実行し、返ってきたPDOStatementオブジェクトを$stmtに格納し、次いでfetchAll()メソッドによって結果を$playersに配列として格納しています。

PHPマニュアル「PHP: PDOStatement::fetchAll - Manual」

「db_connect.php」でフェッチ形式をPDO::FETCH_ASSOCと指定したので、対象テーブルのカラム名がキーとなった配列として格納されています。

6-2. 入力値の保持 - <select>タグ
ranking.php
        <form method="POST">
            <select name="show_method" size="1">
                <option value="all" <?php if ($select === 'all') echo 'selected' ?>>全て</option>
                <option value="easy" <?php if ($select === 'easy') echo 'selected' ?>>易しい</option>
                <option value="difficult" <?php if ($select === 'difficult') echo 'selected' ?>>難しい</option>
            </select>
            <input type="submit" value="表示">
        </form>
「start.php」のtextタイプやradioタイプの<input>タグと同じように、<select>タグでも入力値が保持されるようにしています。

これは$selecteの値に応じてselected属性を対象の<option>タグ内にechoさせることで実現できます。

6-3. ランキングの表示

6-3-1. テーブルの表示
ranking.php
        <div class="table">
            <table class="s-tbl">
                <thead>
                    <tr>
                        <th>順位</th>
                        <th>名前</th>
                        <th>難易度</th>
                        <th>回答時間</th>
                        <th>リセット回数</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($players as $key => $player): ?>
                    <tr>
                        <td><?= ++$key; ?></td>
                        <td><?= $player['name']; ?></td>
                        <td><?= $player['difficulty']; ?></td>
                        <td><?= $player['time']; ?></td>
                        <td><?= $player['reset']; ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
「find_the_mistake.php」と同様に、foreach構文によって配列となっている$playersのデータを<td>タグ内で出力しています。

foreach構文には、

foreach (array_expression as $value)

foreach (array_expression as $key => $value)

の2種類の構文があり、今回は後者を用いることで配列のインデックス番号$keyを取得し、順位を表現しています。

PHPマニュアル「PHP: foreach - Manual」

順位は1から始まりますが、配列のインデックス番号は0から始まるので、プレインクリメント（前置加算子）<?= ++$key; ?>を用いることで、+1させてから出力しています¹。

PHPマニュアル「PHP: 加算子/減算子 - Manual」

6-3-2. テーブルデザイン
style.css
.table {
  margin-top: 10px;
}
.s-tbl {
  border-collapse: collapse;
}
.s-tbl th, .s-tbl td {
  border: 1px solid #000;
  padding: 0.5em;
}
.s-tbl tr:nth-child(even) {
  background: #eee;
}
.s-tbl tr:hover {
  background: #ffffe0;
}
テーブルのCSSに関しては下記の記事から引っ張ってきたものに、少しだけ手を加えました。

ご参考までに。

いつか誰かの役に立つかもしれないweb制作屋の備忘録
「css tableで背景色を交互に変える方法」

これでようやく完成です！

ここまで読んでくださりありがとうございます。

何かおかしなところや改善できる点がございましたら、コメントいただけるとありがたいです！

<< 前の記事【⑤結果表示ページを作る】

5. 結果表示ページ
 5-1. 無効なアクセスの拒否
 5-2. 回答時間の算出
 5-3. 各変数への格納
 5-4. 保存する回答時間とカウント数の上限設定
 5-5. 正解／不正解による表示メッセージの分岐
 5-6. ランキングへの登録
 5-6-1. ランキングテーブルの構成
 5-6-2. ランキングへの登録

インクリメント・デクリメントにはそれぞれプレ（前置）とポスト（後置）があり、それぞれ2連続の加算子／減算子が前と後に付きます（$aへの加算なら++$aと$a++）。これらは「変数返す前に加算／減算する」か「変数を返した後に加算／減算する」かといった違いがあり、今回のコードでポストインクリメント($key++)を用いてしまうとechoされた後に+1されてしまい、意味をなさなくなります。 ↩

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T20:21:37+09:00

HTML・PHP・MySQLだけで作る間違い探しゲーム【⑤結果表示ページを作る】

5. 結果表示ページ

<< 前の記事【④DB接続設定ファイルを作る】

4. DB接続設定
 4-1. PDOオブジェクトの属性
 4-1-1. フェッチ形式の指定
 4-1-2. エラーモードの設定
 4-1-2-1. エラーモードの違いによるエラー文の違い
 4-1-3. エミュレーションの設定
 4-1-3-1. プリペアドステートメント
 4-1-3-2. エミュレーション
 4-2. 例外発生時の処理
 4-2-1. HTTPヘッダの送信
 4-2-2. 処理の中断とエラーメッセージの表示
 4-3. DBの切断について

次の記事 >> 【⑥ランキングページを作る】

6. ランキング表示ページ
 6-1. 表示切り替え機能
 6-2. 入力値の保持 - タグ
 6-3. ランキングの表示
 6-3-1. テーブルの表示
 6-3-2. テーブルデザイン

ここではプレイヤーの回答の正解／不正解、選択した難易度、回答時間、リセット回数を表示するページを実装します。
result.php
<?php

// セッションの開始
session_start();

// 無効なアクセスの拒否
if (empty($_SESSION) || empty($_POST)) {
    header('Location:start.php');
    exit();
}

// 回答時間を算出
$end_time = microtime(true);
$start_time = $_SESSION['start_time'];
$time = sprintf('%05.2f', $end_time - $start_time) . '秒';

// 回答を変数に格納
$answer = $_POST['answer'];

// セッション変数を変数に格納
$name = $_SESSION['name'];
$difficulty = $_SESSION['difficulty'];
$permission = $_SESSION['permission'];
$correct = $_SESSION['correct'];
$count = sprintf('%02d', $_SESSION['count']) . '回';

// セッションの放棄
$_SESSION = [];
setcookie(session_name(), '', time() - 1, '/');
session_destroy();

// 回答時間が100秒以上の場合は値を上書き
if ($time > 100) {
    $time = '100秒以上';
}

// リセット回数が100回以上の場合は値を上書き
if ($count > 100) {
    $count = '100回以上';
}

// 正解・不正解によるメッセージの分岐
if (html_entity_decode($correct) === $answer) {
    $result = '正解です！';
} else {
    $result = '不正解です。。。';
}

// 正解かつ許可されていた場合のみDBに登録
if ($result === '正解です！' && $permission === '許可する') {

    // db接続
    require_once('db_connect.php');

    // 新規登録処理
    $sql = 'INSERT INTO rankings (name, difficulty, time, reset) VALUES (?, ?, ?, ?)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute([$name, $difficulty, $time, $count]);
}

?>
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <title>間違い探し</title>
    </head>
    <body>
        <h1><small>結果は...<?= $result ?></small></h1>
        <h2><small>難易度: <?= $difficulty; ?></small></h2>
        <h2><small>回答時間: <?= $time; ?></small></h2>
        <h2><small>リセット回数: <?= $count; ?></small></h2>
        <button type="button" onclick="location.href='start.php'">スタートページへ</button>
        <button type="button" onclick="location.href='ranking.php'">ランキングページへ</button></a>
    </body>
</html>
セッションの開始や放棄はこれまでのファイルと同様です。

5-1. 無効なアクセスの拒否
result.php
// 無効なアクセスの拒否
if (empty($_SESSION) || empty($_POST)) {
    header('Location:start.php');
    exit();
}
「find_the_mistake.php」ではセッション変数のみ存在確認していましたが、
ここでは押されたボタンのvalue属性の値がPOSTされているはずなので、
その値がない場合もスタートページへリダイレクトさせています。

5-2. 回答時間の算出
result.php
// 回答時間を算出
$end_time = microtime(true);
$start_time = $_SESSION['start_time'];
$time = sprintf('%05.2f', round($end_time - $start_time, 2)) . '秒';
「find_the_mistake.php」で開始時間を記録したのと同様に、
microtime()を用いて終了時間を$end_timeへ格納しています。

次いで、開始時間をセッション変数から$start_timeへ格納し、
sprintf()の第2引数として「終了時間 - 開始時間」の引き算¹の結果を渡しています。

sprintf()は第2引数の文字列を、
第1引数で指定したフォーマットの文字列に変換する関数で、
%05.2fは「小数第2位まで0埋めした5桁の浮動小数点数」を意味します。

これらの各文字は指定子といい、次のような書式で記述します。
[%][符号指定子][パディング指定子][アライメント指定子][表示幅指定子][精度指定子][型指定子]

今回は符合指定子とアライメント指定子以外を下記のように指定しています。

%・・・フォーマットとして認識させるためのパーセント文字²

0・・・パディング指定子 指定した桁数に満たない部分を埋める記号（今回は0埋め）

5・・・表示幅指定子 表示する最低桁数

.2・・・精度指定子 小数点以下の最低表示桁数

f・・・型指定子 引数を浮動小数点数として表示する

詳細に関しては下記もご確認下さい。

PHPマニュアル「PHP: sprintf - Manual」
Let'sプログラミング～初心者の方を対象としたプログラミングの総合学習サイト～
「指定の形式にフォーマット(sprintf) - 文字列関数 - PHP関数」

変換後、最後尾には文字「秒」を連結させ、変数$timeへ格納しています。

これで回答時間が算出できました。

5-3. 各変数への格納
result.php
// 回答を変数に格納
$answer = $_POST['answer'];

// セッション変数を変数に格納
$name = $_SESSION['name'];
$difficulty = $_SESSION['difficulty'];
$permission = $_SESSION['permission'];
$correct = $_SESSION['correct'];
$count = sprintf('%02d', $_SESSION['count']) . '回';
POSTされた値はプレイヤーの回答として$answerに格納しています。

またこのページからの望ましくないページ遷移を防ぐため³、
スタートページと同様にセッションを放棄させます。

問題表示ページのアクセス制限は$_SESSIONが定義されているかどうかで判定しているので、結果ページへやってきた時点でセッションを放棄させることによりよって問題表示ページヘ戻ることは出来なくなります。

これにより、PHPのスクリプト終了時点でセッション変数は初期化されているので、`タグ内で出力させるためには別の変数に格納しておく必要があります。

リセット回数は上記「回答時間の算出」で用いたsprintf()を用いて、「0埋めした2桁の小数値」のフォーマットに変換してから$countに格納しています。

5-4. 保存する回答時間とカウント数の上限設定
result.php
// 回答時間が100秒以上の場合は値を上書き
if ($time > 100) {
    $time = '100秒以上';
}

// リセット回数が100回以上の場合は値を上書き
if ($count > 100) {
    $count = '100回以上';
}
ここまでのコードでは回答時間とリセット回数に上限は存在しません。

このままでも構わないのですが、

ランキングページのレイアウト崩れ

不必要に大きな値が表示されること

を事前に防ぐために、今回は制限を追加します。

具体的には、DBに保存される回答時間・カウント数がそれぞれ

100秒以上

100回以上

の場合に定型文に置き換わるようにしています。

5-5. 正解／不正解による表示メッセージの分岐
result.php
// 正解・不正解によるメッセージの分岐
if (html_entity_decode($correct) === $answer) {
    $result = '正解です！';
} else {
    $result = '不正解です。。。';
}
最も初歩的な条件分岐です。

注意点として、選択された文字が絵文字だった場合
$answerに格納された値（=POSTされた値）はブラウザへの出力を経ている為、
HTMLエンティティとしてではなく絵文字そのものとなっています。

一方、$correctに格納された値（=セッション変数として保持されていた値）は、
ブラウザへの出力はないままなのでHTMLエンティティのまま格納されています。

この為、正解かどうか(=文字列が一致しているかどうか)を判定する為には、

セッション変数として保持されていた値をデコードする

POSTされた値をHTMLエンティティ化する

のどちらかを実行する必要があります。今回は前者を採用しています。

PHPマニュアル「PHP: html_entity_decode - Manual」

後者の場合は下記関数が使えます。

PHPマニュアル「PHP: htmlentities - Manual」

5-6. ランキングへの登録

プレイヤーの回答が正解かつランキングへの登録が許可されている場合のみ、DBへ保存します。
result.php
// 正解かつ許可されていた場合のみDBに登録
if ($result === '正解です！' && $permission === '許可する') {

    // db接続
    require_once('db_connect.php');

    // 新規登録処理
    $sql = 'INSERT INTO rankings (name, difficulty, time, reset) VALUES (?, ?, ?, ?)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute([$name, $difficulty, $time, $count]);
}
5-6-1. ランキングテーブルの構成

まず先に、データを保存するランキングテーブルを構築する必要があります。詳しい方法については「MySQL テーブル作成」などで検索すればたくさんヒットすると思いますので、ここでは割愛します。
create_rankings_table.sql
CREATE TABLE `rankings` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `difficulty` varchar(10) NOT NULL,
  `time` varchar(10) NOT NULL,
  `reset` varchar(15) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
一般的なテーブル構造となっています。各カラムの概要は以下です。

id・・・主キー／符号なし／オートインクリメント属性が指定されたidカラム

name・・・入力された名前を保存する可変長文字列／20字までのカラム

difficulty・・・選択された難易度を保存する可変長文字列／10字までのカラム

time・・・回答時間を保存する可変長文字列／10字までのカラム

reset・・・リセット回数を保存する可変長文字列／15字までのカラム

いわゆる「寿司ビール問題」を回避する為に、文字セットはutf8mb4を、照合順序はutf8mb4_binをデフォルトとしています。

Qiita「寿司ビール問題①　初心者→中級者へのSTEP20/25 - Qiita」 by @kamohicokamo さん

また脚注1.でも少し触れましたが、一部の絵文字の文字数が正常にカウントされません（2倍にカウントされる)ので、nameカラムの文字数上限はバリデーションで制限した10文字の2倍の20文字としてます。

テーブル名やカラム名の命名規則に関しては下記記事が参考になります。

Qiita「データベースオブジェクトの命名規約 - Qiita」 by @genzouw さん

5-6-2. ランキングへの登録
result.php
    // db接続
    require_once('db_connect.php');

    // 新規登録処理
    $sql = 'INSERT INTO rankings (name, difficulty, time, reset) VALUES (?, ?, ?, ?)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute([$name, $difficulty, $time, $count]);
require_once()でDB接続設定用ファイル「db_connect.php」を呼び出してDB接続した後、INSERT文を用いてrankingsテーブルにデータを挿入しています。

SQL文では疑問符プレースホルダーを用いたSQL文を記述し、prepare()メソッドによってDBサーバーにプリペアドステートメントとしてSQL文を渡しています（今回のDB接続設定では静的プレースホルダーを選択している為）。

PHPマニュアル「PHP: PDO - Manual」
PHPマニュアル「PHP: PDO::prepare - Manual」

prepare()メソッドはPDOStatementオブジェクトを返すのでそれを$stmtに格納し、次いでPDOStatementクラスのexecute()メソッドによって、

execute()の引数に渡された値とプレースホルダーのバインド

SQL文の実行

を行っています。

PHPマニュアル「PHP: PDOStatement - Manual」
PHPマニュアル「PHP: PDOStatement::execute - Manual」

bindValue()メソッドを使用せずにexecute()の引数に値を配列として渡す場合の注意点として、NULL以外はすべてPDO::PARAM_STR扱いになるというのがあります。

今回のテーブルはidカラム以外はすべて可変長文字列カラムですので問題ありませんが、テーブルのカラムが文字列以外の型の場合はexecute()の引数は空にし、prepare()とexecute()の間でbindValue()メソッドを使用した方がいいです。

Qiita「PHPでデータベースに接続するときのまとめ - PDO::prepare → PDOStatement::execute の2ステップでクエリを実行する - Qiita」 by @mpyw さん

結果を表示させ、ランキングに登録させることができました。
最後に、ランキングページを実装します。

<< 前の記事【④DB接続設定ファイルを作る】

4. DB接続設定
 4-1. PDOオブジェクトの属性
 4-1-1. フェッチ形式の指定
 4-1-2. エラーモードの設定
 4-1-2-1. エラーモードの違いによるエラー文の違い
 4-1-3. エミュレーションの設定
 4-1-3-1. プリペアドステートメント
 4-1-3-2. エミュレーション
 4-2. 例外発生時の処理
 4-2-1. HTTPヘッダの送信
 4-2-2. 処理の中断とエラーメッセージの表示
 4-3. DBの切断について

次の記事 >> 【⑥ランキングページを作る】

6. ランキング表示ページ
 6-1. 表示切り替え機能
 6-2. 入力値の保持 - タグ
 6-3. ランキングの表示
 6-3-1. テーブルの表示
 6-3-2. テーブルデザイン

microtime()のタイムスタンプはUnixエポックからの経過マイクロ秒数となっています。このため「新しい方（ここでは$end_time）」から「古い方（$start_time）」を引いた値が経過時間のマイクロ秒数となります。 ↩

これがないと各文字が指定子として認識されなくなります。また、この文字自体は指定子ではありません。 ↩

例えば「このページからもう一度問題表示ページへ戻る」などです。個人的には「問題表示ページから結果ページへは一方通行としたい」と考えたためこのような実装となりました。 ↩

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T20:21:14+09:00

HTML・PHP・MySQLだけで作る間違い探しゲーム【④DB接続設定ファイルを作る】

4. DB接続設定

<< 前の記事【③問題表示ページを作る】

3. 問題表示ページの実装
 3-1. 無効なアクセスの拒否
 3-2. リセット回数の計測
 3-3. 問題用文字配列の設定
 3-3-1. 文字ペア配列の選択
 3-3-2. 文字ペア配列及び文字ペアのシャッフル
 3-3-3. 正解文字と不正解文字配列の設定
 3-3-3-1. 正解文字の設定
 3-3-3-2. 不正解文字配列の設定
 3-3-4. 問題用文字配列の生成
 3-4. 開始時刻の記録
 3-5. 選択肢の描写

次の記事 >> 【⑤結果表示ページを作る】

5. 結果表示ページ
 5-1. 無効なアクセスの拒否
 5-2. 回答時間の算出
 5-3. 各変数への格納
 5-4. 保存する回答時間とカウント数の上限設定
 5-5. 正解／不正解による表示メッセージの分岐
 5-6. ランキングへの登録
 5-6-1. ランキングテーブルの構成
 5-6-2. ランキングへの登録

この記事ではDB接続設定ファイル「db_connect.php」を作成します。必要となったところでこのファイルを呼び出すことで、いちいちPDOオブジェクトの属性などを記述しなくても済みます。
db_connect.php
<?php

// 定数定義
const PDO_DSN = 'mysql:host=localhosts;dbname=[FILTERED];charset=utf8mb4';
const USERNAME = '[FILTERED]';
const PASSWORD = '[FILTERED]';

// DB接続
try {
    $dbh = new PDO(PDO_DSN, USERNAME, PASSWORD, [
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
} catch (PDOException $e) {
    header('Content-Type: text/plain; charset=UTF-8', true, 500);
    exit('DB接続に失敗しました' . '<br>' . PHP_EOL . $e->getMessage());
}
冒頭でも述べましたが、DB構築については本記事では扱いません。
接続にはPDOオブジェクトを使用しています。

PDOのインスタンスを作成する為にはPDO()を用いて

第1引数・・・DSN（必須）

第2引数・・・ユーザーネーム（任意）

第3引数・・・パスワード（任意）

第4引数・・・PDOオブジェクトの属性（任意）

を渡す必要があります。

今回のコードでは第1〜第3引数を定数としてを定義してますが、変数定義や引数に直接渡す書き方でも問題ありません。

また、DBにはプレイヤーが入力した名前を保存します。このため絵文字を取り扱う可能性があるので、文字コードはutf8ではなくマルチバイト対応のuft8mb4を指定しています。

コピペする際は、[FILTERED]を適切な値に置き換えて下さい。

PHPマニュアル「PHP: 接続、および接続の管理 - Manual」
PHPマニュアル「PHP: PDO::__construct - Manual」

4-1. PDOオブジェクトの属性

属性に関する公式リファレンスは以下を参照願います。
PHPマニュアル「PHP: PDO::setAttribute - Manual」

また下記Qiita記事が体系的にまとめられており、とても参考になります。ぜひ一読を！

Qiita「PHPでデータベースに接続するときのまとめ - Qiita」 by @mpyw さん
Qiita「【PHP超入門】クラス～例外処理～PDOの基礎 - Qiita」 by @7968 さん

4-1-1. フェッチ形式の指定
db_connect.php
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
PDO::FETCH_ASSOC: は、結果セットに返された際のカラム名で添字を付けた配列を返します。
引用元：PHPマニュアル「PHP: PDOStatement::fetch - Manual」

SQL文で得られた結果をフェッチする際の形式を指定しています。
カラム名がキーとなった配列で返ってくるので直感的に操作しやすいです。
この形式がもっともスタンダードな気がします。

4-1-2. エラーモードの設定
db_connect.php
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
エラーモードは3種類あり、PDO::ERRMODE_EXCEPTIONはエラー発生時にPDOExceptionの例外を発生させます。

また、エラーコードだけでなくその関連情報も返してくれるようにもなります。

どれに設定するかは開発する物や状況によると思いますが、今回はPDO::ERRMODE_EXCEPTIONを選択しました。

4-1-2-1. エラーモードの違いによるエラー文の違い

以下は本ゲームの「result.php」の56行目において、テーブルのカラムとは異なるカラム名を指定したINSERT文を実行した際のエラー文の比較です。

PDO::ERRMODE_SILENT
Fatal error: Uncaught Error: Call to a member function execute() on boolean in /path/to/result.php:58 Stack trace: #0 {main} thrown in /path/to/result.php on line 58
PDO::ERRMODE_WARNING
Warning: PDO::prepare(): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'namae' in 'field list' in /path/to/result.php on line 57

Fatal error: Uncaught Error: Call to a member function execute() on boolean in /path/to/result.php:58 Stack trace: #0 {main} thrown in /path/to/result.php on line 58
PDO::ERRMODE_EXCEPTION
Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'namae' in 'field list' in /path/to/result.php:57 Stack trace: #0 /path/to/result.php(57): PDO->prepare('INSERT INTO ran...') #1 {main} thrown in /path/to/result.php on line 57
デフォルトのPDO::ERRMODE_SILENTでは単に「SQL文を実行するexecute()でエラーが起こった」と表示されるだけであるのに対し、PDO::ERRMODE_WARNINGとPDO::ERRMODE_EXCEPTIONでは「指定されたカラムが見つからない」とより具体的なエラー文となっています。

また、PDO::ERRMODE_WARNINGではE_WARNINGメッセージが追加されており、PDO::ERRMODE_EXCEPTIONでは例外がPDOExceptionクラスになっているという違いもあります。

PHPマニュアル「エラーおよびエラー処理 - Manual」

4-1-3. エミュレーションの設定
db_connect.php
        PDO::ATTR_EMULATE_PREPARES => false
プリペアドステートメントのエミュレーションに関する設定です。

4-1-3-1. プリペアドステートメント

直訳すると「準備された記述」になりますが、一文で言うと
「後から値を入れる部分を別の文字・単語などで仮置きしたSQL文」
かなと思います。

プリペアドステートメントを用いる利点は以下の2つです。

後から入れる値のみを変更しながら、何度も使用できる

SQLインジェクション対策になる

下記記事もご参照ください。

Qiita「【PHP超入門】クラス～例外処理～PDOの基礎 - プリペアドステートメント - Qiita」 by @7968 さん

4-1-3-2. エミュレーション

trueかfalse（ONかOFF）かによってプリペアドステートメントの挙動が変化します。

上述のプリペアドステートメントにおいて、仮置きする文字・単語のことをプレースホルダーといい、

疑問符プレースホルダー

名前つきプレースホルダー

の2種類があり、今回の記事では前者に統一しています。

さらにプレースホルダー自体にも

静的プレースホルダー

動的プレースホルダー

の2種類のタイプがあり、エミュレーションをfalseとすることで静的プレースホルダーが用いられるようになります。

静的プレースホルダーを選択する理由としては、よりセキュアである為です。

こちらに関しては下記記事が参考になります。

Qiita「【PHP超入門】クラス～例外処理～PDOの基礎 - 静的プレースホルダと動的プレースホルダ - Qiita」 by @7968 さん

また、エミュレーションのON／OFFによる挙動の違いに関しては下記記事が参考になります。

Qiita「PHPでデータベースに接続するときのまとめ - エミュレーションに関するまとめ - Qiita」 by @mpyw さん

更に、下記質問もご参考までに。

Teratail「`PDO::ATTR_EMULATE_PREPARES => false`は必要か？」

4-2. 例外発生時の処理
db_connect.php
} catch (PDOException $e) {
    header('Content-Type: text/plain; charset=UTF-8', true, 500);
    exit('DB接続に失敗しました' . PHP_EOL . $e->getMessage() . PHP_EOL);
}
catch()を用いることで、発生した例外を捕捉することができます。

PHPマニュアル「PHP: 例外(exceptions) - Manual」

PDOExceptionが発生した例外のクラス名で、$eが発生した例外のクラスから作成したインスタンスを代入する変数となっています。

4-2-1. HTTPヘッダの送信

エラーメッセージを表示する際、webブラウザにエラーメッセージを「単なるテキストである」と解釈してもらうため、header()を用いてMINEタイプを設定しています。

また、第3引数にはHTTPレスポンスステータスコードを指定し、
サーバー側のエラーであることを明示しています。

MDN web docs「MIME タイプ (IANA メディアタイプ) - HTTP | MDN」
MDN web docs「HTTP レスポンスステータスコード - HTTP | MDN」

4-2-2. 処理の中断とエラーメッセージの表示

続くexit()によって、

自作のエラー文

発生した例外に関するエラーメッセージ

を出力させ、後続の処理を中断させています。

このファイルを呼び出しているということは、DBのアクセスを必要とする処理を行うはずなので、接続に失敗した場合には後続の処理も失敗する可能性が高いためです。

PHPマニュアル「PHP: exit - Manual」

例外に関するエラーメッセージは、$e->getMessage()によってインスタンスのgetMessage()メソッドにアクセスすることで取得しています。

PHPマニュアル「PHP: Error::getMessage - Manual」

PHP_EOLはPHPの定義済み定数で、プラットフォームの行末文字を意味します（EOLは"End Of Line"の略です）。

この定数は、OSを自動判定して行末文字を選定してくれますので、サーバーのOSを気にすることなく改行して表示させることができます。

PHPマニュアル「PHP: 定義済みの定数 - Manual」

4-3. DBの切断について

PDOインスタンスを格納した変数にNULLを代入することで、DBから切断させることができます（今回の場合なら$dbh = NULL）。

接続を閉じるには、他から参照されていないことを保障することでオブジェクトを破棄する必要があります。それには、オブジェクトを保持している変数に対して NULL を代入します。
引用元：PHPマニュアル「PHP: 接続、および接続の管理 - Manual」

しかし、

明示的にこれを行わなかった場合は、スクリプトの終了時に自動的に接続が閉じられます。
引用元：PHPマニュアル「PHP: 接続、および接続の管理 - Manual」

とも書かれており、あえて記述する必要はないかと思われます。

下記もご参照下さい。

Qiita「PHPでデータベースに接続するときのまとめ - データベース接続の切断 - Qiita」 by @mpyw さん

<< 前の記事【③問題表示ページを作る】

3. 問題表示ページの実装
 3-1. 無効なアクセスの拒否
 3-2. リセット回数の計測
 3-3. 問題用文字配列の設定
 3-3-1. 文字ペア配列の選択
 3-3-2. 文字ペア配列及び文字ペアのシャッフル
 3-3-3. 正解文字と不正解文字配列の設定
 3-3-3-1. 正解文字の設定
 3-3-3-2. 不正解文字配列の設定
 3-3-4. 問題用文字配列の生成
 3-4. 開始時刻の記録
 3-5. 選択肢の描写

次の記事 >> 【⑤結果表示ページを作る】

5. 結果表示ページ
 5-1. 無効なアクセスの拒否
 5-2. 回答時間の算出
 5-3. 各変数への格納
 5-4. 保存する回答時間とカウント数の上限設定
 5-5. 正解／不正解による表示メッセージの分岐
 5-6. ランキングへの登録
 5-6-1. ランキングテーブルの構成
 5-6-2. ランキングへの登録

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T20:20:53+09:00

HTML・PHP・MySQLだけで作る間違い探しゲーム【③問題表示ページを作る】

3. 問題表示ページの実装

<< 前の記事【②スタートページを作る】

2. スタートページの実装
 2-1. セッション
 2-1-1. セッションの開始
 2-1-2. セッションの放棄
 2-2. バリデーション
 2-2-1. 無効な送信の拒否
 2-2-2. 名前のバリデーション
 2-2-2-1. 半角スペースのみ無効
 2-2-2-2. 文字数
 2-2-2-3. 空白文字／制御文字無効
 2-2-3. エラーメッセージの表示
 2-2-4. 入力値の保持 - タグ
 2-3. 問題表示ページへの遷移

次の記事 >> 【④DB接続設定ファイルを作る】

4. DB接続設定
 4-1. PDOオブジェクトの属性
 4-1-1. フェッチ形式の指定
 4-1-2. エラーモードの設定
 4-1-2-1. エラーモードの違いによるエラー文の違い
 4-1-3. エミュレーションの設定
 4-1-3-1. プリペアドステートメント
 4-1-3-2. エミュレーション
 4-2. 例外発生時の処理
 4-2-1. HTTPヘッダの送信
 4-2-2. 処理の中断とエラーメッセージの表示
 4-3. DBの切断について

この記事ではゲームのコアとなる問題を生成するページを実装します。

難易度「易しい」の問題例

難易度「難しい」の問題例
find_the_mistake.php
<?php

// セッションの再開
session_start();

// 無効なアクセスの拒否
if (empty($_SESSION)) {
    header('Location:start.php');
    exit();
}

// リセット回数の計測開始
if (isset($_SESSION['count'])) {
    $_SESSION['count']++;
} else {
    $_SESSION['count'] = 0;
}

// ターゲット配列の設定
if ($_SESSION['difficulty'] === '難しい（漢字）') {
    $chars = [
        ['猫', '描'],
        ['犬', '大'],
        ['幸', '辛'],
        ['白', '臼'],
        ['矢', '失'],
        ['力', '刀'],
        ['防', '妨'],
        ['土', '士'],
        ['卵', '卯'],
        ['巨', '臣'],
        ['寒', '塞'],
        ['旅', '族'],
        ['車', '東'],
        ['釘', '針']
    ];
} else {
    $chars = [
        ['&#x1f415;', '&#x1f408;'],
        ['&#x1f405;', '&#x1f406;'],
        ['&#x1f98e;', '&#x1f40d;'],
        ['&#x1f433;', '&#x1f42c;'],
        ['&#x1f339;', '&#x1f337;'],
        ['&#x1f34a;', '&#x1f34b;'],
        ['&#x1f34e;', '&#x1f351;'],
        ['&#x1f955;', '&#x1f336;'],
        ['&#x1f96f;', '&#x1f95e;'],
        ['&#x1f358;', '&#x1f359;'],
        ['&#x1f341;', '&#x1f342;'],
        ['&#x1f332;', '&#x1f333;'],
        ['&#x1f47b;', '&#x1f47d;'],
        ['&#x1f396;', '&#x1f3c5;']
    ];
}

// ターゲット配列のシャッフル
shuffle($chars);
for ($i = 0; $i < count($chars); $i++) {
    shuffle($chars[$i]);
}

// 正解と選択対象配列を設定
$correct = $chars[0][0];
$_SESSION['correct'] = $correct;
for ($j = 0; $j <= 19; $j++) {
    for ($k = 0; $k <= 19; $k++) {
        $targets[$j][$k] = $chars[0][1];
    }
}

// 正解のターゲットを選択対象配列に1つだけ挿入
$key = range(0, 19);
$key1 = array_rand($key);
$key2 = array_rand($key);
$targets[$key1][$key2] = $correct;

// 開始時刻を記録
$_SESSION['start_time'] = microtime(true);

?>
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <title>間違い探し</title>
    </head>
    <body>
        <h1><?= $correct; ?>を見つけよう！</h1>
        <p>
            <small>難易度: <?= $_SESSION['difficulty']; ?></small>
            <button type="button" onclick="location.href='find_the_mistake.php'">分かるか！（リセット）</button>
            <button type="button" onclick="location.href='start.php'">スタートページへ</button>
        </p>
        <form action="result.php" method="POST">
            <table>
                <?php foreach ($targets as $target): ?>
                <tr>
                    <?php for ($l = 0; $l < count($target); $l++): ?>
                    <td><input type="submit" name="answer" value="<?= $target[$l]; ?>"></td>
                    <?php endfor; ?>
                </tr>
                <?php endforeach; ?>
            </table>
        </form>
    </body>
</html>
3-1. 無効なアクセスの拒否
find_the_mistake.php
// 無効なアクセスの拒否
if (empty($_SESSION)) {
    header('Location:start.php');
    exit();
}
不正な画面遷移とエラーを防ぐために、
「start.php」で設定したセッション変数がない場合は、
スタートページにリダイレクトされるようにしています。

3-2. リセット回数の計測

このゲームでは、問題が難しい場合にリセットできる機能を実装しており、
リセットボタンが押されると問題生成ページが再描写されます。

この時、セッション変数にcountをキーとしたリセット回数を記録させています。

セッション変数とすることで、結果ページでもリセット回数を取り扱えるようになります。
find_the_mistake.php
// リセット回数の計測開始
if (isset($_SESSION['count'])) {
    $_SESSION['count']++;
} else {
    $_SESSION['count'] = 0;
}
既に設定されていた場合はインクリメント

それ以外では0を設定

とすることでリセット回数が正しくカウントされるようにしています。

3-3. 問題用文字配列の設定
find_the_mistake.php
// ターゲット配列の設定
if ($_SESSION['difficulty'] === '難しい（漢字）') {
    $chars = [
        ['猫', '描'],
        ['犬', '大'],
        ['幸', '辛'],
        ['白', '臼'],
        ['矢', '失'],
        ['力', '刀'],
        ['防', '妨'],
        ['土', '士'],
        ['卵', '卯'],
        ['巨', '臣'],
        ['寒', '塞'],
        ['旅', '族'],
        ['車', '東'],
        ['釘', '針']
    ];
} else {
    $chars = [
        ['&#x1f415;', '&#x1f408;'],
        ['&#x1f405;', '&#x1f406;'],
        ['&#x1f98e;', '&#x1f40d;'],
        ['&#x1f433;', '&#x1f42c;'],
        ['&#x1f339;', '&#x1f337;'],
        ['&#x1f34a;', '&#x1f34b;'],
        ['&#x1f34e;', '&#x1f351;'],
        ['&#x1f955;', '&#x1f336;'],
        ['&#x1f96f;', '&#x1f95e;'],
        ['&#x1f358;', '&#x1f359;'],
        ['&#x1f341;', '&#x1f342;'],
        ['&#x1f332;', '&#x1f333;'],
        ['&#x1f47b;', '&#x1f47d;'],
        ['&#x1f396;', '&#x1f3c5;']
    ];
}

// ターゲット配列のシャッフル
shuffle($chars);
for ($i = 0; $i < count($chars); $i++) {
    shuffle($chars[$i]);
}

// 正解と選択対象配列を設定
$correct = $chars[0][0];
$_SESSION['correct'] = $correct;
for ($j = 0; $j <= 19; $j++) {
    for ($k = 0; $k <= 19; $k++) {
        $targets[$j][$k] = $chars[0][1];
    }
}

// 正解のターゲットを選択対象配列に1つだけ挿入
$key = range(0, 19);
$key1 = array_rand($key);
$key2 = array_rand($key);
$targets[$key1][$key2] = $correct;
3-3-1. 文字ペア配列の選択

選択された難易度によって漢字か絵文字かを分岐させます。
find_the_mistake.php
// ターゲット配列の設定
if ($_SESSION['difficulty'] === '難しい（漢字）') {
    $chars = [
        ['猫', '描'],
        ['犬', '大'],
        ['幸', '辛'],
        ['白', '臼'],
        ['矢', '失'],
        ['力', '刀'],
        ['防', '妨'],
        ['土', '士'],
        ['卵', '卯'],
        ['巨', '臣'],
        ['寒', '塞'],
        ['旅', '族'],
        ['車', '東'],
        ['釘', '針']
    ];
} else {
    $chars = [
        ['&#x1f415;', '&#x1f408;'],
        ['&#x1f405;', '&#x1f406;'],
        ['&#x1f98e;', '&#x1f40d;'],
        ['&#x1f433;', '&#x1f42c;'],
        ['&#x1f339;', '&#x1f337;'],
        ['&#x1f34a;', '&#x1f34b;'],
        ['&#x1f34e;', '&#x1f351;'],
        ['&#x1f955;', '&#x1f336;'],
        ['&#x1f96f;', '&#x1f95e;'],
        ['&#x1f358;', '&#x1f359;'],
        ['&#x1f341;', '&#x1f342;'],
        ['&#x1f332;', '&#x1f333;'],
        ['&#x1f47b;', '&#x1f47d;'],
        ['&#x1f396;', '&#x1f3c5;']
    ];
}
正解・不正解のペア関係を維持するため¹に、
多次元配列として変数$charsに格納しています。

絵文字は&XXX;という形式で記述されていますが、
これはHTMLエンティティと呼ばれるもので、
これにより見えない文字や標準キーボードでは入力が難しい文字を容易に取り扱えます。

また、viエディタ上でのバグを防ぐこともできます²。

MDN web docs「Entity (エンティティ) - MDN Web Docs 用語集: ウェブ関連用語の定義 | MDN」

今回用いた各エンティティとwebページ上で表示される絵文字の対応はこちら

🐕 => 「?」と 🐈 => 「?」
🐅 => 「?」と 🐆 => 「?」
🦎 => 「?」と 🐍 => 「?」
🐳 => 「?」と 🐬 => 「?」
🌹 => 「?」と 🌷 => 「?」
🍊 => 「?」と 🍋 => 「?」
🍎 => 「?」と 🍑 => 「?」
🥕 => 「?」と 🌶 => 「?」
🥯 => 「?」と 🥞 => 「?」
🍘 => 「?」と 🍙 => 「?」
🍁 => 「?」と 🍂 => 「?」
🌲 => 「?」と 🌳 => 「?」
👻 => 「?」と 👽 => 「?」
🎖 => 「?」と 🏅 => 「?」

絵文字のHTMLエンティティを調べるには下記サイトが参考になります。

Let's EMOJI「Unicode 13.0 絵文字 (Unicode 13.0 Emoji) | Let's EMOJI」

3-3-2. 文字ペア配列及び文字ペアのシャッフル

難易度がどちらの場合でも、正解・不正解のペアは14組あるので、
正解・不正解関係は保ったままでこれらをシャッフルし、問題にランダム性を付与します。
これによりリセットする度に異なる問題が表示されるようになります。
find_the_mistake.php
// ターゲット配列のシャッフル
shuffle($chars);
for ($i = 0; $i < count($chars); $i++) {
    shuffle($chars[$i]);
}
配列のシャッフルにはshuffle()が使えます。

shuffle()は「シャッフルされた配列」ではなく、
「シャッフルが成功したかどうかの真偽値」を返しますので、
変数に格納する必要はありません。

PHPマニュアル「PHP: shuffle - Manual」

まず、文字ペアの多次元配列を格納した$charsをシャッフルし、
「14組の文字ペアの並び（=多次元配列の第1層）」をランダムにします。

続いて、for構文を用いて再帰的にシャッフルすることで、
「文字ペアの中での並び（=多次元配列の第2層）」もランダムにします³。

こうすることで「ペアの中でいつもどちらかが正解／不正解」となってしまうのを防ぎ、
ゲーム性を保てます。

正解・不正解のペアが14組あり、ペアの中でも正解・不正解が入れ替わることから、
各難易度毎に14 × 2 = 28通りの問題を生成できます（ゲーム全体では56通り）。

最初に変数$charsに格納した文字ペアの多次元配列にペアを加えることで、
更にパターンを増やすこともできます。

3-3-3. 正解文字と不正解文字配列の設定

ランダムにシャッフルされた文字ペア配列から1文字だけ選択して正解の文字とします。
次いで、「正解の文字とペアになっている文字」を用いて不正解文字の配列を生成します。
find_the_mistake.php
// 正解と選択対象配列を設定
$correct = $chars[0][0];
$_SESSION['correct'] = $correct;
for ($j = 0; $j <= 19; $j++) {
    for ($k = 0; $k <= 19; $k++) {
        $targets[$j][$k] = $chars[0][1];
    }
}
3-3-3-1. 正解文字の設定

$correctに正解とする文字を代入しています。
文字ペアの多次元配列を再帰的にシャッフルしたので、
$chars[0][0]には、
「28（14 × 2）文字からランダムに選択された1文字」
が格納されています。

結果ページを生成するファイルで正解／不正解の判定を行うので、
セッション変数にもcorrectをキーとして正解文字を格納しています。

3-3-3-2. 不正解文字配列の設定

次いで、正解文字と対になる不正解文字を、
$targetsに20 × 20の多次元配列として格納しています。

対になる文字は$charsの正解文字と同じ階層にある異なるキーの値のはずなので、
今回のコードでは$chars[0][1]が該当します。

この不正解文字を、変数$j,$kに0 ~ 19の範囲で2重の繰り返し処理を回す中で、
$targetsに対しキーを[$j][$k]とした値とすることで、多次元配列を生成しています⁴。

これで正解文字と不正解文字配列の設定が完了しました。

3-3-4. 問題用文字配列の生成

不正解文字配列のランダムな1つの値のみを正解文字に置き換えることで、
問題用の文字配列を作り出します。
find_the_mistake.php
// 正解のターゲットを選択対象配列に1つだけ挿入
$key = range(0, 19);
$key1 = array_rand($key);
$key2 = array_rand($key);
$targets[$key1][$key2] = $correct;
まず、range()を用いて値が0 ~ 19の範囲の整数を値としてもつ配列を生成し、
$keyに格納しています。

その後、array_rand()によって$keyの配列からランダムにキーを取り出します。
これを2回繰り返してそれぞれ$key1と$key2に格納することで、
1 ~ 19の範囲内のランダムな整数を選択しました。

後はこの$key1と$key2を不正解文字配列$targetsのキーとして指定することで、
「多次元配列の中のランダムな1つの値」を指定しています⁵。

PHPマニュアル「PHP: range - Manual」
PHPマニュアル「PHP: array_rand - Manual」

これでようやく問題用の文字配列が完成しました！

3-4. 開始時刻の記録

回答時間を計測する為に問題が表示される直前に開始時刻を記録します。
find_the_mistake.php
// 開始時刻を記録
$_SESSION['start_time'] = microtime(true);
選択された文字の正解／不正解判定と同様に、結果ページで判定するので、
セッション変数として保存しています。

microtime()は現在のタイムスタンプをマイクロ秒まで返す関数で、

引数を指定しない場合はmsec sec形式の文字列

trueを指定した場合はfloat型のマイクロ秒

を、それぞれ返します。

PHPマニュアル「PHP: microtime - Manual」

結果ページでは引き算で回答時間を算出するので、
計算しやすいタイムスタンプ形式となるように引数にはtrueを指定しています。

3-5. 選択肢の描写

問題用の文字配列を、繰り返し処理によって描写し、20×20のボタンを表示させます。
find_the_mistake.php
        <form action="result.php" method="POST">
            <table>
                <?php foreach ($targets as $target): ?>
                <tr>
                    <?php for ($l = 0; $l < count($target); $l++): ?>
                    <td><input type="submit" name="answer" value="<?= $target[$l]; ?>"></td>
                    <?php endfor; ?>
                </tr>
                <?php endforeach; ?>
            </table>
        </form>
<form>タグの中でsubmitタイプの<input>タグを繰り返し表示させることで、
どのボタンを押しても「result.php」へ回答がPOSTされるようになっています。

不正解用文字配列を生成させた時と同様に、
2重の繰り返し処理によって多次元配列の値を表示させます。

まず<?php foreach ($targets as $target): ?>によって
第1層の繰り返し処理します。
繰り返しの対象を<tr>タグで囲んでおり、ボタン群の行の繰り返しに相当します。

上記foreach構文の中で更に繰り返し処理
<?php for ($l = 0; $l < count($target); $l++): ?>
を実行することで、第2層を表示させます⁶。
こちらは<td>タグで囲っており、各行内の列の繰り返しに相当します。

これらの繰り返しにより、
「20個の<td>タグ内のsubmitボタン」
が
「それぞれ20個の<tr>タグで囲われた」
計400個のボタン群を表示させることができます。

<< 前の記事【②スタートページを作る】

2. スタートページの実装
 2-1. セッション
 2-1-1. セッションの開始
 2-1-2. セッションの放棄
 2-2. バリデーション
 2-2-1. 無効な送信の拒否
 2-2-2. 名前のバリデーション
 2-2-2-1. 半角スペースのみ無効
 2-2-2-2. 文字数
 2-2-2-3. 空白文字／制御文字無効
 2-2-3. エラーメッセージの表示
 2-2-4. 入力値の保持 - タグ
 2-3. 問題表示ページへの遷移

次の記事 >> 【④DB接続設定ファイルを作る】

4. DB接続設定
 4-1. PDOオブジェクトの属性
 4-1-1. フェッチ形式の指定
 4-1-2. エラーモードの設定
 4-1-2-1. エラーモードの違いによるエラー文の違い
 4-1-3. エミュレーションの設定
 4-1-3-1. プリペアドステートメント
 4-1-3-2. エミュレーション
 4-2. 例外発生時の処理
 4-2-1. HTTPヘッダの送信
 4-2-2. 処理の中断とエラーメッセージの表示
 4-3. DBの切断について

「間違い探し」なので単に「ランダムに文字を選択させる」だと簡単に見分けがつく文字同士が表示される可能性があり、ゲーム性が損なわれます。この為「似た文字のペア」を設定する必要があります。 ↩

viやvimで直接絵文字を打つとカーソルの位置がおかしくなったり謎の空白が出現するなど、めちゃくちゃバグります。vimにはあまり詳しくないので、回避できる策があるなら教えて欲しいです... ↩

多次元配列に対してshuffle()を用いた場合、最上位層のみシャッフルされます。再帰的にシャッフルするには別途for構文やforeach構文などの繰り返し処理を用いる必要があります。 ↩

文字ペアの多次元配列のシャッフル時にfor構文で$iを使用したので、今回はアルファベット順に$j, $kとしています。$iと$j, 実際は、$kのどちらかは同じでも問題ありません（for構文の最初の式で初期化される為。$jと$kは同時に使用するので分ける必要あり）が、ややこしいので筆者はこのやり方を好んでいます。 ↩

array_rand()はランダムなキーを返すことに注意してください。今回の場合、$key1と$key2に格納されているのは$keyの値ではなくキーです。$keyの配列のキーと値が全く同じである為ややこしいですが、キーにインデックス番号以外が用いられた配列を扱う際は注意が必要です。 ↩

脚注4.と同じように、カウンター変数はアルファベット順に$lとしています。 ↩

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T20:20:34+09:00

HTML・PHP・MySQLだけで作る間違い探しゲーム【②スタートページを作る】

2. スタートページの実装

<< 前の記事【①ゲームの概要】

1. 間違い探しゲームの概要
 1-1. ゲームの全体像
 1-2. 開発環境及びファイルの全体像
 1-2-1. 開発・動作確認済み環境
 1-2-2. 作成するファイル
 1-2-2-1. メインファイル
 1-2-2-2. サブファイル
 1-3. ソースコード
 1-3-1. メインファイルのソースコード
 1-3-2. サブファイルのソースコード
 1-4. 参考にさせて頂いた記事・サイトの一覧

次の記事 >> 【③問題表示ページを作る】

3. 問題表示ページの実装
 3-1. 無効なアクセスの拒否
 3-2. リセット回数の計測
 3-3. 問題用文字配列の設定
 3-3-1. 文字ペア配列の選択
 3-3-2. 文字ペア配列及び文字ペアのシャッフル
 3-3-3. 正解文字と不正解文字配列の設定
 3-3-3-1. 正解文字の設定
 3-3-3-2. 不正解文字配列の設定
 3-3-4. 問題用文字配列の生成
 3-4. 開始時刻の記録
 3-5. 選択肢の描写

まず、最初にプレイヤーが訪れることになるページを生成する「start.php」を実装します。
start.php
<?php

// セッションの開始
session_start();

// 送信されたデータの検証
if (isset($_POST['name'], $_POST['difficulty'], $_POST['permission'])) {

    // 変数への代入
    $name = $_POST['name'];
    $difficulty = $_POST['difficulty'];
    $permission = $_POST['permission'];

    // 名前のバリデーション
    if (empty(trim($name))) {
        $error_msg = '名前に空白は無効です';
    } elseif (mb_strlen($name, 'UTF-8') > 10) {
        $error_msg = '名前は10字以内にしてください';
    } elseif ($name !== preg_replace('/\A[\p{C}\p{Z}]++|[\p{C}\p{Z}]++\z/u', '', $name)) {
        $error_msg = '名前の前後に空白文字や制御文字を含めないで下さい';
    }

    // セッション変数への格納と問題表示ページへの遷移
    if (empty($error_msg)) {
        $_SESSION['name'] = $name;
        $_SESSION['difficulty'] = $difficulty;
        $_SESSION['permission'] = $permission;
        header('Location:find_the_mistake.php');
        exit();
    }
}

// セッションの放棄
$_SESSION = [];
setcookie(session_name(), '', time() - 1, '/');
session_destroy();

?>
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <title>間違い探し</title>
    </head>
    <body>
        <h1>間違い探し</h1>
        <p>20×20個のボタンが表示されるので、指定されたボタンを見つけ出して押しましょう！</p>
        <?php if (isset($error_msg)): ?>
        <hr>
        <ul>
            <li><?= $error_msg; ?></li>
        </ul>
        <hr>
        <?php endif; ?>
        <form method="POST">
            <label>名前
                <small> (10字以内)</small><br>
                <input type="text" name="name" required value="<?php if (isset($name)) echo $name;?>">
            </label>
            <p>
                <span>難易度</span><br>
                <label>
                    <input type="radio" name="difficulty" value="易しい（絵文字）" required <?php if (empty($difficulty) || isset($difficulty) && $difficulty === '易しい（絵文字）') echo 'checked';?>>
                    易しい（絵文字）
                </label>
                <label>
                    <input type="radio" name="difficulty" value="難しい（漢字）" <?php if (isset($difficulty) && $difficulty === '難しい（漢字）') echo 'checked';?>>
                    難しい（漢字）
                </label>
            </p>
            <p>
                <span>ランキングへの登録</span><br>
                <label>
                    <input type="radio" name="permission" value="許可しない" required <?php if (empty($permission) || isset($permission) && $permission === '許可しない') echo 'checked';?>>
                    許可しない
                </label>
                <label>
                    <input type="radio" name="permission" value="許可する" <?php if (isset($permission) && $permission === '許可する') echo 'checked';?>>
                    許可する
                </label>
            </p>
            <input type="submit" value="問題に挑戦!（時間計測が開始されます）">
        </form>
        <p>
            <button type="button" onclick="location.href='ranking.php'">ランキングページへ</button>
        </p>
    </body>
</html>
2-1. セッション

ページ間を移動しても名前や難易度などの値を保持するために、セッションを利用します。
$_SESSIONは定義済みのスーパーグローバル関数で、ページ間で遷移しても値を保持させることができます。

これは 'スーパーグローバル' あるいは自動グローバル変数と呼ばれるものです。スクリプト全体を通してすべてのスコープで使用することができます。
引用元：PHPマニュアル「PHP: $_SESSION - Manual」
start.php
// セッションの開始
session_start();
2-1-1. セッションの開始

セッションを利用するにはsession_start()を記述します。
注意点として、こちらの関数は「ブラウザに何かを出力する前に」呼び出す必要があります。

クッキーに基づくセッションを使用している場合、ブラウザに何か出力を行う前に session_start() をコールする必要があります。
引用元：PHPマニュアル「PHP: session_start - Manual」

この為、安全を見て<?php ?>タグ内の最上部に記述しています。
また、理由については下記質問が参考になります。

Teratail「なぜsession_startより前に何も出力があってはいけない？」

2-1-2. セッションの放棄

<?php ?>タグ内の後半でセッションを放棄しています。
start.php
// セッション変数の初期化
$_SESSION = [];
setcookie(session_name(), '', time() - 1, '/');
session_destroy();
後述のバリデーションに引っ掛からなかった場合には
header()で別ページに遷移させ、exit()で後続処理を停止させていますが、
このページはデフォルトでセッションを放棄させるようにしています。

これによって

バリデーションに引っ掛かった

初回のアクセスだった

場合にはセッションが残らないようになっています。

各コードの意味は以下です。

$_SESSION = []

セッション変数を全て初期化します。

setcookie(session_name(), '', time() - 1, '/');

セッション開始時にセッションIDがクッキーに保存されるので、これも削除します。

session_destroy()

セッションに登録されたデータを全て破棄します。

PHPマニュアル「PHP: session_destroy - Manual」

2つ目のsetcookie()関数ですが、

第1引数 : クッキーの名前

第2引数 : クッキーの値

第3引数 : クッキーの有効期限

第4引数 : サーバー上でクッキーを有効としたいパス

となっています。

第1引数にsession_name()関数によって取得した現在のセッション名（デフォルトではPHPSESSID）、
第3引数にtime()関数による現在のUnixタイムスタンプから- 1した過去のタイムスタンプ（＝有効期限切れ）
第4引数に/を指定することでサーバードメイン配下の全てのパス
を指定し,cookieを実質的に削除しています。

PHPマニュアル「PHP: setcookie - Manual」
PHPマニュアル「PHP: session_name - Manual」
PHPマニュアル「PHP: time - Manual」

これらの処理はバリデーションを通過した場合には、
先述のexit()で処理が止められるので実行されません。

2-2. バリデーション
start.php
// 送信されたデータの検証
if (isset($_POST['name'], $_POST['difficulty'], $_POST['permission'])) {

    // 変数への代入
    $name = $_POST['name'];
    $difficulty = $_POST['difficulty'];
    $permission = $_POST['permission'];

    // 名前のバリデーション
    if (empty(trim($name))) {
        $error_msg = '名前に空白は無効です';
    } elseif (mb_strlen($name, 'UTF-8') > 10) {
        $error_msg = '名前は10字以内にしてください';
    } elseif ($name !== preg_replace('/\A[\p{C}\p{Z}]++|[\p{C}\p{Z}]++\z/u', '', $name)) {
        $error_msg = '名前の前後に空白文字や制御文字を含めないで下さい';
    }

    // 中略

}
2-2-1. 無効な送信の拒否
start.php
if (isset($_POST['name'], $_POST['difficulty'], $_POST['permission'])) {

    // 中略

}
まず、$_POSTの存在確認がされてから、値の検証されるようにしています。
これは、開発者ツールなどでフロントのフォームが改竄された場合の送信を無効とするためです。

2-2-2. 名前のバリデーション
start.php
    // 名前のバリデーション
    if (empty(trim($name))) {
        $error_msg = '名前に空白は無効です';
    } elseif (mb_strlen($name, 'UTF-8') > 10) {
        $error_msg = '名前は10字以内にしてください';
    } elseif ($name !== preg_replace('/\A[\p{C}\p{Z}]++|[\p{C}\p{Z}]++\z/u', '', $name)) {
        $error_msg = '名前の前後に空白文字や制御文字を含めないで下さい';
    }
入力された名前に対し、

半角スペースのみ無効

10文字以内

前後に空白文字／制御文字を含めない

のバリデーションをかけています。

2-2-2-1. 半角スペースのみ無効

trim()関数はスペースを取り除く関数で、その結果が空だった場合にエラーメッセージを格納しています。

文字列の先頭および末尾にあるホワイトスペースを取り除く
引用元：PHPマニュアル「PHP: trim - Manual」

ただし、こちらの関数は全角スペースには対応していないので更にバリデーションをかけます。

2-2-2-2. 文字数

mb_strlen()によって文字数をカウントし、
カウントが10を超えている場合にエラーメッセージを設定しています。

日本語にはマルチバイト文字なので、通常のstrlen()ではなく
マルチバイト対応のmb_strlen()を用いています¹。

2-2-2-3. 空白文字／制御文字無効

名前の前後に空白文字や制御文字が含まれている場合を検知しています。
具体的には、「対象の文字列を空文字に変換する前と後の文字列が等しくない場合」を検知しています。
全角スペースのみもこちらで弾けます。

文字数判定の後にこの判定を入れているのは、
preg_replace()が正規表現を使った重い処理であり、
文字数判定を後にしてしまうと何万文字と言う文字を送りつけられた場合に、
サーバーがダウンしてしまう可能性があるためです（ReDoS攻撃対策）。

これらについては下記参考記事もご参照下さい。

Qiita「【PHP】マルチバイト(全角スペース等)対応のtrim処理 - Qiita」 by @fallout さん
Qiita「正規表現の落とし穴（ReDoS - Regular Expressions DoS） - Qiita」 by @prograti さん

2-2-3. エラーメッセージの表示
start.php
        <?php if (isset($error_msg)): ?>
        <hr>
        <ul>
            <li><?= $error_msg; ?></li>
        </ul>
        <hr>
        <?php endif; ?>
エラーメッセージがある場合のみ、
<hr>タグで囲った中で<ul>タグ<li>タグを用いて
$error_msgに格納されたエラーメッセージを出力しています。

2-2-4. 入力値の保持 - <input>タグ
start.php
        <form method="POST">
            <label>名前
                <small> (10字以内)</small><br>
                <input type="text" name="name" required value="<?php if (isset($name)) echo $name;?>">
            </label>
            <p>
                <span>難易度</span><br>
                <label>
                    <input type="radio" name="difficulty" value="易しい（絵文字）" required <?php if (empty($difficulty) || isset($difficulty) && $difficulty === '易しい（絵文字）') echo 'checked';?>>
                    易しい（絵文字）
                </label>
                <label>
                    <input type="radio" name="difficulty" value="難しい（漢字）" <?php if (isset($difficulty) && $difficulty === '難しい（漢字）') echo 'checked';?>>
                    難しい（漢字）
                </label>
            </p>
            <p>
                <span>ランキングへの登録</span><br>
                <label>
                    <input type="radio" name="permission" value="許可しない" required <?php if (empty($permission) || isset($permission) && $permission === '許可しない') echo 'checked';?>>
                    許可しない
                </label>
                <label>
                    <input type="radio" name="permission" value="許可する" <?php if (isset($permission) && $permission === '許可する') echo 'checked';?>>
                    許可する
                </label>
            </p>
            <input type="submit" value="問題に挑戦!（時間計測が開始されます）">
        </form>
データがPOSTされた場合、

$name

$difficulty

$permission

に入力値が格納されているので、
条件分岐で変数が定義されていた場合は

textタイプの<input>タグではvalue属性に変数の値

radioタイプの<input>タグではchecked属性

をそれぞれechoすることで、
バリデーションで弾かれてページが再読み込みされた場合でも、
入力値を保持させることができます。

また、

$difficulty

$permission

が空だった場合（=初回訪問時）は、

難易度「易しい（絵文字）」

ランキングへの登録「許可しない」

がデフォルトで選択されるようになっています。

2-3. 問題表示ページへの遷移
start.php
    // セッション変数への格納と問題表示ページへの遷移
    if (empty($error_msg)) {
        $_SESSION['name'] = $name;
        $_SESSION['difficulty'] = $difficulty;
        $_SESSION['permission'] = $permission;
        header('Location:find_the_mistake.php');
        exit();
    }
先程のバリデーションをパスした場合は、
$error_msgにエラーメッセージが格納されないので、
この変数が空の場合は「バリデーションをパスした」ものと判断しています。

以降のページで名前と難易度とランキング登録への認否は使用するので、
$_SESSIONにキーを設定して代入しています。

その後、header()によって次のファイルへと遷移させ、後続の処理をexit()により停止させています²。

<< 前の記事【①ゲームの概要】

1. 間違い探しゲームの概要
 1-1. ゲームの全体像
 1-2. 開発環境及びファイルの全体像
 1-2-1. 開発・動作確認済み環境
 1-2-2. 作成するファイル
 1-2-2-1. メインファイル
 1-2-2-2. サブファイル
 1-3. ソースコード
 1-3-1. メインファイルのソースコード
 1-3-2. サブファイルのソースコード
 1-4. 参考にさせて頂いた記事・サイトの一覧

次の記事 >> 【③問題表示ページを作る】

3. 問題表示ページの実装
 3-1. 無効なアクセスの拒否
 3-2. リセット回数の計測
 3-3. 問題用文字配列の設定
 3-3-1. 文字ペア配列の選択
 3-3-2. 文字ペア配列及び文字ペアのシャッフル
 3-3-3. 正解文字と不正解文字配列の設定
 3-3-3-1. 正解文字の設定
 3-3-3-2. 不正解文字配列の設定
 3-3-4. 問題用文字配列の生成
 3-4. 開始時刻の記録
 3-5. 選択肢の描写

実は少々不具合が残っており、「☺️」や「❤️」の場合、mb_stlen()は正しくカウントしてくれません（2倍にカウントされてしまう）。「?」や「?」は正しくカウントしてくれるのに謎です... ↩

後続の処理にセッションの放棄があるので、処理を止めないとせっかくセッションに保存した情報が初期化されてしまいます。 ↩

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T20:19:04+09:00

HTML・PHP・MySQLだけで作る間違い探しゲーム【①ゲームの概要】

1. 間違い探しゲームの概要

20×20のボタンの中から指定された文字が表示されたボタンを見つける、シンプルな間違い探しゲームです！~~解くのが簡単とは言っていない~~

以下の6つの記事で解説しています。

HTML・PHP・MySQLだけで作る間違い探しゲーム【①ゲームの概要】（本記事）

HTML・PHP・MySQLだけで作る間違い探しゲーム【①ゲームの概要】

1. 間違い探しゲームの概要
 1-1. ゲームの全体像
 1-2. 開発環境及びファイルの全体像
 1-2-1. 開発・動作確認済み環境
 1-2-2. 作成するファイル
 1-2-2-1. メインファイル
 1-2-2-2. サブファイル
 1-3. ソースコード
 1-3-1. メインファイルのソースコード
 1-3-2. サブファイルのソースコード
 1-4. 参考にさせて頂いた記事・サイトの一覧

HTML・PHP・MySQLだけで作る間違い探しゲーム【②スタートページを作る】

HTML・PHP・MySQLだけで作る間違い探しゲーム【②スタートページを作る】

2. スタートページの実装
 2-1. セッション
 2-1-1. セッションの開始
 2-1-2. セッションの放棄
 2-2. バリデーション
 2-2-1. 無効な送信の拒否
 2-2-2. 名前のバリデーション
 2-2-2-1. 半角スペースのみ無効
 2-2-2-2. 文字数
 2-2-2-3. 空白文字／制御文字無効
 2-2-3. エラーメッセージの表示
 2-2-4. 入力値の保持 - タグ
 2-3. 問題表示ページへの遷移

HTML・PHP・MySQLだけで作る間違い探しゲーム【③問題表示ページを作る】

HTML・PHP・MySQLだけで作る間違い探しゲーム【③問題表示ページを作る】

3. 問題表示ページの実装
 3-1. 無効なアクセスの拒否
 3-2. リセット回数の計測
 3-3. 問題用文字配列の設定
 3-3-1. 文字ペア配列の選択
 3-3-2. 文字ペア配列及び文字ペアのシャッフル
 3-3-3. 正解文字と不正解文字配列の設定
 3-3-3-1. 正解文字の設定
 3-3-3-2. 不正解文字配列の設定
 3-3-4. 問題用文字配列の生成
 3-4. 開始時刻の記録
 3-5. 選択肢の描写

HTML・PHP・MySQLだけで作る間違い探しゲーム【④DB接続設定ファイルを作る】

HTML・PHP・MySQLだけで作る間違い探しゲーム【④DB接続設定ファイルを作る】

4. DB接続設定
 4-1. PDOオブジェクトの属性
 4-1-1. フェッチ形式の指定
 4-1-2. エラーモードの設定
 4-1-2-1. エラーモードの違いによるエラー文の違い
 4-1-3. エミュレーションの設定
 4-1-3-1. プリペアドステートメント
 4-1-3-2. エミュレーション
 4-2. 例外発生時の処理
 4-2-1. HTTPヘッダの送信
 4-2-2. 処理の中断とエラーメッセージの表示
 4-3. DBの切断について

HTML・PHP・MySQLだけで作る間違い探しゲーム【⑤結果表示ページを作る】

HTML・PHP・MySQLだけで作る間違い探しゲーム【⑤結果表示ページを作る】

5. 結果表示ページ
 5-1. 無効なアクセスの拒否
 5-2. 回答時間の算出
 5-3. 各変数への格納
 5-4. 保存する回答時間とカウント数の上限設定
 5-5. 正解／不正解による表示メッセージの分岐
 5-6. ランキングへの登録
 5-6-1. ランキングテーブルの構成
 5-6-2. ランキングへの登録

HTML・PHP・MySQLだけで作る間違い探しゲーム【⑥ランキングページを作る】

HTML・PHP・MySQLだけで作る間違い探しゲーム【⑥ランキングページを作る】

6. ランキング表示ページ
 6-1. 表示切り替え機能
 6-2. 入力値の保持 - タグ
 6-3. ランキングの表示
 6-3-1. テーブルの表示
 6-3-2. テーブルデザイン

まず、どのようなものなのか概要を説明します。
(サーバー構築やDB構築に関しては本記事群では扱いません)

1-1. ゲームの全体像

初期画面にアクセスすると、

名前の入力欄

難易度の選択ラジオボタン

ランキングへの登録認否の選択ラジオボタン

が表示されます。

後で解説しますが、名前入力欄には

空白文字のみ無効

１０文字以内

名前の前後に空白文字／制御文字無効

のバリデーションを入れており、引っかかった場合はエラーが表示されます。

適切な名前を入力し、「送信」をクリックすると選択した難易度に応じた問題が表示されます。

正解・不正解を問わず、ボタンをクリックすると結果表示ページへ遷移します。
結果には

正解か不正解か

回答に要した時間

難易度

リセット回数

が表示されます。

結果表示ページの「ランキングページへ」をクリックすると回答時間ランキング表示画面へ遷移します。

ランキングは難易度ごとに切り替えることもできます。

1-2. 開発環境及びファイルの全体像

1-2-1. 開発・動作確認済み環境

PHP 7.1.29 (cli)

MySQL Ver 14.14 Distrib 5.6.44

1-2-2. 作成するファイル

メインとなる5つと補助的な2つの合計7つです。そこまで数が多くないのとファイル名のみ（相対パス）で参照できるようにする為、全て同一ディレクトリ内に配置します。

1-2-2-1. メインファイル

start.php

プレイヤーに名前/難易度/ランキング登録認否を選択してもらうページを生成するファイルです。

find_the_mistake.php

問題を作成・表示するファイルです。

20×20の文字が書かれたボタンを出現させ、その中に1つだけ正解を紛れ込ませます。

db_connect.php

DB接続設定用のファイルです。

下記result.phpとranking.phpで使用します。

result.php

回答が正解していたかどうかを示す結果画面です。

回答時間とリセット回数をカウントする機能を実装し、その結果も示します。

ranking.php

ランキングを表示するファイルです。

ここでは難易度別にランキングを表示切り替えできる機能も実装します。

1-2-2-2. サブファイル

create_ranking_table.sql

ランキングデータを保存するテーブルを作成するSQL文を記述したSQLファイルです。

style.css

ランキングページのテーブルの見た目を整えるCSSファイルです。

1-3. ソースコード

GitHubで見たい方はこちらからどうぞ。

1-3-1. メインファイルのソースコード
start.php
<?php

// セッションの開始
session_start();

// 送信されたデータの検証
if (isset($_POST['name'], $_POST['difficulty'], $_POST['permission'])) {

    // 変数への代入
    $name = $_POST['name'];
    $difficulty = $_POST['difficulty'];
    $permission = $_POST['permission'];

    // 名前のバリデーション
    if (empty(trim($name))) {
        $error_msg = '名前に空白は無効です';
    } elseif (mb_strlen($name, 'UTF-8') > 10) {
        $error_msg = '名前は10字以内にしてください';
    } elseif ($name !== preg_replace('/\A[\p{C}\p{Z}]++|[\p{C}\p{Z}]++\z/u', '', $name)) {
        $error_msg = '名前の前後に空白文字や制御文字を含めないで下さい';
    }

    // セッション変数への格納と問題表示ページへの遷移
    if (empty($error_msg)) {
        $_SESSION['name'] = $name;
        $_SESSION['difficulty'] = $difficulty;
        $_SESSION['permission'] = $permission;
        header('Location:find_the_mistake.php');
        exit();
    }
}

// セッションの放棄
$_SESSION = [];
setcookie(session_name(), '', time() - 1, '/');
session_destroy();

?>
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <title>間違い探し</title>
    </head>
    <body>
        <h1>間違い探し</h1>
        <p>20×20個のボタンが表示されるので、指定されたボタンを見つけ出して押しましょう！</p>
        <?php if (isset($error_msg)): ?>
        <hr>
        <ul>
            <li><?= $error_msg; ?></li>
        </ul>
        <hr>
        <?php endif; ?>
        <form method="POST">
            <label>名前
                <small> (10字以内)</small><br>
                <input type="text" name="name" required value="<?php if (isset($name)) echo $name;?>">
            </label>
            <p>
                <span>難易度</span><br>
                <label>
                    <input type="radio" name="difficulty" value="易しい（絵文字）" required <?php if (empty($difficulty) || isset($difficulty) && $difficulty === '易しい（絵文字）') echo 'checked';?>>
                    易しい（絵文字）
                </label>
                <label>
                    <input type="radio" name="difficulty" value="難しい（漢字）" <?php if (isset($difficulty) && $difficulty === '難しい（漢字）') echo 'checked';?>>
                    難しい（漢字）
                </label>
            </p>
            <p>
                <span>ランキングへの登録<small>（正解だった場合のみ）</small></span><br>
                <label>
                    <input type="radio" name="permission" value="許可しない" required <?php if (empty($permission) || isset($permission) && $permission === '許可しない') echo 'checked';?>>
                    許可しない
                </label>
                <label>
                    <input type="radio" name="permission" value="許可する" <?php if (isset($permission) && $permission === '許可する') echo 'checked';?>>
                    許可する
                </label>
            </p>
            <input type="submit" value="問題に挑戦!（時間計測が開始されます）">
        </form>
        <p>
            <button type="button" onclick="location.href='ranking.php'">ランキングページへ</button>
        </p>
    </body>
</html>
find_the_mistake.php
<?php

// セッションの再開
session_start();

// 無効なアクセスの拒否
if (empty($_SESSION)) {
    header('Location:start.php');
    exit();
}

// リセット回数の計測開始
if (isset($_SESSION['count'])) {
    $_SESSION['count']++;
} else {
    $_SESSION['count'] = 0;
}

// ターゲット配列の設定
if ($_SESSION['difficulty'] === '難しい（漢字）') {
    $chars = [
        ['猫', '描'],
        ['犬', '大'],
        ['幸', '辛'],
        ['白', '臼'],
        ['矢', '失'],
        ['力', '刀'],
        ['防', '妨'],
        ['土', '士'],
        ['卵', '卯'],
        ['巨', '臣'],
        ['寒', '塞'],
        ['旅', '族'],
        ['車', '東'],
        ['釘', '針']
    ];
} else {
    $chars = [
        ['&#x1f415;', '&#x1f408;'],
        ['&#x1f405;', '&#x1f406;'],
        ['&#x1f98e;', '&#x1f40d;'],
        ['&#x1f433;', '&#x1f42c;'],
        ['&#x1f339;', '&#x1f337;'],
        ['&#x1f34a;', '&#x1f34b;'],
        ['&#x1f34e;', '&#x1f351;'],
        ['&#x1f955;', '&#x1f336;'],
        ['&#x1f96f;', '&#x1f95e;'],
        ['&#x1f358;', '&#x1f359;'],
        ['&#x1f341;', '&#x1f342;'],
        ['&#x1f332;', '&#x1f333;'],
        ['&#x1f47b;', '&#x1f47d;'],
        ['&#x1f396;', '&#x1f3c5;']
    ];
}

// ターゲット配列のシャッフル
shuffle($chars);
for ($i = 0; $i < count($chars); $i++) {
    shuffle($chars[$i]);
}

// 正解と選択対象配列を設定
$correct = $chars[0][0];
$_SESSION['correct'] = $correct;
for ($j = 0; $j <= 19; $j++) {
    for ($k = 0; $k <= 19; $k++) {
        $targets[$j][$k] = $chars[0][1];
    }
}

// 正解のターゲットを選択対象配列に1つだけ挿入
$key = range(0, 19);
$key1 = array_rand($key);
$key2 = array_rand($key);
$targets[$key1][$key2] = $correct;

// 開始時刻を記録
$_SESSION['start_time'] = microtime(true);

?>
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <title>間違い探し</title>
    </head>
    <body>
        <h1><?= $correct; ?>を見つけよう！</h1>
        <p>
            <small>難易度: <?= $_SESSION['difficulty']; ?></small>
            <button type="button" onclick="location.href='find_the_mistake.php'">分かるか！（リセット）</button>
            <button type="button" onclick="location.href='start.php'">スタートページへ</button>
        </p>
        <form action="result.php" method="POST">
            <table>
                <?php foreach ($targets as $target): ?>
                <tr>
                    <?php for ($l = 0; $l < count($target); $l++): ?>
                    <td><input type="submit" name="answer" value="<?= $target[$l]; ?>"></td>
                    <?php endfor; ?>
                </tr>
                <?php endforeach; ?>
            </table>
        </form>
    </body>
</html>
db_connect.php
<?php

// 定数定義
const PDO_DSN = 'mysql:host=localhost;dbname=[FILTERED];charset=utf8mb4';
const USERNAME = '[FILTERED]';
const PASSWORD = '[FILTERED]';

// DB接続
try {
    $dbh = new PDO(PDO_DSN, USERNAME, PASSWORD, [
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
} catch (PDOException $e) {
    header('Content-Type: text/plain; charset=UTF-8', true, 500);
    exit('DB接続に失敗しました' . PHP_EOL . $e->getMessage() . PHP_EOL);
}
result.php
<?php

// セッションの開始
session_start();

// 無効なアクセスの拒否
if (empty($_SESSION) || empty($_POST)) {
    header('Location:start.php');
    exit();
}

// 回答時間を算出
$end_time = microtime(true);
$start_time = $_SESSION['start_time'];
$time = sprintf('%05.2f', $end_time - $start_time) . '秒';

// 回答を変数に格納
$answer = $_POST['answer'];

// セッション変数を変数に格納
$name = $_SESSION['name'];
$difficulty = $_SESSION['difficulty'];
$permission = $_SESSION['permission'];
$correct = $_SESSION['correct'];
$count = sprintf('%02d', $_SESSION['count']) . '回';

// セッションの放棄
$_SESSION = [];
setcookie(session_name(), '', time() - 1, '/');
session_destroy();

// 回答時間が100秒以上の場合は値を上書き
if ($time > 100) {
    $time = '100秒以上';
}

// リセット回数が100回以上の場合は値を上書き
if ($count > 100) {
    $count = '100回以上';
}

// 正解・不正解によるメッセージの分岐
if (html_entity_decode($correct) === $answer) {
    $result = '正解です！';
} else {
    $result = '不正解です。。。';
}

// 正解かつ許可されていた場合のみDBに登録
if ($result === '正解です！' && $permission === '許可する') {

    // db接続
    require_once('db_connect.php');

    // 新規登録処理
    $sql = 'INSERT INTO rankings (name, difficulty, time, reset) VALUES (?, ?, ?, ?)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute([$name, $difficulty, $time, $count]);
}

?>
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <title>間違い探し</title>
    </head>
    <body>
        <h1><small>結果は...<?= $result ?></small></h1>
        <h2><small>難易度: <?= $difficulty; ?></small></h2>
        <h2><small>回答時間: <?= $time; ?></small></h2>
        <h2><small>リセット回数: <?= $count; ?></small></h2>
        <button type="button" onclick="location.href='start.php'">スタートページへ</button>
        <button type="button" onclick="location.href='ranking.php'">ランキングページへ</button>
    </body>
</html>
ranking.php
<?php

// 難易度がPOSTされている場合は変数に格納
if (isset($_POST['show_method'])) {
    $select = $_POST['show_method'];
} else {
    $select = 'all';
}

// db接続
require_once('db_connect.php');

// 選択された表示形式によってSQL文を分岐
$sql = 'SELECT * FROM rankings ';
switch ($select) {
    case 'difficult':
        $sql .= "WHERE difficulty = '難しい（漢字）' ORDER BY time LIMIT 10";
        break;
    case 'easy':
        $sql .= "WHERE difficulty = '易しい（絵文字）' ORDER BY time LIMIT 10";
        break;
    case 'all':
        $sql .= 'ORDER BY time LIMIT 10';
        break;
}
$stmt = $dbh->query($sql);
$players = $stmt->fetchAll();

?>
<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <link rel="stylesheet" href="style.css">
        <title>間違い探し ランキング</title>
    </head>
    <body>
        <h1>回答時間ランキング</h1>
        <form method="POST">
            <select name="show_method" size="1">
                <option value="all" <?php if ($select === 'all') echo 'selected' ?>>全て</option>
                <option value="easy" <?php if ($select === 'easy') echo 'selected' ?>>易しい</option>
                <option value="difficult" <?php if ($select === 'difficult') echo 'selected' ?>>難しい</option>
            </select>
            <input type="submit" value="表示">
        </form>
        <div class="table">
            <table class="s-tbl">
                <thead>
                    <tr>
                        <th>順位</th>
                        <th>名前</th>
                        <th>難易度</th>
                        <th>回答時間</th>
                        <th>リセット回数</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($players as $key => $player): ?>
                    <tr>
                        <td><?= ++$key; ?></td>
                        <td><?= $player['name']; ?></td>
                        <td><?= $player['difficulty']; ?></td>
                        <td><?= $player['time']; ?></td>
                        <td><?= $player['reset']; ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
        <p>
            <button type="button" onclick="location.href='start.php'">スタートページへ</button>
        </p>
    </body>
</html>
1-3-2. サブファイルのソースコード
create_rankings_table.sql
CREATE TABLE `rankings` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `difficulty` varchar(10) NOT NULL,
  `time` varchar(10) NOT NULL,
  `reset` varchar(15) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
style.css
.table {
  margin-top: 10px;
}
.s-tbl {
  border-collapse: collapse;
}
.s-tbl th, .s-tbl td {
  border: 1px solid #000;
  padding: 0.5em;
}
.s-tbl tr:nth-child(even) {
  background: #eee;
}
.s-tbl tr:hover {
  background: #ffffe0;
}
1-4. 参考にさせて頂いた記事・サイトの一覧

もちろん、公式マニュアル！ RTFM！
PHPマニュアル「PHP: PHP マニュアル - Manual」

PDOによるDB接続に関して体系的に書かれており非常に勉強になりました
Qiita「PHPでデータベースに接続するときのまとめ - Qiita」 by @mpyw さん

クラスの基礎から例外処理までとても丁寧に解説されておりとても参考になりました
Qiita「【PHP超入門】クラス～例外処理～PDOの基礎 - Qiita」 by @7968 さん

エミュレータのON／OFFに関する疑問が解決されました
Teratail「`PDO::ATTR_EMULATE_PREPARES => false`は必要か？」

フロントに関する事なら外せないドキュメント
MDN web docs「開発者向けのウェブ技術 | MDN」

ずっと疑問に思っていたことが解消されました
Teratail「なぜsession_startより前に何も出力があってはいけない？」

正規表現について新たな知見を得られました
Qiita「【PHP】マルチバイト(全角スペース等)対応のtrim処理 - Qiita」 by @fallout さん

ReDoSに関してとても勉強になりました
Qiita「正規表現の落とし穴（ReDoS - Regular Expressions DoS） - Qiita」 by @prograti さん

絵文字のHTMLエンティティを調べるのに活用させて頂きました
Let's EMOJI「Unicode 13.0 絵文字 (Unicode 13.0 Emoji) | Let's EMOJI」

sprintfに関してとても詳細に解説されています
Let'sプログラミング～初心者の方を対象としたプログラミングの総合学習サイト～
「指定の形式にフォーマット(sprintf) - 文字列関数 - PHP関数」

文字コードと照合順序に関しとても勉強になりました
Qiita「寿司ビール問題①　初心者→中級者へのSTEP20/25 - Qiita」 by @kamohicokamo さん

この記事のおかげで命名の迷いがなくなりました
Qiita「データベースオブジェクトの命名規約 - Qiita」 by @genzouw さん

テーブルのデザインの参考にさせていただきました
いつか誰かの役に立つかもしれないweb制作屋の備忘録
「css tableで背景色を交互に変える方法」

どの記事・ページもとても参考になりました！
この場を借りて感謝を申し上げます?

次の記事 >> 【②スタートページを作る】

2. スタートページの実装
 2-1. セッション
 2-1-1. セッションの開始
 2-1-2. セッションの放棄
 2-2. バリデーション
 2-2-1. 無効な送信の拒否
 2-2-2. 名前のバリデーション
 2-2-2-1. 半角スペースのみ無効
 2-2-2-2. 文字数
 2-2-2-3. 空白文字／制御文字無効
 2-2-3. エラーメッセージの表示
 2-2-4. 入力値の保持 - タグ
 2-3. 問題表示ページへの遷移

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T18:52:16+09:00

[Docker / mysql] Can't connect to local MySQL server through socket

Docker で mysql server 立てようとしたら Can't connect to local MySQL server through socket って言われました。

なんだかわからないまま、解決するのに結構時間かかってしまったので、残しておきます。

結論

docker run では、末尾に [COMMAND] という option を指定できるが、mysql サーバ (container) を立てる時には指定してはいけない、ということです。
docker run [OPTIONS] IMAGE[:TAG|@DIGEST] [COMMAND] [ARG...]
Docker run reference? より

OK な例
docker run -d --name mydb --rm -p 3306:3306 mysql:5.7

# してから
docker exec -it /bin/bash  # host
mysql -u root -p           # container
ダメな例
docker run -d --name mydb --rm -p 3306:3306 mysql:5.7 /bin/bash
                                                      ^^^^^^^^^ これをしちゃダメ!!
ﾁｮｯﾄ詳細 (未完成)

まず、docker run するとき、[COMMAND] の部分は optional です。(つまり、あってもなくても良い)

次に、docker image の話を少しします。

docker image は Dockerfile を作って docker build を行うことにより作ることができます。
そしてこの build された image から container を起動できる、という流れになっています。
イメージ図
[Dockerfile] === docker build ==> [image] === docker run ===> [container]
通常、起動直後に実行されるコマンドは、上記のような Dockerfile の最後あたりに CMD ["command"] という形で指定されます

This command is optional because the person who created the IMAGE may have already provided a default COMMAND using the Dockerfile CMD instruction.

これはもちろん、 image の作者が、意図的に、CMD を用いて、実行するコマンドを指定している可能性があります。つまり、起動した container がきちんとサービスとしての振る舞いを行うために CMD ["command"] を書いている可能性がある、ということです。

しかし、docker run の時に、あなたが指定した [COMMAND] option は、この Dockerfile で作者が指定した CMD をオーバーライドして、新たなコマンドの実行を指定してしまうらしいです。

As the operator (the person running a container from the image), you can override that CMD instruction just by specifying a new COMMAND.

CMD (default command or options) ?

なので、mysql container が通常起動された時に実行される (はず) の、このコマンドが実行されないため、mysql server に接続できない、というのが原因であると考えています。

先のリンクにある、CMD 部分をを見て、こう思った方がいるかもしれません。
mysql/5.7/Dockerfile
CMD ["mysqld"]
「じゃあ、docker container が立ち上がった時に mysqld コマンドを実行すればいいんじゃね?」
つまりこうするわけです
# ダメな例
(host) $ docker run -d --name mydb --rm -p 3306:3306 mysql:5.7 /bin/bash

(container) # mysqld
実際私もそう思いました。試しました。ダメでした ?

「タイムスタンプがどうのこうの」と言われてしまい、うまくいきません...

( 調査中です... ご存知の方、いらっしゃいましたらご教示ください... )

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T17:59:25+09:00

割合をSQLで算出する各種方法の比較

こちらの記事で提案されている方法をみて
CASE 式 って遅いのでは？と思っていたので計測してみました。

CASE 式
SELECT
  SUM(CASE WHEN answer = '良かった' THEN 1 ELSE 0 END) / COUNT(*) AS "良かった",
  SUM(CASE WHEN answer = 'ふつう'   THEN 1 ELSE 0 END) / COUNT(*) AS "ふつう",
  SUM(CASE WHEN answer = '悪かった' THEN 1 ELSE 0 END) / COUNT(*) AS "悪かった"
FROM surveys
GROUP BY
SELECT
  answer,
  (100.0 * COUNT(*) / (SELECT COUNT(*) FROM surveys)) AS "rate"
FROM surveys
GROUP BY answer
TL;DR

PostgreSQL と MySQL のみ確認した

基本的には CASE 式 が良い
MySQL で answer にインデックス貼ってあるなら GROUP BY が良い

ソースコード

github:ytoune/practice-20201026-sql-group-by

計測に使った docker image の latest は下記のようになります

mysql:8.0.22

postgres:13.0

計測項目

PostgreSQL と MySQL で answer にインデックス貼ってある場合と貼ってない場合の４種類の環境で下記の実装を比較しました

ソースコードは TypeScript です
// 定数
export const answers = ['良かった', 'ふつう', '悪かった'] as const
CASE SUM
const r = await conn.query(`
  SELECT
    TO_CHAR(
      100.0 * SUM(CASE WHEN answer = '良かった' THEN 1 ELSE 0 END) / COUNT(*),
      '999.9%'
    ) AS "良かった",
    TO_CHAR(
      100.0 * SUM(CASE WHEN answer = 'ふつう'   THEN 1 ELSE 0 END) / COUNT(*),
      '999.9%'
    ) AS "ふつう",
    TO_CHAR(
      100.0 * SUM(CASE WHEN answer = '悪かった' THEN 1 ELSE 0 END) / COUNT(*),
      '999.9%'
    ) AS "悪かった"
  FROM ${table}
`)
return r.rows[0]
CASE SUM 2

DB では数の計算のみにしてアプリ側で割り算した方が速いのでは？と考えて追加しました
const r = await conn.query(`
  SELECT
    SUM(CASE WHEN answer = '良かった' THEN 1 ELSE 0 END) AS "良かった",
    SUM(CASE WHEN answer = 'ふつう'   THEN 1 ELSE 0 END) AS "ふつう",
    SUM(CASE WHEN answer = '悪かった' THEN 1 ELSE 0 END) AS "悪かった"
  FROM ${table}
`)
const row = r.rows[0]
const sum = answers.reduce((s, a) => s + (row[a] | 0), 0)
return Object.fromEntries(
  answers.map(a => [
    a,
    (((100 * row[a]) / sum).toFixed(1) + '%').padStart(7, ' '),
  ]),
)
CASE AVG
const r = await conn.query(`
  SELECT
    TO_CHAR(
      AVG(CASE WHEN answer = '良かった' THEN 100 ELSE 0 END),
      '999.9%'
    ) AS "良かった",
    TO_CHAR(
      AVG(CASE WHEN answer = 'ふつう'   THEN 100 ELSE 0 END),
      '999.9%'
    ) AS "ふつう",
    TO_CHAR(
      AVG(CASE WHEN answer = '悪かった' THEN 100 ELSE 0 END),
      '999.9%'
    ) AS "悪かった"
  FROM ${table}
`)
return r.rows[0]
GROUP BY
const r = await conn.query(`
  SELECT
    answer,
    TO_CHAR(
      100.0 * COUNT(*) / (SELECT COUNT(*) FROM ${table}),
      '999.9%'
    ) AS "rate"
  FROM ${table}
  GROUP BY answer
`)
const rows = Object.fromEntries(
  r.rows
    .sort((q, w) => answers.indexOf(q.answer) - answers.indexOf(w.answer))
    .map(r => [r.answer, r.rate]),
)
return rows
GROUP BY 2

DB では数の計算のみにしてアプリ側で割り算した方が速いのでは？と考えて追加しました
const r = await conn.query(`
  SELECT
    answer,
    100.0 * COUNT(*) AS "count"
  FROM ${table}
  GROUP BY answer
`)
const sum = r.rows.reduce((q, w) => q + Number(w.count), 0)
const rows = Object.fromEntries(
  r.rows
    .sort((q, w) => answers.indexOf(q.answer) - answers.indexOf(w.answer))
    .map(r => [
      r.answer,
      (((100 * r.count) / sum).toFixed(1) + '%').padStart(7, ' '),
    ]),
)
return rows
計測結果

PostgreSQL

index 項目かかった時間 (ms)

なし CASE SUM 55.54177199304104

なし CASE SUM 2 54.995950013399124

なし CASE AVG 58.986200988292694

なし GROUP BY 88.79471999406815

なし GROUP BY 2 65.40900300443172

あり CASE SUM 57.60654000937939

あり CASE SUM 2 59.50466300547123

あり CASE AVG 64.30810299515724

あり GROUP BY 90.37016299366951

あり GROUP BY 2 67.10715100169182

CASE 式 で 0,1 にして SUM する方法が一番良い結果を見せました

MySQL

answer にインデックス貼ってあるなら GROUP BY が良さそうです

index 項目かかった時間 (ms)

なし CASE SUM 435.45847699046135

なし CASE SUM 2 417.71613700687885

なし CASE AVG 430.6346800029278

なし GROUP BY 714.3452910035849

なし GROUP BY 2 690.6780380010605

あり CASE SUM 412.5453009903431

あり CASE SUM 2 391.940383002162

あり CASE AVG 402.8329849988222

あり GROUP BY 216.54291799664497

あり GROUP BY 2 196.9889049977064

感想

そもそも PostgreSQL が速い

index	項目	かかった時間 (ms)
なし	CASE SUM	55.54177199304104
なし	CASE SUM 2	54.995950013399124
なし	CASE AVG	58.986200988292694
なし	GROUP BY	88.79471999406815
なし	GROUP BY 2	65.40900300443172
あり	CASE SUM	57.60654000937939
あり	CASE SUM 2	59.50466300547123
あり	CASE AVG	64.30810299515724
あり	GROUP BY	90.37016299366951
あり	GROUP BY 2	67.10715100169182

index	項目	かかった時間 (ms)
なし	CASE SUM	435.45847699046135
なし	CASE SUM 2	417.71613700687885
なし	CASE AVG	430.6346800029278
なし	GROUP BY	714.3452910035849
なし	GROUP BY 2	690.6780380010605
あり	CASE SUM	412.5453009903431
あり	CASE SUM 2	391.940383002162
あり	CASE AVG	402.8329849988222
あり	GROUP BY	216.54291799664497
あり	GROUP BY 2	196.9889049977064

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T16:12:53+09:00

MySQL インストールから接続(Symfony)までの流れ

環境

mysql Ver 8.0.22 for osx10.15 on x86_64 (Homebrew)

プロジェクトディレクトリのターミナルと、MySQLにログイン状態のターミナル(MySQLインストール後)2つを並べて多くと効率的

SQLにはrootユーザーで接続の場合

プロジェクトの必要に応じて接続ユーザーは考慮してください。

MySQLインストール

brew install mysql

MySQL起動

mysql.server start

MySQLログイン

mysql -u root
ログインユーザーの root は初期導入後はパスワードが設定されていないため、パスワード無しでログインできる。

MySQL導入時に存在するDBを確認

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
導入時に既にいくつかDBの確認があるがけさないこと。

MySQL rootユーザーのパスワード設定

現在のログインユーザーのrootのパスワードを確認

mysql DBへ切り替える

use mysql;でユーザー情報が保存しているDBに切り替える

userテーブルを表示

フィールドがたくさんあるので以下のフィールドを指定
※以前のバージョンはPasswordフィールドが合ったようだが8.0にはないので注意
SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
パスワード設定

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';でユーザー情報が保存しているDBに切り替える
※root-passwordに設定したいパスワードを記入

MySQL ログアウトしてログインしてみる(確認)

ログアウト
exit
ログイン
mysql -u root -p
先程パスワードを設定したので必ず「-p」をつける。パスワード入力できる状態になるので入力してログイン。

新規にDBを作成する (必要な場合)

Doctrine経由で作成するのだが、MySQL8.0で認証方法の設定を変更しておく必要があるので先に行う。

rootユーザーの認証方法を確認

SQLにログイン mysql -u root -p

mysql DBに切り替えるuse mysql

userテーブルのpluginフィールドを確認SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
caching_sha2_passwordをmysql_native_passwordに変更
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root-password';

設定が変わっているか確認
SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
参考記事: https://blog.janjan.net/2018/11/01/mysql8-request-authentication-method-unknown-to-the-client/

Symfonyの設定ファイルを変更

.envファイルを変更。まだこのプロジェクト用のDBはないが次のステップで作成するので、(db_name)にはこれから作るDB名を入力しておく。

編集前
DATABASE_URL=mysql://db_user:db_password@127.0.0.1:3306/db_name?serverVersion=5.7
編集後
DATABASE_URL=mysql://root:(最初の方で設定したrootのパスワード)@127.0.0.1:3306/(db_name)
Doctrine経由でDB作成

php bin/console doctrine:database:create;する。以下表示されればOK。確認したい人はSQL側でshow databases;で確認。
Created database `(db_name)` for connection named default
注意：以下エラーが出た場合は、SQL側で接続ユーザーの認証方法をmysql_native_passwordにしないとエラーが出るので2つ前の設定をしっかりすること。
SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client
Doctrine経由でテーブル作成

ざっくりメモ
- php bin/console make:entityEntityファイルを作成(プロパティを決めます)
- php bin/console make:migrationマイグレーションファイル作成(DBに反映する設定情報のファイルを作ります)
- php bin/console doctrine:migrations:migrateマイグレーション実行(DBに変更を反映します)
- テーブルが新規作成もしくは情報が更新される
- SQLにログインしているターミナルでshow tables;でちゃんとテーブルが作られているか確認
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)  | NO   |     | NULL    |                |
| zip_code   | int           | NO   |     | NULL    |                |
| address    | varchar(255)  | NO   |     | NULL    |                |
| open_time  | int           | NO   |     | NULL    |                |
| close_time | int           | NO   |     | NULL    |                |
| map        | varchar(1000) | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
SQLにログインしているターミナルでDESCRIBE (テーブル名);でちゃんとフォールドが設定されているか確認
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)  | NO   |     | NULL    |                |
| zip_code   | int           | NO   |     | NULL    |                |
| address    | varchar(255)  | NO   |     | NULL    |                |
| open_time  | int           | NO   |     | NULL    |                |
| close_time | int           | NO   |     | NULL    |                |
| map        | varchar(1000) | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T11:48:51+09:00

最強のLaravel開発環境(Docker)を日本時間にする

はじめに（開発環境構築）

https://qiita.com/ucan-lab/items/5fc1281cd8076c8ac9f4

こちらの神リポジトリをcloneし、利用しようとしたときに、
TimeZoneがUTCだったため、日本時間に修正した際の備忘録となります。

下記構成内のDockerfileを修正します。
コンテナを立ち上げたことがあるものに対して実施する際には、
コンテナ停止時にDockerfileを修正し、再ビルドを実施してください。

ディレクトリ構成
├── backend # Laravelプロジェクトのルートディレクトリ
├── infra
│     └── docker
│          ├── mysql
│          │   ├── Dockerfile
│          │   └── my.cnf
│          ├── nginx
│          │   ├── Dockerfile
│          │   └── default.conf
│          └── php
│              ├── Dockerfile
│              ├── php-fpm.d
│              │   └── zzz-www.conf => unixドメインソケットの設定ファイル
│              └── php.ini
├── Makefile
└── docker-compose.yml
mysql

修正箇所
docker-laravel\infra\docker\mysql\Dockerfile
# ENV TZを変更する（4行目付近）
# ENV TZ=UTC \
ENV TZ=Asia/Tokyo \
確認方法

build、コンテナ立ち上げ(up)が完了後、の作業になります。
shell
$ make db
# $ docker-compose exec db bash (上記makeコマンドの内容)
root@XXXXXXX:# mysql -u root -p -h 127.0.0.1
Enter password: secret
# Dockerfileに記載されている8行目付近のPWを記入してください。
#  MYSQL_ROOT_PASSWORD=secret
#                      ￣￣￣￣
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)
system_time_zoneにJSTが表示されれば問題ありません。

nginx

修正箇所
docker-laravel\infra\docker\nginx\Dockerfile
# ENV TZを変更する（6行目付近）
# ENV TZ=UTC
ENV TZ=Asia/Tokyo
確認方法

build、コンテナ立ち上げ(up)が完了後、の作業になります。
shell
$ make web
# $ docker-compose exec web ash  (上記makeコマンドの内容)
/work/backend# date
現在時間が表示されれば問題ありません。

php

修正箇所
docker-laravel\infra\docker\php\Dockerfile
# timezone environmentを変更する（5行目付近）
# ENV TZ=UTC \
  # locale
#  LANG=en_US.UTF-8 \
#  LANGUAGE=en_US:en \
#  LC_ALL=en_US.UTF-8 \
ENV TZ=Asia/Tokyo \
  # locale
  LANG=ja_JP.UTF-8 \
  LANGUAGE=ja_JP:ja \
  LC_ALL=ja_JP.UTF-8 \
docker-laravel\infra\docker\php\Dockerfile
# localの設定を変更（36行目付近）
#  locale-gen en_US.UTF-8 && \
#  localedef -f UTF-8 -i en_US en_US.UTF-8 && \

  locale-gen ja_JP.UTF-8 && \
  localedef -f UTF-8 -i ja_JP ja_JP.UTF-8 && \
確認方法

build、コンテナ立ち上げ(up)が完了後、の作業になります。
shell
$ make app
# $ docker-compose exec app bash (上記makeコマンドの内容)
root@XXXXXXX:/work/backend# php -r 'echo date("Y/m/d H:i:s"),PHP_EOL;'
現在時間が表示されれば問題ありません。

最後に

以上で対応完了となります。
誰かの一助になれば幸いです。

誤りやもっと良い改修方法があればコメントにてご教示いただけますと幸いです。
よろしくお願いいたします。

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T11:31:04+09:00

[解決]　RailsでWebpacker::Manifest::MissingEntryError

はじめに

RailsのWebpacker::Manifest::MissingEntryErrorは、初学者あるあるらしい。
でもすぐ解決できた。

発生した場面

Rails6,MySQLをDockerで環境構築した.
↓
"yay, you are on rails!"
↓
早速書き始めて、root ディレクトリを変えたり、viewを書いたりして、ブラウザで確認。
↓
Webpacker::Manifest::MissingEntryError

解決方法
qiita.rb
bundle exec rails webpacker:install
他の方法
https://github.com/rails/webpacker/issues/1730
https://blog.yuhiisk.com/archive/2018/04/24/rails-error-collection.html#Webpacker

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T09:41:34+09:00

【MySQL】pid not foundが発生したときの対応集

はじめに

mysqlのバージョンを5.7から5.6にしたかった。
なので以下の記事を見ながらダウングレードしようとしました。(以下の記事が悪い訳では一切ない。)

・homebrewで入れたmysqlを8系から5系にダウングレードする

そしたら、myqslをを起動させようとすると
「ERROR! The server quit without updating PID file」
というエラーが、、
これを抜けるために一日費やしました。。
そのため、誰か詰まったときにもっとスムーズに解消できるように、
qiitaに残しておきます。

環境
OS: macOS Catalina 10.15.4
$ mysql.server start
ERROR! The server quit without updating PID file (/usr/local/var/mysql/[マシン名].local.pid).
pidファイルを作ってみる
$ touch ******-puro.local.pid
自分の場合は変わらず、作った瞬間はできるが、すぐに消えてしまう。
何回も作っても自分は駄目だった。

パス問題

パスが通っているかの確認
vim ~/.bash_profile
$ echo 'export PATH="/usr/local/opt/mysql@5.6/bin:$PATH"' >> ~/.bash_profile
$ source ~/.bash_profile
my.cnfの設定
$ mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
所有者権限

mysql.sockを作る
$ mysql.server start
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
$ mysql.server start
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (38)
MySQLアンインストール

mySQLのアンインストールは以下を参考にする。

どうしよう！困った時のMac上のMySQLのアンインストール＆再インストール、動作確認手順

他の記事で、アンインストール何回もやったが、同じくpid not foundがエラーが出てました。

Community Editionを削除したのがデカかったのかもしれない

おわりに

何か間違っている点があれば、教えて頂けたら幸いです。
よろしくお願い致します。

参考にさせて頂いたサイト（いつもありがとうございます）

・MySQLがどうしても起動できない時に試したこと

・【MySQL】pid not foundもしくはpermission deniedの対応方法

・「ERROR! The server quit without updating PID file」となり、MacOSでmysqlにアクセスできない。

・mysqlサーバが立ち上がらないとき〜〜

・MacでHomebrewを使ってinstallしたMySQL5.6とMySQL5.7を切り替えて使う

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T03:39:00+09:00

SQLのパフォーマンスについて

概要

この記事ではパフォーマンスについてまとめていく。
具体的には、indexと実行計画についてコマンドベースでまとめていく。

indexとは

本の索引のようなもので、どの行にどのデータが格納されているかを示すものである。
カラムにindexを付与することで、テーブルからデータを検索する際にindexを使用するようになり、パフォーマンスを向上することができる。ただ、場合によってはパフォーマンスが落ちることもあるのでindexを貼るカラムは慎重に選択する必要がある。

indexの効果が高いカラムの例

WHERE句, ORDER BY句を頻繁に使用するカラム

INNER JOIN句などテーブルの結合条件によく使用するカラム

上記の句を使って、大量のデータが格納されているテーブルで、少量のレコードを検索するような場合や、NULL値が多いカラムからNULLでない値を検索する場合にも有効である。

indexをつけるとパフォーマンスが低下するカラムの特徴

WHERE句, ORDER BY句, INNER JOIN句をあまり使用しないカラム

テーブルに格納されているデータが少量の場合、indexを使った検索をしてもあまりパフォーマンス向上にならず、むしろパフォーマンスが低下してしまう場合がある。

データベース作成からindexの振り方まで

データベース作成から手順を一つずつ見ていく。(MySQLを使用)
まず、データベース、テーブルを作成する。
今回はschoolデータベースを作成し、classテーブルとstudentテーブルを作成する。

classesテーブル

class_id (ID)

number_of_students (生徒数)

studentsテーブル

student_id (ID)

student_name (生徒の名前)

student_profile (生徒のプロフィール)

class_id(クラスID)

SQLは以下である。
CREATE DATABASE school;
USE school;
CREATE TABLE school.classes(
    class_id INT NOT NULL PRIMARY KEY,
    number_of_students INT
);

CREATE TABLE school.students(
    student_id INT NOT NULL PRIMARY KEY,
    student_name VARCHAR(100),
    student_profile TEXT,
    class_id INT NOT NULL
);
補足

上記のSQLをひとつずつ見ていく。
CREATE DATABASE school; ：元となるデータベースを作成
USE school;：使用するデータベースを指定
CREATE TABLE school.class(): テーブルを作成。データベース名.クラス名とすることで特定のデータベースにテーブルを作成できる。()の中に作成するカラムを記述していく
class_id INT NOT NULL PRIMARY KEY,student_name VARCHAR(100),student_profile TEXT,: カラムを作成。(オプションの説明は省略)

続いて、indexを付与していく。
student_id, 2つのテーブルのclass_idにindexを付与する。
CREATE INDEX インデックス名 ON テーブル名 (対象カラム名)で付与できる。
実際のコマンドは以下である。
CREATE INDEX student_id_index
    ON students (student_id);
CREATE INDEX class_id_index
    ON students (class_id);
CREATE INDEX class_id_index
    ON classes (class_id);
indexが付与されているかを確認する。
SHOW INDEX FROM テーブル名;で確認できる。
実際のコマンドは以下である。
SHOW INDEX FROM students;
確認結果は以下である。(2つあるが、上はプライマリーキーなので今は見なくてよい)

Table：テーブル名
Non_unique：indexが重複を含むならなら0, 含めないなら1。
Key_name：インデックス名
Seq_in_indexindexの番号。1から始まる。
Column_name：カラム名
Collation：indexでのソート方法。AはAscending(昇順)。
Cardinality：indexのユニークな値の数。低いほどカラム内で重複するデータが多いことを意味する。
Sub_part：カラムの一部のデータに対してindexを付与している場合に使用される。カラム全体に付与している場合はNULL。
Packed：キーがパックされる方法を示す。パックされない場合はNULL。
Index_type：indexの方法を示す。デフォルトはB-Tree。

あとは、適当にデータを入れておく
-- classesテーブルへのデータ挿入
INSERT INTO classes VALUES (1, 40), (2, 37), (3, 42);
-- studentsテーブルへのデータ挿入
INSERT INTO students VALUES
    (1, 'aaa', 'test1', 1),
    (2, 'bbb', 'test2', 1),
    (3, 'ccc', 'test2', 1);
検索してみる。
(具体的にindexを使うと使わないとで何が違うかは後日本記事に追記予定...)
sql -- where句を使った検索 SELECT * FROM students WHERE class_id = 1; -- order by句を使った検索 SELECT * FROM students ORDER_BY student_name; -- joinを使った検索 SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id;

実行計画とは

実行計画とは、クエリ実行時にどのインデックスを使っているか、あるいは使ってないかをMySQLが判断した結果をまとめたものである。クエリの先頭にEXPLAINコマンドをつけることでそのクエリでインデックスを使っているか、効率的にインデックスを使用できているかを確認することができる。
例として、上のjoinを使った検索においてEXPLAINコマンドを使って実行計画をみる。
EXPLAIN SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id;
結果はこちら。

possible_keys : MySQL がこのテーブル内の行の検索に使用するために選択できる
key : MySQL が実際に使用することを決定したキー (インデックス)
上で作成したindex自体はpossible_keysを見ると反映されていることがわかる。
(今回はデータ量が少ないのでindexを使わない設定になってしまっているのか、keyがNULLとなっている...)

参考

index

MySQLのIndexをはるコツ : https://qiita.com/katsukii/items/3409e3c3c96580d37c2b
【SQL】インデックスの基本知識まとめ : https://qiita.com/aberyotaro/items/ff3046ef12634e2791e3
MySQLでインデックスを貼る時に読みたいページまとめ(初心者向け）: https://qiita.com/C058/items/1c9c57f634ebf54d99bb
インデックスの意味とメリット・デメリット: https://www.dbonline.jp/sqlite/index/index1.html#section2
[DB初心者向け] インデックスの使い方についての解説: https://kirohi.com/index_for_beginer#i-3

パフォーマンス

SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう: https://thinkit.co.jp/article/9658
MySQLの実行計画（EXPLAIN）に関する覚え書 : https://qiita.com/tsuyopon-md/items/9e0517297816f656b541

　 Tweet
Qiitaで続きを読む

投稿日：2020-10-26T03:39:00+09:00

SQLの基礎【パフォーマンスについて】

概要

この記事ではパフォーマンスについてまとめていく。
具体的には、indexと実行計画についてコマンドベースでまとめていく。

indexとは

本の索引のようなもので、どの行にどのデータが格納されているかを示すものである。
カラムにindexを付与することで、テーブルからデータを検索する際にindexを使用するようになり、パフォーマンスを向上することができる。ただ、場合によってはパフォーマンスが落ちることもあるのでindexを貼るカラムは慎重に選択する必要がある。

indexの効果が高いカラムの例

WHERE句, ORDER BY句を頻繁に使用するカラム

INNER JOIN句などテーブルの結合条件によく使用するカラム

上記の句を使って、大量のデータが格納されているテーブルで、少量のレコードを検索するような場合や、NULL値が多いカラムからNULLでない値を検索する場合にも有効である。

indexをつけるとパフォーマンスが低下するカラムの特徴

WHERE句, ORDER BY句, INNER JOIN句をあまり使用しないカラム

テーブルに格納されているデータが少量の場合、indexを使った検索をしてもあまりパフォーマンス向上にならず、むしろパフォーマンスが低下してしまう場合がある。

データベース作成からindexの付与まで

データベース作成から手順を一つずつ見ていく。(MySQLを使用)
まず、データベース、テーブルを作成する。
今回はschoolデータベースを作成し、classテーブルとstudentテーブルを作成する。

classesテーブル

class_id (ID)

number_of_students (生徒数)

studentsテーブル

student_id (ID)

student_name (生徒の名前)

student_profile (生徒のプロフィール)

class_id(クラスID)

SQLは以下である。
CREATE DATABASE school;
USE school;
CREATE TABLE school.classes(
    class_id INT NOT NULL PRIMARY KEY,
    number_of_students INT
);

CREATE TABLE school.students(
    student_id INT NOT NULL PRIMARY KEY,
    student_name VARCHAR(100),
    student_profile TEXT,
    class_id INT NOT NULL
);
補足

上記のSQLをひとつずつ見ていく。
CREATE DATABASE school; ：元となるデータベースを作成
USE school;：使用するデータベースを指定
CREATE TABLE school.class(): テーブルを作成。データベース名.クラス名とすることで特定のデータベースにテーブルを作成できる。()の中に作成するカラムを記述していく
class_id INT NOT NULL PRIMARY KEY,student_name VARCHAR(100),student_profile TEXT,: カラムを作成。(オプションの説明は省略)

続いて、indexを付与していく。
student_id, 2つのテーブルのclass_idにindexを付与する。
CREATE INDEX インデックス名 ON テーブル名 (対象カラム名)で付与できる。
実際のコマンドは以下である。
CREATE INDEX student_id_index
    ON students (student_id);
CREATE INDEX class_id_index
    ON students (class_id);
CREATE INDEX class_id_index
    ON classes (class_id);
indexが付与されているかを確認する。
SHOW INDEX FROM テーブル名;で確認できる。
実際のコマンドは以下である。
SHOW INDEX FROM students;
確認結果は以下である。(2つあるが、上はプライマリーキーなので今は見なくてよい)

Table：テーブル名
Non_unique：indexが重複を含むならなら0, 含めないなら1。
Key_name：インデックス名
Seq_in_indexindexの番号。1から始まる。
Column_name：カラム名
Collation：indexでのソート方法。AはAscending(昇順)。
Cardinality：indexのユニークな値の数。低いほどカラム内で重複するデータが多いことを意味する。
Sub_part：カラムの一部のデータに対してindexを付与している場合に使用される。カラム全体に付与している場合はNULL。
Packed：キーがパックされる方法を示す。パックされない場合はNULL。
Index_type：indexの方法を示す。デフォルトはB-Tree。

あとは、適当にデータを入れておく
-- classesテーブルへのデータ挿入
INSERT INTO classes VALUES (1, 40), (2, 37), (3, 42);
-- studentsテーブルへのデータ挿入
INSERT INTO students VALUES
    (1, 'aaa', 'test1', 1),
    (2, 'bbb', 'test2', 1),
    (3, 'ccc', 'test2', 1);
検索してみる。
(具体的にindexを使うと使わないとで何が違うかは後日本記事に追記予定...)
sql -- where句を使った検索 SELECT * FROM students WHERE class_id = 1; -- order by句を使った検索 SELECT * FROM students ORDER_BY student_name; -- joinを使った検索 SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id;

実行計画とは

実行計画とは、クエリ実行時にどのインデックスを使っているか、あるいは使ってないかをMySQLが判断した結果をまとめたものである。クエリの先頭にEXPLAINコマンドをつけることでそのクエリでインデックスを使っているか、効率的にインデックスを使用できているかを確認することができる。
例として、上のjoinを使った検索においてEXPLAINコマンドを使って実行計画をみる。
EXPLAIN SELECT * FROM students INNER JOIN classes ON students.class_id = classes.class_id;
結果はこちら。

possible_keys : MySQL がこのテーブル内の行の検索に使用するために選択できる
key : MySQL が実際に使用することを決定したキー (インデックス)
上で作成したindex自体はpossible_keysを見ると反映されていることがわかる。
(今回はデータ量が少ないのでindexを使わない設定になってしまっているのか、keyがNULLとなっている...)

参考

index

MySQLのIndexをはるコツ : https://qiita.com/katsukii/items/3409e3c3c96580d37c2b
【SQL】インデックスの基本知識まとめ : https://qiita.com/aberyotaro/items/ff3046ef12634e2791e3
MySQLでインデックスを貼る時に読みたいページまとめ(初心者向け）: https://qiita.com/C058/items/1c9c57f634ebf54d99bb
インデックスの意味とメリット・デメリット: https://www.dbonline.jp/sqlite/index/index1.html#section2
[DB初心者向け] インデックスの使い方についての解説: https://kirohi.com/index_for_beginer#i-3

パフォーマンス

SQL実行計画の疑問解決には「とりあえずEXPLAIN」しよう: https://thinkit.co.jp/article/9658
MySQLの実行計画（EXPLAIN）に関する覚え書 : https://qiita.com/tsuyopon-md/items/9e0517297816f656b541

　 Tweet
Qiitaで続きを読む

20201026のMySQLに関する記事は16件です。

mysqlをdockerで起動しておきたい時のdocker-compose.yml

Youtuberが配信・動画投稿したらメールにて通知するプログラムを作ってみた

はじめに

Youtubeがメール通知機能を2020年8月13日で終了

このプログラムでできること

運用環境

Youtube APIの登録・APIキーの取得

YoutubeのチャンネルページにあるURLを取得する

MySQLでテーブル作成

プログラム

cronを使って定期実行させる

動画投稿されたらメールが届くのを確認

問題点

HTML・PHP・MySQLだけで作る間違い探しゲーム【⑥ランキングページを作る】

6. ランキング表示ページ

6-1. 表示切り替え機能

6-2. 入力値の保持 - <select>タグ

6-3. ランキングの表示

6-3-1. テーブルの表示

6-3-2. テーブルデザイン

HTML・PHP・MySQLだけで作る間違い探しゲーム【⑤結果表示ページを作る】

5. 結果表示ページ

5-1. 無効なアクセスの拒否

5-2. 回答時間の算出

5-3. 各変数への格納

5-4. 保存する回答時間とカウント数の上限設定

5-5. 正解／不正解による表示メッセージの分岐

5-6. ランキングへの登録

5-6-1. ランキングテーブルの構成

5-6-2. ランキングへの登録

HTML・PHP・MySQLだけで作る間違い探しゲーム【④DB接続設定ファイルを作る】

4. DB接続設定

4-1. PDOオブジェクトの属性

4-1-1. フェッチ形式の指定

4-1-2. エラーモードの設定

4-1-2-1. エラーモードの違いによるエラー文の違い

4-1-3. エミュレーションの設定

4-1-3-1. プリペアドステートメント

4-1-3-2. エミュレーション

4-2. 例外発生時の処理

4-2-1. HTTPヘッダの送信

4-2-2. 処理の中断とエラーメッセージの表示

4-3. DBの切断について

HTML・PHP・MySQLだけで作る間違い探しゲーム【③問題表示ページを作る】

3. 問題表示ページの実装

3-1. 無効なアクセスの拒否

3-2. リセット回数の計測

3-3. 問題用文字配列の設定

3-3-1. 文字ペア配列の選択

3-3-2. 文字ペア配列及び文字ペアのシャッフル

3-3-3. 正解文字と不正解文字配列の設定

3-3-3-1. 正解文字の設定

3-3-3-2. 不正解文字配列の設定

3-3-4. 問題用文字配列の生成

3-4. 開始時刻の記録

3-5. 選択肢の描写

HTML・PHP・MySQLだけで作る間違い探しゲーム【②スタートページを作る】

2. スタートページの実装

2-1. セッション

2-1-1. セッションの開始

2-1-2. セッションの放棄

2-2. バリデーション

2-2-1. 無効な送信の拒否

2-2-2. 名前のバリデーション

2-2-2-1. 半角スペースのみ無効

2-2-2-2. 文字数

2-2-2-3. 空白文字／制御文字無効

2-2-3. エラーメッセージの表示

2-2-4. 入力値の保持 - <input>タグ

2-3. 問題表示ページへの遷移

HTML・PHP・MySQLだけで作る間違い探しゲーム【①ゲームの概要】

1. 間違い探しゲームの概要

1-1. ゲームの全体像

1-2. 開発環境及びファイルの全体像

1-2-1. 開発・動作確認済み環境

1-2-2. 作成するファイル

1-2-2-1. メインファイル

1-2-2-2. サブファイル

1-3. ソースコード

6-2. 入力値の保持 - `<select>`タグ

2-2-4. 入力値の保持 - `<input>`タグ

ﾁｮｯﾄ詳細 (未完成)

[解決]　RailsでWebpacker::Manifest::MissingEntryError