前提・実現したいこと

質問が2つあります。どちらか片方でも答えてくれたら助かります。

PHPとMySQLをつかって、学生登録システムを作っています。

質問1
ーーーーーーーーーーーーーーーーーーーー
条件を入力し、学生の絞り込みをしたいのですが、条件を複数選択した場合うまくいきません。
条件を性別だけ、所属だけ、と単独なら正常処理します。
例、性別→女、所属→文化部、で検索すると花子文化部、のび太文化部が表示されてしまう。

今のプログラムでは所属のif分が最後にあり、所属が優先されるプログラムです。
どうやったら、条件を複数選択しても、正しく絞り込みができるでしょうか？
ーーーーーーーーーーーーーーーーーーーー

質問2
ーーーーーーーーーーーーーーーーーーーー
「運動部」で検索しても、yaruo運動部、一人しか検索されません。
正しい処理は
ruck ruck@ 女運動部,帰宅部,生徒会,
yaruo aaa@ 男運動部
yaruo aaa@ 男運動部生徒会
出木杉 aaa@ 男運動部文化部生徒会
4人検索されるはずです。
この問題の解決方法はどうすればいいでしょうか？
質問2は大幅にプログラムとテーブルを書き換えないと駄目ですね( ;∀;)
ーーーーーーーーーーーーーーーーーーーー
ここに質問の内容を詳しく書いてください。（例）PHP(CakePHP)で●●なシステムを作っています。
■■な機能を実装中に以下のエラーメッセージが発生しました。

該当のソースコード

PHP
1<!-- 学生表示画面 -->
2<!-- list.php -->
3
4<?php
5
6$res = "";  //新しいウェブページのエラー回避
7
8$USER= 'root';
9$PW= '1234';
10$DBINFO= "mysql:dbname=cwdb;host=localhost;charset=utf8";
11
12try{
13    $pdo = new PDO($DBINFO,$USER,$PW); //pdoインスタンスの生成
14    $sql = "SELECT * FROM students";
15
16    //$sql = "SELECT * FROM students WHERE student_name LIKE '%山%'";
17    if (isset($_POST['freeword'])) {
18        $sql = "SELECT * FROM students WHERE student_number LIKE '%{$_POST['freeword']}%' OR student_name LIKE '%{$_POST['freeword']}%' OR email LIKE '%{$_POST['freeword']}%' ";
19    }
20
21    if (isset($_POST['sex'])) {
22        if ($_POST['sex'] == "男") {
23            $sql = "SELECT * FROM students WHERE sex = '男'";
24        }elseif ($_POST['sex'] == "女") {
25            $sql = "SELECT * FROM students WHERE sex = '女'";
26        }
27    }
28
29    if (isset($_POST['syozoku'])) {
30        if ($_POST['syozoku'] == "運動部") {
31            $sql = "SELECT * FROM students WHERE syozoku = '運動部'";
32        }elseif ($_POST['syozoku'] == "文化部") {
33            $sql = "SELECT * FROM students WHERE syozoku = '文化部'";
34        }elseif ($_POST['syozoku'] == "帰宅部") {
35            $sql = "SELECT * FROM students WHERE syozoku = '帰宅部'";
36        }elseif ($_POST['syozoku'] == "生徒会") {
37            $sql = "SELECT * FROM students WHERE syozoku = '生徒会'";
38        }
39    }
40
41   $stmt = $pdo->prepare($sql);
42    $stmt->execute(null);
43    $res = "<table border=1>
44            <tr>
45                <th>学籍番号</th>
46                <th>氏名</th>
47                <th>メールアドレス</th>
48                <th>性別</th>
49                <th>所属</th>
50                <th>年</th>
51                <th>編</th>
52            </tr>\n";
53
54    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
55
56        $res .= <<<eod
57        <tr>
58            <td>{$row['student_number']}</td>
59            <td>{$row['student_name']}</td>
60            <td>{$row['email']}</td>
61            <td>{$row['sex']}</td>
62            <td>{$row['syozoku']}</td>
63            <td>{$row['birth_year']}</td>
64            <td><a href = "edit.php?seq={$row['seq']}">編</a></td>
65        </tr>
66eod;
67    }
68
69    $res .= "</table>\n";
70
71}catch(Exception $e){
72    echo "エラー発生" . $e->getMessage();
73}
74
75?>
76
77<!DOCTYPE html>
78<html>
79<head>
80    <title>一覧画面</title>
81</head>
82<body>
83
84<form method="post" action="regist.php">
85    <input type="submit" value="新規登録"></input>
86</form>
87
88<p>
89    <form method="post">
90        条件を絞り込む<br>
91        フリーワード<input type="text" name="freeword"><br>
92        性別
93        <select name="sex">
94            <option></option>
95            <option value="男">男</option>
96            <option value="女">女</option>
97        </select><br>
98
99        所属
100        <select name="syozoku">
101            <option></option>
102            <option value="運動部">運動部</option>
103            <option value="文化部">文化部</option>
104            <option value="帰宅部">帰宅部</option>
105            <option value="生徒会">生徒会</option>
106        </select><br>
107
108        <input type="submit" value="検索する"></input>
109    </form>
110</p>
111
112<form>
113    <input type="submit" value="全員表示"> <!-- URLをクリックした時と同じ処理-->
114</form><br>
115
116<?php echo $res; ?>
117
118
119
120</body>
121</html>

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

