データベース 絞り込み PHP 1対多のテーブルの多に対して複数の条件がある

Question

前提・実現したいこと

PHPとMySQLを使って学校の全生徒の名簿をデータベースにしてチェックボックスを用いて絞り込む機能を実現したいです。
チェックボックスに何も入力がない場合は全てのデータを出力するようにしています。
チェックボックスに入力がある場合はsql文に必要な条件をつけ足して絞り込むつもりです。
最終的に家族構成や性格などの入力も同時に受け付けて絞り込めるようにしたいと思っています。

以上を踏まえて、以下の様なデータベースが与えられたとき、部活番号001に所属している人かつ部活番号002に所属している人のデータを取り出したいです。

####データベースは以下の通りです(問題の個所に対応する部分だけです)。
|学籍番号|氏名|性別|学年|住所|備考|
|:--|:--:|--:|
|001|鈴木太郎|男|1|東京|無し|
|002|田中太郎|男|2|埼玉|無し|
|003|鈴木花子|女|3|東京|無し|
|004|田中花子|女|1|千葉|無し|

学生番号	部活番号
001	001
001	002
001	003
002	001
002	003
003	002
004	001
004	002

####望む結果

学籍番号	列2
001
004

####現状

学籍番号	列2
001
002
003
004

※データベースのテーマは変えています。実際は食材のデータを扱いますがカラム名が伝わりにくいと思ったので想像しやすいテーマにしました。

全体のソースコード

<?php
  $kazoku_arr = array('弟', '妹', '兄', '姉', '父', '母', 'ペット');
  $seikaku_arr = array('熱', '温', '平', '涼', '寒');
  $bukatu_arr = array('陸上', 'バスケ', 'サッカー','茶道', '書道', '文学', '囲碁','美術','バレー','テニス');
  $classification_arr = array('男', '女');

  //inputbox作成用の関数
  function array2inputbox($type, $arr, $name){
    if($type === "single"){
      for ($i = 0; $i < count($arr); $i++) {
        $s = $i+1;
        echo "<input type='radio' name='$name' value='$s'>{$arr[$i]}\n";
      }
    }else if($type === "multi"){
      for ($i = 0; $i < count($arr); $i++) {
        $s = $i+1;
        echo "<input type='checkbox' name='", $name, "[]' value='$s'>{$arr[$i]}\n";
      }
    }
  }

  try {

      // リクエストから得たスーパーグローバル変数をチェックするなどの処理
      //nameとseikakuとclassificationは単一情報
      if(isset($_GET['name']) && $_GET['name']!=''){
      	$name = htmlspecialchars($_GET['name']);
      }else{
      	$name = NULL;
      }
      if(isset($_GET['seikaku']) && $_GET['seikaku']!=''){
      	$seikaku =  htmlspecialchars($_GET['seikaku']);
      }else{
      	$seikaku = NULL;
      }
      if(isset($_GET['classification']) && $_GET['classification']!=''){
      	$classification =  htmlspecialchars($_GET['classification']);
      }else{
      	$classification = NULL;
      }
      //kazokuとbukatuは配列の可能性あり
      if(isset($_GET['kazoku']) && is_array($_GET['kazoku'])){
      	$kazoku = $_GET['kazoku'];
      }else{
      	$kazoku = NULL;
      }

      if(isset($_GET['bukatu']) && is_array($_GET['bukatu'])){
      	$bukatu = $_GET['bukatu'];
      }else{
      	$bukatu = NULL;
      }

      //新型

      // データベースに接続
      $pdo = new PDO(
          'mysql:dbname=db;host=127.0.0.1;charset=utf8',
      'ken',
      '001',
          [
              PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
              PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          ]
      );

      //何も選択されていない場合は全てを取得して表示
      if($name===NULL && $kazoku===NULL && $seikaku===NULL && $bukatu===NULL && $classification===NULL){
        //全データ表示のためのクエリ
        $que_all = 'SELECT * FROM main';
        //PDOStatement
        $stmt = $pdo->query($que_all);
      } else {
        //ベースとなるクエリ
        $que_search = 'SELECT DISTINCT main.* FROM main, has_kazoku, has_bukatus WHERE has_bukatus.name_id = main.name_id AND has_kazoku.name_id = main.name_id ';

        //seikakuのチェック情報からクエリに条件を追加
        if($seikaku != NULL){
          $que_search .= ' AND main.shisei_id =' . $seikaku;
        }

        //classificationのチェック情報からクエリに条件を追加
        if($classification != NULL){
          $que_search .= ' AND main.classification_id =' . $classification;
        }

        //kazokuのチェック情報からクエリに条件を追加
        if($kazoku != NULL){
          $que_kazoku = ' ';
          for($i=0;$i<count($kazoku);$i++){
            if($i===0){
              $que_kazoku .= '&& (';
              $que_kazoku .= 'has_kazoku.kazoku_id =' . $kazoku[$i];
            }
            $que_kazoku .= ' OR has_kazoku.kazoku_id =' . $kazoku[$i];
          }

          $que_search .= $que_kazoku. ')';

        }

        //完成したクエリをpdostatementに
        $stmt = $pdo->query($que_search);
      }


  } catch (PDOException $e) {

      // エラーが発生した場合は「500 Internal Server Error」でテキストとして表示して終了する
      // - もし手抜きしたくない場合は普通にHTMLの表示を継続する
      // - ここではエラー内容を表示しているが， 実際の商用環境ではログファイルに記録して， Webブラウザには出さないほうが望ましい
      header('Content-Type: text/plain; charset=UTF-8', true, 500);
      exit($e->getMessage());

  }

  // Webブラウザにこれから表示するものがUTF-8で書かれたHTMLであることを伝える
  // (これか <meta charset="utf-8"> の最低限どちらか1つがあればいい． 両方あっても良い．)
  //header('Content-Type: text/html; charset=utf-8');

