lesson5 データベース(MySQL)

MySQLを使う流れ

  1. MySQLに接続する
  2. MySQLに命令する
    • テーブルにデータを登録する
    • テーブルからデータを取得する
  3. MySQLとの接続を切断する

MySQLに接続しよう

mysql クライアントからmysqlサーバへの接続

  • -h:host ホスト名

  • -u:user ユーザー名

  • -D database データベース名(大文字)

  • -p password 値を指定しないとターミナルで入力、非表示なのでセキュア

appコンテナからdbコンテナのmysqlに接続する

$ docker-compose exec app mysql -h db -u book_log -D book_log -p

パスワードは「pass」を入力

mysqlとの接続を切断する

>quit

(exitでも可能)

接続と切断

  • MySQLへ接続
    1
    2
    3
    4
    5
    6
    7
    8
    9
    $ docker-compose exec app mysql -h db -u book_log -D book_log -p
    Enter password:
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.5.62 MySQL Community Server (GPL)

    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  • MySQLから切断
    1
    2
    MySQL [book_log]> quit
    Bye

    SQL文でデータベースを操作する

    テーブルの作成

    CREATE TABLE 文で作成
    1
    2
    3
    4
    5
    CREATE TABLE[IF NOT EXISTS]<テーブル名>(
    <列名1><データ型><制約>,
    <列名2><データ型><制約>,
    ・・・
    )<テーブルオプション>;
  • 命名ルール
    • 半角英数
    • アンダーバー
    • 最初の文字はアルファベット
    • 名前の重複はだめ（１つのデータベースに同名のテーブルダメ,１つのテーブルに同名の列はダメ）

データ型　列ごとにデータ型を宣言

  • INTEGER 整数
  • VARCHAR 文字列（可変長文字列）
  • DATE 日付
  • TIMESTAMP 日付と時間の両方を含む値

制約　鉄に入れるデータに制限や条件をかけること

  • NOT NULL データを必ず入れる
  • PRIMARY KEY プライマリーキー（主キー）制約
  • AUTO _INCREMENT データを追加した際に最大値より1づつ増えていく
  • DEFAULT CURRENT_TIMESTAMP 対象の列に値を指定しなかった場合にデフォルト値が現在のタイムスタンプに設定される。

テーブルを作成してみる

mysqlにつないで以下のコマンドをメモ帳などからコピペで実行。

typoでミスらないように。

1
2
3
4
5
6
7
8
9
CREATE TABLE book_log (
  id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
  book_name VARCHAR(255),
  author VARCHAR(100),
  status VARCHAR(50),
  score INTEGER,
  review VARCHAR(500),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARACTER SET=  utf8mb4;

テーブルの確認

SHOW COLUMNS FROM book_log;

1
2
3
4
5
6
7
8
9
10
11
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| book_name  | varchar(255) | YES  |     | NULL              |                |
| author     | varchar(100) | YES  |     | NULL              |                |
| status     | varchar(50)  | YES  |     | NULL              |                |
| score      | int(11)      | YES  |     | NULL              |                |
| review     | varchar(500) | YES  |     | NULL              |                |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+

テーブルの削除

DROP TABLE <テーブル名>;

ファイルからSQL文を実行

テキストファイルにSQL文を保存、下記のコマンドを実行

mysql < text_file

Docker上のMySQLに対して実行するなら

docker-compose exec app /bin/bash (appコンテナ内でコマンドを実行)

mysql -h db -u book_log -D book_log -p < text_file

PHPからMySQLに接続

MySQLiの使い方

  • mysqli_connext関数
    1
    `mysqli_connect('my_host','my_user','my_password','my_db')`

読書ログでMySQLに接続するなら

1
mysqli_connect('db','book_log','pass','book_log');

MySQLに接続できないケースの対策

mysqli_connect()は接続に失敗した場合にFALSEを返す。

FALSEが帰ってきたらアプリケーションを終了させよう

1
2
3
4
5
6
7
$link = mysqli_connect('db','book_log','pass','book_log');
if(!$link) {
	echo 'Error: データベースに接続できません' . PHP_EOL;
	echo 'Debugging error: '. mysqli_connect_error() . PHP_EOL;
	exit;
}
// $linkがFALSEのときにif文が実行-> エラーが表示される

MySQLとの接続を切断する

  • mysqli_close関数

    　$link: mysqli_connect()が返すリンクIDが格納されている
    mysqli_close($link);

テーブルにデータを登録する

MySQL にコマンドで登録する方法

1
2
3
4
5
6
7
8
9
INSERT INTO companies (
  name,
  establishment_date,
  founder
) VALUES (
  'mercari inc',
  '2012-02-01',
  'Shintaro Yamada'
);

結果の確認

1
2
3
4
5
6
SELECT * FROM companies\G
                id: 1
              name: mercari inc
establishment_date: 2012-02-01
           founder: Shintaro Yamada
        created_at: 2021-04-17 19:34:05

PHPからMySQLに対してSQL文を実行する方法

INSERT文を使うと、テーブルに1行のデータを挿入する
$link:mysqli_connect() が返すリンクIDが格納されている

$query:SQL文の文字列

1
mysqli_query($link, $query)

複数行にまたがる文字列を表記しよう（ヒアドキュメント)

  • 構文
    1
    2
    3
    <<<EOT　// ID
    内容
    EOT; // 終わりのID
    $sqlにSQL文を格納し、mysqli()関数の第２引数に渡すと、PHPからSQL文を実行できる
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    $sql = <<<EOT
    INSERT INTO companies (
        name,
        establishment_date,
        founder
    ) VALUES (
        'SmartHR Ink',
        '2013-01-23',
        'Shoji Miyata'
    )
    EOT;
    1
    mysqli_query($link, $sql);

    SQL文実行時にエラーがあった場合、エラーメッセージを表示する