回答4件

まずDBの正規化について学習して下さい
syozokuにカンマ区切りでデータを入れるのはSQLとしては非効率です
所属グループテーブルをつくり、団体ごとにidを振り
ユーザー＝所属連携テーブルを別途つくって紐付けます

sample

とりあえず今のままのデータ構成で検索するならこう

PHP
1$freeword=filter_input(INPUT_POST,'freeword');
2$sex=filter_input(INPUT_POST,'sex',FILTER_VALIDATE_REGEXP,["options"=>["regexp"=>"/^(男|女)$/u","default"=>NULL]]);
3$syozoku=filter_input(INPUT_POST,'syozoku',FILTER_VALIDATE_REGEXP,["options"=>["regexp"=>"/^(運動部|文化部|帰宅部|生徒会)$/u","default"=>NULL]]);
4
5$sql = "SELECT * FROM students WHERE 1 ";
6$data=[];
7
8if(!is_null($freeword) and $freeword!==""){
9  $sql.= " and (0 ";
10  $sql.= " or student_number LIKE ?";
11  $sql.= " or student_name LIKE ?";
12  $sql.= " or email LIKE ?";
13  $sql.= "  ) ";
14  $data=array_merge($data,array_fill(0,3,'%'.$freeword.'%'));
15}
16
17if(!is_null($sex)){
18$sql.= " and sex=?";
19$data[]=$sex;
20}
21if(!is_null($syozoku)){
22$sql.= " and syozoku regexp ?";
23$data[]="(^|,)".$syozoku."(,|$)";
24}
25print $sql."<br>";
26print_r($data);
27
28/*
29検索結果の表示
30$stmt = $pdo->prepare($sql);
31$stmt->execute($data);
32$rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
33print_r($rows);
34*/
35?>
36<form method="post">
37条件を絞り込む<br>
38フリーワード<input type="text" name="freeword"><br>
39性別
40<select name="sex">
41<option value="">---</option>
42<option value="男">男</option>
43<option value="女">女</option>
44</select><br>
45所属
46<select name="syozoku">
47<option value="">------</option>
48<option value="運動部">運動部</option>
49<option value="文化部">文化部</option>
50<option value="帰宅部">帰宅部</option>
51<option value="生徒会">生徒会</option>
52</select><br>
53<input type="submit" value="検索する"></input>
54</form>

投稿2018/02/22 07:51

編集2018/02/22 08:31

yambejp

総合スコア114784

m.ts10806

2018/02/22 07:55

カンマ区切りだけならまだいいんですが、区切りがないのもあるし番号っぽいのもあるし、で、色々と改善点が多そうですよね。「年」の「1970」もかなり怪しいですし。

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

質問1について。

男女の別と部活の別はAND条件なので、
両方の条件が指定されているなら両方を加味したSQL（SELECT分のWHERE句）である必要があります。
SQLで得られた結果をPHP側でさらにフィルターを掛けるのはナンセンスですから。