?>
<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="utf-8">
    <title>データベース</title>
    <meta name="viewport" content="width=device-width">
	  <link rel="stylesheet" href="style.css">
  </head>
  <body>
    <div class="search-form">
      <form method="get" action="index.php" enctype="multipart/form-data">
  			<table border=0>
  				<!--名前-->
  				<tr>
  					<td>名前：</td>
  					<td><input name="name" size="20"></td>
  				</tr>
          <!--性別-->
  				<tr>
  					<td>性別：</td>
  					<td>
  						<?php
                array2inputbox("single", $classification_arr, "classification");
  						?>
  					</td>
  				</tr>
  				<!--家族構成-->
  				<tr>
  					<td>家族構成：</td>
  					<td>
  						<?php
                array2inputbox("multi", $kazoku_arr, "kazoku");
  						?>
  					</td>
  				</tr>
  				<!--性格-->
  				<tr>
  					<td>性格：</td>
  					<td>
  						<?php
                array2inputbox("single", $seikaku_arr, "seikaku");
  						?>
  					</td>
  				</tr>
  				<!--部活-->
  				<tr>
  					<td>部活：</td>
  					<td>
  						<?php
                array2inputbox("multi", $bukatu_arr, "bukatu");
  						?>
  					</td>
  				</tr>
  				<tr>
  					<td><input type="submit" value="検索"></td>
  				</tr>
  			</table>
  		</form>
    </div>
	  <br><BR>
    <div class="result-table">
    <table  class="table2" border=0>
    	<tr><th align='center'>食材名</th><th align='center'>家族構成</th><th align='center'>性格</th><th align='center'>部活</th><th align='center'>備考</th></tr>
        <?php
          //tableとして代入するための文字列を別ファイルから読み込み
        	$base = file_get_contents('base.html');

          //新型
          //http://php.net/manual/ja/pdostatement.fetch.phpを参照
        	while ($row = $stmt->fetch()){
            //家族構成のデータをdb->has_kazoku.name_id と db->main.name_id　と照らし合わせて取得(複数個あることを留意)
            //その後 db->has_kazoku.kazoku_id と db->kazoku_names.kazoku_id　と照らし合わせて文字に変換
            $kazoku_stmt = $pdo->prepare('SELECT DISTINCT kazoku_name FROM main, has_kazoku, kazoku_names WHERE has_kazoku.name_id = :name_id AND kazoku_names.kazoku_id = has_kazoku.kazoku_id');
            $kazoku_stmt->execute([':name_id'=>$row['name_id']]);
            ////家族構成のデータを連結させて一つの文字列に
            $kazoku_results = '';
            while ($oneOfkazoku = $kazoku_stmt->fetch()) {
              $kazoku_results .= $oneOfkazoku['kazoku_name'].' ';
            }

            //部活のデータをdb->has_bukatus.name_id と db->main.name_id　と照らし合わせて取得(複数個あることを留意)
            //その後 db->has_bukatus.bukatu_id と db->bukatu_names.bukatu_id　と照らし合わせて文字に変換
            $bukatu_stmt = $pdo->prepare('SELECT DISTINCT bukatu_name FROM main, has_bukatus, bukatu_names WHERE has_bukatus.name_id = :name_id AND bukatu_names.bukatu_id = has_bukatus.bukatu_id');
            $bukatu_stmt->execute([':name_id'=>$row['name_id']]);
            ////家族構成のデータを連結させて一つの文字列に
            $bukatu_results = '';
            while ($oneOfbukatu = $bukatu_stmt->fetch()) {
              $bukatu_results .= $oneOfbukatu['bukatu_name'].' ';
            }

            //$row['seikaku_id'] を db->seikaku_names->seikaku_name と適合させて代入
            $seikaku_stmt = $pdo->prepare('SELECT seikaku_name FROM main, seikaku_names WHERE seikaku_names.seikaku_id = :seikaku_id');
            $seikaku_stmt->execute([':seikaku_id'=>$row['shisei_id']]);
            $seikaku_result = $seikaku_stmt->fetch();

            //$baseへの値の代入
            printf($base, $row['link_page'], htmlspecialchars($row['name']), $kazoku_results, $seikaku_result['seikaku_name'], $bukatu_results, htmlspecialchars($row['kounou']));
          }
        ?>
	     </table>
  </div>
  </body>
