MySQL、PHPで複数の項目から部分検索ができる検索システム

前提・実現したいこと

以前使っていたMySQL、PHPで複数の項目から検索システムをPDO、bindValueを使って更新しているのですが
実装すると上手くいきません。お力を貸していただけると幸いです。
列の項目には複数の文字が含まれるためLIKEの部分検索を取り入れています。

以前のPHP&HTML コード

//search.phpのコード
function getUserData( $params ) {
  //DBの接続情報
  include_once( 'database.php' );

  //DBコネクタを生成
  $Mysqli = new mysqli( $dsn, $usr, $passwd );
  $Mysqli->set_charset( 'utf8' );
  $query = "SELECT * FROM TABLE1";
  //接続状況チェック
  if ( $Mysqli->connect_error ) {
    error_log( $Mysqli->connect_error );
    exit;
  }
  //入力された検索条件からSQl文を生成
  $where = [];
  if ( !empty( $params[ 'search_name' ] ) ) {
    $where[] = "search_name like '%{$params['search_name']}%'";
  }
  if ( !empty( $params[ 'color' ] ) ) {
    $where[] = "color like '%{$params['color']}%'";
  }
  if ( !empty( $params[ 'category' ] ) ) {
    $where[] = "category like '%{$params['category']}%'";
  }
  if ( $where ) {
    $whereSql = implode( " AND ", $where );
    $sql = "select * from TABLE1 where " . $whereSql;
  } else {
    $sql = "select * from TABLE1";
  }
  $query .= $where;

  //SQL文を実行する
  $UserDataSet = $Mysqli->query( $sql );

  //扱いやすい形に変える
  $result = [];
  while ( $row = $UserDataSet->fetch_assoc() ) {
    $result[] = $row;
  }
  return $result;
}

 //ーーー HTML側 ーーー
<!doctype html>
<?php
include_once( 'search.php' );
$userData = getUserData( $_POST );
?>
<html>
<head>
</head>
<body>
　　//キーワード検索、チェックボックス、セレクションの３タイプで検索
　　<input name="search_name" value="<?php echo isset($_POST['search_name']) ? htmlspecialchars($_POST['search_name']) : '' ?>">
   <input type="checkbox" name="color" value="白" <?php echo isset($_POST['color']) && $_POST['color'] == '白' ? 'selected' : '' ?>><label for="color">白</label>//他省略
　　<select name="category">
   <option value="0" <?php echo empty($_POST['category']) ? 'selected' : '' ?>>分類A</option>//他省略
   </select>
   <button type="submit" name="search">検索</button>
　　
//上記項目以外のデータで結果表示
   <?php if(isset($userData)): ?>
   <?php foreach($userData as $row): ?>
     <img src="../<?php echo htmlspecialchars($row['img']); ?>">
     <?php echo htmlspecialchars($row['original_name']) ?>
   <?php endforeach; ?>
   <?php endif; ?>
</body>
</html>

改定後

//search.phpのコード
function getUserData() {

  include_once( 'database.php' );

   try {
    $db = new PDO( $dsn, $usr, $passwd );
    $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

    $q = ( string )filter_input( INPUT_GET, 'q' );
    $maxKeywords = -1;
    $keywords = preg_split( '/(?:\p{Z}|\p{Cc})++/u', $q, $maxKeywords, PREG_SPLIT_NO_EMPTY );

    if ( $keywords ) {
      foreach ( $keywords as $keyword ) {
        $holders[] = "((search_name LIKE ? ESCAPE '!') OR (color LIKE ? ESCAPE '!') OR (category LIKE ? ESCAPE '!'))";
        $values[] = $values[] = '%' . preg_replace( '/(?=[!_%])/', '!', $keyword ) . '%';
      }

      $sql = "SELECT * FROM TABLE1 WHERE (' . implode(' AND ', $holders) . ')";

      $UserDataSet = $bd->prepare( $sql );
      $UserDataSet->execute( $values );
      $userData = $UserDataSet->fetchAll( PDO::FETCH_ASSOC );
    }

    print '接続に成功しました';

  } catch ( PDOException $e ) {
    header( 'Content-Type: text/plain; charset=UTF-8', true, 500 );
    exit( '接続できませんでした' . $e->getMessage() );
  }

  function h( $str ) {
    return htmlspecialchars( $str, ENT_QUOTES, 'UTF-8' );
  }
}

 //ーーー HTML側 ーーー
<!doctype html>
<?php
include_once( 'search.php' );
$userData = getUserData( $_GET );
?>
<html>
<head>
</head>
<body>
　　//キーワード検索、チェックボックス、セレクションの３タイプで検索
　　<input name="search_name" value="<?php echo isset($search_name) ?= h($search_name) : '' ?>">
   <input type="checkbox" name="color" value="白" <?php echo isset($color) && $color == '白' ? 'selected' : '' ?>><label for="color">白</label>//他省略
