PHPとMySQLをPDO接続で連携する話

こんにちは。たきもとです。

先日はMySQLコマンド一覧をまとめました。
その時の記事はこちら。
MySQL コマンド一覧 | たきもとけんご.com
 
 
今回はPHPでMySQLを操作する方法について紹介します。

動作環境

windows8.1 64bit

vagrant 1.8.1
centOS 6.7
Apache 2.2.15

MySQL 14.14
PHP 5.6.20

PuTTY 0.67

Chrome    
 
 

テーブル設定

MySQLを立ち上げたら次の設定を行います。
これはお決まりの作業です。
詳しくは前回の記事を参考にして下さい。

前回の記事はこちら。
MySQL コマンド一覧 | たきもとけんご.com
 
 

1. mkdir :作業ディレクトリの作成
2. mysql -u root :MySQLにrootでログイン
3. create database ○○ :データベース作成
4. grant all on ○○(←DB名).* to □□(←ユーザ名)@localhost identified by ‘◇◇(←パスワード)’ :ユーザ、パスワードの設定
5. use ○○(←DB名) : データベース選択
6. create table ○○(←テーブル名) (); :テーブル設定

 
 

PDO設定

PDOとは

PHP Data Object MySQLをはじめ、様々なデータベースを扱えるようになる仕組みのこと。    

 
 

準備

index.php

define('DB_DATABASE', 'tst_db');
define('DB_USERNAME', 'tst_user');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e ){
    $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

データベース関連情報は定数としてまとめる。

DSN :Data Source Name データベースシステムに接続するための文字列。
今回はMySQLに接続。

データベースの操作は try, chatchで行う。
オプション(setAttribute)は次のページを参照。
ここではデフォルトのエラーハンドルを指定。
Exception の形で例外を投げる。
PDO::setAttribute | PHPマニュアル    
 
 

テーブルの設定

PDOでSQLを実行するコマンドは3種類ある。

1. excec(): 結果を返さない安全なSQL
2. query(): 結果を返す安全なSQL. 何度も実行されないSQL.
3. prepare(): 結果を返すSQL. 安全対策が必要.複数回実行されるSQL.

その時々のシチュエーションに合わせてどのコマンドを使うかを選択する。
prepare()はSQLをキャッシュしてくれるのでよく使われる（らしい）。

ここではexcecを利用して基本的なデータベースの接続とテーブルへのデータ挿入を実現する。

<?php

// DB設定
define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらchatch(例外処理)に移る

    // Insert
    $db->exec("insert into tstTable (name, tel) values
        ('takimoto','09011112222'),
        ('aaaa','09033334444'),
        ('bbbb','09055556666')
        ");

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    echo $e->getMessage()."\n"; // err時はメッセージを表示

    exit;
}

?>

 

prepare()

prepare()の基本的な使い方

prepare()を使うと,statementオブジェクトが返ってくる。
この返り値を変数に入れて更にコードを記述してゆくのが一般的。

先程と同様に execute() でMySQLに接続する。
ここではprepareと予約語を用いてデータを挿入する。

予約語は ? を使うが、実際には :変数名 という形を取れば何でも良い。
これについては後述する。

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // Insert
    // $db->exec("insert into tstTable (name) values
    $stmt = $db->prepare("insert into tstTable (name, tel) values (?, ?)");
    $stmt->execute(['taki', '08055554444']);
    echo 'inserted: '.$db->lastInsertId();// 最終IDが表示される

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    echo $e->getMessage()."\n";

    exit;
}

?>

 

prepare()で名前付きパラメータを使う

先程は ? を予約語にしたが、別のモノを試してみる。
先述した通り :フィールド名(カラム名) という形を用いる場合、excecute時には

• キー
• 値

という具合に扱う。

予約語 ? は挿入する値が何でもOKというメリットが有る。
一方で、 :フィールド名 の記述は、フィールド名が多い場合に便利。

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // Insert
    $stmt = $db->prepare("insert into tstTable (name, tel) values (:name, :tel)");
    $stmt->execute([':name' => '2ndPerson', ':tel' => '08055554444']);
    echo 'inserted: '.$db->lastInsertId();// 最終IDが表示される

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 

bindValue()

