MYSQLで複数のパターンのquery-selectを準備する場合

Question

入力フォームから複数の情報が選択され、その選択肢に応じてmysqlから情報を取得する一般的な状況です。

例えば以下のような選択肢がある場合、
q1はチェック入力必須として、q2、q3が任意とします。
とすると以下のように3パターンの条件でデータを抽出することが考えられます。
1.q1
2.q1 + q2
2.q1 + q3

<div id="q1">q1</div>
  <input type="checkbox" name="a[]" value="1">1
  <input type="checkbox" name="a[]" value="2" >2

<div id="q2">q2</div>
  <input type="checkbox" name="b[]" value="1">1
  <input type="checkbox" name="b[]" value="2" >2

<div id="q3">q3</div>
  <input type="checkbox" name="c[]" value="1">1
  <input type="checkbox" name="c[]" value="2" >2

実際には、入力項目が多いため、もっと多くのパターンがありえるのですが、
実務的にこのような場合、php & MYSQL (PDO)上
switchのような形で分岐させてそれぞれのswitch内の処理でwhereの条件を変えていくべきなのでしょうか？
未だ不慣れな私にはこのような方法しか思い浮かびません。。（下記の例をご覧ください）
そうなるとかなりのパターンがあるので、適切ではないかとも考えています。
このような処理で実務的、一般的なものがあればアドバイスを頂ければ幸いです。

以下は上記のswitchイメージになります。

