coding tips

posted:2014.06.24

[php] PDOを使ったデータベース処理

phpからデータベースMySQLへの接続方法と、データの追加、閲覧、削除の方法、エラー処理の方法について紹介します。

サンプルをダウンロード
from GitHub

PDOとは

PHPでデータベースを操作する方法の一つにPDOを使う方法があります。 PDOとはPHPからデータベースを操作するためのドライバの名称です。

PDOは従来のmysql_connect関数などに比べて様々なメリットがあります。 たとえば、従来の関数ではmysqlかPostgreSQLで使用する関数を変える必要がありました。 しかし、PDOならば個々のデータベース製品に依存せず、これを同じ記述で使いまわすことができ、汎用性の高いコードが書けます。 加えて、SQLインジェクション対策などセキュリティー面においても便利な機能があります。 また、従来のmysql_connectについてはPHP5.5の段階で非推奨となりました。 特にこだわりがなければPDOを使えばよいでしょう。

データベースの準備

プログラムに入る前にデータベースを準備しましょう。

MySQLにログインし、次のコマンドをそれぞれ実行してください。

/* ユーザ作成 */
GRANT ALL PRIVILEGES ON pdo_test.* to dbuser@'localhost' IDENTIFIED BY 'dbpass';

/* sampleデータベースを作成する */
CREATE DATABASE pdo_test;

/* sampleデータベースを使用する */
USE pdo_test;

/* userテーブルを作成する */
CREATE TABLE table_test
(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name varchar(30) NOT NULL
);

レコードの追加

それでは具体的にコードを見ていきましょう。 次に示すコードができるだけ短い記述で、PDOを用いたデータベース操作の処理を行うものです。

insert.phpはデータベースにレコードを追加する処理です。

  1. 接続先データベースの情報(データベース名、ホスト名、ユーザ名、パスワード)をそれぞれ指定
  2. PDOクラスに接続先データベースの情報をセットし、オブジェクト化
  3. SQL文の作成
  4. SQL文のオブジェクトを取得
  5. 値をパラメータにバインド
  6. SQL実行
  7. データベース接続オブジェクトを破棄
<?php
$dbname = 'pdo_test'; // データベース名
$host = 'localhost';  // ホスト名
$user = 'dbuser'; // ユーザ名
$password = 'dbpass'; //パスワード

// データベース接続オブジェクトを生成
$dbh = new PDO("mysql:dbname={$dbname};host={$host}", $user, $password);

// SQL文の作成
$sql = 'INSERT INTO table_test (name) VALUES (:name)';

// SQL文のオブジェクトを取得
$stmt = $dbh->prepare($sql);

// 値をパラメータにバインド
$stmt->bindValue(':name', 'テスト');

// SQL実行
$stmt->execute();

// データベース接続オブジェクトを破棄
$dbh = null;

次にレコードを取得する処理です。 手順はinsert.phpとほぼ一緒です。

SELECT文により取得したレコードはfetchAllメソッドによって配列として取得し、適宜データを抽出します。

<?php
$dbname = 'pdo_test'; // データベース名
$host = 'localhost';  // ホスト名
$user = 'dbuser'; // ユーザ名
$password = 'dbpass'; //パスワード

// データベース接続オブジェクトを生成
$dbh = new PDO("mysql:dbname={$dbname};host={$host}", $user, $password);

// SQL文の作成
$sql  = ' SELECT * FROM table_test ';

// SQL文のオブジェクトを取得
$stmt = $dbh->prepare($sql);

// SQL実行
$stmt->execute();

// 実行結果を配列で取得
$rows = $stmt->fetchAll();

// 配列を展開
foreach ($rows as $row) {
  echo $row['name'];  // データベースの値を取得
}

// データベース接続オブジェクトを破棄
$dbh = null;

PDOのエラー処理

