【PHP】PODでプリペアドステートメントなSQLを動的に作成していたときのtips
はじめに
まず、こちらを見ていただきたい。
<?php // クエリ生成 $sql = "SELECT * FROM goods WHERE del_flg = 0 "; if (isset($_POST['name']) && $_POST['name'] != '') { $sql .= "AND name LIKE ? "; } if (isset($_POST['type']) && $_POST['type'] != '') { $sql .= "AND type = ? "; } if (isset($_POST['price']) && $_POST['price'] != '') { $sql .= "AND price >= ? AND price < ? "; } // PDO処理 $stmt = $pod->prepare($sql); $count = 1; if (isset($_POST['name']) && $_POST['name'] != '') { $stmt->bindValue($count++, '%'.$_POST['name'].'%'); } if (isset($_POST['type']) && $_POST['type'] != '') { $stmt->bindValue($count++, $_POST['type']); } if (isset($_POST['price']) && $_POST['price'] != '') { $stmt->bindValue($count++, $_POST['price']); $stmt->bindValue($count++, $_POST['price'] + PRICE_RANGE); } $result = $stmt->execute();
これは商品(goods)を商品名(name)や種類(type)や価格(price)で検索する処理です。
画面としてはだいたいこんな感じです。
要するに「部分一致検索」「完全一致検索」「範囲検索」のサンプルです。
- 商品名(name)はフリーテキスト検索の部分一致検索。
- 種類(type)はラジオボタンやプルダウンで選択されたの値の完全一致検索。
- 価格(price)はfromとtoを指定する範囲検索。
($_POST['price']にはfromの値が入っており、toはfromにPRICE_RANGEを加算して求めている)
ポイント
- 検索条件を追加するかの判定(if文)が、文字結合の箇所と
bindValue
の2箇所で行われている。(しかも全く同じ判定式) - 検索の種類に応じて、bindする値の数や扱いが異なる。(完全一致検索はそのままbind。部分一致検索は値に'%'を結合してからbind。範囲検索は一つのクエリ結合に対して複数の値をbind。)
本題
関数作った。
<?php /** * @param PDOStatement Object * @param array of `array('value'=>$v[,'type'=>$t][,'name'=>$n])`. * @return result */ function pdo_stmt_bind_values($stmt, $stmt_params) { foreach ($stmt_params as $index => $param) { if (is_array($param)) { // 配列であれば、値とdata_typeがセットされていたとして扱う if (!array_key_exists('value', $param)) { // valueが設定されていなければ処理中断 return false; } if (array_key_exists('name', $param)) { // nameが指定されていれば「:name」形式 if (is_null($param['value'])) { // 値がNULLなら、data_typeに明示的にNULLを指定 $stmt->bindValue($param['name'], $param['value'], PDO::PARAM_NULL); } elseif (array_key_exists('type', $param)) { // data_typeが指定されていたなら指定 $stmt->bindValue($param['name'], $param['value'], $param['type']); } else { // シンプルに値をバインド $stmt->bindValue($param['name'], $param['value']); } } else { // nameが指定されていなれば1からのindex形式 if (is_null($param['value'])) { // 値がNULLなら、data_typeに明示的にNULLを指定 $stmt->bindValue($index+1, $param['value'], PDO::PARAM_NULL); } elseif (array_key_exists('type', $param)) { // data_typeが指定されていたなら指定 $stmt->bindValue($index+1, $param['value'], $param['type']); } else { // シンプルに値をバインド $stmt->bindValue($index+1, $param['value']); } } } else { // 配列でなければ、値がセットされていたとして扱う if (is_null($param)) { // 値がNULLなら、data_typeに明示的にNULLを指定 $stmt->bindValue($index+1, $param, PDO::PARAM_NULL); } else { // シンプルに値をバインド $stmt->bindValue($index+1, $param); } } } return true; }
こうやって使う。
<?php // クエリ生成 $stmt_params = array(); $sql = "SELECT * FROM goods WHERE del_flg = 0 "; if (isset($_POST['name']) && $_POST['name'] != '') { $sql .= "AND name LIKE ? "; array_push($stmt_params, '%'.$_POST['name'].'%'); } if (isset($_POST['type']) && $_POST['type'] != '') { $sql .= "AND type = ? "; array_push($stmt_params, $_POST['type']); } if (isset($_POST['price']) && $_POST['price'] != '') { $sql .= "AND price >= ? AND price < ? "; array_push($stmt_params, $_POST['price']); array_push($stmt_params, $_POST['price'] + PRICE_RANGE); } // PDO処理 $stmt = $pod->prepare($sql); pdo_stmt_bind_values($stmt, $stmt_params); $result = $stmt->execute();
SQL文の生成とbind値の設定が1箇所にまとまってスッキリ。
まとめ
ORM(ORマッパー)導入しよ?(笑)
冗談はさておき、古いプロジェクトでは往々にして文字結合でSQL文を作っていることがあります。
そういったプロジェクトを改修する場合に、既存のSQL文の生成部分を全てORM記述に置き換えるというわけにはいきません。
なので、この方法が役立つ場面があるんじゃないかと思い、Tipsとして残しておきます。
作成したbind用の関数は、名前付きプリペアードステートメントにも対応しているので、必要に応じて改造してもいいかもしれません。