</html>

<!--base.html-->
<tr><td align='center'><a href="%s">%s</a></td><td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td></tr>

###　該当部分

//kazokuのチェック情報からクエリに条件を追加
        if($kazoku != NULL){
          $que_kazoku = ' ';
          for($i=0;$i<count($kazoku);$i++){
            if($i===0){
              $que_kazoku .= '&& (';
              $que_kazoku .= 'has_kazoku.kazoku_id =' . $kazoku[$i];
            }
            $que_kazoku .= ' OR has_kazoku.kazoku_id =' . $kazoku[$i];
          }

          $que_search .= $que_kazoku. ')';

        }

試したこと

どうしても思いつかないのでOR検索になるようにしています。

ここに問題に対して試したことを記載してください。

Answer 1

あまり良いやり方ではありませんが、一番簡単なのを書きます。

テーブル名がないので、二つ目のテーブルを部活リストとすると

SELECT 生徒番号
FROM 部活リスト
WHERE 部活番号 IN (’001’, ’002’)
GROUP BY 生徒番号
HAVING COUNT(*) = 2 -- 選択した部活番号の個数

Comments

[kantomi]

生徒マスタ（メニューマスタ、食材マスタかな？）は、上記のSQLをサブクエリにして、そのサブクエリに対して JOIN すれば良いでしょう。

[001KEN001]

返答ありがとうございます。大変参考になりました。SQLサーバー上では望む結果を得られるようになったのでphpで今から実装します。一つ気になったのですが、「あまり良いやり方ではありませんが…」と書かれていらっしゃいますが、どのあたりがあまり良くないのでしょうか。教えていただきたいです。

[kantomi]

選択した個数を別建てで設定するのは、あまりSQL的とは言えません。
再帰SQLでできますが、MySQLの再帰クエリは、8.0.3 からの機能なので控えました。

Answer 2

活番号001に所属している人かつ部活番号002に所属している人のデータを取り出したいです。
とのことでしたが、コードは、家族での条件になっています。
部活の条件が見当たらない気がします。
OR以外には、IN句を使用することもできます。 bukatu_id IN (チェックしたID1,チェックしたID2)

Comments

[001KEN001]

返答ありがとうございます。本当ですね。変数名を家族にしてしまっています。kazokuもbukatuも変数名が違うこと以外は全く同じ処理を行うことを考えておりますので、kazokuと書かれている部分はbukatuと置き換えていただいて差し支えないです。
IN句は引数を含むデータを返すという理解なのですが、それを使って望む結果（001,004）を出力することができなかったのですが、何か方法があるのですか？