[PHP] MySQLをPDOで操作する

この記事は 2020年6月21日 に書かれたものです

※この記事は専門学校の講義用に作成されたものです

今回はPHPからMySQLへ接続しデータの取得と追加の練習を行います。

データベースとのやり取りには様々な罠が待ち構えており、ちょっと書き損じた(設定を忘れた)だけでサービスが崩壊することもよくあります。誇張ではなく日常的に発生しており、例えばゲームやWebサービスがローンチしたとき、またはメンテナンスが開けたとき、サーバにまともにつながらずTwitterなどで話題になっているのを目にしますよね。またデータベースは個人情報はもとより、お金にまつわるデータも取り扱いますのでセキュリティにも気をかける必要がありますが、下手をすると漏洩や改ざんの温床になりかねません。

人類にデータベースは早すぎるのかもしれないと私もよく感じていますが、しかしながらこれらの事故の大半は「うっかりミス」だったりもします。情報をアップデートし続ける必要はありますが、基本や定石に忠実に従っていればトラブルを未然に防ぐことも可能です。このシリーズではそんな定石についてもおいおい取り上げていきたいと思います。

1 データベースの準備
- 1.1 データベースとテーブルを作成
- 1.2 初期データを挿入
2 PHPからMySQLを操作
3 RESTful API化する
- 3.1 MySQLのデータを返却する
  - 3.1.1 ソースコード
  - 3.1.2 実行する
- 3.2 MySQLにデータを追加する
  - 3.2.1 ソースコード
  - 3.2.2 実行する
4 続き
5 参考ページ

データベースの準備

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: 定義済み定数 - Manual

http://php.net/manual/ja/pdo.constants.php

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)

続き

[PHP] MySQLをPDOで操作する – 入門編その2 「トランザクション」

https://blog.katsubemakito.net/php/pdo-mysql2

※この記事は専門学校の講義用に作成されたものです前回に引き続きPHPからMySQLを操作します。今回のテーマは「トランザクション」への対応です。トランザクションをマスターすれば、2つ以上のSQLを実行した際にいずれかの実行が失敗した場合でも、そのすべてを無かったことにできちゃいます。データベースは堅牢に作ってあるとは言え、いつ障害など不測の事態が発生するか分かりません。データベースはユーザーの個人情報やお金など非常に重要なデータを扱うこともありますからうまく動作しなかった場合には元の健全な状態に戻して上げ...

ねこの足跡R

参考ページ

コメント欄は休止中です。お問い合わせはこちらからどうぞ。ご質問はTwitterにリプを投げてください。

このブログを応援する

お寄せいただいたお気持ちは全額サーバ代や次の記事を執筆するための原資として活用させていただいております。この記事が参考になった場合などぜひご検討ください。

PayPalで300円支払う
※金額は任意で変更できます。
※100円でも泣いて喜びますw
※住所の入力欄が現れた場合は「no needed」を選択ください
これまでのご協力者さま

- Sponsored Link -

[PHP] MySQLをPDOで操作する – 入門編その1

データベースの準備

データベースとテーブルを作成

初期データを挿入

PHPからMySQLを操作

データを取得する

データを挿入する – その1「疑問符プレースホルダ」

データを挿入する – その2「名前付けされたプレースホルダ」

bindValueで指定するデータ型

例外処理

RESTful API化する

MySQLのデータを返却する

ソースコード

実行する

MySQLにデータを追加する

ソースコード

実行する

続き

参考ページ

コメント

同じカテゴリの記事

ブログ内を検索

Languages (Google translation)

Sponsored Link

カテゴリー

Sponsored Link

最近人気のあるページ

Sponsored Link

Twitter

feed

Sponsored Link

データベースの準備

データベースとテーブルを作成

初期データを挿入

PHPからMySQLを操作

データを取得する

データを挿入する – その1「疑問符プレースホルダ」

データを挿入する – その2「名前付けされたプレースホルダ」

bindValueで指定するデータ型

例外処理

RESTful API化する

MySQLのデータを返却する

ソースコード

実行する

MySQLにデータを追加する

ソースコード

実行する

続き

参考ページ

コメント

関連した記事

同じカテゴリの記事

ブログ内を検索

Languages (Google translation)

Sponsored Link

カテゴリー

Sponsored Link

最近人気のあるページ

Sponsored Link

Twitter

feed

Sponsored Link