PDOクラスでデータベース操作

データベースを利用する

前回までphpMyAdminでデータベースの操作を行ってきました。
PHPは、前はMySQLやPostgreSQLなどの種類に応じてそれぞれ専用の関数を使い操作してきましたが、PHP5.1以降は各RDBMSを操作できるPDOクラスが搭載されています。
今回はPDOクラスを利用してユーザー情報を登録するアプリを作成します。

データベース抽象化レイヤー

データベース抽象化レイヤーはデータベース間の違いを吸収し、共通の方法でデータベース管理システムを操作するためのクラスです。
各DBMSは専用のクライアントライブラリを備えていて、例えばMySQLに接続するときはmysql_connectという関数のように各データベース専用の関数を使うのですが、データベース抽象化レイヤーを利用することで共通の命令を使って操作します。

抽象化レイヤーの種類

PHPでは抽象化レイヤーとして以下の３種類が標準で用意されています。

抽象化レイヤー内容
PDOPHP Data Objects。リレーショナルデータベース管理システムを扱うための機能がたくさんあります
ODBC主にWindows上で使用される汎用的な抽象化レイヤー。PHP以外の言語でも扱え、PHPでは抽象化を行わず、ODBCのライブラリで抽象化する
DBADBMと呼ぶデータベースのための抽象化レイヤー。DBMはRDBMSとは異なる仕組みを持つデータベース。

MySQLでは最も適しているPDOを利用することが望ましいです。こちらのサイトでもPDOを利用して説明していきます。

PDOに同梱されているドライバー

PDOには、PDOのコアモジュールと各データベースのクライアントライブラリを橋渡しするためのドライバーが同梱されています。他にもありますがとりあえず以下を覚えておいてください。

ドライバーサポートされるデータベース
PDO_
MYSQL		MySQL 3.x~5.x
PDO_PGSQLPostgreSQL
PDO_SQLITESQLite 2~3
PDO_DBLIBMicrosoft SQL Server / FreeTDS / Sybase
PDO_SQLSRVMicrosoft SQL Server / SQL Azure
PDO_OCIOracle Call Interface
PDO_IBMIBM DB2
PDO_ODBCODBC v3

データベース接続と切断

PDOクラスを使ってMySQLの操作を行います。

PHPでMySQLのデータベースに接続する

ここでPDOクラスを利用してデータベースに接続と切断処理を行います。接続できなかった時はエラー処理を行います。

PDOクラスのコンストラクターはデータベースに接続し、データベースを操作するためのオブジェクトを返します。構文は下記。

PDO::__construct(string $dsn, $username, $username, $password[, array $options])

パラメータの$dsnは、データベースが稼働するサーバーのホスト名やデータベース名を指定します。

'mysql:host=ホスト名; port=番号; dbname=データベース名; charset=文字セット;'
  • mysql: PDOドライバー名。接頭辞として指定
  • host: データベースサーバーが存在するホスト名を指定します
  • port: データベースサーバーが待機しているポート番号を必要に応じて指定
  • dbname: データベース名を指定
  • charset: エンコーディング方式

今回はローカルマシンでMySQLを稼働させますので、ホスト名は「localhost」、またはIPアドレス「127.0.0.1」と指定すればOKです。

コンストラクターを呼び出せばデータベースに接続してPDOオブジェクトを返されます。
切断をする時はPDOオブジェクトにNULLをセットします。データベースの切断はプログラムの終了時に自動的に行われますが、明示的に切断した方がいいです。

$pdo = new PDO($dsn, $user, $password[, $options]);//データベースに接続してインスタンス化
$db = NULL;//切断

接続に失敗した時の処理

接続に失敗すると、コンストラクターはPDOExceptionクラスのオブジェクトを投げます。
例外処理を行う場合はtry…catch文でしたね。catchブロックでPDOExceptionオブジェクトを取得し、メッセージを表示して処理を終了します。

try{
//接続処理
} catch (PDOEXception $e) {
  exit("データベース接続エラー：{$e->getMessage()}");
}

