PHP7とMySQLi拡張モジュールでデータベース操作を行う
- 公開日
- 更新日
- カテゴリ：Basics
- タグ：PHP,Basics,MySQLi,DB
MySQLi クラスは、PHP から MySQL データベースサーバへ接続を行う為のネイティブな拡張モジュールで、PHP に標準で同梱されている MySQL ネイティブドライバの１つです。
PHP7 でサポートされている MySQL 用の API としては他に PDO_MySQL がありますが、mysqli は手続き型のインターフェイスが提供されていたり、mysqlnd のノンブロッキングな非同期クエリに対応していたりと、機能も豊富です。
今回は MySQLi クラスを使って MySQL との通信を行いデータベース操作を行っていきます。
Contents
開発環境
今回の開発環境は以下の通りです。
- MySQL 5.7
- PHP 7.2
mysqli
mysqli クラス
http://php.net/manual/ja/class.mysqli.php
PHP の ver 5 以降に標準で同梱されています。
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/バイナリ
上記４つのみです、日時は文字列として渡します。
ちなみに、渡すパラメータの数だけここに型を列挙していく必要があります。なので例えば、「数値」「文字列」「文字列」「数値」を渡す場合は「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();
- SQL 文の組み立ての際は、パラメータ部分を「?」にします。
- prepare() メソッドに SQL 文を渡して、クエリを実行するための SQL ステートメントを準備します。
- bind_param() メソッドでマーカにパラメータをバインドします。
- execute() メソッドでクエリを実行します。
- 処理が完了したら、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() メソッドを利用する形で、他の２つの関数がそこへぶら下がっている形になります。
getBindTypes() メソッドでは、mysqli_stmt::bind_param メソッドの第一引数に渡す型記号の集合を作成します。
arrayToPassByReference() メソッドでは、mysqli_stmt::bind_param メソッドの第二引数以降に渡すパラメータの配列を作成します。既に配列で渡っていますが、リファレンス渡しにする必要がある為、ここで処理を行います。
そして、メインの bindParams() メソッドでは、２つのメソッドで作成したものをマージし、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 操作を行う機会も減ってきていると思いますが、ネイティブで実装しなければいけない、もしくはそうした方が良い案件もある場合に使えるので是非試してみてください。
