PHP Logo PHP + MySQL

更新日:2022年03月11日

作成日:2017年02月03日

PHPから MySQL に接続

MySQLでは全ての権限を持つ管理者ユーザー(root)ではなく、権限を制限した一般ユーザーを作成し、そのIDとパスワードで接続する方法が一般的です。

ユーザーの追加

ユーザーを追加するには、以下のような方法があります。

  • CREATE USER でパスワードを設定して新規ユーザーを作成し、次に GRANT で権限や特性を設定。(以下で説明)
  • GRANT で権限や特性、パスワードを設定して新規ユーザーを作成。
    ※但し、NO_AUTO_CREATE_USER SQL モードが指定されている場合は、GRANT ステートメントで新規ユーザーを自動的に作成しません。

通常、データベース管理者は最初に CREATE USER を使用してアカウントを作成し、次に GRANT を使用してその権限や特性を定義します。

以下は、CREATE USER の書式です。

CREATE USER 'ユーザー名'@'localhost' IDENTIFIED BY 'パスワード';

以下は foo というユーザー名、password というパスワードのアカウントを作成する例です。(実際にはパスワードは複雑にする必要があります)

mysql> CREATE USER 'foo'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)

CREATE USER 構文

ユーザを表示

ユーザを表示するには mysql データベースの user テーブルを参照します。

mysql> SELECT host, user, password FROM mysql.user;
+-----------+------+-----------------+
| host      | user | password        |
+-----------+------+-----------------+
| localhost | root | **************  |
| 127.0.0.1 | root | **************  |
| ::1       | root | **************  |
| localhost |      | **************  |
| localhost | pma  | **************  |
| localhost | foo  | **************  |
+-----------+------+-----------------+
6 rows in set (0.00 sec)

権限付与

続いて GRANT 構文を使って権限を付与します。以下が書式です。

GRANT 権限 ON レベル TO ユーザー名@ホスト名;

GRANT 構文

各レベルに応じて権限を設定するには以下のようにします。

/*グローバルレベルで権限を付与*/
GRANT 権限 ON *.* TO ユーザー名@ホスト名;

/*データベースレベルで権限を付与*/
GRANT 権限 ON データベース名.* TO ユーザー名@ホスト名;

/*テーブルレベルで権限を付与*/
GRANT 権限 ON データベース名.テーブル名 TO ユーザー名@ホスト名;

グローバル権限は管理権限です。つまり、特定のサーバー上のすべてのデータベースに適用されます。グローバル権限を割り当てるには、ON *.* 構文を使用します。

主な権限の種類
権限 意味と付与可能なレベル
ALL [PRIVILEGES] GRANT OPTION を除き、指定されたアクセスレベルにあるすべての権限を付与します
ALTER ALTER TABLE の使用を有効にします。
CREATE データベースおよびテーブルの作成を有効にします。
DELETE DELETE の使用を有効にします。
DROP データベース、テーブル、およびビューの削除を有効にします。
FILE ユーザーがサーバーにファイルを読み取らせたり書き込ませたりできるようにします。
INSERT INSERT の使用を有効にします。
SELECT SELECT の使用を有効にします。
TRIGGER トリガー操作を有効にします。
UPDATE UPDATE の使用を有効にします。

/*foo にすべてのデータベース(*.*)の全権限(ALL)を付与*/
GRANT ALL ON *.* TO 'foo'@'localhost';

/*fooに特定のテーブル(データベースdb2 のテーブルtb1)のデータ操作権限を付与*/
GRANT SELECT,INSERT,UPDATE,DELETE ON db2.tb1 TO 'foo'@'localhost';

以下は、ユーザー foo にデータベース db1 の全権限を付与する例です。

mysql> GRANT ALL ON db1.* TO 'foo'@'localhost';
Query OK, 0 rows affected (0.00 sec)

権限の確認

権限を確認するには SHOW GRANTS 文を使います。

ユーザー foo の権限を確認する例です。

mysql> SHOW GRANTS FOR 'foo'@'localhost'\G
*************************** 1. row ***************************
Grants for foo@localhost: GRANT USAGE ON *.* TO 'foo'@'localhost' IDENTIFIED BY
PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
*************************** 2. row ***************************
Grants for foo@localhost: GRANT ALL PRIVILEGES ON `db1`.* TO 'foo'@'localhost'
2 rows in set (0.00 sec)

権限の削除

特定の権限を削除する場合は以下の構文を使用します。

REVOKE 権限[,権限]... ON データベース名.テーブル名 FROM ユーザー名@ホスト名;

与えられている全ての権限を削除するには、以下の構文を使用します。

REVOKE ALL PRIVILEGES ON データベース名.テーブル名 FROM ユーザー名@ホスト名;

この REVOKE 構文を使用するには、mysql データベースに対するグローバルな CREATE USER 権限または UPDATE 権限が必要です。

以下は、ユーザー foo のデータベースdb1の全権限を削除する例です。

REVOKE ALL PRIVILEGES ON db1.* FROM 'foo'@'localhost';

ユーザーの削除

ユーザーを削除するには、DROP USER 構文を使用します。

mysql> DROP USER 'foo'@'localhost';

パスワードの変更

パスワードを変更するには SET PASSWORD を使います。以下が書式です。

/*現在接続しているユーザーのパスワードを変更*/
SET PASSWORD = PASSWORD('xxxxxx');

/*指定したユーザーのパスワードを変更*/
SET PASSWORD FOR ユーザー = PASSWORD('xxxxxx');

以下はユーザー foo のパスワードを XXXX に変更する例です。

mysql> SET PASSWORD FOR 'foo'@'localhost' = PASSWORD('XXXX');
Query OK, 0 rows affected (0.00 sec)

文字エンコードの指定とセキュリティ

データベースを使う場合、文字エンコードの設定が必要になります。PHP マニュアルの「文字セットと文字のエスケープ」には以下のような記述があります。

文字セットはきちんと理解して設定しておかないといけません。 すべての操作に影響が及ぶし、セキュリティの問題を引き起こす可能性があるからです。 たとえば、文字列のエスケープ (mysqli なら mysqli_real_escape_string()、 mysql なら mysql_real_escape_string()、 そして PDO_MySQL なら PDO::quote()) は文字セットの設定に従った動きをします。 これらの関数は、クエリで設定した文字セットは使わないことを知っておくことが大切です。

※文字エンコードを適切に指定しないとセキュリティの問題を引き起こすので注意が必要です。

MySQL への接続 / mysqli

PHP には、MySQL への接続用の API は以下の三種類がありますが、かつては良く使われていた「Mysql 関数」は現在非推奨になっていて、PHP7で削除されています。

  • Mysqli クラス(または Mysqli 関数):オブジェクト指向型(クラス)と手続き型(関数)
  • PDO クラス:オブジェクト指向型のみ
  • Mysql 関数:PHP5.5 からは非推奨となっているので使用しません。(PHP7で削除)

どの API を使うか

以下はデータベースを操作する基本的な処理の流れです。

  1. データベースに接続する
  2. SQL 文を発行する(SELECT, INSERT, UPDATE, DELETE 等)
  3. 結果を受け取る
  4. データベースを切断する