データベース接続できたか確認するプログラム

では、実際にデータベース接続できたか確認するプログラムを作成します。

<?php
$dsn = 'mysql:dbname=store; host=127.0.0.1';
$user = 'root';
$password = 'pass';

try {
  $pdo = new PDO($dsn, $user, $password);
  print '接続成功';
} catch (PDOException $e){
  exit("データベース接続エラー：{$e->getMessage()}");
}

$pdo = NULL;
?>

とりあえず上記を実行してみてください。下のようなエラーが出ます。

これはパスワードが違う時に表示されます。

自分が設定したパスワードを上記コードの$passwordに代入してみましょう。
パスワードを忘れてしまった人はXAMPPの環境構築のページから設定した値を確認しましょう。

パスワードを再設定して再度実行すると以下のように表示されます。

もし他のエラーが出た時は$dsnの中身や$userの中身など、コードの記述を確認しましょう。

ユーザー登録アプリケーションを作成する

では、ユーザー情報を入力してデータベースに登録するプログラムを作成します。

データベースとテーブルを用意する

データベースに登録するのでまずはデータベースとテーブルを作成しないといけませんね。
データベースは以前作成した「store」を使ってください。テーブル名は「user」としましょう。

カラムデータ型主キー
user_idVARCHAR(30)○
nameVARCHAR(30)
mailVARCHAR(30)
telVARCHAR(15)
userテーブル

接続ロジックを外部ファイル化する

まずはデータベースに接続する部分を外部ファイル化します。理由としてデータベースに接続するたびに毎回記述するのはめんどくさい冗長ですし、パスワードなどのような情報がそこら中に記述するのはまずいです。接続情報に変更があればその箇所を全て書き換えなければいけないからです。

ではdbconnect()関数を作成します。パスワードはご自身で決めたパスワードで、dbnameも自分で作成したのであれば都度変更してください。

<?php
function dbConnect(){

  $dsn = 'mysql:dbname=store; host=127.0.0.1; charset=utf8mb4';
  $user = 'root';
  $password = 'pass';

  try {
    $pdo = new PDO($dsn, $user, $password);
  } catch (PDOException $e){
    exit("データベース接続エラー：{$e->getMessage()}");
  }

  return $pdo;//戻り値はPDOオブジェクト
}
?>

登録情報入力画面を作成する

では、登録情報入力ページを作成しましょう。

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>ユーザー登録画面</title>
</head>
<body>
<h1>ユーザー登録画面</h1>
  <form action="userReg.php" method="POST">
    <dl>
      <dt>ユーザーID</dt>
      <dd><input type="text" name="user_id" size="20" maxlength="20" placeholder="IDを入力"></dd>
      <dt>氏名</dt>
      <dd><input type="text" name="name" size="20" maxlength="20" placeholder="氏名を入力"></dd>
      <dt>メールアドレス</dt>
      <dd><input type="text" name="mail" size="30" maxlength="30" placeholder="メールアドレスを入力"><dd>
      <dt>電話番号</dt>
      <dd><input type="text" name="tel" size="20" maxlength="15" placeholder="電話番号を入力"></dd>
    </dl>
    <input type="submit" value="登録">
  </form>
</body>

こんな画面になりましたでしょうか。maxlengthで文字数制限をしてデータベースに登録する時に、カラムの長さ以内に収まるようにしています。入力項目についてtypeはとりあえず全てtextにしています。

SQLのクエリはプリペアドステートメントを使って発行

先ほど作成した入力フォームから送られたデータをデータベースに登録するのですが、そのためにSQLのクエリを送信しなければなりません。以下の手順で行います。

クエリを生成

下記のようにクエリを用意します。

$sql = 'INSERT INTO user(user_id, name, mail, tel)
       VALUES(:user_id, :name, :mail, :tel)';

valuesの中に:user_idといった記述がありますが、これはプレースホルダーといって連想配列であり、キー名の先頭に「:」を付けて指定します。後に「:キー名=>値」と、実際に値を設定します。

