PDOでデータベースを操作する

投稿日:2015/12/23
更新日:2018/02/24

Tags:: MySQL PHP

Categories:: プログラミング

DBに接続する

PDOとは

PHP Data Objectの略。
データベースを操作する為のクラス

データベースの接続

$dbh = new PDO(接続子, ユーザー名, パスワード, オプション);

第一引数
- MySQLで接続を行う場合
  mysql:dbname = DB名;host = MySQLサーバー;charset = 文字コード;
第二引数
- ユーザー名
  デフォルトであれば、root
第三引数
- パスワード
  DBに接続するためのパスワード
  デフォルトであれば空白
第四引数
- 接続時のオプション
  連想配列で渡すこと

Ex.1)データベースの接続(接続時のオプションを$driver_optionsで指定)

$dsn  = 'mysql:dbname=db;host=localhost;charset=utf8';
$user = 'root';
$pw   = 'pw';
$driver_options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
];
try {
    //データベースに接続
    $pdo = new PDO(
         $dsn,
         $user,
         $pw,
         $driver_options
    );
} catch(PDOException $e) {
    $e->getMessage();
}

接続後にオプションで指定することも出来る。
$driver_optons で指定するのとあまり変わらない。

Ex.2)データベースの接続(接続後にオプションで指定)

$dsn  = 'mysql:dbname=db;host=localhost;charset=utf8';
$user = 'root';
$pw   = 'pw';
try {
    //データベースに接続
    $pdo = new PDO(
         $dsn,
         $user,
         $pw,
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch(PDOException $e) {
    $e->getMessage();
}

■オプションについて
PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION
SQLでエラーを吐いた時に、例外として処理する。

※これを指定しないとどうなるの？？
SQLを実行したときのエラーを吐かない。
エラーの特定がしやすいから、ちゃんと例外処理しといた方がいいとおもう。

DBを切断する

データベースの切断

$dbh = null;

静的にSQLコマンドを実行する

PDO::query

指定したSQL文を1回だけ実行する。
ユーザー入力が不要な場合は、queryメソッドを実行すればいい。

$sql = 'SELECT * FROM `table`';
$stmt = $pdo->query($sql);
foreach($stmt as $val) {
    echo $val['key']; // tableの中身を取得する
}

PDO::exec

指定したSQL文を1回だけ実行して、結果の行数を返す。
ユーザー入力が不要で、行数が欲しい場合はexecメソッドを実行する。
また、SELECT句の場合は結果を返さないため、その際はqueryメソッドを使用する。

$sql = 'UPDATE `table` SET `field`="テスト"';
$stmt = $pdo->exec($sql); // $stmtにはUPDATEした件数が入る

動的にSQLコマンドを実行する

PDO::prepare

指定したSQL文を実行するための準備を行う。
プレースホルダを使う際、疑問符(?)か名前付き(:name)のどちらかを使用する。
併用するとエラーが出るため、どちらか一つを選ぶこと。
また、クエリの実行にはexecuteメソッドが必要（後述）

bindValue

prepareステートメントで使用したプレースホルダの値を入れます。

第一引数
- 疑問符の場合
  第一引数に?の位置番号を入れる(1起点)
  bindValue(1, ‘hoge’, PDO::PARAM_STR);
- 名前付きの場合
  プレースホルダで指定した名前をそのまま入れる
  bindValue(‘:name’, ‘hoge’, PDO::PARAM_STR);
第二引数
指定の文字列や数値等を入れる(変数でも可能)
第三引数
型を入れる

bindParam

prepareステートメントで使用したプレースホルダの値を入れます。
基本的な使い方は、bindValueと同じ。

疑問符の場合
第一引数に?の位置番号を入れる(1起点)
bindParam(1, $exe, PDO::PARAM_STR);
名前付きの場合
プレースホルダで指定した名前をそのまま入れる
bindParam(‘:name’, $exe, PDO::PARAM_STR);
エラーパターン
bindParam(1, ‘hoge’, PDO::PARAM_STR);
bindParam(‘:name’, ‘hoge’, PDO::PARAM_STR);
第二引数で変数を入れていないため、エラーが出る。

bindValueは値をバインドするため、同じ変数を使用しても変わらずにその値を使用できる
bindParamは変数を参照としてバインドするため、変数そのものが変わる（実行後にバインドした変数が文字列型にされる）
但しエミュレーションがONの場合にのみ該当する。
また、bindValueを使えばほぼほぼ問題ないようなので、bindParamは使わなくていいっぽい。

■参考
Qiita – PHPでデータベースに接続するときのまとめ

execute

prepareステートメントを実行する。
またプレースホルダの値をexecuteで渡すことも出来る。
executeで値を渡す際は、bindValue,bindParamで渡す時と違いがいくつかある。

配列で渡す
$stmt->execute(array($bind1, $bind2, bind3));
または
$stmt->execute([$bind1, $bind2, bind3]);
プレースホルダが疑問符の場合は0始まり
$stmt->execute(array(0 => $bind1, 1 => $bind2, 2 => bind3));
※キーが適切であれば、下記のように書くことも可能
$stmt->execute(array(1 => $bind2, 2 => $bind3, 0 => bind1));
ちなみに、1のようにキー指定をしない場合は、0から順にバインドされる。
プレースホルダが名前付きの場合
$stmt->execute(array(‘:name1’ => $bind1, ‘:name2’ => $bind2, ‘:name3’ => bind3));
または、「:」を省略して書くことも出来る。
$stmt->execute(array(‘name1’ => $bind1, ‘name2’ => $bind2, ‘name3’ => bind3));

fetchAll

二次配列でSQLの中身を全て抽出する
設定にもよるが、デフォでやると二次配列が無駄に作られる。
連想配列と添字の両方で配列が作成されるため、
例えば、3×3のテーブルからデータを引っ張ってくる際も、
本来は9つのデータ格納で済むが、倍の18つのデータを格納することになる。

色々な例を下記に記す。

フィールドの特定の部分だけ抜き取りたい。
fetchAll(PDO::FETCH_COLUMN, [offset]);
offsetには、配列の二次元目で取って来たいフィールド数値を入れる（0起点）。
戻り値は、一次元配列で返ってくる。

例えば、3×3のテーブルからデータを引っ張ってくる際に、2列目のデータを取りたいなぁ。と思った場合は
$stml = $stml->fetchAll(PDO::FETCH_COLUMN, 1);
とすれば、$stmlには2列目のデータが1次元配列として取得される。

1列目のデータしか取得は出来ないが、
指定列のデータをまとめて、2次元配列で取得したい場合。
$stml = $stml->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP, 1);
こうすれば、2列目の値で同じ値のものがあれば、同じグループとしてまとめて1列目のデータを吐き出してくれる。

例を記すとすれば
array(‘kuma’, ‘inu’, ‘marimo’)//1列目
array(‘ぷ’, ‘り’, ‘ぷ’)//2列目
の配列だとすれば。

[
    ["ぷ"]=> array(2) {
        [0]=>'kuma',
        [1]=>'marimo'
    },
    ["り"]=> array(1) {
        [0]=>'inu'
    }
];

の配列でまとめて吐き出してくれる。

連想配列で抜き取りたい
fetchAll(PDO::FETCH_ASSOC);
2次元目が連想配列で抜き取れる。
添字で抜き取りたい
fetchAll(PDO::FETCH_NUM);
2次元目が0起点の添字で抜き取れる。
count関数でfor文を回して、2次元目を添字指定で抜き出すことが出来る。
クラス名で抜き取る
fetchAll(PDO::FETCH_CLASS, “ClassName”);
第二引数で指定したクラス名を引っ張って来てくれる。

検索機能を実装する

簡単な検索機能

Ex)一つの単語に一致する検索