テーブルの一部を変更したい、またはフィールド(カラム)毎に値を設定したい時に便利な方法。
bindValueについてはこちらのページに詳細がまとめられています。
PDOStatement::bindValue | php.net    

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // Insert
    $stmt = $db->prepare("insert into tstTable (name, score) values (?, ?)");
    /**
    * bindValue(引数1, 引数2, 引数3);
    * 引数1 | prepareした予約語の?の位置を指定
    * 引数2 | 挿入する変数(値)
    * 引数3 | 挿入した値のデータ型を指定
    */
    $name  = 'taki';
    $stmt->bindValue(1, $name, PDO::PARAM_STR);
    $score = 23;
    $stmt->bindValue(2, $score, PDO::PARAM_INT);
    $stmt->execute();
    $score = 44;
    $stmt->bindValue(2, $score, PDO::PARAM_INT);
    $stmt->execute();

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

    bindValueの第3引数にはデータ型を指定する。
ここで指定するデータ型についてはこちら。
定義済み定数 | php.net

execute() の引数を指定しない場合は、bindValueで指定した値がそのまま実行される。
bindValueをループと併用すると、多くのデータの一部を変更するような処理が実現可能。  
 
   

名前付きパラメータを用いる場合

名前付きパラメータ使用時は、bindValue第1引数に次のように指定する。

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // Insert
    // $stmt = $db->prepare("insert into tstTable (name, score) values (?, ?)");
    $stmt = $db->prepare("insert into tstTable (name, score) values (:name, :score)");
    /**
    * bindValue(引数1, 引数2, 引数3);
    * 引数1 | prepareした予約語の?の位置を指定
    * 引数2 | 挿入する変数(値)
    * 引数3 | 挿入した値のデータ型を指定
    */
    $name  = 'taki';
    // $stmt->bindValue(1, $name, PDO::PARAM_STR);
    $stmt->bindValue(':name', $name, PDO::PARAM_STR);
    $score = 11;
    $stmt->bindValue(':score', $score, PDO::PARAM_INT);
    $stmt->execute();
    $score = 22;
    $stmt->bindValue(':score', $score, PDO::PARAM_INT);
    $stmt->execute();


    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 

bindParam()

• bindValue(): ?(予約語)と値をバインド
• bindParam(): 変数への参照をバインド

bindParam()では予め変数を指定しておき、後から変数の中身を変更することが可能。 コードをすっきりまとめたいときに。

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // Insert
    $stmt = $db->prepare("insert into tstTable (name, score) values (?, ?)");
    // $stmt = $db->prepare("insert into tstTable (name, score) values (:name, :score)");

    $name  = 'taki';
    $stmt->bindValue(1, $name, PDO::PARAM_STR);
    $stmt->bindParam(2, $score, PDO::PARAM_INT);
    $score = 11;
    $stmt->execute();
    $score = 22;
    $stmt->execute();
    $score = 33;
    $stmt->execute();


    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 

query()による抽出

本ページ上部でquery()について次のように記述しました。
改めて書くと次のようになります。

PDOでSQLを実行するコマンドは3種類ある

excec(): 結果を返さない安全なSQL
query(): 結果を返す安全なSQL. 何度も実行されないSQL.
prepare(): 結果を返すSQL. 安全対策が必要.複数回実行されるSQL.

ここではquery()とPDO::Fetchを用いた抽出方法を紹介する。
PDO::Fetchについてはこちらを参照。
PDOStatement::fetch | php.net    

指定したデータの全てを表示する場合は次のメソッドを利用する。
PDOStatement::fetchAll | php.net

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    /**
    * select
    *
    * fetchAll         | 全ての結果行を含む配列を返す
    * PDO::FETCH_ASSOC | キー, 値 のペアで結果が返ってくるオプション
    * rowCount()       | 対象の行数を返すメソッド
    */
    // select
    $stmt  = $db->query("select * from tstTable");
    $users = $stmt->fetchALL(PDO::FETCH_ASSOC);//レコードの表示

    foreach ($users as $val) {
        echo "<pre>"; // ブラウザでの表示をキレイにするために
        var_dump($val);
        echo "</pre>";
    }

    echo $stmt->rowCount()." records found !";

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 

prepare()を使った条件付き抽出

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // select
    $stmt  = $db->prepare("select score from tstTable where score > ?");
    $stmt->execute([20]);// ? の値を指定
    $users = $stmt->fetchALL(PDO::FETCH_ASSOC);

    foreach ($users as $val) {
        echo "<pre>";
        var_dump($val);
        echo "</pre>";
    }

    echo $stmt->rowCount()." records found !";

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 

like を使った抽出