プリペアドステートメントを生成

prepare()メソッドを使ってプリペアドステートメントを作成します。プリペアドステートメントとは上のようにSQL文を準備しておき、その後はクエリ内のパラメータだけを変更してクエリを実行できる仕組みのことです。作成は以下の通り。

$stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

$pdoにはPDOオブジェクト、$sqlにクエリを格納した変数、array()の中にオプションとしてプリペアドステートメント内のプレースホルダーにアクセスする方法を連想配列で指定します。キー名も値もPDOクラスの定数を使って指定します。上の例ですと前後に移動しながらプレースホルダーにアクセスできるようにします。
プレースホルダーにはカーソルという考え方があり、カーソルを進めるように順番にアクセスするのか、それとも進んだ後に戻れるようにするのかといったことを指定できます。

prepare()メソッドの戻り値はプリペアドステートメントを格納したPDOStatementオブジェクトを返します。処理に失敗した場合はFALSEを返し、PDOException例外を投げます。

execute()メソッドでプリペアドステートメント実行

プリペアドステートメントを生成しましたのでプレースホルダー(クエリ)に実際にデータを当てはめていきます。このことをバインドするといいます。メソッドの引数でプレースホルダーにデータをバインドして、実行します。構文は以下。

PDOStatement::execute($input_parameters)

実際にプレースホルダーに値をバインドしてプリペアドステートメントを実行します。

$stmt->execute(array(':user_id' => $_POST['user_id'],
                     ':name' => $_POST['name'],
                     ':mail' => $_POST['mail'],
                     ':tel' => $_POST['tel']));

1つのプレースホルダーに対して重複して値をバインドすることはできません。また、全てのプレースホルダーに値をバインドしないとエラーになります。

データベースへの登録を行うプログラムを作成

クエリを作成し、プリペアドステートメントの生成、実行するという流れでプログラムを記述します。

<?php
require_once 'dbManager.php';

try {
  $pdo = dbConnect();
  $sql = 'INSERT INTO user(user_id, name, mail, tel)
          VALUES(:user_id, :name, :mail, :tel)';
  $stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
  $stmt->execute(array(':user_id' => $_POST['user_id'],
                       ':name' => $_POST['name'],
                       ':mail' => $_POST['mail'],
                       ':tel' => $_POST['tel']));
  $pdo = NULL;
} catch(PDOException $e){
  exit("エラー発生：{$e->getMessage()}");
}