try {
switch (XXX){
case 1:
  $stmt=$pdo->prepare("SELECT XXX FROM XXX
    where ABC = :ABC");
    $stmt->BindValue(':ABC',$a,PDO::PARAM_STR);
  break;
case 2://q1 & q2
  $stmt=$pdo->prepare("SELECT XXX FROM XXX
    where ABC = :ABC  and  DEF =:DEF");
    $stmt->BindValue(':ABC',$a,PDO::PARAM_STR);
    $stmt->BindValue(':DEF',$b,PDO::PARAM_STR);
以下省略
・
・
・
default:
・・
}

$stmt->execute();
    $result2=$stmt->fetchall(PDO::FETCH_NUM);
} catch (Exception $e) {
  prep($e->getMessage());
}

Answer 1

「ABC」とか「DEF」のフィールド名がどこから来ているのか不明なため、$_POST['a'] = ABC、$_POST['b'] = DEFと仮定します。

$sql = "SELECT * FROM XXX WHERE 1=1 ";
if(isset($_POST['a'])){
    $data_a = $_POST['a'];
    $sql.= " AND ( ";
    foreach($data_a as $key => $val){
       $sql.= " `ABC` = :ABC".$key." OR ";
    }
    $sql = rtrim($sql, ' OR ');
    $sql.= " ) ";
}
if(isset($_POST['b'])){
    $data_b = $_POST['b'];
    $sql.= " AND ( ";
    foreach($data_b as $key => $val){
        $sql.= " `DEF` = :DEF".$key." OR ";
    }
    $sql = rtrim($sql, ' OR ');
    $sql.= " ) ";
}
$stmt=$pdo->prepare($sql);
if(isset($_POST['a'])){
    foreach($data_a as $key => $val){
        $stmt->bindValue(':ABC'.$key, $val, PDO::PARAM_STR);
    }
}
if(isset($_POST['b'])){
    foreach($data_b as $key => $val){
        $stmt->bindValue(':DEF'.$key, $val, PDO::PARAM_STR);
    }
}
$stmt->execute();

もしa=1、b=1,2がチェックされていれば

SELECT XXX FROM XXX WHERE 1=1 AND (`ABC` = 1) AND (`DEF` = 1 OR `DEF` = 2)

というSQL文ができあがると思います。

無駄でややこしいことをしていますが、こんな感じの処理方法もありますよという例です。
速度とかエラー処理とか考えずに書いていますので、実際に利用する際はそのあたりを考えて実装してください。

Comments

[pegy]

コメント誠にありがとうございます。
ifでネスト化して分岐する例をご教示頂いたものと存じます。このように分岐処理するのがメリットがあり一般的、実務で管理がしやすいという事でしょうか？
様々な方法で処理できるとは存じますが、もしベストまたは望ましい方法であれば、そのポイントを重ねてで恐縮ですが、ご教示願いたく存じます

[rjunakc]

あくまでも一例です。
実際のフォーム内容や選択肢により最適解は変わる可能性があります。
上記のコードの場合、チェックボックスの選択肢が運用後増えても、SQLコードは変更する必要がないという意味ではメリットがあります。
ただし、ifやforeachを多用しているため、コード的に美しくはないですし、速度的にもメリットがあるとは思えません。（数百万レコードくらいないと速度的なデメリットは感じにくいでしょうが）

[SugiuraY]

承知を致しました、コメント頂き誠にありがとうございます。

Answer 2

こんな感じでしょうかね

<form method="get">
<div id="q1">q1</div>
<input type="checkbox" name="a[]" value="1">1
<input type="checkbox" name="a[]" value="2" >2
<div id="q2">q2</div>
<input type="checkbox" name="b[]" value="1">1
<input type="checkbox" name="b[]" value="2" >2
<div id="q3">q3</div>
<input type="checkbox" name="c[]" value="1">1
<input type="checkbox" name="c[]" value="2" >2
<input type="submit" value="go">
</form>
<?PHP

$a=filter_input(INPUT_GET,'a',FILTER_VALIDATE_INT,["flags"=>FILTER_REQUIRE_ARRAY]);
$b=filter_input(INPUT_GET,'b',FILTER_VALIDATE_INT,["flags"=>FILTER_REQUIRE_ARRAY]);
$c=filter_input(INPUT_GET,'c',FILTER_VALIDATE_INT,["flags"=>FILTER_REQUIRE_ARRAY]);

$sql = 'SELECT XXX FROM XXX WHERE 1 ';
$data=[];

if(count($a)>0){
$data=array_merge($data,$a);
$sql.="AND ABC IN(".implode(",",array_fill(0,count($a),"?")).") ";
}
if(count($b)>0){
$data=array_merge($data,$b);
$sql.="AND DEF IN(".implode(",",array_fill(0,count($b),"?")).") ";
}
if(count($c)>0){
$data=array_merge($data,$c);
$sql.="AND GHI IN(".implode(",",array_fill(0,count($c),"?")).") ";
}
print $sql;
/* 事前に$pdoは定義しておく*/
$stmt = $pdo->prepare( $sql);
$stmt->execute($data);

?>

Comments

[SugiuraY]

なるほど、条件を設定して満たせばwhere句に条件を結合していくのですね。
目からうろこでした。大変勉強になります。
是非採用させて頂きます。宜しくお願いいたします。

Answer 3

任意のところは入力があったら文字連結させていくのはどうでしょうか？

サンプルはname="a[]"としてるので、実際には、$_POST['a'][0]に入りますが、
ややこしいので$_POST['a']にしておきます。

//POSTで検索条件を受け取る
$a = $_POST['a'];
$b = $_POST['b'];

$sql = 'SELECT XXX FROM XXX WHERE';
//$aは必須入力だからifいれない
 $sql .= ' abc = :abc';
//$bは任意入力
if(!empty($b)) {
$sql .= ' AND def LIKE :def';
}
$stmt = $pdo -> prepare($sql);
//プリペアドステートメントを実行する(インジェクション対策）
$stmt -> bindValue(':abc', $a, PDO::PARAM_STR);
if(!empty($b)) {
$stmt -> bindValue(':def', '%'.$b.'%', PDO::PARAM_STR);
}
$stmt -> execute($sql);

Comments

[KiyoshiMotoki]

$_POST['b'] はチェックボックスの値、すなわち配列なので、

　$stmt -> bindValue(':def', '%'.$b.'%', PDO::PARAM_STR);

部分は "%Array%" という文字列をバインドしていることになりますよ。

http://php.net/manual/ja/language.types.string.php#language.types.string.casting
> 配列は常に "Array" という文字列に変換されるので、（以下略）

$_POST['a'] も、同様ですね。

[SugiuraY]

コメント有難うございます、
上記の通り文字連結の発想がなかったため、大変勉強になりました。
早速取り組んでみたいと思います。

Answer 4

もしくは、$a, $bのデフォルト値を決めて1個のクエリにしてしまうか。

$sql = 'SELECT XXX FROM XXX (''=:ABC OR ABC=:ABC) and (''=:DEF OR DEF=:DEF)';
$stmt = $pdo -> prepare($sql);
$stmt->BindValue(':ABC',$a,PDO::PARAM_STR);
$stmt->BindValue(':DEF',$b,PDO::PARAM_STR);

Comments

[SugiuraY]

コメント有難うございます。
正直に申しあげて未だ不慣れで分からない部分があったのですが、全パターンをandでつないでいくということでしょうか？またデフォルト値を決めるというのは具体的にどのようなことでしょうか？
度々恐れ入り増すが宜しくお願い申し上げます。

[haru666]

例えば、整数のマスタ値なんかだと分かりやすいですが、マスタ値が1から始まるなら0を未設定としてクエリを構築します。そうすると、パラメータが0の場合はtrue、それ以外の場合、カラムが一致しているか判定、というコードを組むことができます。
(0=:Param OR Param=:Param)

これを数珠繋ぎにすることで複数条件のON/OFFを１クエリ上で実現できます。
（クエリオプティマイザでよくよく検証する必要はありますけどね！）

文字列検索の場合でも、文句が設定されていればフィルタ、空なら無視、という条件を、１クエリ上で実現できます。空とnullの違いには気を付ける必要があると思いますが、こういう書き方をするとPHP側のコードはかなりシンプルになります。