[PHP]PDOを使ったMySQLデータベースへの接続と基本操作

PHP 5.5 以降では mysql_query 関数が非推奨になり、代替として MySQLi や PDO の使用が推奨されています。
にもかかわらず古い書籍を参考にしたプログラムでは依然として mysql_query を使用していたり、
PDO を使いつつもプリペアドステートメントを使用していないなどのケースが見られるため、
PDO と MySQL を使ったデータベースへの接続方法と INSERT, SELECT などの基本的なクエリについて考えてみたいと思います。

下記サンプルは現状 PHP 5.3.6 以降を想定しています。
仕様変更などで最新の方法でなくなった場合はこのページを修正もしくは削除する予定です。
情報の誤りや古くなっている内容があった場合はコメントやメールフォームでご連絡下さい。

・接続

<?php
define('DB_NAME', 'sampledb');
define('DB_HOST', 'localhost');
define('DB_USER', 'user');
define('DB_PASS', 'password');

$dsn = 'mysql:dbname=' . DB_NAME . ';host=' . DB_HOST . ';charset=utf8';
$option = array(
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
	PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
	PDO::ATTR_EMULATE_PREPARES => false,
	PDO::ATTR_STRINGIFY_FETCHES => false
);

try {
	$db = new PDO($dsn, DB_USER, DB_PASS, $option);
} catch (PDOException $e){
	echo $e->getMessage();
}

接続に必要なデータベース名などの情報は定数にしました。そのほうがクラス内で使う際にも都合が良いと思います。データベース名、ホスト名、文字コードは DSN の形で指定します。
ここでは文字コードとして「utf8」を指定しています。「utf-8」のようなハイフンを含めたものにしないで下さい。絵文字を扱う可能性がある場合は「utf8mb4」を指定して下さい。
PHP 5.3.6 以上で上記の文字コード指定をしていれば「SET NAMES」や「SET CHARACTER SET」は使用しません。

使用しているオプションは次のとおりです。目的によっては不要なものも有ります。

「PDO::ATTR_ERRMODE」 デフォルトのエラー発生時の処理方法を指定します。サンプルでは Exception の形で例外を投げます。

「PDO::ATTR_DEFAULT_FETCH_MODE」 SELECT 等でデータを取得する際の型を指定します。「PDO::FETCH_ASSOC」のとき配列変数の形で取得します。

「PDO::MYSQL_ATTR_USE_BUFFERED_QUERY」 クエリにバッファを使用するかどうかです。移植性に影響するので使わないケースも有りますが、SELECT した行数を取得する関数 rowCount() が使えるようになるというメリットが有ります。

「PDO::ATTR_EMULATE_PREPARES」 MySQLネイティブのプリペアドステートメント機能の代わりにエミュレートしたものを使う設定です。

「PDO::ATTR_STRINGIFY_FETCHES」 取得時した内容を文字列型に変換するかのオプションです。上記のエミュレートが有効なときは int 型のフィールドであってもこの指定にかかわらず文字列型に変換されます。

参考: PDO::setAttribute
http://php.net/manual/ja/pdo.setattribute.php

クエリ

値を代入する必要のない固定の SQL 文を実行する場合は query() を使います。

$sql = "INSERT INTO `users` (user_name) VALUES('John Doe')";

$db->query($sql);

query() は一つのクエリ文のみ実行できます。複数実行するには exec() を使います。

$sql = <<<SQL
INSERT INTO `users` (user_name) VALUES('John Doe');
INSERT INTO `users` (user_name) VALUES('Jane Doe');
SQL;

$db->exec($sql);

ただし SELECT などの実行結果を取得する場合には exec() は使用できません。

INSERT 文

PDO でクエリを組み立てる際、従来の mysql_real_escape_string のようなエスケープ方法ではなく、プリペアードステートメントを用います。
prepare() にプレースホルダ（挿入位置を決めるための文字列）を用いた SQL を渡し、実際の値は bindValue() や bindParam() などで指定します。
プレースホルダにバインド（代入）する際に内容は適切にエスケープされます。

