おべんきょうメモ

Web制作の勉強メモ ほぼほぼ自分用

PHPメモ データベースとMySQL編

php

首いてえ
データベースに接続したりSQL文でデータベースをあれこれするメモ

この記事を参考にしています
qiita.com

データベースに接続する

データベースに接続するにはPDOオブジェクトを生成する
構文としてはこれ

<?php
$dbh = new PDO($dsn, $user, $password, $options);

$dbh はDataBase Handlerとのこと まぁ慣習に従っとくのがいいでしょう
引数を1番目から順に見ていくわね

$dsn：データベースに接続するために必要な情報

dsnってのはData Source Nameの略だそう
ここの書き方は使うデータベースによって違うらしい（というかPDO関係の関数全体が多分そうだと思う……）のだけど、とりあえずMySQLの話として進めます
書き方としては

<?php
$dsn = 'mysql:dbname=DBの名前;host=DBのアドレス;charset=文字コード';

気をつけることと言えばDBの名前はダブルクオーテーションとかで括る必要はないことと、最近だと文字コードは概ねUTF-8になると思うんだけどここでそれを記述する際にはハイフンが不要で、具体的に言うとcharset=utf8と記述する、僕一回ここ引っかかったんだよ

$user,$password：データベースのユーザー名とパスワード

まぁこれはぶっちゃけそのままですね
MySQLのユーザー名とパスワードを入れます 強いて言うなら使うユーザーの権限がやりたいことと合致してるかを気をつけるくらいか

$options：接続時のオプション

接続時のいろいろなオプションを連想配列形式で設定します
オプションはいろいろあるけど現時点（の知識）で設定する必要があるべきだろうのは以下

PDO::ATTR_ERRMODE

エラーが起こったときの挙動を選択できる
デフォルトはPDO::ERRMODE_SILENT

PDO::ERRMODE_EXCEPTION：エラー・例外をスローしてくれる
PDO::ERRMODE_WARNING：SQLで発生したエラーをPHPのWarningとして報告する
PDO::ERRMODE_SILENT：エラーコードのみを設定してあとは何もしない

下2つについてはPDOStatement::executeメソッドの返り値が false でないかを毎回確認する必要があるとのこと お手本にしてた動画ではPDO::ERRMODE_SILENTを使っていたが 確かにクエリが成功したか失敗したかを毎回確認していたな

PDO::ATTR_DEFAULT_FETCH_MODE

fetch()とかfetchAll()メソッドに結果をかけたときのデータの形式を選択する、という認識でいいんじゃなかろうか デフォルトはPDO::FETCH_BOTH

PDO::FETCH_BOTH：カラム番号とカラム名の両方をキーとする連想配列で取得する
PDO::FETCH_NUM：カラム番号をキーとする配列で取得する
PDO::FETCH_ASSOC：カラム名をキーとする連想配列で取得する
PDO::FETCH_OBJ：カラム名をプロパティとするオブジェクト形式で取得する

まぁよっぽどでなければPDO::FETCH_ASSOCが一番無難で扱いやすいんじゃないかと思う（だから無駄に太字にした）、参考にしているQiitaの記事を書いた人もそう言っています

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY

MySQL専用のオプションらしい
trueのときにバッファクエリを使用します バッファクエリってなんやねん

  • バッファクエリ
    一度に全ての情報をデータベースから予め取得しておいて、詳細はPHPの方で取り出させる
  • 非バッファクエリ
    一件ごとに都度データベースサーバーと通信を行う

よっぽどな特殊ケースじゃない限りは基本的にtrueでいいでしょう、非バッファクエリはサーバーに負担がかかるとか複数クエリの並行処理が出来ないなんかのデメリットがやたら多いそうな（どうもこれはコマンドラインからバッチ処理を実行する用途という意味合いが大きい模様、現状の僕とはあまり関係ない部分ですね……）
デフォルトはどうもバージョンによってころころ変わっているようなので（現時点ではPHP公式では「デフォルトでバッファクエリを使う」という旨の記載はあった）こっちからオプションでちゃんと指定してあげとくのがよさそう

これらを踏まえて、オプションを設定する 例えばこう

<?php
$option = array(
  PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
);