　　<select name="category">
   <option value="0" <?php echo empty($category) ? 'selected' : '' ?>>分類A</option>//他省略
   </select>
   <button type="submit" name="search">検索</button>
　　
　　//$holders[]に指定した列以外のデータで結果表示
   <?php if (isset($err)) : ?>
      <p><?= h($err->getMessage()); ?></p>
    <?php endif; ?>
    <?php if (0 < count($userData)): ?>
      <?php foreach ($userData as $row) : ?>
        <div class="box_warp">
          <div><img class="circular" height="80" src="../<?= h($row['img']); ?>"></div>
          <div class="break-word"><?= h($row['original_name']); ?></div>
      </div>
      <?php endforeach; ?>
    <?php endif; ?>
</body>
</html>

tanat

2019/11/24 05:44

「上手くいかない」の詳細が記述されていないので、適切な回答を得るのは難しいと思われます。https://teratail.com/help/question-tips を参考にして、何がどう上手くいかないのかが伝わるように情報を追記してください。

TaraIkura

2019/11/24 07:17

早速に有難うございます。上手くいかない部分ですが、改定後の上部、データベースの読み込みはできているのですが、 ”//queryのデータ”以下が実装できなく、検索結果が１と表記されてしまいます。アドバイスお待ちしております。

hentaiman

2019/11/24 08:32

returnの後に何か処理が書いてあるのが気になる

tanat

2019/11/24 11:34

> TaraIkuraさん質問は編集できるので、こちらでは無く質問に追記をお願いします。

m.ts10806

2019/11/24 21:46

$usertable ↑これどこで定義されてるんでしょ。

TaraIkura

2019/11/25 01:34 編集

ご回答有難うございます。 returnの位置を修正し、database.phpに記載していた$usertableをそのままTABLE1と記載し直しました。

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

回答4件

ベストアンサー

追記・修正依頼欄

早速に有難うございます。上手くいかない部分ですが、改定後の上部、データベースの読み込みはできているのですが、 ”//queryのデータ”以下が実装できなく、検索結果が１と表記されてしまいます。
アドバイスお待ちしております。

と言うのがまだよくわかりませんが、

addcslashesについてaddslashesと誤解していたので削除

そもそも論として

PHP
1
2  //扱いやすい形に変える
3  $result = [];
4  while ( $row = $UserDataSet->fetch(PDO::FETCH_ASSOC)) {
5    $result[] = $row;
6  }
7  return $result;
8  $UserDataSet->execute();

で、
SQLが実行されるのは
$UserDataSet->execute();
なのに、returnより後に記述されているのでSELECT文が実行されていません。

まずはPHPマニュアルを参考にして、最低限の機能とコードでPDOの挙動について把握されることをお勧めします。

PHP
1
2$qPart = array_fill( 0, count( $userData ), "(?, ?, ?, ?, ?)" );
3

↑$userDataが未定義なので

foreach ( $userData as $row ) { //bind the values one by one

↑foreachの中の処理が多分実行されていない

PHP
1$stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $search_name ) . '%' ], PDO::PARAM_STR );

↑$stmtも未定義なので、foreachの処理が実行されたらエラーになるはず

投稿2019/11/24 11:42

編集2019/11/25 02:06

tanat

総合スコア18713

TaraIkura

2019/11/25 01:20

ご回答有難うございます。 $UserDataSet->execute(); の位置を変更してみました。また、addcslashesは使用してはいけません。とあったのでpreg_replaceに変更してみました。参照> https://qiita.com/mpyw/items/b00b72c5c95aac573b71#like%E6%A4%9C%E7%B4%A2 しかし、接続状況は変わらず、動きません。。

tanat

2019/11/25 02:01

すみません、addcslashesについては誤解だったので回答から削除しました。他の箇所もおかしなところがあるので追記しました。（コードの転記ミス？も含まれている気がしますが）が、まずは最小限のコード（結果が出るとわかっているフィールド＋固定値でただのLIKE検索とか）で動くことを確認されることをお勧めします。その上で、一か所ずつ修正していって要求される仕様に近づけてみて下さい。（現状ではデバッグの方法そのものが分かっていない様に見えますので、何がどう動いていないかをご自身で確認することも難しい状態に見えます。その状態では適切な回答は難しいです）

TaraIkura

2019/11/26 15:52