MySQL との接続

mysqli ではオブジェクト指向型と手続き型の書き方がありますが、この例ではオブジェクト指向型を使用します。

MySQL サーバーへ接続するには、mysqli::__construct メソッドを使用してインスタンスを生成します。コンストラクタなので、実際にはnew 演算子を用いて生成を行います。以下が書式です。

$mysqli = new mysqli('host', 'username', 'passwd', 'dbname');
host
ホスト名または IP アドレスです。この引数に NULL または "localhost" を渡すと ローカルホストとみなされます。
username
MySQL のユーザー名。
passwd
パスワード。省略したり NULL を渡したりした場合、MySQL サーバーはパスワードを持たないユーザーレコードについてのみ認証を試みます。
dbname
データベース名。省略して後から指定可能。指定した場合は、クエリが行われるデフォルトのデータベースとなります。
返り値
MySQL サーバーへの接続を表すオブジェクトを返します。

以下は mysqli::__construct() の使用例です。

<?php
$mysqli = new mysqli('localhost', 'foo', 'password', 'db1');

/* エラーがあれば、それを表示して終了します。 */
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

/* 接続に成功すればメッセージを表示 */
echo 'Success... ' . $mysqli->host_info . "<br>";

$mysqli->close();  /* 接続を閉じます */
?>
  • $mysqli->connect_error:エラーの内容を表す文字列を返します。エラーが発生しなかった場合は NULL を返します。
  • $mysqli->connect_errno: mysqli_connect() コールが失敗した場合、 エラーコードを返します。ゼロは、何もエラーが発生しなかったことを示します。
  • $mysqli->host_info:サーバーのホスト名と接続の型を文字列で返します。

接続に成功すれば「Success... localhost via TCP/IP」のようなメッセージが表示されます。

また、$connect_error は PHP 5.3.0 より前のバージョンでは動作しないので、PHP 5.3.0 より前のバージョンとの互換性を保ちたい場合は mysqli_connect_error() を使って以下のようにします。

<?php
$mysqli = new mysqli('localhost', 'foo', 'password', 'db1');

/*PHP 5.3.0 より前のバージョンとの互換性を保ちたい場合は $connect_error のかわりにこのようにします*/
if (mysqli_connect_error()) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

echo 'Success... ' . $mysqli->host_info . "<br>";

$mysqli->close();
?>

パスワード等の情報は外部ファイルにする

パスワード等のユーザー情報は、ブラウザからアクセスできない場所に、外部ファイルとして置き、「require_once」などで読み込むようにします。

この例では、以下のような「db_info.php」というファイルを作成し、そこにホスト名、ユーザー名、パスワード、データベース名などを定義しておきます。(この例では、それぞれの値を定数として定義しています。)

<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'foo');
define('DB_PASSWORD', 'password');
define('DB_NAME', 'db1');
?>

以下は、ユーザー情報を定義したファイル「db_info.php」を読み込んで、MySQL サーバーへ接続する例です。

<?php
require_once('/home/xxxx/etc/test/var/db_info.php'); 
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
?>

データベースの選択・変更

mysqli::select_db を使ってデータベースを選択・変更することができます。

以下は接続時にデータベースを指定せずに、接続後データベースを選択する例です。

<?php
require_once('/home/xxxx/etc/test/var/db_info.php'); 
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD);

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

$mysqli -> select_db('db1');  /* データベースを選択 */
・・・
?>

文字コードの指定

日本語を使用する場合は、文字化けしないように文字コードを指定する必要があります。文字コードの指定は Mysqli::set_charset メソッドを利用します。

以下は、クライアントのデフォルト文字セットを UTF-8 に設定する例です。

$mysqli->set_charset("utf-8");

SQL の実行

以下のようなテーブル tb1 を使って SQL 文(クエリ)を実行してみます。

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

3 rows in set (0.04 sec)    
mysql> select * from tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 小川 | 1920-03-22 |
| C006 | 鴻池 | 1950-03-23 |
| C007 | 中谷 | 1943-11-15 |
| C008 | 松田 | 1965-07-09 |
+------+------+------------+
8 rows in set (0.01 sec)

データベースに接続後、文字コードを指定し、クエリを変数に代入し、mysqli::query を使ってクエリを実行します。

<?php
function h($str) {  //エスケープ処理用関数
    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}
require_once('/home/xxxx/etc/test/var/db_info.php');
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD);
 