んで最後に冒頭で挙げたPDOオブジェクトを生成
ただまぁデータベースってとにかく使うから逐一これを記述してたら日が暮れるのでこれら処理をまとめて関数にしちゃう

<?php
function dbConnect(){

// ローカルに立てたMySQLのxider_databaseっていうDBに接続するのを想定
$dsn = 'mysql:dbname=xider_database;host=localhost;charset=utf8';
$user = 'mitsuya';
$pass = 'mi3tsu2ya8'; // てきとうな例
$option = array(
  PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
);

$dbh = new PDO($dsn, $user, $pass, $option);
return $dbh;
}

そうしたら後は使うときに

<?php
$dbh = dbConnect();

の1行だけで接続完了できるね
PDO::ERRMODE_EXCEPTIONを使うときはこの関数もtry-catchで囲んであげる必要がありますね

クエリを実行する

実行の仕方は実は何種類かあるみたいだけど現状習ったやり方を整理しとく

基本的な構文としては

<?php
// $dbh に既にPDOオブジェクトが生成されているという前提
$stmt = $dbh->prepare('SQL文');
$stmt->execute([プレースホルダの中身を配列で]);

という感じ
プレースホルダーには疑問符プレースホルダと名前付きプレースホルダーの2種類がある

疑問符プレースホルダ

配列形式、一応数字をキーにした連想配列としても扱えはする

<?php
// $name と $city に既に条件となる値が入っているとする
$stmt = $dbh->prepare('SELECT * FROM users WHERE name = ? AND city = ?');
$stmt->execute([$name, $city]);

プレースホルダの中身の指定は

<?php
$stmt->execute([
  0 => $name, 
  1 => $city
]);

みたいなやり方も出来る（キー指定なので順番が逆でも数字が合ってればオッケー）が、やる意味あるんか？

名前付きプレースホルダ

連想配列形式、やや記述が長くはなるがキーが名前で付く分読みやすいとは思う

<?php
// $name と $city に既に条件となる値が略
$stmt = $dbh->prepare('SELECT * FROM users WHERE name = :name AND city = :city');
$stmt ->execute([
  ':name' => $name,
  ':city' => $city
]);

ちなみにプレースホルダの頭のコロンは省略も出来るらしい

<?php
$stmt ->execute([
  'name' => $name,
  'city' => $city
]);

そんな感じ

流れとしてはprepare()でSQL文をプレースホルダという虫食い（なんか文学的な表現だな……）状態で渡してやり、execute()でその虫食いを埋める、という理解でいいと思う
どうせならこれも関数化した方が読みやすそうなのでそうする

<?php
function queryPost($dbh, $sql, $data){
  $stmt = $dbh ->prepare($sql);
  $stmt ->execute($data);
  return $stmt;
}

使うときは事前にPDOオブジェクトの生成とそれぞれ変数にSQL文、プレースホルダの中身を格納しておく

具体例

<?php
// DB接続は上の方で書いた関数を使うとする
$dbh = dbConnect();
$sql = 'SELECT * FROM users WHERE name = :name AND city = :city';
$data = array(
  ':name' => $name,
  ':city' => $city
);
$stmt = queryPost($dbh, $sql, $data);

// データベースの中身を具体的に参照してどうこうする場合はfetchが要る（たぶん）
$result = $stmt->fetch(PDO::FETCH_ASSOC);

PDO::ATTR_ERRMODEがPDO::ERRMODE_WARNINGもしくはPDO::ERRMODE_SILENTのときは逐一メソッドの返り値がfalseかどうか確認する必要があるという話だったが、その際に結果に応じて処理を分ける（ログを吐き出すとかね）場合にはわざわざ毎回書いてたら面倒だから関数にその処理を含めてもいいかもね

<?php
function queryPost($dbh, $sql, $data){
  $stmt = $dbh ->prepare($sql);
  // ここでクエリがfalseを返してないかの判定
  if(!$stmt ->execute($data)){
    error_log('クエリ失敗');
    // 他にやる処理（画面にエラーを表示するとか）あれば書く
    return false;
  }else{
    error_log('クエリ成功');
    return $stmt;
  }
}

こんな感じで

例外処理の話

DB周り、僕からはどうにも出来ない/予測できないエラー、要は例外が発生することがある
そんなときにtry-catchで例外をスローしてあげる

