phpからデータベースMySQLへの接続方法と、データの追加、閲覧、削除の方法、エラー処理の方法について紹介します。
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はデータベースにレコードを追加する処理です。
<?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;
データベースを操作していると様々なエラーが発生することがあります。 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;
それでは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>