likeを使ったあいまい検索についてはこちらのページで紹介しました。
MySQL コマンド一覧 | たきもとけんご.com    
ここではその応用として、idに 5 を含むデータを抽出する方法を紹介します。

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    /**
    * select
    *
    * fetchAll         | 全ての結果行を含む配列を返す
    * PDO::FETCH_ASSOC | キー, 値 のペアで結果が返ってくるオプション
    * rowCount()       | 対象の行数を返すメソッド
    */
    // select
    $stmt  = $db->prepare("select id from tstTable where id like ?");
    $stmt->execute(['%5%']);// 数字の5を含むidを抽出
    $users = $stmt->fetchALL(PDO::FETCH_ASSOC);

    foreach ($users as $val) {
        echo "<pre>"; // ブラウザでの表示をキレイにするために
        var_dump($val);
        echo "</pre>";
    }

    echo $stmt->rowCount()." records found !";

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 

order by ~~ desc を用いてデータを降順(逆順)に並べる方法

データの並べ替えには order を利用します。
このあたりのコマンドはこちらにまとめました。
MySQL コマンド一覧 | たきもとけんご.com    

execute()の引数は基本的には文字列で渡される。

limitを指定した場合は数字を扱うので、bindValueを利用する。
数字を扱うので、データ型まで指定する。

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    
    // select
    $stmt  = $db->prepare("select score from tstTable order by score desc limit ?");
    $stmt->bindValue(1, 1, PDO::PARAM_INT);
    $stmt->execute();
    $scores = $stmt->fetchALL(PDO::FETCH_ASSOC);

    foreach ($scores as $val) {
        echo "<pre>"; 
        var_dump($val);
        echo "</pre>";
    }

    echo $stmt->rowCount()." records found !";

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 

PDO::FETCH_CLASS

抽出したデータを直接クラスで使いたいときがある。
そんな時に PDO::FETCH_CLASSを利用すると便利。    

FETCH_CLASSでは自動的にテーブルのカラムをクラスのプロパティとして設定してくれる。

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

class ClassUser {
    // property
    // public $id;
    // public $name;
    // public $tel;
    // public $score;

    // method
    public function show(){
        echo $this->name."(".$this->id.")";
    }
}


try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // select
    $stmt  = $db->query("select * from tstTable");
    $users = $stmt->fetchALL(PDO::FETCH_CLASS, 'ClassUser');

    foreach ($users as $val) {// ここがClassUserになる
        echo "<pre>";
        $val->show();// クラスで指定したmethodがここで使える
        echo "</pre>";
    }

    echo $stmt->rowCount()." records found !";

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 

レコードの更新・削除

PHPからMySQLを操作してレコードの更新・削除を行うことができる。

• update: テーブルの更新
• delete: テーブルの削除

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);

class ClassUser {
    // property
    // public $id;
    // public $name;
    // public $tel;
    // public $score;

    // method
    public function show(){
        echo $this->name."(".$this->id.")";
    }
}


try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // update
    $stmt = $db->prepare("update tstTable set score = :score where id = :id");
    $stmt->execute([
        ':score' => 100,
        ':id'    => 16,
    ]);

    echo 'row has updated as follow:'. $stmt->rowCount();


    // delete
    $stmt = $db->prepare("delete from tstTable where id = :id");
    $stmt->execute([
        ':id'  => 17,
    ]);

     echo 'row has deleted as follow:'. $stmt->rowCount();




    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    // $db->rollback();
    echo $e->getMessage()."\n";

    exit;
}

?>

 
 

transaction

ある処理が確実に終わっていることを知りたい時がある。
そんなときに便利なのがtransactionだ。
transactionはある一連の処理が終わることを保障してくれる仕組み。
 
 
たとえば、次のようなテーブルがあるとする。

+----+------+------+-------+
| id | name | tel  | score |
+----+------+------+-------+
| 15 | taki | NULL |    11 |
| 16 | taki | NULL |   100 |
+----+------+------+-------+

id=16 から id=15 へ scoreを40点移動させることを考える。
このとき、
id:16 → 100 – 40 = 60
id:15 → 11 + 40 = 51
となるはず。

このscoreの移動の一方が何らかの理由で行われない時、作業を中断して元の状態に戻すまでの処理を考える。

<?php

define('DB_DATABASE', 'DB_1605');
define('DB_USERNAME', 'takimoto');
define('DB_PASSWORD', 'xxxxxxx');
define('PDO_DSN', 'mysql:dbhost=localhost; dbname='. DB_DATABASE);


try {
    // connect
    $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //errが出たらexceptionに移る

    // transaction
    $db->beginTransaction();// transaction開始の合図
    $db->exec("update tstTable set score = score - 40 where id = 16");
    $db->exec("update tstTable set score = score + 40 where id = 15");
    $db->commit();// 結果を反映させる合図

    // disconnect
    $db = null; //明示的に接続を切る

} catch (PDOException $e ){
    $db->rollback();// transactionのどちらかが失敗したら元に戻す
    echo $e->getMessage()."\n";

    exit;
}

?>