MySQL+PHPでのwhereのon/offについて

HTML+MySQL+PHPの初心者です．

HTML上でのラジオボタンを使い，名簿から特定の条件にヒットする人を検索するものとします．
設計として，例えば

「男」「女」のラジオボタンが指定された場合その性別を，
どちらも選択されていない場合，性別を問わない

検索をしたいものとします．

<?php
$gender = $_POST["gender"];
if(isset($_POST["gender"]))
    $sql = "SELECT * FROM members WHERE gender=$gender";
else
    $sql = "SELECT * FROM members";
?>

この場合，例えば上記のようなSELECT文が考えられると思いますが，
その他の検索オプションが増えたときはSELECT文が煩雑なif文だらけになるため，
より良いアプローチがあるはずだと思います...

上手いやり方をご教授お願いします．

また，教科書には小さなクエリの書き方しか載っていないのですが
巨大なクエリを美しく書くための方針，知っておくべき用語も合わせて教えて頂けると幸いです．

行動規範の内容に同意します

回答5件

ベストアンサー

implode や empty など、PHP の関数をうまく利用すれば（多少は）簡潔に書けます。

それとは別に、質問欄に提示されているコードには問題があります。
POST パラメータから受け取った値をプリペアードステートメントを介さず SQL文に組み込んでいるため、SQLインジェクションの脆弱性があるからです。
http://nippondanji.blogspot.jp/2010/01/sql.html

SQL文内にパラメータを指定する場合は、必ずプリペアードステートメントを使用するようにしましょう。
http://php.net/manual/ja/pdo.prepared-statements.php

例えば、私なら以下のように書きます。

php
1// $_POST['gender'] = '1';
2// $_POST['name'] = 'John Price';
3// $_POST['prefectures'] = array(2, 5, 11);
4
5$sql = 'SELECT * FROM members';
6
7$conditions = array();
8$parameters = array();
9
10if(isset($_POST['gender'])) {
11    $conditions[] = 'gender = ?';
12    $parameters[] = array(
13            'value' => (int) $_POST['gender'],
14            'type' => PDO::PARAM_INT
15    );
16}
17
18if (isset($_POST['name'])) {
19    $conditions[] = 'name = ?';
20    $parameters[] = array(
21            'value' => $_POST['name'],
22            'type' => PDO::PARAM_STR
23    );
24}
25
26// 都道府県は、チェックボックスで複数選択可能。
27if (!empty($_POST['prefectures'])) {
28    $tmp = array();
29    foreach ($_POST['prefectures'] as $prefecture) {
30        $tmp[] = 'prefecture = ?';
31        $parameters[] = array(
32                'value' => (int) $prefecture,
33                'type' => PDO::PARAM_INT
34        );
35    }
36    $conditions[] = '(' . implode(' OR ', $tmp) . ')';
37}
38
39// 条件が1つでも指定されていれば、'WHERE'キーワードとともにそれらをSQL文に連結。
40if (!empty($conditions)) {
41    $sql .= ' WHERE ' . implode(' AND ', $conditions);
42}
43
44// var_dump($sql);
45// var_dump($parameters);
46
47$stmt = $pdo->prepare($sql);
48
49$i = 1;
50foreach ($parameters as $param) {
51   $stmt->bindValue($i++, $param['value'], $param['type']);
52}
53
54$stmt->execute();

$stmt->bindValue（または$stmt->bindParam）でパラメータをバインドする際は、第三引数は省略しないようにしてください。
省略した場合は一律、文字列として扱われるため、思わぬ挙動をすることがあるからです。
http://blog.tokumaru.org/2016/04/pdoint.html

最後に、

巨大なクエリを美しく書くための方針，知っておくべき用語も合わせて教えて頂けると幸いです．

については、「クエリビルダー」というキーワードで検索してみてください。
PHP の場合、「フレームワーク」に同梱されていることが多いようですので、興味があればそちらも調べてみてください。

投稿2016/09/12 04:22

編集2016/09/12 04:38

KiyoshiMotoki

総合スコア4791

eng27

2016/09/12 06:28 編集

回答ありがとうございます！美しい回答です！例として挙げられたコードは長いですが，非常に可読性が高いですね用語も教えて下さってありがとうございます！クエリビルダーですね..初心者なので，どのように検索をヒットさせれば良いのか困っていたところなので非常に勉強になります．あとプリペアードステートメントに気をつけます...勉強します！汗

行動規範の内容に同意します

よくやる方法としては、条件を配列にいれて、最後にimplodeして一つのwhere句にする方法です。

php
1<?php
2$where = [];
3$gender = $_POST["gender"];
4if(isset($_POST["gender"])) {
5    $where[] = "gender=$gender";
6}
7$age = $_POST["age"];
8if(isset($_POST["age"])) {
9    $where[] = "age=$age";
10}
11if(empty($where)) {
12    $sql = "SELECT * FROM members";
13} else {
14    $sql = "SELECT * FROM members WHERE " . implode('AND', $where);
15}
16?>

投稿2016/09/11 20:49

popobot

総合スコア6586

eng27

2016/09/12 06:30

回答ありがとうございます！私はあまりに初心者なのでimplodeをこのように使うとは想像もせず...目からウロコでした！勉強になります

行動規範の内容に同意します

私は「WHERE 1」派です、プログラム的にわかりやすい構造になります

PHP
1$pdo = new PDO($dsn,$user,$password);
2$data=[];
3$sql = "SELECT * FROM members WHERE 1";
4if(isset($_POST["gender"])){
5$sql.=" AND gender=?";
6$data[]=$_POST["gender"];
7}
8$stmt = $pdo->prepare($sql);
9$stmt->execute($data);

投稿2016/09/12 00:45

yambejp

総合スコア114843

eng27

2016/09/12 06:33

回答ありがとうございます！確かにシンプルでわかりやすい構造で，条件が多くとも見通しが良さそうですね！

行動規範の内容に同意します

動的SQLの場合はどうしてもこのような作りになってしまうのは仕方ないと思います。
他の回答にあるように配列に入れておいて、一括で変換をかけるようなやりかたもあるかと思いますが、例外的な条件が出てきたときに対応が難しくなります。
例えば全ての条件がAND条件（もしくはOR）なら良いですが、ある条件だけはORにしたいとか、そういった場合です。

それよりも今回提示されているコードの場合、$gender = $_POST["gender"]はif文内で行うべきではないでしょうか。

PHP
1if(isset($_POST["gender"])){
2    $gender = $_POST["gender"];
3    $sql = "SELECT * FROM members WHERE gender=$gender";
4} else {
5    $sql = "SELECT * FROM members";
6}

もしくは、

PHP
1if(isset($_POST["gender"])){
2    $sql = "SELECT * FROM members WHERE gender=$_POST['gender']";
3} else {
4    $sql = "SELECT * FROM members";
5}