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

mkdir :作業ディレクトリの作成
mysql -u root :MySQLにrootでログイン
create database ○○ :データベース作成
grant all on ○○(←DB名).* to □□(←ユーザ名)@localhost identified by ‘◇◇(←パスワード)’ :ユーザ、パスワードの設定
use ○○(←DB名) : データベース選択
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種類ある。

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

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

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

// 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と予約語を用いてデータを挿入する。

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

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というメリットが有る。
一方で、 :フィールド名の記述は、フィールド名が多い場合に便利。

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

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引数に次のように指定する。

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

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

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 "

"; // ブラウザでの表示をキレイにするために

var_dump($val);

echo "";

}

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

// disconnect

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

} catch (PDOException $e ){

// $db->rollback();

echo $e->getMessage()."\n";

exit;

}

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

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 "

";

var_dump($val);

echo "";

}

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

// disconnect

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

} catch (PDOException $e ){

// $db->rollback();

echo $e->getMessage()."\n";

exit;

}

like を使った抽出

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

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 "

"; // ブラウザでの表示をキレイにするために

var_dump($val);

echo "";

}

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を利用する。
数字を扱うので、データ型まで指定する。

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 "

";

var_dump($val);

echo "";

}

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では自動的にテーブルのカラムをクラスのプロパティとして設定してくれる。

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 "

";

$val->show();// クラスで指定したmethodがここで使える

echo "";

}

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

// disconnect

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

} catch (PDOException $e ){

// $db->rollback();

echo $e->getMessage()."\n";

exit;

}

レコードの更新・削除

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

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

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の移動の一方が何らかの理由で行われない時、作業を中断して元の状態に戻すまでの処理を考える。

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;

}

動作環境

テーブル設定

PDO設定

PDOとは

準備

テーブルの設定

prepare()

prepare()の基本的な使い方

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

bindValue()

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

bindParam()

query()による抽出

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

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

like を使った抽出

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

PDO::FETCH_CLASS

レコードの更新・削除

transaction

こちらの記事も読まれています

MySQL コマンド一覧

MySQL geometry型の基礎

MySQL view作成方法

DBはじめました。

MySQL 外部キー制約の基本

MySQL Cannot add foreign key constraint で怒られた