###やりたいこと
PHP、MySQLで検索機能を作成中です。

google等の検索窓のように、検索ワードをスペースで複数入力し検索するカタチを目指しています。

二つのテーブルを結合して、複数カラムのどちらかに含まれる複数の検索ワード（AND検索）を照会する検索機能を実装したいと考えています。

テーブル結合のうえ、複数ワードの検索機能は実装できたのですが、複数カラムに跨った検索が思うように実装できません。

例えば、下記のカラムの内容で「アカ　テスト」と検索したときに、nameがアカでmessageにテストが含まれるpost_id1~3を取得したいです。

初学者のため、質問の仕方等、的を得ていない部分もあると思いますが、教えていただけると嬉しいです。

カラムの中身
PostテーブルとMembersテーブルを結合しカラムpost_id,name,messageを取得しました。
mysql> SELECT post_id,name,message FROM Post LEFT OUTER JOIN Members ON Post.member_id = Members.member_id;
+---------+--------+---------------------------------------------------+
| post_id | name | message |
+---------+--------+---------------------------------------------------+
| 1 | アカ | テスト文１ |
| 2 | アカ | テスト文２ |
| 3 | アカ | テスト文３ |
| 4 | アカ | 文１ |
| 5 | ao | aoの文１ |
| 6 | pink | pinkのテスト　文　１ 555 333 桃 |
| 7 | pink | ピンク　test 2 文章 |
+---------+--------+---------------------------------------------------+

####考えている構造

カラムをORで繋ぎ、繋いだ物をANDで絞り込みすることで実装できると考えました

word1検索(name or message) AND word2検索(name or message)

###現状のコード
"name LIKE '%$word%' OR message LIKE '%$word%'"で複数カラムから検索し、' . implode(' and ', $keyword) . 'でANDで絞り込みをしているつもりなのですが、出て欲しい検索結果になりません。

どのように直せば、複数カラムのいずれかに含まれる複数のワードを絞り込む検索機能を実装できるのか、教えて欲しいです。

php
1if( empty($_GET['keyword']) ) {
2  $error_message = 'キーワードを入力してください。';
3} else {
4  $keyword = $_GET['keyword'];
5  //エスケープ処理
6  $keyword = htmlspecialchars( $keyword, ENT_QUOTES);
7  // 最初および最後から空白文字を取り除く
8  $keyword = trim($keyword); 
9  //全角スペースを半角スペースへ
10  $keyword = preg_replace('/　/', ' ', $keyword);
11  //連続する半角スペースを1つの半角スペースに変換する
12  $keyword = preg_replace('/\s+/', ' ', $keyword); 
13  // 文字列を分割して配列にする 
14  $keyword_split = preg_split('/(\s|　)+/', $keyword);
15}
16
17if(!isset($error_message)){
18
19  try{
20    // データベースに接続
21    $db = dbConnect();
22    
23    $sql2 = "SELECT * FROM Post
24    LEFT OUTER JOIN Members ON Post.member_id = Members.member_id "; 
25    if ($keyword != '') {
26      $keyword = array();
27      foreach ($keyword_split as $word) {
28      array_push($keyword, "name LIKE '%$word%' OR message LIKE '%$word%'");
29    }
30    $sql2 .= ' WHERE (' . implode(' and ', $keyword) . ')';
31    }
32    $sql2.=" ORDER BY post_date DESC"; 
33
34    $stmt = $db->prepare($sql2);
35    $stmt->execute(); 
36    echo "OK";
37    echo "<br>";
38  } catch(PDOException $e){
39    echo "失敗:" . $e->getMessage() . "\n";
40    exit();
41  }
42}
43

###開発環境
php7.3
MySQL8

m.ts10806

2021/04/21 08:00

まずSQLだけ組んでみては完成予想図もなしにいきなり組み込むのは無理です

yambejp

2021/04/21 08:30 編集

テーブル構造やサンプルを提示して具体的にどういったデータからどういった条件でどう表示したいか説明してください「aaa bbb ccc」で検索したときに「aaa」「bbb」「ccc」のすべてが、nameかmessageに含まれるものを抽出するということでしょうか？

yambejp

2021/04/21 08:28