header('Location: http://'.$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF']).'/inputUser.php');

?>

では、dbManager.phpとinputUser.php、userReg.phpを作成できたところでinputUser.phpを表示し、テキトーに入力して登録してみてください。データベースのパスワードなどは各自設定してください。

登録画面
登録できたデータ

テーブルのデータを確認するとご自身が入力したデータが登録されていることが確認できましたね。

vindValue()を用いてクエリを実行する

上のプログラムではPDO::prepare→PDO::Statement::executeの２ステップでクエリを実行しましたが、こちらの方が手順が少なくて済みますが以下の注意点があります。

  • もし間違ったデータ型でバインドをしても、MySQL/SQLiteはデータベース側で自動的にキャストし直してくれますが、パフォーマンスの低下やバグの原因になるので、可能な限り避けた方がいいようです。
  • 既にbindValue()で値がバインドされていた場合でも、それらは全て無視され、全てのバインドをこの引数で行わなければなりません。

すなわち、PDO::prepare→PDOStatement::bindValue→PDO::Statement::executeの３ステップでクエリを実行した方が安心です。

他の箇所は省略しますが、vindValueを利用したコードは以下の通りです。

$stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->bindValue(':age', (int)$_POST['age'], PDO::PARAM_INT);//int型に指定
$stmt->bindValue(':name', $_POST['name']);
$stmt->execute();

bintValue()でバインドして値を設定してから実行するというステップで行っています。

登録データを確認するページを作成

次にデータを取得する方法を学習する目的でデータベースに登録されたデータを一覧で表示してみましょう。

SELECT…FROM文で取り出す

カラムに登録されたデータを取り出すにはSQLのSELECT…FROM文を使います。
１件のレコードに対して全てのカラムを取り出すことも特定のカラムだけを取り出すこともできます。
構文は以下の通りです。

SELECT * FROM テーブル名;
-- *は全ての列を表示する
SELECT カラム名1, カラム名2 FROM テーブル名;

取り出したデータの並び順を指定する場合以下のようにORDER BY句を使います。

SELECT *
FROM テーブル名
ORDER BY カラム名 ASC;
--カラム名に並び替えの基準になるカラムを指定、ASCで昇順に並び替えます。
--ASCの代わりにDESCを指定すると降順になります。

カーソルを使ってフェッチする

取り出したデータは結果セットとしてメモリ上に展開されますが、データを取り出しただけで宙ぶらりんの状態なのでPHP側に取り込む必要があります。

結果セットとはデータベースから取り出されたデータを一時的に保持する仮想テーブルのようなものです。

PDOStatement::fetch()メソッド

結果セットからレコードを取得します。

fetch($fetch_style)

$fetch_styleで取得したレコードをどのような形式のデータで返すのかを定数で指定します。

結果セットからレコードのデータを取り出すことをフェッチするといいます。フェッチをする代表的なメソッドがfetch()で、このメソッドで１行ずつレコードを取り出します。prepare()メソッドはプリペアドステートメントを格納したPDOStatementオブジェクトを生成しますが、このオブジェクトには、ステートメントを実行した後、結果セットが格納されるようになってます。
つまり、prepare()→execute()で結果セットを取得し、fetch()で取り出します。
結果セットから全てのレコードを取得するには繰り返し実行する必要があります。この際カーソルが使われます。

user_idnamemailtel
1testuser1abc@ex.jp1234567891
2testuser2efg@ex.jp1112223334
結果セット

初期状態のカーソルの位置はレコードの直前、user_idなどのカラムのところに設定されています。fetch()メソッドを最初に実行した時のカーソルの位置がuser_id「１」のレコードに移動し各カラムのデータを取り出します。その次にuser_id「２」になるという感じでメソッドを繰り返し実行することで全てのレコードを取り出していきます。最後の行に達したらfetch()はFALSEが返され終了します。

データの受け取り方は、先述の通りフェッチモードを指定することでどのような形式で受け取るのかを指定します。

定数取得できるデータの形式例
PDO::FETCH_BOTH配列/連想配列$row[0]/$row[‘カラム名’]
PDO::FETCH_ASSOC連想配列$row[‘カラム名’]
PDO::FETCH_NUM配列$row[0]
PDO::FETCH_BOUNDbindColumn()メソッドでバインドされたPHP変数$カラム名
PDO::FETCH_INTO指定したクラスのプロパティにフェッチ–
PDO::FETCH_NAMEDPDO::FETCH_ASSOCと同じ形式の連想配列だが、
同じ名前のカラムがあった場合は全ての値を返す		$row[‘カラム名’]
PDO::FETCH_OBJカラム名と同名のプロパティを有する匿名のオブジェクト$row->カラム名
PDO::FETCH_CLASS指定したクラスのインスタンス$row->カラム名

デフォルトではPDO::FETCH_BOTHとなっておりますが、無駄なデータのやりとりが発生するので基本的にはPDO::FETCH_ASSOCを使います。

登録データを一覧表示するページを作成

早速一覧表示するページを作成します。

<?php
require_once 'dbManager.php';
require_once 'Esc.php';
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>ユーザー一覧画面</title>
</head>
<body>
<h1>ユーザー一覧表示</h1>
<table border="2">
  <tr>
    <th>user_ID</th><th>name</th><th>mail</th><th>tel</th>
  </tr>
<?php
try{
  $pdo = dbConnect();
  $stmt = $pdo->prepare('SELECT * FROM user ORDER BY user_id ASC');
  $stmt->execute();
  while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
//一旦PHPを終了します
?>
  <tr>
    <td><?php esc($row['user_id']); ?></td>
    <td><?php esc($row['name']); ?></td>
    <td><?php esc($row['mail']); ?></td>
    <td><?php esc($row['tel']); ?></td>
  </tr>
<?php
  }
  $pdo = NULL;
}catch(PDOException $e){
  die("エラー発生：{$e->getMessage()}");
}
?>
</table>
</body>
</html>

