MENU
  1. ホーム
  2. PHP
  3. SQLの部分一致でバインド変数使いたい

SQLの部分一致でバインド変数使いたい

PHP

PHPの課題制作の中で、部分一致検索（ワードでの絞り込み機能）を作ったのですが、バインド変数を使って作るにあたって、少し難航したのでまとめておきたいと思います。

目次

やりたいこと

機能要件

今回やりたいことは
契約しているサービス一覧を表示する画面で、サービス名と色（を登録時に選ばせている）で絞り込みを行う
という内容です。詳細の要件は以下の通り。

・何もしていなければ全件表示される
・検索ワードを入れたら、そのワードがサービス名と部分一致するものが表示される
・色を選択したら、その色（を登録時に選択した）のサービスが表示される
・ワードを入力し、かつ、色を選択していたら、両方の条件が合致したものが表示される

ちなみに色については本来は完全一致で良いのですが、何も選ばれていない場合は全件表示する、が実現できなくなるので、色についてもコード記述上は、部分一致としています。

非機能要件

SQLの実行にあたっては、セキュリティ対策（SQLインジェクション等）のため、バインド変数を使うようにしたいということがありました。

結論

ポイント

バインド変数を使ってSQLのLIKEを使う場合は以下の点がポイントでした。

  • SQL文の方には「％」は書かない
  • バインド変数に入れる、元の変数の方で、‘%’.値.’%’; という書き方で、%を入れておく（つまり、%と変数に入れる値を文字列結合する）

コード例

上記を踏まえて、以下のように記述をすると、前述の要件通りに表示されるようになりました。

//POST情報
$searchname = '%'.$_POST["searchname"].'%';
$searchcolor = '%'.$_POST["searchcolor"].'%';

//SQLでデータ取得
$stmt = $pdo->prepare("SELECT * FROM gs_kadai_table 
                       WHERE sname LIKE :searchname AND color LIKE :searchcolor");
$stmt->bindValue(':searchname', $searchname, PDO::PARAM_STR);
$stmt->bindValue(':searchcolor', $searchcolor, PDO::PARAM_STR);
$status = $stmt->execute();

経緯

まずはphpMyAdmin上でSQL文を確認

割といつもやるのですが、一旦phpMyAdmin上でSQL文を書いてみて表示されるかを確認しました。

その結果、MySQLでは以下のSQL文で、機能要件通りに絞り込みの表示が出来ました。
（本来は変数となる場所のサービス名をABCサービス、色を黄色で仮置き）

SELECT * FROM `gs_kadai_table` WHERE sname LIKE '%ABCサービス%' AND color LIKE '%yellow%';

それをPHP上に置き換えてみる

まずはバインド変数を使わない形で記述する

まずはバインド変数を使わない形で、先ほどのSQL文に変数名を置き換えて書いてみると、こちらはうまく表示されました！

//POST情報
$searchname = $_POST["searchname"];
$searchcolor = $_POST["searchcolor"];

//SQLでデータ取得
$stmt = $pdo->prepare("SELECT * FROM gs_kadai_table 
                       WHERE sname LIKE '%$searchname%' AND color LIKE '%$searchcolor%'");
$status = $stmt->execute();

次にバインド変数を使って記述してみる

次に、バインド変数を使う記述に変更するため、先ほどのSQL文の「$」を「:」に変えて、bindValueを追加してみたのですが、エラーになってしまいました。

//POST情報
$searchname = $_POST["searchname"];
$searchcolor = $_POST["searchcolor"];

//SQLでデータ取得
$stmt = $pdo->prepare("SELECT * FROM gs_kadai_table 
                       WHERE sname LIKE '%:searchname%' AND color LIKE '%:searchcolor%'");
$stmt->bindValue(':searchname', $searchname, PDO::PARAM_STR);
$stmt->bindValue(':searchcolor', $searchcolor, PDO::PARAM_STR);
$status = $stmt->execute();

SQL文を下記のように変えてみたら、エラーは出なくなったけど、データが1件も表示されず。。。

$stmt = $pdo->prepare("SELECT * FROM gs_kadai_php2 
                       WHERE sname LIKE '%':searchname'%' AND color LIKE '%':searchcolor'%'");

途方に暮れていたところ以下の記事の中に正解を発見

Qiita
LIKEをbindしようとしたら二度怒られた。 - Qiita LIKE検索をPHPからしたい。bindなんたら～でLIKEを書いたら怒られた。…

これを踏まえて、%は変数の方に文字列結合で格納し、その値をbindValueに入れて、パラメータの値（:で始まる値）をSQLに入れる、という形で無事希望要件通りの動作をしてくれるようになりました。

//POST情報
$searchname = '%'.$_POST["searchname"].'%';
$searchcolor = '%'.$_POST["searchcolor"].'%';

//SQLでデータ取得
$stmt = $pdo->prepare("SELECT * FROM gs_kadai_table 
                       WHERE sname LIKE :searchname AND color LIKE :searchcolor");
$stmt->bindValue(':searchname', $searchname, PDO::PARAM_STR);
$stmt->bindValue(':searchcolor', $searchcolor, PDO::PARAM_STR);
$status = $stmt->execute();
変数を下記のように記述するのがポイント

$searchname = ‘%’.$_POST[“searchname”].’%’;
$searchcolor = ‘%’.$_POST[“searchcolor”].’%’;

PHP

関連記事

目次