ズバリ書くと面倒なので察してほしいのですが、私なら、
１）例えば

    $criteria = array();

    if (isset($_POST['sex'])) {
        if ($_POST['sex'] == "男") {
            $criteria[] = "sex = '男' ";  // 配列の末尾に追加
        }elseif ($_POST['sex'] == "女") {
            $criteria[] = "sex = '女' ";
        }
    }

    if (isset($_POST['syozoku'])) {
        if ($_POST['syozoku'] == "運動部") {
            $criteria[] = "syozoku LIKE '%運動部%' ";
        }elseif ($_POST['syozoku'] == "文化部") {
            $criteria[] = "syozoku LIKE '%文化部%' ";
        }elseif ($_POST['syozoku'] == "帰宅部") {
            $criteria[] = "syozoku LIKE '%帰宅部%' ";
        }elseif ($_POST['syozoku'] == "生徒会") {
            $criteria[] = "syozoku LIKE '%生徒会%' ";
        }
    }

    if (count($criteria) > 0) {
        $sql = "SELECT * FROM students WHERE " . implode('AND ', $criteria);
    }

みたいにすると、指定のあった要素がANDで結合されたSQL文が出来上がるかと。

質問2については、
人テーブルと所属テーブルの間に人１対所属多になる関連付けを示すテーブルが必要ですね。

投稿2018/02/22 07:38

編集2018/02/22 07:44

退会済みユーザー

総合スコア0

回答１

MySQLに渡すクエリで検索条件をANDで２つ指定すれば全てを満たした行のみ返ってきます。

具体的には

SQL
1SELECT * FROM students WHERE sex = '女' AND syosoku LIKE '%文化部%';

で表示されます。

しかし、 LIKE句で中間一致検索をしているため、syozokuカラムに索引を作成しても
使われないため、レコード件数が多い(数万とか)と性能問題を引き起こします。

回答２

これも

SQL
1SELECT * FROM students WHERE syosoku LIKE '%運動部%';

というように中間一致検索で解決できます。

参考

本来はテーブルを正規化すべきかと思います。
http://tech.nikkeibp.co.jp/it/article/COLUMN/20130416/471174/

中間一致検索について
https://so-kai-app.sakura.ne.jp/blog/1527/2016/12/14/

投稿2018/02/22 07:40

編集2018/02/22 08:18

yo4da

総合スコア25

ベストアンサー

pdo使っているのにbind使ってなかったり、sqlのエスケープされてなくて非常に勿体無いですね。。

入力コントロールの内容そのまま問い合わせに使うのであれば、わざわざ大量にif文使わなくて良いと思います。

質問1

それぞれの条件のときにわざわざselect文で完結してしまっているからですね。
条件を配列で持っておき、andでつなげてSELECTとすることで、複数条件で検索可能となります。

※とりあえず現状のソースを活かすので冒頭に挙げたエスケープは入れてません。

php
1    $where = [];
2    if (isset($_POST['sex']) && $_POST['sex'] != "") {
3        $where[] = " ( sex = '".$_POST['sex']."' ) ";
4    }
5    if (isset($_POST['syozoku']) && $_POST['syozoku'] != "") {
6        $where[] = " ( syozoku like '%".$_POST['sex']."%' ) ";
7    }
8
9    $sql = 'SELECT * FROM student';
10
11    if(count($where) > 0){
12      $sql .= " where ".implode(" and ",$where);
13    }
14

質問2

全て「イコール」で検索しているからです。
現状のデータ構造を変えられないのであれば、とりあえず質問１の回答のソースのようにlikeの部分一致で検索すれば良いはず。

余談：
所属名称や性別などはコードで持っておき、別のテーブルにした方が良いです。
今回の所属名や性別などは名称があまり変わらないかもしれませんが、
日本語や名称を持っておくと万が一名称が変わったときに管理しきれなくなります。
所属も複数登録する可能性があるのであれば、同じテーブル内で規則性ないセパレータで持つよりも、
「生徒所属管理テーブル」のようなものを作って、「生徒コード」「所属コード」で1対多の関係で持っておき、
生徒テーブルから随時参照するような形でしておく方が、~~管理はしやすくなります。~~それぞれの役割がはっきりします。
1つのテーブルに全て詰め込むのは後々のメンテナンス性まで考えた場合に賢いやり方ではありません。