queryの戻り値を$resultに格納、$result がTRUEならデータを追加と表示、$resultがFALSEならエラーメッセージを表示する。

1
2
3
4
5
6
7
$result = mysqli_query($link, $sql);
if ($result) {
    echo 'データを追加しました' . PHP_EOL;
} else {
    echo 'Error: データの追加に失敗しました' . PHP_EOL;
    echo 'Debugging error: ' . mysqli_error($link) . PHP_EOL;
}

バリデーション処理

論理演算子

  • ! 否定の演算子 // TRUEでないときTRUEを返す

  • && 論理積の演算子 // 左から判定し、TRUEなら右の判定へとすすむ。すべてTRUEならTRUEを返す

  • || 論理積の演算子 // 左から判定し、TRUEになった時点でTRUEを返す

書籍名のバリデーション　・空文字でないか？　・255文字以下か？

// バリデーション処理する関数

1
2
3
4
5
6
7
8
9
10
11
12
13
function validate($reviews)
{
    $errors = [];   // エラーを格納する配列

    // 書籍名のバリデーション　書籍名が空のときと、255文字以上のとにきにエラーを配列に格納し、処理を中断する
    if (!mb_strlen($reviews['title'])) {
        $errors['title'] = '書籍名を入力してください';
    } elseif (mb_strlen($reviews['title']) > 255) {
        $errors['title'] = '書籍名は255文字以内で入力してください';
    }

    return $errors;
}

評価のバリデーション　5以下の整数であるか？(１～５)

型キャスト（型の変換）

  • (int) 整数への変換
  • (bool) 論理値への変換
  • (string) 文字列への変換

例文

1
2
$a = '2';　// string(1) "2"
$ var_dump((int)$a); // int(2) 文字列から整数へキャストされた

比較演算子

  • $a == $b 等しい場合にTRUE 比較時に型キャストされる
  • $a === $b 型キャストは行われない（厳密な比較）
  • $a != $b 等しくない場合にTRUE 比較時に型キャストされる
  • $a !== $b 型キャストは行われない（厳密な比較)
  • $a < $b $aが$bより小さい場合にTRUE
  • $a > $b $aが$bより大きい場合にTRUE
  • $a <= $b $aが$bより小さいか等しい場合(以下)にTRUE
  • $a >= $b $aが$bより大きいか等しい場合(以上)にTRUE

入力時に整数にキャストしておく
$review['score'] = (int)trim(fgets(STDIN));

1から5の整数かのバリデート処理

1
2
3
4
5
if (!$review['score']) {
    $errors['score'] = '評価を整数で入力してください';
} elseif ($review['score'] > 5 || $review['score'] <1) {
    $errors['score'] = '評価は1から5の整数で入力してください';
}

入力してほしい文字列と一致しているかのバリデーション

in_array()

  • 使い方
    in_array(探す値, 配列, <型のチェック>) : TRUE か FALSE を返す。
    1
    2
    3
    4
    5
    $status = ['未読', '読了', '読んでる'];    // この配列の値に一致しているもの以外をFALSEにする

    if (!in_array($review['status'], $status)) {
        $errors['status'] = '(読了,読んでる,未読)のうちいずれかを入力してください';
    }

    SQLのテーブルからデータを取得する

SELECT文を使う

1
SELECT <列名>,<列名> FROM テーブル名; // 指定した列のデータを取得する。
1
SELECT ＊ FROM テーブル名;  // すべての列のデータを取得する。

PHPからSQLのSELECT文を実行してテーブルからデータを取得する。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// データベースに接続する
$link = mysqli_connect('db', 'book_log', 'pass', 'book_log');

// SQL文を実行、結果（mysqli_resultオブジェクト)が返ってくる。結果を$rsultsに格納。
$sql = 'SELECT name, founder FROM companies';
$results = mysqli_query($link, $sql);

// mysqli_resultオブジェクトを連想配列で取得（変換する、ここでは$companyに連想配列を格納し,
while文でループ処理している）
while($company = mysqli_fetch_assoc($results)) {
    echo '会社名: ' . $company['name'] . PHP_EOL;
    echo '代表者: ' . $company['founder'] . PHP_EOL;
}
// データの使用後にメモリを解放している。
mysqli_free_result($results);

mysqli関数　まとめ

PHPからMySQLに接続

mysqli_connect関数

mysqli_connect('my_host','my_user','my_password','my_db')

$link = mysqli_connect('my_host','my_user','my_password','my_db') // リンクIDを$linkに格納

PHPからMySQLとの接続を切断する

mysqli_close関数
mysqli_close($link)
$link: mysqli_connect()が返すリンクID

PHPからMySQLに対してSQL文を実行する

mysqli_query($link, $query)

$link:mysqli_connect() が返すリンクID

$query:SQL文の文字列

SQL文を(ヒアドキュメントなどで)$queryに格納し、$link(リンクID)と一緒にmysqli_query()に渡す。

INSERT INTO文　(登録)

1
2
3
4
5
6
7
8
9
10
11
$sql = <<<EOT
INSERT INTO companies (
    name,
    establishment_date,
    founder
) VALUES (
    'SmartHR Ink',
    '2013-01-23',
    'Shoji Miyata'
)
EOT;

SELECT文　(データの取得)

1
$sql = 'SELECT name, founder FROM companies';

SQLの結果であるmysqli_resultオブジェクト を連想配列で取得する

1
mysqli_fetch_assoc($results)

SQLの結果をメモリから解放する。

1
mysqli_free_result($results);