ご回答とご指摘有難うございます。確認に時間がかかり申し訳ありません。その後、アドバイスをいただいたように最小限のコードで色々と試してみました。結果、$dbのreturnを削除し、可変長プレースホルダに書き換えをし、HTMLのhtmlspecialchars部分をphp内に記述してみたところ、tryとcatchにて「接続に成功しました」と出るところまで実行の確認ができました。しかし、実際のデータがHTML上で表示されなく、困っております。質問内を更新してみましたので、ご教授いただけると幸いです。参照＞https://qiita.com/mpyw/items/b00b72c5c95aac573b71#%E5%8F%AF%E5%A4%89%E9%95%B7%E3%83%97%E3%83%AC%E3%83%BC%E3%82%B9%E3%83%9B%E3%83%AB%E3%83%80%E3%81%AE%E5%B0%8E%E5%85%A5

tanat

2019/11/27 00:03

うーんと、もう一段階最小限の、 SQL自体を固定文字列にして(プレースホルダを使わない)にして結果を表示させるというのは試されましたか? 今のコードは置いておいて、1から書いてみるのが確実です。質の高い具体的なコードを参考にされること自体はとても良いのですが、まずは PHP デバッグ方法とかで検索してみて、問題箇所を効率的に特定する方法を調べてみてください。今回のケースであれば、 $userData = getUserData( $_GET ); var_dump($userData); とすると、getUserDataが結果を返せていない事がわかるはずです。 php 自作関数あたりで調べると、returnの役割が把握できると思います。

TaraIkura

2019/12/03 09:10

ご丁寧にご回答有難うございました。ご指摘いただいた部分、調べながら１から書き直してみています。もう少し時間がかかりそうですが、頑張ってみます。

tanat

2019/12/03 09:36

フィードバックありがとうございます。 1から書いてみると、分からない部分の把握もしやすくなるので、そこに絞って質問されるとそのものズバリな回答も得られやすいかと思います。

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

if(!empty($AAAA)) {
$AAAA = addslashes($AAAA);
$where .= "△△△ LIKE '%$AAAA%' && ";
}

if(!empty($BBBB)) {
$BBBB = addslashes($BBBB);
$where .= "□□□ >1 && ";
}

if(!empty($where)) {
$where = substr($where, 0, -4);//末尾の&&を取り除く
$where = " WHERE " . $where;
$where.=" ORDER BY timestamp DESC";
}

$query.=$where;

私はあいまい検索で、上の様にWhere句をどんどん&&で繋いでいき最後に末尾の&&を取り除いています。20項目位でのあいまい検索の作成です。温泉とかの検索で、検索項目が例えば「アルカリ温泉、東北、営業時間は0時まで、海が見える」などと言う検索をしたりしたい時にこういうwhereのつなぎ方でできます。後は、別記入のsql文にwhereを付け足すだけです。

投稿2020/03/06 16:29

tukino-hikari

総合スコア26

TaraIkura

2020/03/08 13:36

有難うござます！ご教授いただいた方法で試してみます。

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

とりあえず、動作確認できるだけのデータや情報がないので、気になったところだけ

php
1<?php
2
3function getUserData()
4{
5
6    //include_once('database.php');
7
8    $dsn = 'mysql:host=localhost;database=database:charset=utf8';
9    $user = 'root';
10    $password = 'password';
11
12    try {
13        $db = new PDO($dsn, $user, $password);
14        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
15        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
16
17        $q = filter_input(INPUT_GET, 'q');
18
19        $maxKeywords = -1;
20        $keywords = preg_split('/(?:\p{Z}|\p{Cc})++/u', $q, $maxKeywords, PREG_SPLIT_NO_EMPTY);
21
22        if (count($keywords) > 0) {
23            
24            $values = []; // add
25            
26            foreach ($keywords as $keyword) {
27                $holders[] = "((search_name LIKE ? ESCAPE '!') OR (color LIKE ? ESCAPE '!') OR (category LIKE ? ESCAPE '!'))";
28                //$values[] = $values[] = '%' . preg_replace( '/(?=[!_%])/', '!', $keyword ) . '%';
29                $values[] = '%' . preg_replace('/(?=[!_%])/', '!', $keyword) . '%';
30            }
31
32            //$sql = "SELECT * FROM TABLE1 WHERE (' . implode(' AND ', $holders) . ')";
33            $sql = 'SELECT * FROM TABLE1 WHERE (' . implode(' AND ', $holders) . ')';
34
35            //$UserDataSet = $bd->prepare( $sql );
36            $UserDataSet = $db->prepare($sql);
37            $UserDataSet->execute($values);
38            //$userData = $UserDataSet->fetchAll(PDO::FETCH_ASSOC);
39            return $UserDataSet->fetchAll(PDO::FETCH_ASSOC);
40        }
41
42        //print '接続に成功しました';
43
44    } catch (PDOException $e) {
45        header('Content-Type: text/plain; charset=UTF-8', true, 500);
46        exit('接続できませんでした' . $e->getMessage());
47    }
48
49//    function h($str)
50//    {
51//        return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
52//    }
53}
54
55function h($str)
56{
57    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
58}