データベースを操作していると様々なエラーが発生することがあります。 SQLの文法ミス、キーの重複、ネットワーク等環境に起因するもの、など挙げればきりがありません。

データベースで何かエラーが発生した場合には、そのエラーに応じて適切な後処理をする必要があります。 ではPDOを用いてエラー情報を取得する方法を見ていきましょう。

次のコードは先ほどのinsert.phpを少し修正して、PDOのエラー時に例外をスローさせ、それをtry-catch文で処理する方法です。 SQL文で意図的に文法エラーを起こしています。

<?php
$dbname = 'pdo_test'; // データベース名
$host = 'localhost';  // ホスト名
$user = 'dbuser'; // ユーザ名
$password = 'dbpass'; //パスワード

// データベース接続オブジェクトを生成
$dbh = new PDO("mysql:dbname={$dbname};host={$host}", $user, $password);

// PDOエラー時に例外をスローする
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// SQL文の作成(文法エラー)
$sql = 'ERR INSERT INTO table_test (name) VALUES (:name)';

// SQL文のオブジェクトを取得
$stmt = $dbh->prepare($sql);

// 値をパラメータにバインド
$stmt->bindValue(':name', 'テスト');

try {
  // SQL実行
  $stmt->execute();
} catch (PDOException $e) {
  // エラー処理
  echo 'pdo failed: '.$e->getMessage();
}

// データベース接続オブジェクトを破棄
$dbh = null;

実用例~ユーザーの登録削除~

サンプルをダウンロード
from GitHub

それではPDOを用いて具体的にアプリケーションを作ってみましょう。

今回はログインユーザーの情報を管理するためのアプリケーションを作ります。新規追加と削除、閲覧の機能があります。

まずはデータベースを準備しましょう。MySQLにログインし、次のコマンドを実行してください。

/* ユーザ作成 */
GRANT ALL PRIVILEGES ON sample.* to dbuser@'localhost' IDENTIFIED BY 'dbpass';

/* sampleデータベースを作成する */
CREATE DATABASE sample;

/* sampleデータベースを使用する */
USE sample;

/* userテーブルを作成する */
CREATE TABLE user
(
  id INT AUTO_INCREMENT PRIMARY KEY,
  login_id varchar(30) NOT NULL UNIQUE KEY,
  login_password char(32) NOT NULL,
  name varchar(30) NOT NULL,
  is_deleted CHAR(1) NOT NULL DEFAULT '0',
  update_date DATETIME NOT NULL
);

ここからがプログラムの部分です。

DatabaseCommonクラスはデータベース接続クラスの親クラスとなります。 データベースへのログイン情報を管理し、prepareなどの共通機能を子クラスに提供します。

コンストラクタでPDOオブジェクトを生成し、デストラクタで破棄します。

<?php
class DatabaseCommon {
  private $dbh;

  function __construct() {
    $dbname = 'sample';
    $host = 'localhost';
    $user = 'dbuser';
    $password = 'dbpass';

    try {
      $this->dbh = new PDO("mysql:dbname={$dbname};host={$host}", $user, $password);
      $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
      die('Database Connection failed: '.$e->getMessage());
    }
  }

  function __destruct() {
    $this->dbh = null;
  }

  protected function prepare($sql, $driverOptions = array() ) {
    return $this->dbh->prepare($sql, $driverOptions);
  }
}

databaseクラスはDatabaseCommonクラスを継承したデータベース操作用のクラスです。 目的の処理に合わせてSQL文を実行します。

insertUserメソッドではINSERT文を用いてユーザーを追加しています。

パスワード変数$loginPasswordについてはmd5関数でパスワード文字列のハッシュ値を取得しています。 最終的にこれをパスワード列(login_password)に与える値としています。 外部から攻撃された際にパスワードそのものが漏えいするリスクを軽減するためです。

