PHPでのデータベースプログラミング

カテゴリ：PHP編

目次
PHPでWebアプリケーションを作成する場合、データベースとの連携は欠かせません。 この記事では殆どのレンタルサーバーで用意されているデータベースであるMySQLを使用して、データベース操作を行うための方法について解説します。

データベースを操作するモジュール

MySQLを操作するためには、PDOもしくはmysqliモジュールを使用します。

PDOとmysqli

PDO（PHP Data Objects）は、データベースを抽象化し、MySQL以外のデータベース（PostgreSQLなど）も同じメソッドを使用して操作できる汎用的なAPIを提供します。 一方、mysqliはMySQL（MySQL4.1以上）専用のAPIとなり、他のデータベースの操作には使用できません。その反面、PDOには無い詳細なMySQLの機能を利用できるという利点があります。 ここではモダンなプログラミング手法で記述でき、将来的にデータベースの乗り換えも手軽であるPDOモジュールを用いることにします。

サンプルのデータベースにはSQLの副問合せとは？で使用したブログ用テーブル（blogとcategory_listの2つのテーブルから成る）を使用することにします。 

CREATE TABLE category_list (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(100) UNIQUE KEY NOT NULL
);

CREATE TABLE blog (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    text TEXT NOT NULL,
    date DATETIME,
    category_id INT NOT NULL,
    FOREIGN KEY (category_id) REFERENCES category_list(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

参考サンプルのblogデータベースを作成するには、MySQLにログイン（mysql -u root -p）し、
create database blog;
use blog
を実行し、上記を貼り付けて実行してください。

データベースへの接続

PDOモジュールでデータベースに接続する場合、以下のように記述します。 

$db = new PDO($dsn, $username, $password);

$dsnはデータソース、$usernameはユーザー名、$passwordはパスワードとなり、それらをコンストラクタの引数に渡します。

例） 

$dsn = "mysql:host=localhost;dbname=blog";
$username = "root";
$password = "pass";
$db = new PDO($dsn, $username, $password);

SQLクエリの実行

PDOでSQLクエリを実行する場合、先程作成した$db（PDOのインスタンス）からquery()メソッドを呼び出し、実行したいSQLクエリ文を引数として渡します。 

$db->query("SQLクエリ文");

Notequery()メソッドは実行結果を返さないSQLコマンド（INSERT、UPDATE、DELETE）を実行するためのメソッドです。

テーブルへのデータ挿入

以下の例ではcategory_listテーブルにカテゴリー名を挿入しています。 

$db->query("INSERT INTO category_list (category) VALUES ('データベース')");

次はblogテーブルに記事を追加します。カテゴリーカラムはcategory_listへの副問合せで「データベース」のidを取得し挿入しています。 

$db->query("INSERT INTO blog (title, text, date, category_id) VALUES ('ブログ始めました', '今日はデータベースのお勉強をしました。', NOW(), (SELECT id FROM category_list WHERE category = 'データベース'))");

テーブルデータの取得

追加したレコードを表示するには$dbのprepare()メソッドにSELECT文を渡し、戻り値を$statementに格納します。 次に$statementのexecute()メソッドを呼び出しクエリを実行します。 

$statement = $db->prepare("SELECT id, (SELECT category FROM category_list WHERE category_id = id) AS category, title, text, date FROM blog WHERE id = 1");
$statement->execute();

Noteprepare()メソッドは実行結果を返すSQLコマンド（SELECT）を実行するためのメソッドです。

$statementにはデータベースのクエリ結果が配列として格納されているため、while文を用いて1要素ずつ取り出し出力します。 配列から1要素ずつ取り出すためにはfetch()メソッドを使用します。 

while ($row = $statement->fetch()) {
    print_r($row);
}

最後に$statementを空にしておきます。 

$statement = null;

ここまでのコードを実際に実行し、データベースへの挿入、抽出を実行してみます。

サンプル

ファイル名：sample1.php 

//データベース接続
$dsn = "mysql:host=localhost;dbname=blog";
$username = "root";
$password = "pass";
$db = new PDO($dsn, $username, $password);

//レコードの挿入
$db->query("INSERT INTO category_list (category) VALUES ('データベース')");
$db->query("INSERT INTO blog (title, text, date, category_id) VALUES ('ブログ始めました', '今日はデータベースのお勉強をしました。', NOW(), (SELECT id FROM category_list WHERE category = 'データベース'))");

//レコードの抽出
$statement = $db->prepare("SELECT id, (SELECT category FROM category_list WHERE category_id = id) AS category, title, text, date FROM blog WHERE id = 1");
$statement->execute();

//クエリ結果を出力
while ($row = $statement->fetch()) {
    print_r($row);
}

$statement = null;

以下のように出力されたでしょうか。

実行例） 

$ php sample1.php
Array
(
    [id] => 1
    [0] => 1
    [category] => データベース
    [1] => データベース
    [title] => ブログ始めました
    [2] => ブログ始めました
    [text] => 今日はデータベースのお勉強をしました。
    [3] => 今日はデータベースのお勉強をしました。
    [date] => 2020-06-06 16:27:30
    [4] => 2020-06-06 16:27:30
)

テーブルデータの変更

テーブルのレコードを変更する場合は、結果を取得する必要がないため、query()メソッドを使用します。 先程のブログ記事のtitleカラムを変更してみます。 

$db->query("UPDATE blog SET title = 'データベースの勉強' WHERE id = 1");

サンプル

ファイル名：sample2.php 

$dsn = "mysql:host=localhost;dbname=blog";
$username = "root";
$password = "pass";
$db = new PDO($dsn, $username, $password);

//レコードの更新
$db->query("UPDATE blog SET title = 'データベースの勉強' WHERE id = 1");

$statement = $db->prepare("SELECT id, (SELECT category FROM category_list WHERE category_id = id) AS category, title, text, date FROM blog WHERE id = 1");
$statement->execute();

while ($row = $statement->fetch()) {
    print_r($row);
}

$statement = null;

以下のように表示されたでしょうか。

実行例） 

$ php sample2.php
Array
(
    [id] => 1
    [0] => 1
    [category] => データベース
    [1] => データベース
    [title] => データベースの勉強
    [2] => データベースの勉強
    [text] => 今日はデータベースのお勉強をしました。
    [3] => 今日はデータベースのお勉強をしました。
    [date] => 2020-06-06 16:27:30
    [4] => 2020-06-06 16:27:30
)

トランザクション

トランザクションはデータベースへの一連の変更処理をひとまとめにしたものです。 例えば銀行口座からお金を振り込む場合、自分の口座の残高から振込額を減算、相手口座の残高に振込額を加算する処理などが挙げられます。

トランザクションを実施する場合は、まず対象のコードを try ブロック内に記述します。またトランザクションに失敗した場合の処理は catch ブロック内に記述します。 

try {
    //トランザクションのコード
}
catch (Exception $error) {
    //トランザクション失敗時のコード
    $db->rollback(); //トランザクションに失敗したらロールバック
    echo "トランザクション失敗：" . $error->getMessage();
}

Noteトランザクション失敗時のrollback()メソッドはトランザクションの処理をキャンセル（トランザクション開始直前の状態に戻す）します。

次に、try ブロック内でbeginTransaction()メソッドを呼びトランザクションの開始（オートコミット無効）を指示します。 

try {
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //エラー、例外レポートを有効
    $db->beginTransaction(); //ここからトランザクション処理を開始
    ...
}

Noteエラーレポートを受け取るために、上記の例のようにsetAttribute()メソッドにエラーレポート（PDO::ATTR_ERRMODE）、例外のスロー（PDO::ERRMODE_EXCEPTION）属性を渡しておくと良いでしょう。

トランザクションで実行する一連のSQL文はexec()メソッドで指定し、最後にcommit()メソッドを呼び出せば、トランザクションを実行します。 

try {
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //エラー、例外レポートを有効
    $db->beginTransaction(); //ここからトランザクション処理を開始

    $db->exec("SQL文1");
    $db->exec("SQL文2");

    $db->commit(); //トランザクションを実行
}

Noteオートコミットモードを無効にした場合、exec()メソッドを実行するまでSQL文は実行（コミット）されません。

以下の例では、category_listテーブルのid 1のレコードのid値を2に変更した上で、id 1に新しい行を追加する処理をトランザクションで実施しています。

サンプル

ファイル名：sample3.php 

$dsn = "mysql:host=localhost;dbname=blog";
$username = "root";
$password = "pass";
$db = new PDO($dsn, $username, $password);

//トランザクション
try {
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //エラー、例外レポートを有効
    $db->beginTransaction(); //ここからトランザクション処理を開始

    $db->exec("UPDATE category_list SET id = 2 WHERE id = 1");
    $db->exec("INSERT INTO category_list (id, category) VALUES ('1', 'PHP言語')");

    $db->commit(); //トランザクションを実行
}
catch (Exception $error) {
    $db->rollback(); //トランザクションに失敗したらロールバック
    echo "トランザクション失敗：" . $error->getMessage();
}
//トランザクションここまで

$statement = $db->prepare("SELECT * from category_list");
$statement->execute();

while ($row = $statement->fetch()) {
    print_r($row);
}

$statement = null;

実行例） 

$ php sample3.php
Array
(
    [id] => 1
    [0] => 1
    [category] => PHP言語
    [1] => PHP言語
)
Array
(
    [id] => 2
    [0] => 2
    [category] => データベース
    [1] => データベース
)

SQLインジェクション対策

最後にセキュリティ上必須となるSQLインジェクション対策について説明します。 SQLインジェクションはSQL文中の値に不正なSQLコマンドを指定することで、データベースの不正操作（改竄や消去）を試みる攻撃を指します。 SQLインジェクションを防ぐためには、値自体がクエリとして解釈されないように処理する必要があります。

具体的には、値の入った変数を直接SQL文中に指定するのではなく、bindValue()メソッドを使用して値をバインドする手法を取ります。（これをプレースホルダと呼びます）

対策前： 

$id = 1;
$statement = $db->prepare("SELECT * from blog WHERE id =" . $id);
$statement->execute();

対策後： 

$id = 1;
$statement = $db->prepare("SELECT * from blog WHERE id = :id");
$statement->bindValue(':id', $id, PDO::PARAM_INT); //:idに$idをバインド
$statement->execute();

サンプル

ファイル名：sample4.php 

$dsn = "mysql:host=localhost;dbname=blog";
$username = "root";
$password = "pass";
$db = new PDO($dsn, $username, $password);

$id = 1;
$statement = $db->prepare("SELECT * from blog WHERE id = :id");
$statement->bindValue(':id', $id, PDO::PARAM_INT);
$statement->execute();

while ($row = $statement->fetch()) {
    print_r($row);
}

$statement = null;

実行例） 

$ php sample4.php
Array
(
    [id] => 1
    [0] => 1
    [title] => データベースの勉強
    [1] => データベースの勉強
    [text] => 今日はデータベースのお勉強をしました。
    [2] => 今日はデータベースのお勉強をしました。
    [date] => 2020-06-06 16:27:30
    [3] => 2020-06-06 16:27:30
    [category_id] => 2
    [4] => 2
)

以上、PHPでのデータベースプログラミングの解説でした。

公開日時：2020年06月07日 08:09:53
最終更新日時：2022年08月22日 10:36:27

なお、レンタルサーバー選びで迷ったらこちらの記事で主要レンタルサーバーのプランと料金を比較していますので、是非参考にしてみてください。

PHP編に戻る

「PHP編」に関する他の手順
カテゴリ一覧

このページのトップに戻る