※この記事は専門学校の講義用に作成されたものです
今回はPHPからMySQLへ接続しデータの取得と追加の練習を行います。
データベースとのやり取りには様々な罠が待ち構えており、ちょっと書き損じた(設定を忘れた)だけでサービスが崩壊することもよくあります。誇張ではなく日常的に発生しており、例えばゲームやWebサービスがローンチしたとき、またはメンテナンスが開けたとき、サーバにまともにつながらずTwitterなどで話題になっているのを目にしますよね。またデータベースは個人情報はもとより、お金にまつわるデータも取り扱いますのでセキュリティにも気をかける必要がありますが、下手をすると漏洩や改ざんの温床になりかねません。
人類にデータベースは早すぎるのかもしれないと私もよく感じていますが、しかしながらこれらの事故の大半は「うっかりミス」だったりもします。情報をアップデートし続ける必要はありますが、基本や定石に忠実に従っていればトラブルを未然に防ぐことも可能です。このシリーズではそんな定石についてもおいおい取り上げていきたいと思います。
データベースの準備
PHP側のコードを書き始める前にデータベース側を準備しておきます。ここではMySQLのインストールや初期設定は完了している前提でお話します。
データベースとテーブルを作成
今回は例としてソーシャルゲーム用のデータベースと、ユーザー情報を格納するテーブルを作成することにします。テーブル定義は以下。これを「sgrpg.sql」という名前で保存しておきます。
/*---------------------------*/ /* データベースを新規作成 */ /*---------------------------*/ CREATE DATABASE sgrpg; USE sgrpg; /*---------------------------*/ /* テーブルを作成 */ /*---------------------------*/ -- ユーザー CREATE TABLE User( id integer AUTO_INCREMENT, lv integer, -- レベル exp integer, -- 経験値 money integer, -- 所持金 PRIMARY KEY(id) );
MySQLのクライアント経由で先ほどのSQLを実行します。ここではMySQLにログインするためのユーザーIDはsenpaiとしています。エラーメッセージなどが表示されなければ正常に終了しています。
$ mysql -u senpai -p < sgrpg.sql Enter password:
ではMySQLにログインし本当に作成が成功しているか確認します。show databasesでデータベースの一覧を表示することができます。
$ mysql -u senpai -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 28 Server version: 5.7.27-log MySQL Community Server (GPL) (中略) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sgrpg | | sys | +--------------------+ 5 rows in set (0.00 sec)
テーブルも作成が成功したか確認しておきましょう。use (データベース名)でこれから利用するデータベースを指定、desc (テーブル名)でテーブル構造を表示します。
mysql> use sgrpg; Database changed mysql> desc User; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | lv | int(11) | YES | | NULL | | | exp | int(11) | YES | | NULL | | | money | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
初期データを挿入
今回はデータを取り出して表示するところまでを取り上げますが、現状テーブルは空っぽなので最初のデータを人力で入れてあげます。User.idはAUTO_INCREMENTにしているので指定しません。
INSERT INTO User(lv, exp, money) VALUES (1, 1, 3000), (20, 23456, 80000), (99, 99999999, 120000);
これも同様に成功したか確認しておきます。
mysql> select * from User; +----+------+----------+--------+ | id | lv | exp | money | +----+------+----------+--------+ | 1 | 1 | 1 | 3000 | | 2 | 20 | 23456 | 80000 | | 3 | 99 | 99999999 | 120000 | +----+------+----------+--------+ 3 rows in set (0.00 sec)
PHPからMySQLを操作
データベースの準備が終わったら、先ほどのテーブルにPHPから接続してみましょう。
データを取得する
お待たせしました。ここからようやくメインディッシュですｗ ここでは適当な場所に「get.php」という名前で保存し、Terminalなどから以下のPHPを実行します。
<?php /** * MySQLに接続しデータを取得する * */ //------------------------------------------------- // 準備 //------------------------------------------------- $dsn = 'mysql:dbname=sgrpg;host=127.0.0.1'; // 接続先を定義 $user = 'senpai'; // MySQLのユーザーID $pw = 'indocurry'; // MySQLのパスワード // 実行したいSQL $sql = 'SELECT * FROM User'; // Userテーブルの全ての列を取得する //------------------------------------------------- // SQLを実行 //------------------------------------------------- $dbh = new PDO($dsn, $user, $pw); // 接続 $sth = $dbh->prepare($sql); // SQL準備 $sth->execute(); // 実行 // 取得した内容を表示する while( $buff = $sth->fetch(PDO::FETCH_ASSOC) ){ // 実行結果から1レコード取ってくる $id = $buff['id']; $lv = $buff['lv']; $exp = $buff['exp']; $money = $buff['money']; // 表示する printf("%d, %d, %d, %d\n", $id, $lv, $exp, $money); }
実行するとMySQLへ挿入したデータが表示されたでしょうか？
$ php get.php 1, 1, 1, 3000 2, 20, 23456, 80000 3, 99, 99999999, 120000
データを挿入する - その1「疑問符プレースホルダ」
データの挿入、更新、削除はほぼ同じコードで実現できます。以下のコードを「insert.php」として適当な場所に保存します。
<?php /** * MySQLに接続しデータを追加する * */ //------------------------------------------------- // 準備 //------------------------------------------------- $dsn = 'mysql:dbname=sgrpg;host=127.0.0.1'; // 接続先を定義 $user = 'senpai'; // MySQLのユーザーID $pw = 'indocurry'; // MySQLのパスワード // 実行したいSQL $sql = 'INSERT INTO User(lv, exp, money) VALUES(?,?,?)'; // 挿入したいデータ $lv = 5; $exp = 3000; $money = 15000; //------------------------------------------------- // SQLを実行 //------------------------------------------------- $dbh = new PDO($dsn, $user, $pw); // 接続 $sth = $dbh->prepare($sql); // SQL準備 // プレースホルダに値を入れる $sth->bindValue(1, $lv, PDO::PARAM_INT); $sth->bindValue(2, $exp, PDO::PARAM_INT); $sth->bindValue(3, $money, PDO::PARAM_INT); // 実行 $sth->execute();
こちらも同様にTerminalなどで実行した後に、MySQL上にデータが新しく追加されたか確認します。
$ php insert.php
4レコード目にPHPからデータが追加されたのがわかりますね。
$ mysql -u senpai -p (中略) mysql> select * from User; +----+------+----------+--------+ | id | lv | exp | money | +----+------+----------+--------+ | 1 | 1 | 1 | 3000 | | 2 | 20 | 23456 | 80000 | | 3 | 99 | 99999999 | 120000 | | 4 | 5 | 3000 | 15000 | +----+------+----------+--------+
データを挿入する - その2「名前付けされたプレースホルダ」
データの挿入、更新、削除は別の書き方もできます。以下のコードを「insert2.php」として適当な場所に保存します。
<?php /** * MySQLに接続しデータを追加する * */ //------------------------------------------------- // 準備 //------------------------------------------------- $dsn = 'mysql:dbname=sgrpg;host=127.0.0.1'; // 接続先を定義 $user = 'senpai'; // MySQLのユーザーID $pw = 'indocurry'; // MySQLのパスワード // 実行したいSQL $sql = 'INSERT INTO User(lv, exp, money) VALUES(:lv,:exp,:money)'; // 挿入したいデータ $lv = 6; $exp = 4000; $money = 20000; //------------------------------------------------- // SQLを実行 //------------------------------------------------- $dbh = new PDO($dsn, $user, $pw); // 接続 $sth = $dbh->prepare($sql); // SQL準備 // プレースホルダに値を入れる $sth->bindValue(':lv', $lv, PDO::PARAM_INT); $sth->bindValue(':exp', $exp, PDO::PARAM_INT); $sth->bindValue(':money', $money, PDO::PARAM_INT); // 実行 $sth->execute();
こちらも同様にTerminalなどで実行した後に、MySQL上にデータが新しく追加されたか確認します。
$ php insert2.php
5レコード目に先ほどのPHPからデータが追加されました。
$ mysql -u senpai -p (中略) mysql> select * from User; +----+------+----------+--------+ | id | lv | exp | money | +----+------+----------+--------+ | 1 | 1 | 1 | 3000 | | 2 | 20 | 23456 | 80000 | | 3 | 99 | 99999999 | 120000 | | 4 | 5 | 3000 | 15000 | | 5 | 6 | 4000 | 20000 | +----+------+----------+--------+ 5 rows in set (0.00 sec)
bindValueで指定するデータ型
サンプルでは整数型を示すPDO::PARAM_INTを利用しましたが、これ以外にも次のような定数が用意されています。
| 定数 | データ型 |
|---|---|
| PDO::PARAM_BOOL | 真偽値(Boolean) |
| PDO::PARAM_NULL | NULL |
| PDO::PARAM_INT | 整数(INTEGER) |
| PDO::PARAM_STR | 文字列(VARCHAR) |
| PDO::PARAM_LOB | バイナリや巨大なデータ |
詳細が知りたい場合は公式サイトを参照してください。 php.net
例外処理
サーバは生き物みたいなもので、いつ正常に動かなくなるか分かりません。ただデータベースに問い合わせる度に毎回戻り値をチェックするのは正直面倒です。そこで実行時エラーが発生したら例外を投げ、try〜catchで補足することができます。
PDOのインスタンス作成後に$dbh->setAttribute()でエラーモードを変更するだけです。
try{
// 接続
$dbh = new PDO($dsn, $user, $pw);
// ★エラー時に例外を発生させる★
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL準備
$sth = $dbh->prepare($sql);
// プレースホルダに値を入れる
$sth->bindValue(':lv', $lv, PDO::PARAM_INT);
$sth->bindValue(':exp', $exp, PDO::PARAM_INT);
$sth->bindValue(':money', $money, PDO::PARAM_INT);
// 実行
$sth->execute();
}
catch( PDOException $e ) {
printf("[Error] %s¥n", $e->getMessage());
}
RESTful API化する
最後に応用です。せっかくPHPで書いたものですのでJavaScriptから取得できるようにAPI化します。以下のソースコードをWebサーバから見える場所(ドキュメントルート)に保存します。
MySQLのデータを返却する
ソースコード
<?php /** * MySQLに接続しデータを取得する * */ // 以下のコメントを外すと実行時エラーが発生した際にエラー内容が表示される // ini_set('display_errors', 'On'); // ini_set('error_reporting', E_ALL); //------------------------------------------------- // 引数を受け取る //------------------------------------------------- // ユーザーIDを受け取る $uid = isset($_GET['uid'])? $_GET['uid']:null; // Validation if( ($uid === null) || (!is_numeric($uid)) ){ sendResponse(false, 'Invalid uid'); exit(1); } //------------------------------------------------- // 準備 //------------------------------------------------- $dsn = 'mysql:dbname=sgrpg;host=127.0.0.1'; // 接続先を定義 $user = 'senpai'; // MySQLのユーザーID $pw = 'indocurry'; // MySQLのパスワード // 実行したいSQL $sql1 = 'SELECT * FROM User WHERE id=:id'; // Userテーブルの指定列を取得 //------------------------------------------------- // SQLを実行 //------------------------------------------------- try{ $dbh = new PDO($dsn, $user, $pw); // 接続 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // エラーモード $sth = $dbh->prepare($sql); // SQL準備 // プレースホルダに値を入れる $sth->bindValue(':id', $uid, PDO::PARAM_INT); // 実行 $sth->execute(); // 実行結果から1レコード取ってくる $buff = $sth->fetch(PDO::FETCH_ASSOC); } catch( PDOException $e ) { sendResponse(false, 'Database error: '.$e->getMessage()); // 本来エラーメッセージはサーバ内のログへ保存する(悪意のある人間にヒントを与えない) exit(1); } //------------------------------------------------- // 実行結果を返却 //------------------------------------------------- // データが0件 if( $buff === false ){ sendResponse(false, 'Not Fund user'); } // データを正常に取得 else{ sendResponse(true, $buff); } /** * 実行結果をJSON形式で返却する * * @param boolean $status * @param array $value * @return void */ function sendResponse($status, $value=[]){ header('Content-type: application/json'); echo json_encode([ 'status' => $status, 'result' => $value ]); }
実行する
ブラウザから実行してみましょう。
存在しているidを指定するとデータベースのレコードがまるっと返ってきます。
存在しないidを指定すると「Not Found」が返されます。
実行時エラーのときの挙動も確認しておきましょう。ここではMySQLへログインする際のユーザーIDを存在しないものに変更しました。
この状態で実行すると無事に例外を補足出来ていることがわかります。
MySQLにデータを追加する
ここでは新しくユーザーを追加し、追加したレコードのidを返却しています。
ソースコード
<?php /** * MySQLに接続しデータを追加する * */ // 以下のコメントを外すと実行時エラーが発生した際にエラー内容が表示される // ini_set('display_errors', 'On'); // ini_set('error_reporting', E_ALL); //------------------------------------------------- // 初期値 //------------------------------------------------- define('DEFAULT_LV', 1); define('DEFAULT_EXP', 1); define('DEFAULT_MONEY', 3000); //------------------------------------------------- // 準備 //------------------------------------------------- $dsn = 'mysql:dbname=sgrpg;host=127.0.0.1'; // 接続先を定義 $user = 'senpai'; // MySQLのユーザーID $pw = 'indocurry'; // MySQLのパスワード // 実行したいSQL $sql1 = 'INSERT INTO User(lv, exp, money) VALUES(:lv, :exp, :money)'; $sql2 = 'SELECT LAST_INSERT_ID() as id'; // AUTO INCREMENTした値を取得する //------------------------------------------------- // SQLを実行 //------------------------------------------------- try{ $dbh = new PDO($dsn, $user, $pw); // 接続 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // エラーモード //------------------------------------------------- // 新規にレコードを作成 //------------------------------------------------- // SQL準備 $sth = $dbh->prepare($sql1); $sth->bindValue(':lv', DEFAULT_LV, PDO::PARAM_INT); $sth->bindValue(':exp', DEFAULT_EXP, PDO::PARAM_INT); $sth->bindValue(':money', DEFAULT_MONEY, PDO::PARAM_INT); // 実行 $sth->execute(); //------------------------------------------------- // AUTO INCREMENTした値を取得 //------------------------------------------------- // SQL準備 $sth = $dbh->prepare($sql2); // 実行 $sth->execute(); // 実行結果から1レコード取ってくる $buff = $sth->fetch(PDO::FETCH_ASSOC); } catch( PDOException $e ) { sendResponse(false, 'Database error: '.$e->getMessage()); // 本来エラーメッセージはサーバ内のログへ保存する(悪意のある人間にヒントを与えない) exit(1); } //------------------------------------------------- // 実行結果を返却 //------------------------------------------------- // データが0件 if( $buff === false ){ sendResponse(false, 'Database error: can not fetch LAST_INSERT_ID()'); } // データを正常に取得 else{ sendResponse(true, $buff['id']); } /** * 実行結果をJSON形式で返却する * * @param boolean $status * @param array $value * @return void */ function sendResponse($status, $value=[]){ header('Content-type: application/json'); echo json_encode([ 'status' => $status, 'result' => $value ]); }
実行する
ブラウザからアクセスすると、意図した挙動をするでしょうか？再読み込みをする度にresultで返される数値が上がっていきます。
MySQLを直接のぞきレコードが正常に追加されていれば成功です。
mysql> select * from User; +----+------+----------+--------+ | id | lv | exp | money | +----+------+----------+--------+ | 1 | 1 | 1 | 3000 | | 2 | 20 | 23456 | 80000 | | 3 | 99 | 99999999 | 120000 | | 4 | 5 | 3000 | 15000 | | 5 | 6 | 4000 | 20000 | | 6 | 10 | 3000 | 15000 | | 7 | 6 | 4000 | 20000 | | 8 | 1 | 1 | 3000 | | 9 | 1 | 1 | 3000 | +----+------+----------+--------+ 9 rows in set (0.00 sec)
