様々な検索方法を実装したことがなかったので練習がてら色々試しています。
建物種別のカラムには3種類の値が入っていて、今そこを検索するためのSQK文を書いている最中です。
ラジオボックスを複数選択すると何も表示されません。
1つだけならちゃんとぞれぞれが表示されます。
ソースは下記になりますが、どう改良したらよろしいでしょうか?
$limit = filter_input(INPUT_GET,"limit",FILTER_VALIDATE_INT,["options"=>["default"=>10]]); $sql = sprintf(" SELECT a.`id`, a.`title`, a.`area`, a.`buildingtype`, a.`floorplan`, a.`rento`, a.`deposit`, a.`keymoney`, a.`body`, a.`created`, a.`modified`, c.`companyName` FROM articles a INNER JOIN users u ON a.`user_id` = u.`id` INNER JOIN companys c ON u.`company_id`= c.`id` WHERE title LIKE ? AND ( buildingtype LIKE ? ) AND ( buildingtype LIKE ? ) AND ( buildingtype LIKE ? ) LIMIT %s",$limit ); $search = '%'.filter_input(INPUT_GET,"searchs").'%'; $mansion = '%'.filter_input(INPUT_GET,"mansion").'%'; $apartment = '%'.filter_input(INPUT_GET,"apartment").'%'; $residentialHome = '%'.filter_input(INPUT_GET,"residentialHome").'%'; $bodys = $pdo->prepare($sql); $bodys -> execute([ $search, $mansion, $apartment, $residentialHome ]);
追記
<?php $dsn = 'mysql:dbname=test;host=localhost;charset=utf8'; $user = 'root'; $password = ''; $option = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); $pdo = new PDO($dsn, $user, $password, $option); $sql = "SELECT MIN(rento) as rento FROM articles"; $minRentos = $pdo->query($sql); $minRento=$minRentos->fetch(); $sql = "SELECT MAX(rento) as rento FROM articles"; $maxRentos = $pdo->query($sql); $maxRento=$maxRentos->fetch(); $limit = filter_input(INPUT_GET,"limit",FILTER_VALIDATE_INT,["options"=>["default"=>10]]); $sql = sprintf(" SELECT a.`id`, a.`title`, a.`area`, a.`buildingtype`, a.`floorplan`, a.`rento`, a.`deposit`, a.`keymoney`, a.`body`, a.`created`, a.`modified`, c.`companyName` FROM articles a INNER JOIN users u ON a.`user_id` = u.`id` INNER JOIN companys c ON u.`company_id`= c.`id` WHERE title LIKE ? AND (( buildingtype LIKE ? ) or ( buildingtype LIKE ? ) or ( buildingtype LIKE ? )) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) AND( floorplan LIKE ? ) LIMIT %s",$limit ); $search = '%'.filter_input(INPUT_GET,"searchs").'%'; $mansion = '%'.filter_input(INPUT_GET,"mansion").'%'; $apartment = '%'.filter_input(INPUT_GET,"apartment").'%'; $residentialHome = '%'.filter_input(INPUT_GET,"residentialHome").'%'; $madori1 = '%'.filter_input(INPUT_GET,"1R").'%'; $madori2 = '%'.filter_input(INPUT_GET,"1K").'%'; $madori3 = '%'.filter_input(INPUT_GET,"1DK").'%'; $madori4 = '%'.filter_input(INPUT_GET,"1LDK").'%'; $madori5 = '%'.filter_input(INPUT_GET,"2K").'%'; $madori6 = '%'.filter_input(INPUT_GET,"2DK").'%'; $madori7 = '%'.filter_input(INPUT_GET,"2LDK").'%'; $madori8 = '%'.filter_input(INPUT_GET,"3K").'%'; $madori9 = '%'.filter_input(INPUT_GET,"3DK").'%'; $madori10 = '%'.filter_input(INPUT_GET,"3LDK").'%'; $madori11 = '%'.filter_input(INPUT_GET,"4K").'%'; $madori12 = '%'.filter_input(INPUT_GET,"4DK").'%'; $madori13 = '%'.filter_input(INPUT_GET,"4LDK").'%'; $madori14 = '%'.filter_input(INPUT_GET,"4LDKS").'%'; $madori15 = '%'.filter_input(INPUT_GET,"5LDK~").'%'; $bodys = $pdo->prepare($sql); $bodys -> execute([ $search, $mansion, $apartment, $residentialHome, $madori1, $madori2, $madori3, $madori4, $madori5, $madori6, $madori7, $madori8, $madori9, $madori10, $madori11, $madori12, $madori13, $madori14, $madori15 ]); ?> <!-- ここまで裏の処理 --> <form action="" method="get"> 表示件数 <select name="limit"> <?php for ($i= 5; $i <=50;$i=$i+5){ $selected = $i == 10 ? 'selected': ''; echo '<option value="'. $i .'"'. $selected .'> '. $i .'件づつ</option>'; } ?> </select> <br /> <br /> フリーワード <input type="text" name="searchs"> <br /> <br /> 家賃 <select name="limitMin"> <?php echo '<option value="'.sprintf('%0.1f', 0) .'">下限なし</option><br />'; $startPrice = round($minRento['rento'],-3)/10000; $shredded = 0.5; for ($startPrice; $startPrice <= (round($maxRento['rento'],-3)/10000); $startPrice = $startPrice + $shredded) { echo '<option value="'.sprintf('%0.1f', $startPrice) .'">'.$startPrice .'万円以上</option><br />'; } ?> </select> ~ <select name="limitMax"> <?php $startPrice = round($minRento['rento'],-3)/10000; $shredded = 0.5; for ($startPrice + $swing; $startPrice <= (round($maxRento['rento'],-3)/10000); $startPrice = $startPrice + $shredded) { echo '<option value="'.sprintf('%0.1f', $startPrice) .'">'.$startPrice .'万円以上</option><br />'; } echo '<option value="'.sprintf('%0.1f', 0) .'" selected>上限なし</option><br />'; ?> </select> <br /> <br /> 建物種別 <input type="checkbox" name="mansion" value="マンション">マンション <input type="checkbox" name="apartment" value="アパート">アパート <input type="checkbox" name="residentialHome" value="一戸建て">一戸建て・その他 <br /> <br /> 間取 <?php $madoris = array( '1R', '1K', '1DK', '1LDK', '2K', '2DK', '2LDK', '3K', '3DK', '3LDK', '4K', '4DK', '4LDK', '4LDKS', '5LDK~', ); ?> <?php foreach($madoris as $madori ){ echo '<input type="checkbox" name="' . $madori . '" value="' . $madori . '">' . $madori . ''; } ?> <br /> <br /> <input type="submit" name="submit" value="選択した条件で検索する"> </form> <table border="" width="100%"> <thead> <tr> <th>NO</th> <th>タイトル</th> <th>概要</th> <th>間取</th> <th>建物種別</th> <th>賃料</th> <th>敷金/礼金</th> <th>管理会社</th> <th>公開日</th> </tr> </thead> <tbody> <?php foreach($bodys as $body){ ?> <tr> <td><?php echo $body['id']; ?></td> <td><?php echo $body['title'] ?></td> <td><?php echo $body['body'] ?></td> <td><?php echo $body['floorplan'] ?></td> <td><?php echo $body['buildingtype'] ?></td> <td><?php echo number_format($body['rento']) ?></td> <td><?php echo sprintf('%.1f', ($body['deposit'] / $body['rento']))."/".sprintf('%0.1f', ($body['keymoney'] / $body['rento'])) ?></td> <td><?php echo $body['companyName'] ?></td> <td><?php echo date('Y年m月d日', strtotime($body['created'])); ?></td> </tr> <?php } ?> </tbody> </table>
回答3件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/08/30 03:57
2016/08/30 04:25
2016/08/30 04:35
2016/08/30 04:42
2016/08/30 04:48
2016/08/30 04:50
2016/08/30 05:06
2016/08/30 05:08
2016/08/30 05:18
2016/08/30 05:46