どうでしょう、登録したデータの一覧が表示されましたでしょうか。次は編集可能にしましょう。

データベースのデータを読み込んで編集する

表示するだけでは面白くないので、編集できるようにしましょう。

input要素をテーブルに並べて編集できるよう表示する

input要素を並べてそこに取り出したデータを表示させて編集可能にします。送信する個々のデータはname属性の値で識別できますが、複数のレコードを扱うのでname属性だけでは識別できません。
今回はカウントする用の変数を後ろにくっつけて識別できるようにしましょう。

ではユーザーを一覧表示して、編集できる画面を作りましょう。

<?php
require_once 'dbManager.php';
require_once 'Esc.php';
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>ユーザー編集画面</title>
</head>
<body>
<h1>ユーザー編集</h1>
<form method="POST" action="update.php">
<table border="2">
  <tr>
    <th>user_ID</th><th>name</th><th>mail</th><th>tel</th>
  </tr>
<?php
try{
  $pdo = dbConnect();
  $stmt = $pdo->prepare('SELECT * FROM user ORDER BY user_id ASC');
  $stmt->execute();
  $count = 0;
  while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    $count++;
//一旦PHPを終了します
?>
  <tr>
    <td>
      <?php esc($row['user_id']); ?>
      <input type="hidden" name="user_id<?php print($count); ?>"
      value="<?php print esc($row['user_id']); ?>" />
    </td>
    <td>
      <input type="text" name="name<?php print($count); ?>"
      value="<?php print esc($row['name']); ?>" />
    </td>
    <td>
      <input type="text" name="mail<?php print($count); ?>"
      value="<?php print esc($row['mail']); ?>" />
    </td>
    <td>
      <input type="text" name="tel<?php print($count); ?>"
      value="<?php print esc($row['tel']); ?>" />
    </td>
  </tr>
<?php
  }

}catch(PDOException $e){
  exit("エラー発生：{$e->getMessage()}");
}
?>
</table>
<input type="hidden" name="count" value="<?php print($count); ?>" />
<input type="submit" value="データ更新"/>
</form>
</body>
</html>

次に実際にテーブルの更新処理を行うプログラムを作成します。

更新するSQL文は以下の通り。

UPDATE テーブル名 SET カラム名 = 更新後のデータ WHERE 条件;
<?php
require_once "dbManager.php";
try {
  $pdo = dbConnect();
  $sql = 'UPDATE user SET user_id=:user_id, name=:name, mail=:mail,
           tel=:tel WHERE user_id=:user_id';
  $stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
  for($i = 1; $i <= $_POST['count']; $i++){//レコード数に達するまで処理を繰り返す
    //$stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
    $stmt->bindValue(':user_id', $_POST['user_id'.$i]);
    $stmt->bindValue(':name', $_POST['name'.$i]);
    $stmt->bindValue(':mail', $_POST['mail'.$i]);
    $stmt->bindValue(':tel', $_POST['tel'.$i]);
    $stmt->execute();
  }
} catch(PDOException $e) {
  die("エラー発生：{$e->getMessage()}");
}

$pdo = NULL;
header('Location: http://'.$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF']).'/updateUser.php');
?>

では編集画面を表示してテキトーに入力して更新しましょう。

これで登録、表示、更新ができるようになりましたね。自分で１からデータベースを作るところからオリジナルのデータを作成するのもいいでしょう。

コメント

タイトルとURLをコピーしました