// isset($_GET['search'])の代用
$search = (string)filter_input(INPUT_GET, 'search');
if($search) {
    // orderテーブルのchargeカラム内で検索文字に一致する内容を抽出
    $sql = "SELECT * FROM `order` WHERE charge LIKE :search ESCAPE '!'";
    $stmt = $pdo->prepare($sql);
    // LIKE検索のために「%キーワード%」の形式にする
    $name = '%' . preg_replace('/(?=[!_%])/', '!', $search) . '%';
    $stmt->bindValue(':search', $name, PDO::PARAM_STR);
    $stmt->execute();
    $stmt = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

複数検索を付ける

Ex.1)静的な複数検索(検索単語数が決まっている場合(今回は二つの単語で検索))

// isset($_GET['search'])の代用
$search = (string)filter_input(INPUT_GET, 'search');
if($search) {
    // 半角スペース・全角スペース・ハードスペース($nbsp;)・すべてのASCII制御文字を複数検索区分けの対象にする。
    $regex = "/[\\x0-\x20\x7f\xc2\xa0\xe3\x80\x80]++/u";
    $keywords = preg_split($regex, $search, -1, PREG_SPLIT_NO_EMPTY);
    foreach ($keywords as $keyword) {
        // LIKE検索のために「%キーワード%」の形式にする
        $values[] = '%' . preg_replace('/(?=[!_%])/', '!', $keyword) . '%';
    }
    // AND条件で結合する
    $sql = "SELECT * FROM `order` INNER JOIN `company` ON order.c_id = company.c_id WHERE ((order.charge LIKE ? ESCAPE '!') AND (company.c_charge LIKE ? ESCAPE '!'))";
    // 実行
    $stmt = $pdo->prepare($sql);
    $stmt->execute($values);
    $stmt = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Ex.2)動的な複数検索(スペースで区切られた単語を検索する)