if ($mysqli->connect_error) {
    die('接続エラー (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
// データベースの選択
$mysqli -> select_db('db1'); 
// 文字コードの指定
$mysqli->set_charset("utf-8");  
// クエリを変数に代入
$query = "SELECT * FROM tb1";   
// クエリを実行 
$result = $mysqli -> query($query);   
// クエリの結果を確認
if (!$result) {  
  print('クエリーが失敗しました。' . $mysqli -> error);
  $mysqli -> close();
  exit();
}
//レコードの取得
while ($row = $result -> fetch_assoc()) {    
  //エスケープ処理して出力
  echo "id: ".h($row["id"])." (".h($row["name"]).") ".h($row["birth"]) ."<br>";
}

$result->free();  // 結果セットを閉じます
$mysqli->close();  // 接続を閉じます
?>

mysqli::query のパラメータには、クエリ文字列を指定します。クエリ内のデータは適切にエスケープするか、プリペアドステートメントを使う必要があります。

mysqli::query は、失敗した場合に FALSE を返します。結果を判定して失敗した場合は、エラーを表示し終了します。

SELECT, SHOW, DESCRIBE あるいは EXPLAIN が成功した場合は、mysqli::query は mysqli_result オブジェクト(クエリにより得られた結果セット)を返します。

それ以外のクエリが成功した場合は、TRUE を返します。

この例ではレコードの取得は、mysqli::query の戻り値 mysqli_result オブジェクトの fetch_assoc メソッドを使って1レコードずつ取り出します。 (色々な方法で取得することができます)

fetch_assoc メソッドは、取得した行に対応する連想配列を返します。もしもう行がない場合には NULL を返します。また、連想配列の各キーが、結果セットのカラムを表します。

クエリの結果をエスケープ処理して出力しています。

そして mysqli_result::free を使って、結果に関連付けられたメモリを開放します。結果オブジェクトが必要なくなった場合は、常に mysqli_result::free でメモリを開放します。

最後にデータベースへの接続を閉じます。問題がなければ、この例の場合以下のように表示されます。

id: C002 (鈴木) 1959-12-25
id: C003 (田中) 1978-08-15
id: C004 (青山) 1967-09-17
id: C005 (小川) 1920-03-22
id: C006 (鴻池) 1950-03-23
id: C007 (中谷) 1943-11-15
id: C008 (松田) 1965-07-09

プリペアドステートメント(mysqli)

プリペアドステートメントを使うと、先に SQL 文を準備(prepare)しておき、SQL 文の実行時にパラメータのみを引き渡してデータベース操作を行うことができます。

プリペアドステートメントを使うと以下のようなメリットがあります。

  • SQL インジェクションを防ぐように自動的にエスケープする
  • 同じ SQL 文を繰り返し実行する場合、高速に動作する

SQL 文の中に、文字データとしての「'」や「\」などの特殊文字を入れたい場合は、それらの文字をエスケープする必要がありますが、プリペアドステートメントの仕組みを使うと、バインドする側(データベースサーバー)がそのエスケープ処理を行ってくれます。

つまり、プリペアドステートメントを利用することにより、利用者はSQL文の要素に対するエスケープ処理を別途行う必要がなくなります。

以下はプリペアドステートメントを使う処理の流れです。

  1. SQL 文を準備するようにデータベースに要求する
  2. プレースホルダに値をバインドする(割り当てる)
  3. SQL 文を実行する
  4. 結果を取得するための変数をバインドする(割り当てる)
  5. 結果を取得する
  6. ステートメントを閉じる

以下は、MySQLi のプリペアドステートメントの基本的な使い方です。

mysqli::prepare は、プリペアドステートメントを作成するメソッドで、SQL 文を準備するようにデータベースに要求します。

mysqli_stmt mysqli::prepare ( string $query )

//例
$stmt = $mysqli->prepare("INSERT INTO tb1 VALUES (?, ?, ?, ?)");

SQL クエリを準備し、後でそのステートメントを操作するために使用するステートメントハンドル(ステートメントオブジェクト)を返します。 クエリは、単一の SQL 文である必要があります。

パラメータ

query:クエリを表す文字列。(ステートメントの最後にセミコロンや \g を付けません。)

返り値

ステートメントオブジェクトを返します。 エラー時には FALSE を返します。

以下は、プリペアドステートメントの使用例です。

前述と同じテーブルを使用します。

mysql> select * from tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 小川 | 1920-03-22 |
| C006 | 鴻池 | 1950-03-23 |
| C007 | 中谷 | 1943-11-15 |
| C008 | 松田 | 1965-07-09 |
+------+------+------------+
8 rows in set (0.01 sec)

SELECT 文の WHERE で id を指定して、そのレコードを表示する例です。

<?php
function h($str) {  //エスケープ処理用関数
    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}
$mysqli = new mysqli('localhost', 'foo', 'password'); 
if ($mysqli->connect_error) {
    die('接続エラー (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
$mysqli -> select_db('db1');  // データベースの選択 
$mysqli->set_charset("utf-8");  // 文字コードの指定 

$id_value = "C005";  //ユーザーからPOSTされた値と仮定  

//プリペアドステートメントを作成します
if ($stmt = $mysqli->prepare("SELECT name, birth FROM tb1 WHERE id = ?")) {  
    // プレースホルダ(?)にパラメータをバインドします 
    $stmt->bind_param("s", $id_value);   
    // クエリを実行します
    $stmt->execute();     
    //結果保持用の変数(結果変数)の初期化(省略可能)
    $name = $birth = NULL;    
    // 結果変数をバインドします 
    $stmt->bind_result($name, $birth);    
    // 値を取得します 
    $stmt->fetch();    
    // 値をエスケープ処理して出力します
    echo "id: ".h($id_value)."名前: ".h($name)."生年月日: ".h($birth);
    // ステートメントを閉じます
    $stmt->close();
}
$mysqli->close();  // 接続を閉じます
?>    

SQL 文を指定して prepare メソッドを呼び出すと、SQL 文を準備するようにデータベースに要求します。

「?」はプレースホルダと呼ばれる、実際の値がまだ割り当てられていない要素です。

$stmt = $mysqli->prepare("SELECT name FROM tb1 WHERE id = ?")

値を置き換えたい部分にプレースホルダを記述しておくと、後からその部分のみを指定した値で置き換えることができます。置き換え用の値は、bind_param メソッドでデータ型を指定する必要があります。

プレースホルダは、それが SQL 文の適切な位置にある場合のみ有効です。 例えば INSERT 文の VALUES() リストの中 (行に登録するカラム値を指定する) や WHERE 句で列のデータと比較する値などが 適切な位置の例です。

しかし、識別子 (テーブルやカラムの名前) や SELECT 文で選択する項目の名前に指定したり、 (等号 = のような) 二項演算子の両側にパラメータを指定したりすることはできません。

$stmt にはステートメントオブジェクトが入ります。エラー時には FALSE が入ります。

mysqli_stmt::bind_param

bind_param メソッドは、プリペアドステートメントのプレースホルダに変数をバインド(割り当て)します。以下が書式です。

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed amp;$... ] )

第1引数の $types は、割り当てる値のデータ型を「型指定文字」で指定します。

以下のように複数のプレースホルダがある場合は、それぞれの型を順番に記述します。また、プレースホルダの数と型の文字数、変数の数は一致している必要があります。

第2引数以降には実際に割り当てる値の変数を指定します。

$stmt = $mysqli->prepare("INSERT INTO tableX VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $value1, $value2, $value3, $value4);
型指定文字
型指定文字 意味
i 整数(integer)
d 不動小数点数(double)
s 文字列(string)
b バイナリなどのデータ(blob)

プレースホルダに当てはめる値を指定したら、excute メソッドで SQL 文を実行します。

ステートメントが UPDATE、DELETE あるいは INSERT であった場合、 変更された行の総数は $affected_rows メソッドで取得可能です。同様に、クエリが結果セットを返す場合は fetch メソッドを使用できます。

結果を取得する変数の割り当て(バインド)

SQL の SELECT 文を実行した場合、検索結果(結果セット)がデータベースから返ります。bind_result メソッドを使うと、結果を受け取るための変数を割り当てることができます。

SELECT 文で指定したカラムに対応する変数を bind_result メソッドに指定します。

結果の取得

データを取得するために fetch メソッドが呼び出されると、プリペアドステートメントから結果を読み込み、カラムのデータを bind_result メソッドでバインドした変数に格納します。

上述のサンプルでは、取得される値が1レコードなので、変数をエスケープ処理して単に出力しています。

複数のレコードが取得される可能性がある場合は、while ($stmt->fetch()) のようにして1レコードずつ取得することができます。以下は fetch メソッドの戻り値です。

説明
TRUE 成功。データが取得されました。
FALSE エラーが発生しました。
NULL 行/データがもうありません。

複数のレコードが取得される可能性がある場合の例

$id_value = "C%";  //ユーザーからPOSTされた値と仮定  
//プリペアドステートメントを作成します
if($stmt=$mysqli->prepare("SELECT id,name,birth FROM tb1 WHERE id LIKE ?")){
    // プレースホルダ(?)にパラメータをバインドします 
    $stmt->bind_param("s", $id_value);    
    // クエリを実行します
    $stmt->execute();     
    //結果保持用の変数の初期化
    $id = $name = $birth = NULL;    
    // 結果変数をバインドします 
    $stmt->bind_result($id, $name, $birth);      
    // 値を取得します
    while ($stmt->fetch()) {   
        // 値を出力します
        echo "id: " . h($id). "(". h($name) . ")  " .h($birth) ."<br>";
    }
    // ステートメントを閉じます
    $stmt->close();
}

最後に、close メソッドでステートメントを閉じます。

INSERT 文の例

以下のようなテーブル tb にレコードを追加する例です。

mysql> DESC tb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| sales | int(11)     | YES  |     | NULL    |       |
| month | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

<?php
function h($str) {
  return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}
$mysqli = new mysqli('localhost', 'foo', 'password', 'db1');
if ($mysqli->connect_error) {
  die('接続エラー (' . $mysqli->connect_errno . ') '
          . $mysqli->connect_error);
}
$mysqli->set_charset("utf-8");  // 文字コードの指定   
// insert ステートメントを準備します 
$query ="INSERT INTO tb (id, sales, month) VALUES (?, ?, ?)";
//プリペアドステートメントを作成します
if ($stmt = $mysqli->prepare($query)) {  
  // プレースホルダ(?)にパラメータをバインドします 
  $stmt->bind_param("sii", $id, $sales, $month);   
  $id = "C003";
  $sales = 500;
  $month = 7; 
  // クエリを実行します
  $stmt->execute(); 
  // $stmt->affected_rows は変更された行の数を返します  
  printf("%d Row inserted.<br>", $stmt->affected_rows);
  $id = "C006";
  $sales = 400;
  $month = 7; 
  // クエリを実行します
  $stmt->execute(); 
  // $stmt->affected_rows は変更された行の数を返します  
  printf("%d Row inserted.<br>", $stmt->affected_rows);
  // ステートメントを閉じます
  $stmt->close();
}
// tb からすべての行を取得してテーブルで表示します。
//パラメータ要素が存在しない固定的なSQLの実行なのでプリペアドステートメントを使用していません。
$query = "SELECT * FROM tb";
if ($result = $mysqli->query($query)) {
  echo "<table><tr><th>id</th><th>sales</th><th>month</th></tr>";
  // $result->fetch_row は結果の行を数値添字配列で取得します
  while ($row = $result->fetch_row()) {
    echo "<tr><td>". h($row[0]). "</td><td>". h($row[1])
    . "</td><td>".h($row[2]). "</td></tr>";
  }
  echo "</table>";
  // 結果セットを開放します
  $result->close();
}
$mysqli->close();  // 接続を閉じます
?>

PDO を使った接続

PDO(PHP Data Object)とは、PHP標準(5.1.0以降)のデータベース接続クラス(データベース抽象化のためのクラス)のことです。PDO は、データアクセスの抽象化レイヤを提供してくれるので、使用しているデータベースが何であるかに関わらず、同じ 関数を使用してクエリの発行やデータの取得が行えます。

別の言い方をすると、PDO は異なる種類のデータベースに対して、共通のインターフェースを提供することにより、プログラムではデータベースの種類の違いを意識せずに、コードを書くことができます。

この例では、PDO を使用して MySQL データベースに接続します。XAMPP を使用している場合、MySQL ドライバーはデフォルトで有効になっています。XAMPP を使用していない場合には、php.ini ファイルで extension=php_pdo_mysql.dll の行がコメントアウトされていないことを確認してみてください。

PDO クラスを使って接続

PDO を使ってデータベースに接続するには、PDO クラスを利用します。

MySQL サーバーへ接続するには、PDO::__construct メソッドを使用してインスタンスを生成します。コンストラクタなので、実際には new 演算子を用いて生成を行います。以下が書式です。

$dbh = new PDO($dsn, $username, $password, $options)

new 演算子を使用して PDO クラスのインスタンスを生成して、生成したインスタンスを $dbh という変数に代入しています。変数名は何でもかまいませんが、ここでは良く使われる $dbh(データベースハンドラ)という名前の変数にしています。

パラメータ

dsn (Data Source Name/データソース名)
以下を指定します。
  • データベース名(dbname)
  • データベースのサーバー(ホスト)名(host):ホスト名またはIPアドレスを指定します。
    localhost は自分自身のホスト名, 127.0.0.1 は自分自身のIPアドレス
  • 文字エンコード(charset):UTF の場合、UTF-8 ではなく utf8 であることに注意。MySQL5.5.3 以降の場合は,4バイトの絵文字等も正常に取り扱える utf8mb4 を使用すると良いです。
先頭にデータベースの種類を指定して : で区切り、
各項目は「項目名=値」のように指定して ; で区切ります。

例: mysql:dbname=db1;host=localhost;charset=utf8
username
DSN 文字列のユーザー名。いくつかの PDO ドライバではオプションです。
password
パスワード。DSN 文字列で必要とされる場合に指定。
options
ドライバ固有の接続オプションを指定するキー => 値の配列。

MySQL に接続する場合は、以下のように記述します。

$dbh = new PDO(
      'mysql:dbname=データベース名;host=サーバー名;charset=文字エンコード',
      'ユーザー名',
      'パスワード',
       オプション
    );

返り値

成功時に PDO オブジェクトを返します。

エラー / 例外

PDO::__construct() は、 指定されたデータベースへの接続に失敗した場合、 PDOException を投げます。(例外処理に対応)

オプションの設定

PDO クラスをインスタンス化する際にオプションを設定ることができます。例外処理を行うかなど、どの機能を使うかなどのオプションを設定します。

オプションは連想配列で指定します。array() を使い「変更したい属性」 => 「値」のように指定します。

$dbh = new PDO(
      'mysql:dbname=データベース名;host=サーバー名;charset=文字エンコード',
      'username',
      'password',
       array(
        変更したい属性 => 値,
        変更したい属性 => 値,
       )
    );

array() の代わりに配列の短縮構文(PHP5.4 以降)では [] を使って以下のように記述することもできます。

$dbh = new PDO(
      'mysql:dbname=データベース名;host=サーバー名;charset=文字エンコード',
      'username',
      'password',
       [
        変更したい属性 => 値,
        変更したい属性 => 値,
       ]
    );

上記では PDO クラスをインスタンス化するときにオプションを設定しましたが、インスタンス化した後でオプションを設定することもできます。

インスタンス化した後にオプションを設定するには、setAttribute メソッドを使います。但し、オプションの中には、インスタンス化のときだけしか設定できないものがあるので、それらのオプションはインスタンス化する際に連想配列でオプションを設定する必要があります。

「MySQL 関数 (PDO_MYSQL)」に掲載されている定義済み定数の中のいくつかには「この定数を使うのは、新しいデータベースハンドルを作るときの driver_options 配列内だけであることに注意しましょう。 」という記述がありますが、これらのオプションはインスタンス化する際に連想配列でオプションを設定する必要があります。

$dbh = new PDO(
      'mysql:dbname=データベース名;host=サーバー名;charset=文字エンコード',
      'username',
      'password'
    );
    $dbh->setAttribute(変更したい属性 , 値);
    $dbh->setAttribute(変更したい属性 , 値);

以下は、データベース名「db1」、サーバー名「localhost」、文字エンコード「utf8」、ユーザー名「foo」、パスワード「password」の場合の例です。

$dbh = new PDO(
    'mysql:dbname=db1;host=localhost;charset=utf8',
    'foo',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

以下は、setAttribute を使ってオプションを指定する例です。

$dbh = new PDO(
  'mysql:dbname=db1;host=localhost;charset=utf8',
  'foo',
  'password'
);
$dbh->setAttribute(PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES , false);

エラーモードの設定

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

PDO::ATTR_ERRMODE はエラーレポートの属性(Attribute)で、値は PDO::ERRMODE_EXCEPTION を指定して例外を投げるように設定します。

参考:PDO::setAttribute

静的プレースホルダを使うように設定

PDO::ATTR_EMULATE_PREPARES => false

データベースがプリペアドステートメントに対応していても、データベース操作用ライブラリ(PDOなど)の設定により、クライアントサイドプリペアドステートメント(動的プレースホルダ)として実行されてしまう場合があるので注意が必要です。

PDO のデフォルト設定では、クライアントサイドプリペアドステートメント(動的プレースホルダ)として実行されるようになっています。PDO でサーバーサイドプリペアドステートメント(静的プレースホルダ)を使うには、PDO の設定で、PDO::ATTR_EMULATE_PREPARES 属性を FALSE に設定する必要があります。(静的プレースホルダと動的プレースホルダ)

以下では何らかの理由でデータベースへの接続が失敗した場合、例外(PDOException)を投げるようにして、後続の処理で不用意なエラーが発生するのを防ぐために try~catch ブロックで接続の成否を確認します。(例外処理

/*PDO を使ったデータベースへの接続*/
try {
    $dbh = new PDO(
        'mysql:host=localhost;dbname=db1;charset=utf8',
        'foo',
        'password',
        [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
  } catch (PDOException $e) {
      $error = $e->getMessage();
  }   

データベース接続の属性の取得

PDO::getAttribute を使うと、データベース接続の属性値を取得することができます。

try {
    $dbh = new PDO(
        'mysql:host=localhost;dbname=db1;charset=utf8',
        'foo',
        'password',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );

    //調べたい属性の配列
    $attributes = array(
      "AUTOCOMMIT", "ERRMODE", "CASE", "CLIENT_VERSION", "CONNECTION_STATUS",
      "ORACLE_NULLS", "PERSISTENT", "PREFETCH", "SERVER_INFO",
      "SERVER_VERSION", "TIMEOUT". "EMULATE_PREPARES"
    );
    //それぞれの属性の値を getAttribute で取得して表示
    foreach ($attributes as $val) {
      echo "PDO::ATTR_$val: ";
      echo $dbh->getAttribute(constant("PDO::ATTR_$val")) . "<br>\n";
    }

} catch (PDOException $e) {
    $error = $e->getMessage();
}

<結果の例(MySQL)>
PDO::ATTR_AUTOCOMMIT: 1
PDO::ATTR_ERRMODE: 2
PDO::ATTR_CASE: 0
PDO::ATTR_CLIENT_VERSION: mysqlnd 5.0.11-dev - 20120503 - $Id: bf9ad53b...b8c5c77 $
PDO::ATTR_CONNECTION_STATUS: localhost via TCP/IP
PDO::ATTR_ORACLE_NULLS: 0
PDO::ATTR_PERSISTENT:
PDO::ATTR_PREFETCH:

※MySQL では表示されない属性や値もあるのがわかります。

プリペアドステートメント(PDO)

プリペアドステートメントを使うと、先に SQL 文を準備(prepare)しておき、SQL 文の実行時にパラメータのみを引き渡してデータベース操作を行うことができます。

プリペアドステートメントを使うと以下のようなメリットがあります。ユーザーからの入力値をクエリで使う場合などでは、必ずプリペアドステートメントを使うようにします。

  • SQL インジェクションを防ぐように自動的にエスケープする
  • 同じ SQL 文を繰り返し実行する場合、高速に動作する

以下はプリペアドステートメントを使う処理の流れです。

  1. SQL 文を準備するようにデータベースに要求する
  2. プレースホルダに値をバインドする(割り当てる)
  3. SQL 文(プリペアドステートメント)を実行する
  4. 結果を取得する

以下のようなテーブル tb1 を使って SQL 文(クエリ)を実行してみます。

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> select * from tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 小川 | 1920-03-22 |
| C006 | 鴻池 | 1950-03-23 |
| C007 | 中谷 | 1943-11-15 |
| C008 | 松田 | 1965-07-09 |
+------+------+------------+
8 rows in set (0.01 sec)

変数 $id と $name には、ユーザーからPOSTされた値が入っていると仮定して、プリペアドステートメントを使ってクエリを実行する例です。

ユーザーからの入力を受け取って SQL 文を動的に生成する場合はプリペアドステートメントとプレースホルダを必ず使うようにします。

function h($str) {  //エスケープ処理用関数
    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}

try {
  $dbh = new PDO(
    'mysql:dbname=db1;host=localhost;charset=utf8',
    'foo',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
  );
  
  $id = "C005";  //ユーザーからPOSTされた値と仮定
  $name = "田中";  //ユーザーからPOSTされた値と仮定
  $prepare = $dbh->prepare('SELECT name, birth FROM tb1 WHERE id = ? OR name = ?');
  $prepare->bindValue(1, $id, PDO::PARAM_STR);
  $prepare->bindValue(2, $name, PDO::PARAM_STR);
  $prepare->execute();
  $result = $prepare->fetchAll();
  foreach($result as $row) {
    echo (h($row['name'] .': '.$row['birth'])) . "<br>";
  }
    
} catch (PDOException $e) {

    $error = $e->getMessage();

} 

上記実行結果:
田中: 1978-08-15
小川: 1920-03-22

以下のメソッドを使ってクエリを実行します。

  1. PDO::prepare:SQL ステートメントを準備
  2. PDOStatement::bindValue:値をパラメータにバインド
  3. PDOStatement::execute:プリペアドステートメントを実行

1. PDO::prepare SQL ステートメントを準備

プリペアドステートメントを実行する準備を行い、PDOStatement オブジェクトを返すメソッドです。

public PDOStatement PDO::prepare ($statement [,$driver_options])

パラメータ $statement:
有効な SQL ステートメント(文字列)を指定します。

戻り値
正常にステートメントを準備できた場合、「プリペアドステートメント(準備済みの SQL)」を表す PDOStatement オブジェクトを返します。 もしステートメントを準備できなかった場合は FALSE を返すか PDOException を発行します。 (エラー処理 の方法に依存)

SQL ステートメントは、文が実行されるときに実際の値に置き換えられる 0 個以上の疑問符 (?) もしくは名前 (:name) プレースホルダを含むことができます。

プレースホルダには、疑問符プレースホルダと名前付きプレースホルダの2種類がありますが、これらの2種類のプレースホルダを混在させることはできません。

例では、接続の際に生成した PDO オブジェクト($dbh)の prepare メソッドにアロー演算子 -> を使ってアクセスしています。

WHERE id = ? OR name = ? の「?」がプレースホルダになります。

そして戻り値の PDOStatement オブジェクトを変数($prepare)に代入しています。

$prepare = 
$dbh->prepare('SELECT name, birth FROM tb1 WHERE id = ? OR name = ?');

プレースホルダ

プレースホルダとは、パラメータの置き場所です。prepare メソッドで指定する SQL ステートメントには「?」または「:名前」という形式のプレースホルダを含めることができ、実行時に動的にパラメータを埋め込むことができます。

ユーザーの入力を伴う SQL ステートメントは、エスケープ処理を行う必要がありますが、プレースホルダに埋め込まれる際に、パラメータが自動的にエスケープ処理されるのでエスケープ処理を行う必要はありません。

//疑問符プレースホルダの例
$dbh->prepare('SELECT name,birth FROM tb1 WHERE id = ? OR name = ?');

//名前付きプレースホルダの例
$dbh->prepare('SELECT name,birth FROM tb1 WHERE id = :id OR name = :name');

2. PDOStatement::bindValue 値をパラメータにバインド

値をパラメータにバインドする PDOStatement オブジェクトのメソッドです。プリペアドステートメントで使用する SQL 文の中で、対応するプレースホルダに値をバインドします。

public bool PDOStatement::bindValue ($parameter, $value ,$data_type )

パラメータ

  • $parameter:パラメータ ID。名前つきプレースホルダを使用する プリペアドステートメントの場合は、 :name 形式のパラメータ名となります。 疑問符プレースホルダを使用するプリペアドステートメントの場合は、 1 から始まるパラメータの位置となります。
  • $value:パラメータにバインドする値。(文字列以外のものを扱う場合は明示的にキャストした方が良い場合もあるみたいです。希望通りの型で処理されているかは、MySQL のログを見ればある程度確認することができます。)
  • $data_type:パラメータに対して PDO::PARAM_* 定数を使った明示的なデータ型を指定します。

戻り値

成功した場合に TRUE を、失敗した場合に FALSE を返します。

値をバインドするには、bindValue メソッド以外に、bindParam メソッドがありますが、通常は bindValue メソッドを使えば良いようです。

疑問符プレースホルダ

疑問符プレースホルダを使用する場合、第1パラメータの $parameter:パラメータ ID は、1 から始まるパラメータの位置を指定します。

$prepare = $dbh->prepare('SELECT name, birth FROM tb1 WHERE id = ? OR name = ?');
  $prepare->bindValue(1, $id, PDO::PARAM_STR);
  $prepare->bindValue(2, $name, PDO::PARAM_STR);

名前付きプレースホルダ

名前付きプレースホルダでは、先頭に : を付け、半角英数字とアンダースコアからなる任意の文字で名前を指定します。

$prepare = $dbh->prepare('SELECT name, birth FROM tb1 WHERE id = :id OR name = :name');
$prepare->bindValue(':id', $id, PDO::PARAM_STR);
$prepare->bindValue(':name', $name, PDO::PARAM_STR);

また、バインド時には、第1パラメータの $parameter:パラメータ ID は、先頭の : を省略することができます。

$prepare = $dbh->prepare('SELECT name, birth FROM tb1 WHERE id = :id OR name = :name');
$prepare->bindValue('id', $id, PDO::PARAM_STR);
$prepare->bindValue('name', $name, PDO::PARAM_STR);

3. PDOStatement::execute プリペアドステートメントを実行

プリペアドステートメントを実行するには、execute メソッドを使います。

public bool PDOStatement::execute ([ array $input_parameters ] )

プリペアドステートメントを実行することで、PDOStatement のインスタンスに結果セットが格納されます。後で fetchAll() メソッド等でそれらのデータを取り出します。

パラメータ

このパラメータはオプションです。bindValue メソッドなどを使って値をバインドした場合は指定する必要はありません。

input_parameters:実行される SQL 文の中のバインドパラメータと同数の要素からなる、 値の配列。すべての値は PDO::PARAM_STR(文字列)として扱われるので注意が必要です。

戻り値

成功した場合に TRUE を、失敗した場合に FALSE を返します。

  $prepare = $dbh->prepare('SELECT name, birth FROM tb1 WHERE id = :id OR name = :name');
$prepare->bindValue('id', $id, PDO::PARAM_STR);
$prepare->bindValue('name', $name, PDO::PARAM_STR);
$prepare->execute();

execute メソッドの引数に配列を渡すと,それらを全てバインドしてそのままSQLを実行してくれますが,すべての値は PDO::PARAM_STR(文字列)として扱われるので注意が必要です。

また, 既に bindValue メソッドで値がバインドされている場合でも,それらは全て無視されます。(全てのバインドをこの引数で行う必要があります)

疑問符プレースホルダの例

$id = "C005";  //ユーザーからPOSTされた値と仮定
$name = "田中";  //ユーザーからPOSTされた値と仮定
$prepare = $dbh->prepare('SELECT name, birth FROM tb1 WHERE id = ? OR name = ?');
$prepare->execute([$id,$name ]);

名前付きプレースホルダの例

$id = "C005";  //ユーザーからPOSTされた値と仮定
$name = "田中";  //ユーザーからPOSTされた値と仮定
$prepare = $dbh->prepare('SELECT name, birth FROM tb1 WHERE id = :id OR name = :name');
$prepare->execute([':id' => $id, ':name' => $name]);
//コロンは省略可能なので以下でも同じ
$prepare->execute(['id' => $id, 'name' => $name]);  

結果セットの取得

結果セットとは、SELECT 命令によって取り出されたメモリ上の一時的なテーブル(仮想テーブル)のことで、query メソッドや prepare/excute メソッドを利用することで取得できます。

WHERE 条件句のパラメータのみを変更して繰り返し実行するような場合や、ユーザーからの入力に基づく動的なクエリを発行するような場合は prepare/excute メソッドを利用します。

この例では前述と同じ以下のようなテーブル tb1 を使います。

mysql> desc tb1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> select * from tb1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
| C001 | 山田 | 1980-01-23 |
| C002 | 鈴木 | 1959-12-25 |
| C003 | 田中 | 1978-08-15 |
| C004 | 青山 | 1967-09-17 |
| C005 | 小川 | 1920-03-22 |
| C006 | 鴻池 | 1950-03-23 |
| C007 | 中谷 | 1943-11-15 |
| C008 | 松田 | 1965-07-09 |
+------+------+------------+
8 rows in set (0.01 sec)

取得した結果セットからデータを取り出すことを「フェッチする」と言いますが、データをフェッチする PDOStatement オブジェクトのメソッドには以下のようなものがあります。

PDOStatement::fetch

public mixed PDOStatement::fetch ([int $fetch_style [,int $cursor_orientation [,int $cursor_offset ]]] ) 

fetch メソッドは、デフォルトでは次の行を指定のフェッチモードで取り出します。fetch メソッドは、次の行が存在しない場合に FALSE を返します。

パラメータ

$fetch_style

結果セットから取り出したデータをどのような形式の変数に格納するか(取得形式)を指定するものです。 デフォルトは PDO::ATTR_DEFAULT_FETCH_MODE の値 (PDO::FETCH_BOTH) で、以下のような値を設定することができます。

PDO::ATTR_DEFAULT_FETCH_MODE は PDO クラスをインスタンス化する際のオプションの設定時や、setAttribute メソッドで指定することができます。デフォルトは PDO::FETCH_BOTH です。

$fetch_style(一部抜粋)
定数 意味
PDO::FETCH_NUM 結果セットに返された際の 0 から始まるカラム番号を添字とする配列を返します。 $row[0]
PDO::FETCH_ASSOC 結果セットに 返された際のカラム名で添字を付けた配列を返します。 $row['name']
PDO::FETCH_BOTH (デフォルト)結果セットに返された際のカラム名と 0 で始まるカラム番号で添字を付けた配列を返します。 $row[0]
$row['name']
PDO::FETCH_BOUND TRUE を返し、結果セットのカラムの値を PDOStatement::bindColumn() メソッドでバインドされた変数に代入します。 $name
PDO::FETCH_OBJ 結果セットに返された際のカラム名と同名のプロパティのオブジェクトを返します。 $row->name

$cursor_orientation
省略
$cursor_offset
省略

戻り値

この関数が成功した場合の返り値は、取得形式($fetch_style)によって異なります。 失敗した場合は常に FALSE を返します。

以下は、while 文を使用する例です。

$id = "C005";  //ユーザーからPOSTされた値と仮定
$name = "田中";  //ユーザーからPOSTされた値と仮定
$prepare = $dbh->prepare('SELECT name, birth FROM tb1 WHERE id = :id OR name = :name');
$prepare->bindValue('id', $id, PDO::PARAM_STR);
$prepare->bindValue('name', $name, PDO::PARAM_STR);
$prepare->execute();

while($row = $prepare ->fetch(PDO::FETCH_ASSOC)) {
  echo (h($row['name'] .': '.$row['birth'])) . "<br>";
}

//エスケープ処理用関数
function h($str) {  
  return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}

出力結果
田中: 1978-08-15
小川: 1920-03-22

以下は、while 文の代わりに foreach 文を使用する例です。

foreach ($prepare as $row) {
  echo (h($row['name'] .': '.$row['birth'])) . "<br>";
}

以下は第1パラメータ($fetch_style)に PDO::FETCH_BOUND を指定する例です。

PDO::FETCH_BOUND の場合は、取得したパラメータを PDOStatement::bindColumn() メソッドで個別にバインドする必要があります。また、bindColumn() メソッドは execute() メソッドを実行した後に行います。

$id = "C005";  //ユーザーからPOSTされた値と仮定
$name = "田中";  //ユーザーからPOSTされた値と仮定
$prepare = $dbh->prepare('SELECT * FROM tb1 WHERE id = :id OR name = :name');
$prepare->bindValue('id', $id, PDO::PARAM_STR);
$prepare->bindValue('name', $name, PDO::PARAM_STR);
$prepare->execute();

/* カラム番号(1 から始まる)によってバインドする */
$prepare->bindColumn(1, $id);
$prepare->bindColumn(2, $name);
  
/* カラム名によってバインドする */
$prepare->bindColumn('birth', $birth);

while ($row = $prepare->fetch(PDO::FETCH_BOUND)) {
  echo h($id . ":" . $name . ":" . $birth). "<br>";
}

出力結果
C003: 田中: 1978-08-15
C005: 小川: 1920-03-22

PDOStatement::fetchAll

fetchAll メソッドは全てのデータを配列として返します。

public array PDOStatement::fetchAll ([ $fetch_style [,$fetch_argument [,$ctor_args ]]] )   

fetch メソッドと同じように、fetchAll メソッドにも引数に定数を指定することで、結果セットから取り出したデータをどのような形式の変数に格納するか(取得形式)を指定することができます。

パラメータ

$fetch_style

結果セットから取り出したデータをどのような形式の変数に格納するか(取得形式)を指定するものです。 デフォルトは PDO::ATTR_DEFAULT_FETCH_MODE の値 (PDO::FETCH_BOTH) です。fetch メソッドの定数以外に以下のようなものがあります。

PDO::FETCH_COLUMN:結果セットから単一カラムの全ての値を含む配列を返す場合に指定します。 fetch_argument パラメータにどのカラムを返すかを指定することができます。

$fetch_argument

この引数は、fetch_style の値によって意味が異なります。fetch_style で PDO::FETCH_COLUMN を指定した場合、 0 から始まる番号のカラムを指定します。省略した場合は 0 を指定したとみなされます。

$ctor_args
省略

戻り値

結果セットに残っている全ての行を含む配列を返します。 この配列は、カラム値の配列もしくは各カラム名に対応するプロパティを持つオブジェクトとして各行を表します。 取得結果がゼロ件だった場合は空の配列を返し、 失敗した場合は FALSE を返します。

以下はパラメータを指定しない(デフォルト PDO::FETCH_BOTH)例です。

function h($str) {  //エスケープ処理用関数
    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}

try {
  $dbh = new PDO(
    'mysql:dbname=db1;host=localhost;charset=utf8',
    'foo',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
  ); 
  $id = "C005";  //ユーザーからPOSTされた値と仮定
  $name = "田中";  //ユーザーからPOSTされた値と仮定
  $prepare = $dbh->prepare('SELECT * FROM tb1 WHERE id = :id OR name = :name');
  $prepare->bindValue('id', $id, PDO::PARAM_STR);
  $prepare->bindValue('name', $name, PDO::PARAM_STR);
  $prepare->execute();
  $result = $prepare->fetchAll();  //デフォルト(パラメータ指定なし)
  foreach($result as $row) {
    echo (h($row['id'] .': '.$row['name'] .': '.$row['birth'])) . "<br>";
    //以下でも同じ
    //echo (h($row[0] .': '.$row[1] .': '.$row[2])) . "<br>";
  }
} catch (PDOException $e) {
    $error = $e->getMessage();
}

出力結果
C003: 田中: 1978-08-15
C005: 小川: 1920-03-22

特定のカラムだけを全て取得したい場合,第1パラメータに PDO::FETCH_COLUMN を指定し,第2パラメータにカラムの「番目」を渡します。「番目」は 0 から始まり、省略した場合は 0 を指定したとみなされます。

$id = "C005";  //ユーザーからPOSTされた値と仮定
  $name = "田中";  //ユーザーからPOSTされた値と仮定
  $prepare = $dbh->prepare('SELECT * FROM tb1 WHERE id = :id OR name = :name');
  $prepare->bindValue('id', $id, PDO::PARAM_STR);
  $prepare->bindValue('name', $name, PDO::PARAM_STR);
  $prepare->execute();
  $result = $prepare->fetchAll(PDO::FETCH_COLUMN, 2);
  foreach($result as $row) {
    echo (h($row)) . "<br>";
  }

出力結果
1978-08-15
1920-03-22

PDOStatement::fetchColumn

結果セットから特定の1カラムのみを取得します。

public mixed PDOStatement::fetchColumn ([ column_number = 0 ] )

結果セットの次行から単一カラムを返し、行がもうない場合には FALSE を返します。

パラメータ

$column_number

先頭から数えてそのカラムが何番目にあるかを指定します。「番目」は 0 から始まり、省略した場合は 0 を指定したとみなされます。

戻り値

結果セットの次行から単一カラムを返します。

注意:
PDOStatement::fetchColumn() は、boolean のカラムを取得するときに使っていけません。 boolean 値としての FALSE と、もう取得する行がない場合の FALSE を区別できないからです。代わりに PDOStatement::fetch() を使います。

※ PDOStatement::fetchColumn() を使用してデータを処理する場合、同一行から他のカラムを返す方法はありません。

以下のようなテーブル tb を使用します。

mysql> DESC tb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(10) | YES  |     | NULL    |       |
| sales | int(11)     | YES  |     | NULL    |       |
| month | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> SELECT * FROM tb;
+------+-------+-------+
| id   | sales | month |
+------+-------+-------+
| C001 |   100 |     4 |
| C003 |    77 |     5 |
| C007 |   312 |     4 |
| C002 |   238 |     4 |
| C004 |    98 |     5 |
| C006 |   156 |     4 |
| C001 |   210 |     5 |
| C007 |   180 |     5 |
| C004 |   222 |     6 |
| C003 |   198 |     6 |
+------+-------+-------+
10 rows in set (0.00 sec)

以下は、fetchColumn() メソッドの使用例です。ユーザー入力した月($month)に該当する Sales(2つ目のカラム。0から始まるので1番目のカラム)の値を表示しています。

function h($str) {  //エスケープ処理用関数
    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}

try {
  $dbh = new PDO(
    'mysql:dbname=db1;host=localhost;charset=utf8',
    'foo',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
  );  
  $month = 5;  //ユーザーからPOSTされた値と仮定
  $prepare = $dbh->prepare('SELECT * FROM tb WHERE month = ?');
  $prepare->bindValue(1, $month, PDO::PARAM_INT);
  $prepare->execute();
  
  while ($result = $prepare->fetchColumn(1)) {
    echo h("sales: " . $result). "<br>";
  }  
} catch (PDOException $e) {
  $error = $e->getMessage();
}

出力結果
sales: 77
sales: 98
sales: 210
sales: 180

以下は同じテーブルで、ユーザーが入力した月のセールスの平均を表示する例です。平均(AVG)は最初のカラムになるので0番目になります。(0の場合、省略可能)

$month = 5;  //ユーザーからPOSTされた値と仮定
$prepare = $dbh->prepare('SELECT AVG(sales) FROM tb WHERE month = ?');
$prepare->bindValue(1, $month, PDO::PARAM_INT);
$prepare->execute();

while ($result = $prepare->fetchColumn()) {
  echo h($month. "月のセールス平均: " . $result);
}

出力結果
5月のセールス平均: 141.2500

これまではプリペアド・ステートメントを使用する方法を見てきしましたが、信頼できないデータ (ユーザーからの入力など) を使用していない場合には、PDO::queryメソッドを使って、クエリーを直接実行するという方法を選択することができます。 ※但し、プリペアドステートメントを使わないので、自分でエスケープ処理をする必要があります

データベース接続を閉じる

データベースへの接続に成功すると、PDO クラスのインスタンスがスクリプトに返されます。この PDO オブジェクトが存在する間、接続がアクティブであり続けます。 接続を閉じるには、データベースに接続している PDO オブジェクトを破棄します。それには、オブジェクトを保持している変数に対して NULL を代入します。

明示的にこれを行わなかった場合は、スクリプトの終了時に自動的に接続が閉じられます。そのため、通常は接続を閉じる処理は不要です。

$dbh = null;
$prepare = null;

PHP マニュアル:接続、および接続の管理

参考:【PHP超入門】クラス~例外処理~PDOの基礎

参考:PHPでデータベースに接続するときのまとめ

参考:PDOでの数値列の扱いにはワナがいっぱい(2)

MySQL のログの取得

データベースを使ったプログラムをデバッグするのに、データベースのクエリログを参照すると役に立ちます。

ログをオンにするには、root でログインして、SET GLOBAL general_log = 'ON' コマンドを実行します。これにより MySQL サーバーの再起動なしに、クエリログがファイルに保存されるようになります。

ログの状態(オン・オフ)とファイルの場所は、SHOW VARIABLES LIKE "general_log%" で確認できます。

ログをオフにするには、SET GLOBAL general_log = 'OFF' コマンドを実行します。

C:\Users\User>mysql -u root -p
Enter password:

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE "general_log%";
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | ON                                  |
| general_log_file | C:\xampp\mysql\data\PC-name.log     |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)

mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE "general_log%";
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| general_log      | OFF                                 |
| general_log_file | C:\xampp\mysql\data\PC-name.log     |
+------------------+-------------------------------------+
2 rows in set (0.00 sec)

ログファイルはテキストファイルですが、改行は UNIX フォーマット(LF)になっています。Windows のメモ帳では、改行されないので Notepad ++ などのエディタで開くと改行も表示されます。

クエリログを確認すれば、プリペアドステートメントで「静的プレースホルダ」と「動的プレースホルダ」のどちらが使われているのかも確認することができます。

ログを確認して、Prepare の後に Execute を実行していれば、「静的プレースホルダ」になります。「動的プレースホルダ」の場合は、Queryだけ実行されています。

84 Connect	foo@localhost on 
84 Init DB	db1
84 Prepare	SELECT id, name, birth FROM tb1 WHERE id LIKE ?
84 Execute	SELECT id, name, birth FROM tb1 WHERE id LIKE 'C%'
84 Close stmt	
84 Quit	

静的プレースホルダと動的プレースホルダ

以下は、「安全なSQLの呼び出し方(独立行政法人情報処理推進機構)」からの抜粋です。

プレースホルダによる組み立ては、バインドをいつ行うのかによって以下の2種類に分けることができます。

  • 静的プレースホルダ(安全)
  • 動的プレースホルダ

静的プレースホルダはプレースホルダのままのSQL文をデータベースエンジン側にあらかじめ送信して、実行前に、SQL 文の構文解析などの準備をしておく方式です。SQL 実行の段階で、実際のパラメータの値をデータベースエンジン側に送信し、データベースエンジン側がバインド処理します。

静的プレースホルダでは、SQL 文の構文がバインド前に確定することから、プレースホルダに渡す文字列はクォートして記述する必要がありません。そのため、シングルクォートのエスケープ処理も必要ありません。また、数値リテラルもそのまま適切にバインドされます。

このことから、セキュリティの観点で、静的プレースホルダは最も安全です。静的プレースホルダでは、SQLを準備する段階でSQL文の構文が確定し、後からSQL構文が変化することがないため、パラメータの値がリテラルの外にはみ出す現象が起きません。その結果として、SQL インジェクションの脆弱性が生じません。

動的プレースホルダは準備された文(Prepared Statement)とは異なり、プレースホルダを利用するものの、パラメータのバインド処理をデータベースエンジン側で行うのではなく、アプリケーション側のライブラリ内で実行する方式です。

動的プレースホルダは静的プレースホルダとは異なり、バインド処理を実現するライブラリによっては、SQL構文を変化させるようなSQLインジェクションを許してしまう、脆弱な実装のものが存在する可能性を否定できません。

(備考)静的プレースホルダは「サーバーサイドプリペアドステートメント」、動的プレースホルダは「クライアントサイドプリペアドステートメント」と呼ばれることもあります。