RitoLabo

PHP7とMySQLi拡張モジュールでデータベース操作を行う

  • 公開:
  • 更新:
  • カテゴリ: PHP Basics
  • タグ: PHP,7.2,7.1,Basics,MySQLi,DB

MySQLiクラスは、PHPからMySQLデータベースサーバへ接続を行う為のネイティブな拡張モジュールで、PHPに標準で同梱されているMySQLネイティブドライバの1つです。

PHP7でサポートされているMySQL用のAPIとしては他にPDO_MySQLがありますが、mysqliは手続き型のインターフェイスが提供されていたり、mysqlndのノンブロッキングな非同期クエリに対応していたりと、機能も豊富です。

今回はMySQLiクラスを使ってMySQLとの通信を行いデータベース操作を行っていきます。

アジェンダ
  1. 開発環境
  2. mysqli
  3. DB接続
  4. データベース
  5. 基本的な記法によるCRUD
    1. SELECT
    2. INSERT
    3. UPDATE
    4. DELETE
  6. プリペアドステートメント
    1. SELECT
    2. INSERT
    3. UPDATE
    4. DELETE
  7. 動的なプリペアドステートメント

開発環境

今回の開発環境は以下の通りです。

  • Linux CentOS 7
  • Apache 2.4
  • MySQL 5.7
  • PHP 7.2

mysqli

mysqli クラス
http://php.net/manual/ja/class.mysqli.php

  • PHPの5系・7系に標準で同梱されています。
  • この記事を公開した時点でも、開発が行われています。
  • PHPが公式で利用を推奨しています。

つまりは、PHP7で問題なく使える。という事です。

DB接続

データベースへの接続は、mysqliクラスをインスタンス化し、引数にDB情報を渡す事でコンストラクタで行われます。

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

引数の部分はそれぞれ置き換えてください。

この時、接続に失敗した場合はRuntimeExceptionが投げられますが、以下のようにして接続確認を行う事も出来ます。

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($mysqli->connect_error) {
throw new RuntimeException("Connect failed: %s\n", $mysqli->connect_error);
}

データベース

DB接続が行えたところで、今回のデモで使うデータベースを以下に記します。

  • データベース名:sample
  • テーブル名:frameworks

カラム定義

+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| type | tinyint(1) | NO | MUL | 0 | |
+-------+--------------+------+-----+---------+----------------+
  • id 主キー
  • name フレームワーク名
  • type 言語タイプ

PHPやJavaScript、それにPythonのフレームワーク名を収録したテーブルで操作を行います。

mysql> SELECT * FROM `frameworks`;
+----+----------------+------+
| id | name | type |
+----+----------------+------+
| 1 | Laravel | 1 |
| 2 | CakePHP | 1 |
| 3 | Symfony | 1 |
| 4 | Silex | 1 |
| 5 | Zend Framework | 1 |
| 6 | CodeIgniter | 1 |
| 7 | FuelPHP | 1 |
| 8 | Slim | 1 |
| 9 | Yii | 1 |
| 10 | Flight | 1 |
| 11 | Ethna | 1 |
| 12 | BEAR.Sunday | 1 |
| 13 | Kohana | 1 |
| 14 | Ice Framework | 1 |
| 15 | AngularJS | 2 |
| 16 | Backbone.js | 2 |
| 17 | Ember.js | 2 |
| 18 | Vue.js | 2 |
| 19 | Knockout.js | 2 |
| 20 | React | 2 |
| 21 | Riot.js | 2 |
| 22 | Aurelia.js | 2 |
| 23 | Bottle | 3 |
| 24 | Django | 3 |
| 25 | Flask | 3 |
| 26 | Pyramid | 3 |
| 27 | Plone | 3 |
| 28 | Tornado | 3 |
| 29 | CherryPY | 3 |
| 30 | pandas | 3 |
+----+----------------+------+

必要なら、以下のSQL文を流せば作成できます。

