teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問MySQL、PHPで複数の項目から部分検索ができる検索システム

編集履歴

質問編集履歴

2

$dbのreturnを削除し、以下を可変長プレースホルダの形に書き換えました。また、HTMLのhtmlspecialchars部分をphp内に記述してみました。

2019/11/26 15:32

投稿

TaraIkura
TaraIkura

スコア5

title CHANGED
File without changes
body CHANGED
@@ -84,77 +84,73 @@
84
84
  ```
85
85
  //search.phpのコード
86
86
  function getUserData() {
87
- //DBの接続情報
87
+
88
88
  include_once( 'database.php' );
89
89
 
90
- //DBコネクタを生成
91
- try {
90
+ try {
92
91
  $db = new PDO( $dsn, $usr, $passwd );
93
- $db->exec( 'SET NAMES utf8' );
94
92
  $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
95
93
  $db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
96
- } catch ( PDOException $e ) {
97
- die( "接続エラー1: {$e->getMessage()}" );
98
- }
99
- return $db;
100
- 　//ここまでは接続確認済です。
101
-
102
- //queryのデータ
103
- try {
104
- 　 $query = "SELECT * FROM TABLE1 WHERE search_name LIKE ? ESCAPE '!' AND color LIKE ? ESCAPE '!' AND category LIKE ? ESCAPE '!' ";
105
- $qPart = array_fill( 0, count( $userData ), "(?, ?, ?, ?, ?)" );
106
- $query .= implode( ",", $qPart );
107
94
 
108
- $search_name = $_POST[ 'search_name' ];
109
- $color = $_POST[ 'color' ];
110
- $category = $_POST[ 'category' ];
95
+ $q = ( string )filter_input( INPUT_GET, 'q' );
96
+ $maxKeywords = -1;
97
+ $keywords = preg_split( '/(?:\p{Z}|\p{Cc})++/u', $q, $maxKeywords, PREG_SPLIT_NO_EMPTY );
111
98
 
112
- //SQL文を実行する
99
+ if ( $keywords ) {
113
- $UserDataSet = $db->prepare( $query );
100
+ foreach ( $keywords as $keyword ) {
114
- $i = 1;
115
- foreach ( $userData as $row ) { //bind the values one by one
116
- $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $search_name ) . '%' ], PDO::PARAM_STR );
101
+ $holders[] = "((search_name LIKE ? ESCAPE '!') OR (color LIKE ? ESCAPE '!') OR (category LIKE ? ESCAPE '!'))";
117
- $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $color ) . '%' ], PDO::PARAM_STR );
102
+ $values[] = $values[] = '%' . preg_replace( '/(?=[!_%])/', '!', $keyword ) . '%';
118
- $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $category ) . '%' ], PDO::PARAM_STR );
119
- }
103
+ }
120
- $UserDataSet->execute();
121
- //扱いやすい形に変える
122
- $result = [];
123
- while ( $row = $UserDataSet->fetch(PDO::FETCH_ASSOC)) {
124
- $result[] = $row;
125
- }
126
- return $result;
127
104
 
105
+ $sql = "SELECT * FROM TABLE1 WHERE (' . implode(' AND ', $holders) . ')";
106
+
107
+ $UserDataSet = $bd->prepare( $sql );
108
+ $UserDataSet->execute( $values );
109
+ $userData = $UserDataSet->fetchAll( PDO::FETCH_ASSOC );
110
+ }
111
+
112
+ print '接続に成功しました';
113
+
128
114
  } catch ( PDOException $e ) {
115
+ header( 'Content-Type: text/plain; charset=UTF-8', true, 500 );
129
- die( "接続エラー2: {$e->getMessage()}" );
116
+ exit( '接続できませんでした' . $e->getMessage() );
130
117
  }
118
+
119
+ function h( $str ) {
120
+ return htmlspecialchars( $str, ENT_QUOTES, 'UTF-8' );
121
+ }
131
122
  }
132
123
 
133
124
  //ーーー HTML側 ーーー
134
125
  <!doctype html>
135
126
  <?php
136
127
  include_once( 'search.php' );
137
- $userData = getUserData( $_POST );
128
+ $userData = getUserData( $_GET );
138
129
  ?>
139
130
  <html>
140
131
  <head>
141
132
  </head>
142
133
  <body>
143
134
  　　//キーワード検索、チェックボックス、セレクションの３タイプで検索
144
- 　　<input name="search_name" value="<?php echo isset($search_name) ? htmlspecialchars($search_name) : '' ?>">
135
+ 　　<input name="search_name" value="<?php echo isset($search_name) ?= h($search_name) : '' ?>">
145
136
  <input type="checkbox" name="color" value="白" <?php echo isset($color) && $color == '白' ? 'selected' : '' ?>><label for="color">白</label>//他省略
146
137
  　　<select name="category">
147
138
  <option value="0" <?php echo empty($category) ? 'selected' : '' ?>>分類A</option>//他省略
148
139
  </select>
149
140
  <button type="submit" name="search">検索</button>
150
141
  　　
151
- //上記項目以外のデータで結果表示
142
+ 　　//$holders[]に指定した列以外のデータで結果表示
152
- <?php if(isset($userData)): ?>
143
+ <?php if (isset($err)) : ?>
144
+ <p><?= h($err->getMessage()); ?></p>
145
+ <?php endif; ?>
146
+ <?php if (0 < count($userData)): ?>
153
- <?php foreach($userData as $row): ?>
147
+ <?php foreach ($userData as $row) : ?>
148
+ <div class="box_warp">
149
+ <div><img class="circular" height="80" src="../<?= h($row['img']); ?>"></div>
154
- <img src="../<?php echo htmlspecialchars($row['img']); ?>">
150
+ <div class="break-word"><?= h($row['original_name']); ?></div>
155
- <?php echo htmlspecialchars($row['original_name']) ?>
151
+ </div>
156
- <?php endforeach; ?>
152
+ <?php endforeach; ?>
157
- <?php endif; ?>
153
+ <?php endif; ?>
158
154
  </body>
159
155
  </html>
160
156
  ```

