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を用いて具体的にアプリケーションを作ってみましょう。
今回はログインユーザーの情報を管理するためのアプリケーションを作ります。新規追加と削除、閲覧の機能があります。
まずはデータベースを準備しましょう。MｙSQLにログインし、次のコマンドを実行してください。
/* ユーザ作成 */ 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>