データベースにユーザー名(VARCHAR)、会員番号(BIGINT)、メールアドレス(VARCHAR, NULL可)からなるレコードを挿入してみます。
SQL のプレースホルダーにはそれぞれ「:name」「:number」「:email」という名前をつけました。
パラメータ名はわかりやすいもので構いませんが、「:」から始まる必要があります。

$sql = <<<SQL
INSERT INTO `users` (`user_name`, `register_num`, `user_email`)
 VALUES(:name, :number, :email)
SQL;

$user_name = 'John';
$number = 1234;
$email  = null;

$stmt = $db->prepare($sql);
$stmt->bindValue(':name', $user_name, PDO::PARAM_STR);
$stmt->bindValue(':number', $number, PDO::PARAM_INT);

$pdo_type = is_null($email) ? PDO::PARAM_NULL : PDO::PARAM_STR;
$stmt->bindValue(':email', $email, $pdo_type);

$stmt->execute();

bindValue() はプレースホルダーのパラメータ名、値、型を指定します。
型は必須ではないのですが省略時は文字列型と判断されるので面倒でも指定します。
NULL を許可するフィールドの場合は型も切り替えておきます。

PDO::PARAM_STR は文字型、PDO::PARAM_INT は数値型で、FLOAT や DOUBLE などは有りません。
他にも PDO::PARAM_BOOL や PDO::PARAM_LOB などがあります。

参考: PDO 定義済み変数
http://www.php.net/manual/ja/pdo.constants.php

プレースホルダーには疑問符「?」を使うことも出来ます。

$sql = <<<SQL
INSERT INTO `users` (`user_name`, `register_num`, `user_email`)
 VALUES(?, ?, ?)
SQL;

この場合 bindValue を行う際は、1 から始まる番号を順番に指定します。

$stmt->bindValue(1, $user_name, PDO::PARAM_STR);
$stmt->bindValue(2, $number,    PDO::PARAM_INT);
$stmt->bindValue(3, $email,     $pdo_type);

SELECT 文

登録されている内容から次の条件でレコードを抽出します

・名前が「John」から始まる・会員番号が 1,000 以上・メールアドレスは NULL

SQL の組み立ては INSERT の時と同様です。
LIKE を使った部分一致をする場合、「%」は「:name%」とせずに、
bindValue の段階で「John%」という値を代入します。

$sql = <<<SQL
SELECT `user_name`, `register_num` FROM `users`
 WHERE `user_name` LIKE :name ESCAPE '#'
 AND `user_email` <=> :email
 AND `register_num` >= :number
SQL;

$stmt = $db->prepare($sql);

$user_name = 'John';
$user_name = preg_replace('/([_%#])/', '#$1', $user_name) . '%';

$number    = 1000;
$email     = null;

$stmt = $db->prepare($sql);
$stmt->bindValue(':name', $user_name, PDO::PARAM_STR);
$stmt->bindValue(':number', $number, PDO::PARAM_INT);

$pdo_type = is_null($email) ? PDO::PARAM_NULL : PDO::PARAM_STR;
$stmt->bindValue(':email', $email, $pdo_type);

$stmt->execute();

$result = $stmt->fetchAll();

var_dump($result);

NULL であるものを探す場合「`user_email` IS NULL」のようにするのが一般的ですが、プレースホルダーに NULL 以外の値を代入する可能性がある場合「<=>」を使って比較します。

MySQLの部分一致は「%」を使いますが、$user_name 自体に「%」や「_」というメタ文字が含まれている可能性を考慮してエスケープしておく必要があります。
「John_1」のような値が渡された場合「John#_1」のような形になるように置換します。この場合の「#_」はメタ文字ではない「_」であることを意味します。
メタ文字の前に何の文字を付けてエスケープするかは「LIKE :name ESCAPE ‘#’」とあるように ESCAPE をつかって決めることが出来ます。
サンプルではシャープ記号を使いましたが他の記号でも構いません。

PHP Archive

Webサイト開発初心者の備忘録

[PHP]PDOを使ったMySQLデータベースへの接続と基本操作

コメントを残す