ユーザーの削除はdeleteUserメソッドを使います。 SQL文を見てみるとUPDATE文を使用しています。 システムを構築する際にユーザー情報は、他のテーブルと関係を持つことが多々あります。 ログインすることがなくなったとしても、データとして整合性を保つため、データベース上は残しておくことがあります。

テーブルuserでは、列is_deletedが削除フラグとなっており、これが0の場合はアカウントが有効、1の場合は無効と判断することにしています。

<?php
require_once 'database_common.php';

class Database extends DatabaseCommon {
  function __construct() {
    parent::__construct();
  }

  /* ユーザ追加 */
  public function insertUser($loginId, $loginPassword, $name) {
    $loginPassword = md5($loginPassword);

    $sql  = ' INSERT INTO user ';
    $sql .= ' ( ';
    $sql .= '  login_id ';
    $sql .= ' ,login_password ';
    $sql .= ' ,name ';
    $sql .= ' ,update_date ';
    $sql .= ' ) VALUES ( ';
    $sql .= '  :login_id ';
    $sql .= ' ,:login_password ';
    $sql .= ' ,:name ';
    $sql .= ' ,NOW() ';
    $sql .= ' ) ';

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

    $stmt->bindValue(':login_id', $loginId);
    $stmt->bindValue(':login_password', $loginPassword);
    $stmt->bindValue(':name', $name);

    try {
      $stmt->execute();
    } catch (PDOException $e) {
      die('insertUser failed: '.$e->getMessage());
    }
  }

  /* ユーザ削除(フラグをセット) */
  public function deleteUser($id) {
    $sql  = 'UPDATE user SET is_deleted = :is_deleted WHERE id = :id';

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

    $stmt->bindValue(':is_deleted', '1');
    $stmt->bindValue(':id', $id, PDO::PARAM_INT);

    try {
      $stmt->execute();
    } catch (PDOException $e) {
      die('insertUser failed: '.$e->getMessage());
    }
  }

  /* 一覧HTML作成 */
  public function getUserList() {
    $this->selectUser($rows);
    $html =<<<_EOD
<table border="1">
<tr>
<th>login id</th>
<th>name</th>
<th>delete</th>
</tr>
_EOD;
    foreach ($rows as $row) {
      $html .= <<<_EOD
<tr>
<td>{$row['login_id']}</td>
<td>{$row['name']}</td>
<td>
<form method="post" action="index.php">
<input type="hidden" name="id" value="{$row['id']}">
<input type="submit" name="delete" value="delete">
</form>
</td>
</tr>
_EOD;
    }
    $html .= "</table>";
    echo $html;
  }

  /* 一覧用データ取得 */
  private function selectUser(&$rows) {
    $sql  = ' SELECT ';
    $sql .= ' id';
    $sql .= ',login_id';
    $sql .= ',name';
    $sql .= ' FROM user ';
    $sql .= ' WHERE is_deleted = :is_deleted ';
    $sql .= ' ORDER BY id ';

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

    $stmt->bindValue(':is_deleted', '0');

    try {
      $stmt->execute();
      $rows = $stmt->fetchAll();
    } catch (PDOException $e) {
      die('selectUser failed: '.$e->getMessage());
    }
  }
}

index.phpは画面管理を行います

postされた内容に応じて、削除と新規追加の処理を分けています。

<?php
require_once 'database.php';
$db = new Database();
// 追加
if (isset($_POST['insert'])) {
  $db->insertUser($_POST['loginId'], $_POST['loginPassword'], $_POST['name']);
}
// 削除
if (isset($_POST['delete'])) {
  $db->deleteUser($_POST['id']);
}
?>
<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>Document</title>
</head>
<body>
  <form method="post" action="index.php">
    <label>login id<input type="text" name="loginId"></label>
    <label>login password<input type="text" name="loginPassword"></label>
    <label>name<input type="text" name="name"></label>
    <input type="submit" name="insert" value="追加">
  </form>
  <?php $db->getUserList(); ?>
</body>
</html>
動作確認環境