# frameworksテーブル作成
CREATE TABLE `frameworks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`type` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `type_index` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;


# データ挿入
INSERT INTO `frameworks`
VALUES
(1,'Laravel',1),(2,'CakePHP',1),(3,'Symfony',1),(4,'Silex',1),
(5,'Zend Framework',1),(6,'CodeIgniter',1),(7,'FuelPHP',1),
(8,'Slim',1),(9,'Yii',1),(10,'Flight',1),(11,'Ethna',1),
(12,'BEAR.Sunday',1),(13,'Kohana',1),(14,'Ice Framework',1),
(15,'AngularJS',2),(16,'Backbone.js',2),(17,'Ember.js',2),
(18,'Vue.js',2),(19,'Knockout.js',2),(20,'React',2),
(21,'Riot.js',2),(22,'Aurelia.js',2),(23,'Bottle',3),
(24,'Django',3),(25,'Flask',3),(26,'Pyramid',3),(27,'Plone',3),
(28,'Tornado',3),(29,'CherryPY',3),(30,'pandas',3);

基本的な記法によるCRUD

まずは最も基本的な記法でCRUD(Create/Read/Update/Delete)を行います。

SELECT

SELECT文でデータを取得します。

// SQL文の作成
$sql = "SELECT * FROM `frameworks` WHERE id < 5";

// クエリ実行
$result = $mysqli->query($sql);

// 結果の出力
while ($row = $result->fetch_assoc()) {
print_r($row);
}

// 結果セット開放
$result->free();

// 接続クローズ
$mysqli->close();

SQL文を作成し、クエリを実行し結果を格納、そしてそれを出力しています。ネイティブなモジュールなので後始末もしっかり行います。結果セットを開放し、接続をクローズします。

結果は以下になります。

Array
(
[id] => 1
[name] => Laravel
[type] => 1
)
Array
(
[id] => 2
[name] => CakePHP
[type] => 1
)
Array
(
[id] => 3
[name] => Symfony
[type] => 1
)
Array
(
[id] => 4
[name] => Silex
[type] => 1
)

INSERT

INSERT文でデータを挿入します。

// SQL文の作成
$sql = "INSERT INTO `frameworks` (name, type) VALUES ('new1', 1)";

// クエリ実行
$mysqli->query($sql);

組み立てたSQL文をquery()メソッドで実行します。これでインサートが実行されます。

UPDATE

UPDATE文でデータを更新します。

// SQL文の作成
$sql = "UPDATE `frameworks` SET `name`='new_2', `type`=8 WHERE `id` = 32";

// クエリ実行
$mysqli->query($sql);

こちらもSQL文を組み立て、それを実行しています。基本的な流れはINSERT文と同じです。

DELETE

DELETE文でデータを削除します。

// SQL文の作成
$sql = "DELETE FROM `frameworks` WHERE `id` = 32";

// クエリ実行
$mysqli->query($sql);

こちらも、基本的な流れは同じです。

プリペアドステートメント

上記で記したような、SQL文をベタ書きでクエリを実行する機会はなかなかないと思います。内部で決まった値があるのであれば良いですが、外部からパラメータが渡ってくる場合、特にユーザの入力を受け付けたりする場合には最新の注意を払わなければがっつりセキュリティホールの出来上がりです。そこで、プリペアドステートメントを用いて、パラメータをバインドします。

SELECT

SELECT文でデータを取得します。

// SQL文の組み立て
$sql = "SELECT * FROM `frameworks` WHERE `id` < ?";

// パラメータ
$id = 5;

// 実行するための SQLステートメント準備
$stmt = $mysqli->prepare($sql);

// マーカにパラメータをバインド
// i: Integer / s:String / d:Double / b:Blob
$stmt->bind_param('i', $id);

// クエリ実行
$stmt->execute();

// 結果変数をバインド
$stmt->bind_result($row['id'], $row['name'], $row['type']);

// 結果の出力
while ($stmt->fetch()) {
echo'<pre>'; print_r($row); echo'</pre>';
}

// ステートメントクローズ
$stmt->close();

一点解説すると、bind_param()メソッドの第一引数に渡している文字列は、パラメータの型になります。例では、数値を渡しているので「i」を渡しています。

各型の値は、それぞれの頭文字になっています。

  • i: Integer/数値
  • s: String/文字列
  • d: Double/浮動小数点
  • b: Blob/バイナリ

上記4つのみです、日時は文字列として渡します。

ちなみに、渡すパラメータの数だけここに型を列挙していく必要があります。なので例えば、「数値」「文字列」「文字列」「数値」を渡す場合は「issi」となります。

INSERT

INSERT文でデータを挿入します。

// インサートする値
$name = 'new_2';
$type = 2;

// SQL文の組み立て
$sql = "INSERT INTO `frameworks` (name, type) VALUES (?, ?)";

// 実行するための SQLステートメント準備
$stmt = $mysqli->prepare($sql);

// マーカにパラメータをバインド
$stmt->bind_param( 'si', $name, $type);

// クエリ実行
$stmt->execute();

// ステートメントクローズ
$stmt->close();
  1. SQL文の組み立ての際は、パラメータ部分を「?」にします。
  2. prepare()メソッドにSQL文を渡して、クエリを実行するためのSQLステートメントを準備します。
  3. bind_param()メソッドでマーカにパラメータをバインドします。
  4. execute()メソッドでクエリを実行します。
  5. 処理が完了したら、close()メソッドでステートメントをクローズします。

UPDATE

UPDATE文でデータを更新します。

// 更新するパラメータ
$name = 'new_22';
$type = 2;

// 更新対象のID
$id = 32;

// SQL文の組み立て
$sql = "UPDATE `frameworks` SET `name`=?, `type`=? WHERE `id` = ?";

// 実行するための SQLステートメント準備
$stmt = $mysqli->prepare($sql);

// マーカにパラメータをバインド
$stmt->bind_param('sii', $name, $type, $id);

// クエリ実行
$stmt->execute();

// ステートメントクローズ
$stmt->close();

基本的な流れは同じですが、更新する値も、更新対象を指定する値も、バインドする時は一緒に渡します。

DELETE

DELETE文でデータを削除します。

// 削除対象のID
$id = 32;

// SQL文の組み立て
$sql = "DELETE FROM `frameworks` WHERE id = ?";

// 実行するための SQLステートメント準備
$stmt = $mysqli->prepare($sql);

// マーカにパラメータをバインド
$stmt->bind_param('i', $id);

// クエリ実行
$stmt->execute();

// ステートメントクローズ
$stmt->close();

ここまでくるとお気づきの通り、データベースの中身が変更される操作に関しては、SQL文やパラメータが違うだけで、プリペアドステートメント自体の操作は同じです。

動的なプリペアドステートメント

ここからは応用になります。プリペアドステートメントでの一連の実装を見てきて、気になる点があります。それは、パラメータや結果のバインドを行う部分です。必要なパラメータ分、結果として取り出す値の分の定義を行わなければならず、かなり手間です。今回はここを動的に行えるようにします。

まずは、マーカーにパラメータをバインドを行うbind_param部分です。

/**
* 型記号を作成する
* @param $params
* @return string
*/
function getBindTypes($params)
{
$types = '';
foreach ($params as $param) {
$types .= substr(gettype($param), 0, 1);
}
return $types;
}
/**
* バイインド用の配列を作成する
* @param $params
* @return array
*/
function arrayToPassByReference(&$params)
{
$new_params = [];
foreach ($params as $key => $value) {
$new_params[$key] = $params[$key];
}
return $new_params;
}
/**
* 動的にマーカーへパラメータをバインドする
* @param $stmt object mysqli_stmt object
* @param $params array パラメータ
* @return object
*/
function bindParams($stmt, $params)
{
$params = arrayToPassByReference($params);
array_unshift($params, getBindTypes($params));
call_user_func_array([$stmt, 'bind_param'], $params);
return $stmt;
}

bindParams()メソッドを利用する形で、他の2つの関数がそこへぶら下がっている形になります。

getBindTypes()メソッドでは、mysqli_stmt::bind_paramメソッドの第一引数に渡す型記号の集合を作成します。

arrayToPassByReference()メソッドでは、mysqli_stmt::bind_paramメソッドの第二引数以降に渡すパラメータの配列を作成します。既に配列で渡っていますが、リファレンス渡しにする必要がある為、ここで処理を行います。

そして、メインのbindParams()メソッドでは、2つのメソッドで作成したものをマージし、call_user_func_array() メソッドでmysqli_stmtオブジェクトへ渡しbind_paramを実行しています。

次に、結果変数をバインドするbind_result部分です。

/**
* 動的に結果変数をバインドする
* @param $stmt object mysqli_stmt object
* @return object
*/
function bindResult($stmt)
{
global $row;

$fields = [];
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$fields[$field->name] = &$row[$field->name];
}
call_user_func_array([$stmt, 'bind_result'], $fields);
return $stmt;
}

結果変数をバインドする為に、結果データからカラム名を抽出し登録用の配列をリファレンス渡しで作成します。その際に、予め作成しておいた変数$rowをグローバル宣言しておきます。

これらを用いてデータ取得を行うと以下のようになります。

// 取得対象パラメータ
$params = [1,2,3,4,5];

// 型のバリデーション
$validate = in_array(true, array_map('is_int', $params));

if ($validate) {
// SQL文の組み立て
$sql = "SELECT * FROM `frameworks` WHERE `id` IN(?,?,?,?,?)";

// 実行するための SQLステートメント準備
$stmt = $mysqli->prepare($sql);

// マーカにパラメータをバインド
$stmt = bindParams($stmt, $params);

// クエリ実行
$stmt->execute();

$row = [];

// 結果変数をバインド
$stmt = bindResult($stmt);

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

// ステートメントクローズ
$stmt->close();
}

パラメータと結果変数のバインド時に、引数をつらつらと並べなくても良くなりました。ポイントとしては、動的に値を渡すので型指定も動的になります。なので外から値が来る場合はバリデーションを一枚挟むようにします。

実行結果は以下になります。

Array
(
[id] => 1
[name] => Laravel
[type] => 1
)
Array
(
[id] => 2
[name] => CakePHP
[type] => 1
)
Array
(
[id] => 3
[name] => Symfony
[type] => 1
)
Array
(
[id] => 4
[name] => Silex
[type] => 1
)
Array
(
[id] => 5
[name] => Zend Framework
[type] => 1
)

ちなみにこれらは、SELECTだけでなくINSERT/UPDATE/DELETEにも使えます。

まとめ

昨今ではLaravelやCakePHPなどのフレームワークの発展もあり、なかなかネイティブなPHPコードでDB操作を行う機会も減ってきていると思いますが、ネイティブで実装しなければいけない、もしくはそうした方が良い案件もある場合に使えるので是非試してみてください。