1

1. 接続が確認できている部分にコメント追加 2. database.phpに設定した$usertable をテーブル名に変更 3. ->execute();の位置を変更

2019/11/26 15:32

投稿

TaraIkura
TaraIkura

スコア5

title CHANGED
File without changes
body CHANGED
@@ -14,13 +14,12 @@
14
14
  //DBコネクタを生成
15
15
  $Mysqli = new mysqli( $dsn, $usr, $passwd );
16
16
  $Mysqli->set_charset( 'utf8' );
17
- $query = "SELECT * FROM $usertable";
17
+ $query = "SELECT * FROM TABLE1";
18
18
  //接続状況チェック
19
19
  if ( $Mysqli->connect_error ) {
20
20
  error_log( $Mysqli->connect_error );
21
21
  exit;
22
22
  }
23
-
24
23
  //入力された検索条件からSQl文を生成
25
24
  $where = [];
26
25
  if ( !empty( $params[ 'search_name' ] ) ) {
@@ -34,9 +33,9 @@
34
33
  }
35
34
  if ( $where ) {
36
35
  $whereSql = implode( " AND ", $where );
37
- $sql = "select * from $usertable where " . $whereSql;
36
+ $sql = "select * from TABLE1 where " . $whereSql;
38
37
  } else {
39
- $sql = "select * from $usertable";
38
+ $sql = "select * from TABLE1";
40
39
  }
41
40
  $query .= $where;
42
41
 
@@ -95,12 +94,14 @@
95
94
  $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
96
95
  $db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
97
96
  } catch ( PDOException $e ) {
98
- die( "接続エラー: {$e->getMessage()}" );
97
+ die( "接続エラー1: {$e->getMessage()}" );
99
98
  }
100
99
  return $db;
101
-
100
+ 　//ここまでは接続確認済です。
101
+
102
102
  //queryのデータ
103
+ try {
103
- $query = "SELECT * FROM $usertable WHERE search_name LIKE ? AND color LIKE ? AND category LIKE ?";
104
+ 　 $query = "SELECT * FROM TABLE1 WHERE search_name LIKE ? ESCAPE '!' AND color LIKE ? ESCAPE '!' AND category LIKE ? ESCAPE '!' ";
104
105
  $qPart = array_fill( 0, count( $userData ), "(?, ?, ?, ?, ?)" );
105
106
  $query .= implode( ",", $qPart );
106
107
 
@@ -112,17 +113,21 @@
112
113
  $UserDataSet = $db->prepare( $query );
113
114
  $i = 1;
114
115
  foreach ( $userData as $row ) { //bind the values one by one
116
+ $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $search_name ) . '%' ], PDO::PARAM_STR );
115
- $UserDataSet->bindValue( $i++, $row[ '%' . addcslashes( $search_name, '\_%' ) . '%' ], PDO::PARAM_STR );
117
+ $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $color ) . '%' ], PDO::PARAM_STR );
116
- $UserDataSet->bindValue( $i++, $row[ '%' . addcslashes( $color, '\_%' ) . '%' ], PDO::PARAM_STR );
117
- $UserDataSet->bindValue( $i++, $row[ '%' . addcslashes( $category, '\_%' ) . '%' ], PDO::PARAM_STR );
118
+ $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $category ) . '%' ], PDO::PARAM_STR );
118
119
  }
120
+ $UserDataSet->execute();
119
121
  //扱いやすい形に変える
120
122
  $result = [];
121
123
  while ( $row = $UserDataSet->fetch(PDO::FETCH_ASSOC)) {
122
124
  $result[] = $row;
123
125
  }
124
126
  return $result;
127
+
125
- $UserDataSet->execute();
128
+ } catch ( PDOException $e ) {
129
+ die( "接続エラー2: {$e->getMessage()}" );
130
+ }
126
131
  }
127
132
 
128
133
  //ーーー HTML側 ーーー