“`php
// isset($GET[‘search’])の代用
$search = (string)filter_input(INPUT_GET, ‘search’);
if($search) {
$regex = “/[\x0-\x20\x7f\xc2\xa0\xe3\x80\x80]++/u”;
$keywords = preg_split($regex, $search, -1, PREG_SPLIT_NO_EMPTY);
foreach ($keywords as $keyword) {
// プレースホルダのLIKE部分を用意
$holders[] = “(order.charge LIKE ? ESCAPE ‘!’) OR (company.c_name LIKE ? ESCAPE ‘!’) OR (company.c_charge LIKE ? ESCAPE ‘!’)”;
// LIKE検索のために「%キーワード%」の形式にする
// ORの数だけ配列($values[])を付ける
$values[] = $values[] = $values[] = ‘%’ . preg_replace(‘/(?=[!%])/’, ‘!’, $keyword) . ‘%’;
}
// AND条件で結合する
$sql = “SELECT * FROM order INNER JOIN company ON order.c_id = company.c_id WHERE (” . implode(” AND “, $holders) . “)”;
// 実行
$stmt = $pdo->prepare($sql);
$stmt->execute($values);
$stmt = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

transaction

$pdo->beginTransaction();
以降で、SQLエラーが起こった場合は、エラー前にexec等でSQL文を実行していても、実行する前の状態になる。
beginTransactionを宣言すると、オートコミットモードをオフにして、
commitを宣言しなければ、実行されないようになります。

つまり、beginTransaction宣言後に何かしらのエラーがあった場合、
rollbackを宣言すれば、beginTransaction宣言前の状態に戻す事が出来ます。

問題なく完了した場合は、
$pdo->commit();
を宣言します。
beginTransactionで問題がなければ、commitでSQLを実行します。
また、次回beginTransactionが宣言されるまでオートコミットモードに戻します。

エラーが発生した場合は、
$pdo->rollback();
を宣言し、beginTransaction宣言前の状態に戻します。

function insertScript($c_id=4,$s_name=NULL,$title='New Title',$body='New Body') {
    try {
        //DB接続
        $pdo  = connect();
        //SQLの例外発生時、php側でも例外としてcatchする
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        //SQL文
        $sql  = 'INSERT INTO `table` (id, name, title, body) VALUES (:id, :name, :title, :body)';
        //実行準備 SQLステートメントの準備
        $stmt = $pdo->prepare($sql);
        //トランザクションの開始（）
        $pdo->beginTransaction();
        $stmt->bindValue(':id', $c_id, PDO::PARAM_INT);
        $stmt->bindValue(':name', $s_name, PDO::PARAM_STR);
        $stmt->bindValue(':title', $title, PDO::PARAM_STR);
        $stmt->bindValue(':body', $body, PDO::PARAM_STR);
        $stmt->execute();
        $lastId = $pdo->lastInsertId();
        $pdo->commit();
    } catch(PDOException $e) {
        $pdo->rollback();
        $error = '失敗しました' . $e->getMessage();
        return $error;
    }
    return $lastId;
}

$pdo->lastInsertId()
INSERT文で実行した後のオートインクリメントの値を返す。

カラム数値を返す

$stmt->rowCount()
insert, delete, update 文で作用したカラム数を返す。
1つ削除したら、1を返し
5つ更新きたら、5を返します。
select文はSQLによっては、正しく動作しないこともあるよう。

$pdo->query(‘SELECT COUNT(*) FROM table‘)->fetchColumn();
前項で記載した方法ではselect文のカラム数を取ることが推奨されていない。
本項のやり方でselect文で作用したカラム数を取ることが出来る。

LIMIT句について

select文の条件を指定する際、
LIMIT句については、
bindValueで指定しないとエラーが発生する。

$sql  = 'SELECT * FROM `table_name` WHEHE `id`=? LIMIT ?';
$stmt = $pdo->prepare($sql);
$stmt = $pdo->execute([1, 1]);//ここでエラー！！

エラーを避けるには

$stmt->bindValue(1, 1,PDO::PARAM_INT);
$stmt->bindValue(2, 1,PDO::PARAM_INT);
$stmt = $pdo->execute();

で正常に作動する。
ちなみに、今回のように二つ「?」があるケースで
LIMIT句だけbindValueして、あとはexecuteでいっか！っていうのは出来ない。全部bindValueしてあげよう。
何故かは知らない…。

使用した関数の説明

preg_replace(pattern, replace, str[, limit[, count]])
正規表現で置換が出来る（文字列、配列でも可能）
1. 第一引数(pattern)
  検索パターンを指定する
2. 第二引数(replace)
  replaceで置換パターンを指定する
3. 第三引数(str)
  strに原文を入れる
4. 第四引数(limit)
  置換する最大回数を指定する（デフォルトでは指定なし）
5. 第五引数(count)
  指定した変数に置換回数が渡される
```
$str = '!!ssadl;kjfas!!!asd;flj!adslkjfa!';
preg_replace('/!/', '', $str, -1, $count); echo $count;// =>7
```
```
$str = '!!ssadl;kjfas!!!asd;flj!adslkjfa!';
$rep = preg_replace('/!/', '', $str);
echo $ rep;// =>ssadl;kjfasasd;fljadslkjfa
```
filter_input(type, name [, filter[, options]])
1. 第一引数(type)
  INPUT_GET、INPUT_POST、 INPUT_COOKIE、INPUT_SERVE、 INPUT_ENV のいずれかを指定する
2. 第二引数(name)
  取得する名前を指定する。
  $_GET[‘number’]であれば、’number’と記載する。
3. 第三引数(filter)
  フィルターの型を指定する。デフォルトでは「FILTER_DEFAULT」で何もフィルタリングをしない。
  filterの型
4. 第四引数(options)
  オプションあるいはフラグの論理和の連想配列。オプションを指定可能なフィルタの場合、この配列の “flags” フィールドにフラグを指定。
  ぶっちゃけよく分からない。
5. 返り値
  要求された変数の値が返る。
  フィルタリングに失敗した場合はfalse
  変数が未設定の場合はnullが返る
preg_split(pattern, subject[, limit[, flag]])
正規表現で配列に分割
1. 第一引数(pattern)
  検索パターンを指定する
2. 第二引数(subject)
  入力文字列(原文)
3. 第三引数(limit)
  分割する最大回数を指定する（デフォルトでは指定なし）
4. 第四引数(flag)
  次のフラグを組み合わせたものとする（ビット和演算子 | で組み合わせる）ことが可能。
  - PREG_SPLIT_NO_EMPTY
    このフラグを設定すると、空文字列でないものだけが preg_split() により返されます。
  - PREG_SPLIT_DELIM_CAPTURE
    このフラグを設定すると、文字列分割用のパターン中のカッコによるサブパターンでキャプチャされた値も同時に返されます。
  - PREG_SPLIT_OFFSET_CAPTURE
    このフラグを設定した場合、各マッチに対応する文字列のオフセットも返されます。これにより、返り値は配列となり、配列の要素 0 はマッチした文字列、要素 1 は subject におけるマッチした文字列のオフセット値となることに注意してください。

感想
PDO果てしない…。
記事が長くなり過ぎて、もうまとまりが付かない。
用途毎に別記事でまとめようと思った。

Baccho Log

PDOでデータベースを操作する

DBに接続する

PDOとは

データベースの接続

DBを切断する

データベースの切断

静的にSQLコマンドを実行する

PDO::query

PDO::exec

動的にSQLコマンドを実行する

PDO::prepare

bindValue

bindParam

execute

fetchAll

fetch

検索機能を実装する

transaction

カラム数値を返す

LIMIT句について

使用した関数の説明

コメントするコメントをキャンセル

PDOでデータベースを操作する

DBに接続する

PDOとは

データベースの接続

DBを切断する

データベースの切断

静的にSQLコマンドを実行する

動的にSQLコマンドを実行する

検索機能を実装する

カラム数値を返す

LIMIT句について

使用した関数の説明

コメントする コメントをキャンセル

コメントするコメントをキャンセル