'%$word%'のような処理はNGです prepareで指示するところは理解しているようなのできちんとプレースホルダーで処理してください

hmom

2021/04/21 09:24

m.ts10806さま最初にSQLから考えるといいんですね。教えていただきありがとうございます。 yambejpさまテーブル構造や、実際に抽出したいデータの例があるといいんですね！質問に追記します。ご指摘ありがとうございます。プレースホルダーについても調べてみます。

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

回答2件

まず最初に、

php
1$keyword = htmlspecialchars( $keyword, ENT_QUOTES);

データベースに格納する、あるいは検索する文字列を、
html表示上の不都合をなくすように加工するhtmlspecialchars()を使うのはやめましょう。
生のデータで扱わないといけませんし、
html出力する直前に加工して表示するよう、例えば

php
1function h($str)
2{
3    echo htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
4}

などとして使えばいいんです。（参照：PHP で h() を書くなら一緒に echo しよう。 - こせきの技術日記）

本題。
nameとmessageを相手に、入力があるかないかわからないけど対応させたいわけですね。
where句以降を作るのに例えばこんなとか。

php
1$phrases = [];
2$keywords = [];
3if (isset($_POST['name'])) {  // 値が入っていれば配列に追加する
4  $phrases[] = 'name like ?';
5  $keywords[] = '%' . $_POST['name'] . '%';
6}
7if (isset($_POST['message'])) {  // 値が入っていれば配列に追加する
8  $phrases[] = 'message like ?';
9  $keywords[] = '%' . $_POST['message'] . '%';
10}
11var_dump($phrases);  // 中身をチェックしてみてね
12var_dump($keywords);  // 中身をチェックしてみてね
13$where_phrase = implode(' and ', $phrases);
14var_dump($where_phrase);  // 中身をチェックしてみてね
15$sql2 .= 'where ' . $where_phrase;
16// （略）
17$stmt = $db->prepare($sql2);
18$stmt->execute($keywords);  // バインドパラメータ使う

みたいになる？机上のコードなのでテストよろしく。
var_dump()とかvar_export()で変数の中身を覗いてみるといいよ。

投稿2021/04/21 08:42

編集2021/04/22 09:10

退会済みユーザー

総合スコア0

hmom

2021/04/21 09:18

細かいコードまで、ありがとうございます！ htmlspecialchars()の使い方まで教えていただきありがとうございます。参考サイトも見て勉強します。教えていただいたコードを参考にもう一度コードを考えてみます！！

hmom

2021/04/22 08:31

昨日はご回答ありがとうございました。質問文が説明不足だったので、自分が作りたい形がきちんとお伝えできていなかったようです。入力する窓はgoogle等のように一つで、スペースで検索ワードを区切って複数ワードを検索できるようにしたかったのですが、可能であれば、今一度コード等教えていただけませんでしょうか。こちらの説明不足でお手数おかけして、申し訳ありません。お返事いただけたら嬉しいです。

hmom

2021/04/22 11:41

早速のご回答ありがとうございます！！変数の中身を確認してみます。

hmom

2021/04/23 07:26

昨日は回答ありがとうございました。教えていただいたようにvar_dump()で中身を確認したのですが、０になってしまいうまく確認できませんでした。色々調べた結果、concatでカラムを連結することで希望の結果をえられるようになりました。また、機会がありましたら、ご指導いただけたら嬉しいです。今回は、丁寧なご対応ありがとうございした。

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

ベストアンサー

nameとmessageを連結した生成列を指定してやればよいでしょう。
なお%・・・%のようなデータはインデックスにかからないので効率的な検索は困難です

sample

SQL
1create table tbl(id int primary key,name varchar(30),message varchar(30),name_message varchar(61) AS (concat(name,' ',message)));
2insert into tbl(id,name,message) values
3(1,'aaa bbb','ccc ddd'),
4(2,'aaa','bbb ccc'),
5(3,'aaa bbb ccc','ddd'),
6(4,'aaa','bbb'),
7(5,'aaa','ddd'),
8(6,'ddd','ccc');
9
10select * from tbl
11where 1
12and name_message regexp 'aaa'
13and name_message regexp 'bbb'
14and name_message regexp 'ccc'