回答編集履歴

調整

2017/10/16 14:28

投稿

yambejp

スコア114814

test CHANGED Viewed

@@ -392,7 +392,7 @@
   $stmt = $pdo->prepare($sql);
-  $stmt->execute([]);
+  $stmt->execute($data);
 */

調整

2017/10/16 14:28

投稿

yambejp

スコア114814

test CHANGED Viewed

@@ -260,8 +260,12 @@
+※テストのため、以下のソースを適当な名前のphpに保存して実行して下さい
 ```PHP
+<?PHP
 $todofuken1=filter_input(INPUT_POST,"todofuken1");
 $job_type=filter_input(INPUT_POST,"SHOKUSHU_CD",FILTER_DEFAULT,["options"=>["default"=>[]],"flags"=>FILTER_REQUIRE_ARRAY]);
@@ -392,4 +396,124 @@
 */
+?>
+<form method="POST">
+<table>
+<tbody>
+<tr>
+<th>勤務地を指定</th>
+<td>
+<select name="todofuken1" id="todofuken1">
+<option value="">都道府県を選択</option>
+<option value="13">東京都</option>
+<option value="14">神奈川県</option>
+<option value="15">埼玉県</option>
+<option value="16">千葉県</option>
+</select>
+</td>
+</tr>
+<tr><th>職種を指定</th>
+<td>
+<div class="three_line clear">
+<input type="checkbox" name="SHOKUSHU_CD[]" value="1" id="shokushuLoop1">
+<label for="shokushuLoop1">オフィスワーク・事務</label>
+<input type="checkbox" name="SHOKUSHU_CD[]" value="2" id="shokushuLoop2">
+<label for="shokushuLoop2">営業・販売・サービス</label>
+<input type="checkbox" name="SHOKUSHU_CD[]" value="3" id="shokushuLoop3">
+<label for="shokushuLoop3">Ｗｅｂ・クリエイティブ</label>
+</div>
+</td>
+</tr>
+<tr><th>時給を指定</th>
+<td>
+<input type="text" class="text_input_s" name="PAY_START" maxlength="8" size="8" value="" style="ime-mode:disabled">&nbsp;円以上?
+</td>
+</tr>
+<tr><th>キーワードを指定</th><td><input type="text" class="text_input_l" name="FREE_KEYWORD" maxlength="30" size="20" value="" style="ime-mode:active" placeholder="キーワードを入力してください。"></td></tr>
+<tr>
+<th>こだわり条件を指定</th>
+<td>
+<div class="accordion_block">
+<h2 style="background-image: url(&quot;../../img/common/icon_arrow_up01.png&quot;);">働き方・特徴</h2>
+<div class="accordion_box three_line clearfix" style="display: block;">
+<input type="checkbox" name="TOKUCYO[]" value="紹介予定派遣" id="tokucyoLoop1101"><label for="tokucyoLoop1101">紹介予定派遣</label>
+<input type="checkbox" name="TOKUCYO[]" value="1日7時間未満" id="tokucyoLoop1102"><label for="tokucyoLoop1102">1日7時間未満</label>
+</div>
+</div>
+<div class="accordion_block">
+<h2 style="background-image: url(&quot;../../img/common/icon_arrow_up01.png&quot;);">業界</h2>
+<div class="accordion_box three_line clearfix" style="display: block;">
+<input type="checkbox" name="GYOUKAI[]" value="マスコミ関連" id="tokucyoLoop3301"><label for="tokucyoLoop3301">マスコミ関連</label>
+<input type="checkbox" name="GYOUKAI[]" value="金融関連" id="tokucyoLoop3302"><label for="tokucyoLoop3302">金融関連</label>
+<input type="checkbox" name="GYOUKAI[]" value="流通・サービス関連" id="tokucyoLoop3303"><label for="tokucyoLoop3303">流通・サービス関連</label>
+<input type="checkbox" name="GYOUKAI[]" value="IT・Web関連" id="tokucyoLoop3304"><label for="tokucyoLoop3304">IT・Web関連</label>
+</div>
+</div>
+</td>
+</tr>
+</tbody>
+</table>
+<p class="center"><input type="submit" value="この条件で検索する"></p>
+</form>
 ```

修正・追記

2017/10/16 11:49

投稿

yambejp

スコア114814

test CHANGED Viewed

@@ -84,7 +84,7 @@
     $sql.="OR work_type IN (".implode(",",array_fill(0,count($work_type),"?")).") ";
-    $data[]=$work_type;
+    $data=array_merge($data,$work_type); /*記載ミス訂正*/
   }
@@ -92,7 +92,7 @@
     $sql.="OR gyoukai IN (".implode(",",array_fill(0,count($gyoukai),"?")).") ";
-    $data[]=$gyoukai;
+    $data=array_merge($data,$gyoukai); /*記載ミス訂正*/
   }
@@ -249,3 +249,147 @@
 ```
+※一部記載ミスを訂正しました
+# より標準SQLらしく
+1や0で処理していたものをvariantに変えました
+またINをORの羅列に変えました
+```PHP
+$todofuken1=filter_input(INPUT_POST,"todofuken1");
+$job_type=filter_input(INPUT_POST,"SHOKUSHU_CD",FILTER_DEFAULT,["options"=>["default"=>[]],"flags"=>FILTER_REQUIRE_ARRAY]);
+$jikyu=filter_input(INPUT_POST,"PAY_START",FILTER_VALIDATE_INT);
+$work_type=filter_input(INPUT_POST,"TOKUCYO",FILTER_DEFAULT,["options"=>["default"=>[]],"flags"=>FILTER_REQUIRE_ARRAY]);
+$gyoukai=filter_input(INPUT_POST,"GYOUKAI",FILTER_DEFAULT,["options"=>["default"=>[]],"flags"=>FILTER_REQUIRE_ARRAY]);
+$data=[];
+$sql ="SELECT * ";
+$sql.=" FROM kyujin ";
+$sql.="LEFT JOIN job_type ";
+$sql.=" ON kyujin.job_type=job_type.id ";
+$sql.=" LEFT JOIN area  ";
+$sql.=" ON  kyujin.area = area.id ";
+$sql.=" where true ";
+$flg[0]=(!is_null($todofuken1) and $todofuken1!=="");
+$flg[1]=count($job_type)>0;
+$flg[2]=$jikyu>0;
+$flg[3]=count($work_type)>0;
+$flg[4]=count($gyoukai)>0;
+if($flg[0]){
+  $sql.="AND kyujin.area = ? ";
+  $data[]=$todofuken1;
+}
+if($flg[1] or $flg[2] or $flg[3] or $flg[4]){
+  $sql.="AND ( false ";
+  if($flg[1]){
+    foreach($job_type as $val){
+      $sql.="OR kyujin.job_type = ? ";
+      $data[]=$val;
+    }
+  }
+  if($flg[2]){
+    $sql.="OR jikyu>=? ";
+    $data[]=$jikyu;
+  }
+  if($flg[3]){
+    foreach($work_type as $val){
+      $sql.="OR work_type = ? ";
+      $data[]=$val;
+    }
+  }
+  if($flg[4]){
+    foreach($gyoukai as $val){
+      $sql.="OR gyoukai = ? ";
+      $data[]=$val;
+    }
+  }
+  $sql.=") ";
+}
+if(!in_array(true,$flg)){
+  $sql.="AND false ";
+}
+print $sql;
+print "<hr>";
+print_r($data);
+/*
+  $stmt = $pdo->prepare($sql);
+  $stmt->execute([]);
+*/
+```