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

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

プリペアドステートメントはSQLのテンプレートのようなもので、複雑なSQLをシンプルに記述するための仕組みです。

この形式で書くとコードがスッキリし、ミスも未然に防ぎやすくなります。
また、時間が経ってからコードを見直しても理解しやすくなるため、メンテナンス性においても非常に有用です。

まずは、実際に普通にSQLを書いた場合とどう違うのかをみていきましょう。
通常のmysqliでINSERT文を書くと次のようなコードになります。

プリペアドステートメントを使わないコード例

// SQL文の作成
$sql = "INSERT INTO gc_granola (
	name, price, better_before, modify_datetime, create_datetime
) VALUES (
	'$name',
	$price,
	'$better_before',
	'$date',
	'$date'
)";

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

データは変数を使って動的に登録できるようにすることはよくあると思います。
すると、SQL文を作成するときに「‘(シングルクォーテーション)」と「“(ダブルクォーテーション)」を文法として正しい形で書かなければならず、ちょっと紛らわしい。
そして書いた時点では理解していても、後で見直すとちょっと厄介だったりします。

次にプリペアドステートメントで書いたコードを見てきます。

プリペアドステートメントを使ったコード例

// SQL文の作成
$stmt = $mysqli->prepare("INSERT INTO gc_granola (
	name, price, better_before, modify_datetime, create_datetime
) VALUES (
	?, ?, ?, ?, ?
)");

// パラメータを設定
$stmt->bind_param( 'sisss', $name, $price, $better_before, $date, $date);

// 実行
$res = $stmt->execute();
$stmt->close();

今回はSQL文と、DBヘ登録するデータを設定する部分が別れています。
コードの行数自体は増えていますが、「SQL文」と「データの設定」を分けて書くことでスッキリしました。

prepareメソッドではSQL文のみを作成し、返り値としてstmtクラスのインスタンスを返します。

SQL文中の「?(半角ハテナ)」の箇所には本来データが入りますが、「?」を置くことでbind_paramメソッドからデータを登録できるようになります。

その次のbind_paramメソッドでは、1つ目のパラメータで登録するデータの型を指定します。
型は大きく4タイプあり、整数型(Integer)の「i」、浮動小数点型(Double)の「d」、文字列(String)の「s」、バイナリデータ(Blob)の「b」のいずれかで指定します。日付や時間などは文字列の「s」でOKです。

今回の場合では、先頭から順に商品名$nameの「s」、価格$priceは「i」、賞味期限の日付が入る$better_beforeは「s」、更新日と登録日が入る最後の二つも「s」になり、その結果「sisss」が1つ目のパラメータになります。

2つ目のパラメータからは、実際に登録したいデータを順に渡していきます。

bind_paramメソッドはあくまで値の設定のみを行い、その次のexecuteメソッドでSQLを実行します。
そして、最後にcloseメソッドでstmtクラスを閉じて終了です。

それでは、実際に「オブジェクト型」と「手続き型」でそれぞれコードを書いていきましょう。

オブジェクト型の書き方

まずはオブジェクト型のmysqliを使ったコードの書き方からみていきましょう。

コード例

$mysqli = new mysqli( 'host_name', 'user_name', 'password', 'database_name');

if( $mysqli->connect_errno ) {
	echo $mysqli->connect_errno . ' : ' . $mysqli->connect_error;
}	

$mysqli->set_charset('utf8');

date_default_timezone_set('Asia/Tokyo');
$date = date('Y-m-d H:i:s');

// 登録するデータ
$name = 'テストグラノーラ';
$price = 300;
$better_before = '2017-01-10 00:00:00';

$stmt = $mysqli->prepare("INSERT INTO gc_granola (
	name, price, better_before, modify_datetime, create_datetime
) VALUES (
	?, ?, ?, ?, ?
)");

$stmt->bind_param( 'sisss', $name, $price, $better_before, $date, $date);
$stmt->execute();

$stmt->close();
$mysqli->close();

解説

少し長いコードですが、全体の流れは次のようになります。

1. 1. データベースへ接続(1行〜５行)
2. 2. 文字コード & タイムゾーンの設定(6行〜8行)
3. 3. 現在の日時を取得(9行)
4. 4. 登録するデータを宣言(11行〜14行)
5. 5. prepareメソッドでSQL文を作成(16行〜20行)
6. 6. bind_paramメソッドでパラメータをセット(22行)
7. 7. SQLの実行(23行)
8. 8. stmtクラスを閉じる(25行)
9. 9. データベースとの接続を閉じる(26行)

データベースの接続までの手順について詳しくは「mysqliを使ってデータベースへ接続」を、INSERT文の実行について詳しくは「mysqliでデータを新規登録（INSERT）」を参照してください。

手続き型の書き方

続いて、「手続き型」のコードを書いていきます。

コード

$db_link = mysqli_connect( 'host_name', 'user_name', 'password', 'database_name');

if( mysqli_connect_errno($db_link) ) {
	echo mysqli_connect_errno($db_link) . ' : ' . mysqli_connect_error($db_link);
}

mysqli_set_charset( $db_link, 'utf8');

date_default_timezone_set('Asia/Tokyo');
$date = date('Y-m-d H:i:s');

// 登録するデータ
$name = 'テストグラノーラ';
$price = 300;
$better_before = '2017-01-10 00:00:00';

$stmt = mysqli_prepare( $db_link, "INSERT INTO gc_granola (
name, price, better_before, modify_datetime, create_datetime
) VALUES (
?, ?, ?, ?, ?
)");

mysqli_stmt_bind_param( $stmt, 'sisss', $name, $price, $better_before, $date, $date);
mysqli_stmt_execute($stmt);

mysqli_stmt_close($stmt);
mysqli_close($db_link);

解説

mysqli_stmt_bind_param関数やmysqli_stmt_close関数で、1つ目のパラメータにstmtクラスを渡す必要があります。
それ以外のSQL文の書き方、パラメータの指定方法などは「オブジェクト型」と同一です。