<?php
try{
  // 諸々処理、記述省略
}catch(Exception $e){
  error_log('エラー発生：' . $e ->getMessage());
  // もし画面にエラー表示させるとかやるならそれも記述
}

tryの中身に書いていることをやり、その中でもし何らかの例外が発生したらcatchの中身を実行、という理解で凡そ良いはず
Exceptionはまぁ読んで字のごとし例外のこと（クラスです）だが、例外にもいろいろ種類があってもし発生した例外の種類によって処理を振り分けたいならここを変える PDOExceptionとかDOMException とかOutOfRangeExceptionとかいろいろ……
Exceptionは全ての例外が投げ込まれる（というか正しくは全ての例外の親がExceptionであり、それの子にDOMExceptionやらRuntimeExceptionやらがあり、更にRuntimeExceptionの子、Exceptionから見ると孫の位置にPDOExceptionがある 詳細は以下）
PHP: Exception - Manual

$eには例外のいろんなデータが入っている、ここから更に任意のメソッドでデータを取り出せる
多分圧倒的に使うことが多いのはgetMessage()、まぁそのままだが例外が示してるメッセージを取り出す、どういう理由で例外になったか的な……

ちなみに例外を投げる条件を作ってやることも出来る

<?php
throw new Exception('例外のメッセージ');

例外のメッセージ 部分がgetMessage()で取り出せる内容になる
ちなみにこれException部分に任意の名前を付けてcatchを分岐してやることも出来るっぽいね

SQL文

まぁしょうみな話逐一調べても良いような気はするけど割と使用頻度が高いであろうSELECT INSERT UPDATEのことはまとめておこう

SELECT

大雑把に言うと【検索】が感覚としては近いと思う
既に登録されてるユーザーかどうか調べるとか、ログインユーザーの情報を表示させるとか投稿を表示させるとかそういう場面で使う

<?php
$sql = 'SELECT 範囲 FROM テーブル名 WHERE 検索条件';

範囲はまぁ*にして全体を対象にするのが一番多そう
検索条件は例えばpriceが5000以上とかあるかもしれないし、セッションにユーザーid保存させといてそれと一致するデータだけ取り出すとかみたいなこともある

ちなみにSELECTは countと言ってデータではなく条件に該当するフィールドの「数」を返すSQL文もある
具体的にはSELECT count(*)という形で使う、括弧の中は多分他の値も入れられるけど現状の僕の知識の範囲内なら全体*でいいと思う
検索件数を表示させるとか、条件に該当するデータが「あるかどうかだけ」知りたいときとかにいいね

INSERT

雑に言うと【新規登録】、データベースのテーブルの最後尾にデータを追加する

<?php
$sql = 'INSERT INTO テーブル名 (項目名1,項目名2,項目名3,...) VALUE (中身1,中身2,中身3,...)';

項目名はそのまま項目名、項目名の中身は大体プレースホルダーを突っ込んどいて後でその中身を埋める

UPDATE

要は【更新】、データベースの値を書き換える

<?php
$sql = 'UPDATE テーブル名 SET 更新内容 WHERE 更新するフィールドの条件';

更新内容はまぁそのままだけど例えばメールアドレスを変更するで～って話ならemail = :emailみたいな書き方になるんじゃない
更新するフィールドの条件は例えばログイン中のユーザーのメールアドレスを更新したいって場合はuser_id = :user_idとしておいてプレースホルダにセッションに保存されたユーザーidを突っ込んでやるとか
SELECTもそうだが条件はもちろん複数指定することも可能、WHERE 条件1 AND 条件2 AND ...みたいに書いてやる

おまけ password_hashの話

データベースやMySQLの機能っていうわけではないが、まぁデータベース周りで使うことがほとんどだと思うのでpassword_hash()のことも書く
パスワードをデータベースに保存する場合、そのままだとパスワードが平文で保存されてしまって非常によろしくない
そういう場合に使うのがpassword_hash()、指定した文字列をなんかすごいアルゴリズムで暗号化してくれる

<?php
password_hash(暗号化したい文字列, 暗号化の形式);

暗号化の形式、いろいろあるが（僕自身の知識もまだ薄いので）基本的にはPASSWORD_DEFAULT一択で良いと思う
PHP公式サイト曰く「使ってるPHPがリリースされた時点で一番安全な形式を選択してる（意訳）」