yyy4_mu
@yyy4_mu

Are you sure you want to delete the question?

If your question is resolved, you may close it.

Leaving a resolved question undeleted may help others!

We hope you find it useful!

スペースによる絞り込み検索(複数カラム)

MySQLでの検索に関する質問です
検索フォームに入力された値を取り出すSQL文が分からず困っています。
実行可能かどうか、
また実行可能であれば実行方法をご教示願います。

やりたいこと

検索フォームで入力された値(検索したい単語をスペースで複数指定可)を
2つのテーブルのカラムから検索

(例)
検索ワード:やまだ 大阪
期待する結果:やまだ商店大阪 〒123-456 大阪府吹田市・・・

環境

PHP 7.4
MySql

前提

テーブル定義

得意先マスター情報
テーブル名 master_customer

customer_id                 INT UNSIGNED NOT NULL AUTO_INCREMENT        COMMENT '得意先ID',
customer_cd                 CHAR(8) NOT NULL                            COMMENT '得意先コード',
customer_name1              VARCHAR(40) NOT NULL                        COMMENT '得意先名1',
address_id                  INT UNSIGNED NOT NULL DEFAULT 0             COMMENT 'アドレスID'
delete_flag                 TINYINT(1) NOT NULL DEFAULT 0               COMMENT '削除フラグ',
create_at                   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
updated_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP COMMENT '更新日時',

PRIMARY KEY( customer_id ),
UNIQUE KEY( customer_cd )

データ
customer_id  | customer_cd | customer_name1 | address_id
1       | 00000001   | たなか商店東京   |1
2       | 00000002   | たなか商店大阪   |2
3            | 00000003  | やまだ商店東京   |3
4            | 00000004  | やまだ商店大阪  |4
・
・
・
アドレスマスター情報
テーブル名 master_address
address_id            INT UNSIGNED NOT NULL AUTO_INCREMENT        COMMENT 'アドレスID',
zip_code_1            CHAR(3) DEFAULT NULL                        COMMENT '郵便番号(前3桁)',
zip_code_2            CHAR(4) DEFAULT NULL                        COMMENT '郵便番号(後4桁)',
pref_id               TINYINT UNSIGNED NOT NULL DEFAULT 0         COMMENT '都道府県ID',
address_1             VARCHAR(64) DEFAULT NULL                    COMMENT '住所',
address_2             VARCHAR(32) DEFAULT NULL                    COMMENT '建物名・部屋番号',
create_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '登録日時',
updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',

PRIMARY KEY( address_id )

データ
address_id |zip_code_1 |zip_code_2 | pref_id |    address_1     | address_2 |
1          |123        |4567       | 10      | 東京都大田区・・・|オオタビル・・・|
2          |456        |7891       | 30      | 大阪府大阪市・・・|ウメダビル・・・|    
3          |111        |2222       | 10      | 東京都中野区・・・|中野ビル・・・ |
4          |222        |3333       | 30      | 大阪府吹田市・・・|              |
・
・
・

※pref_idはPHP側に定数で指定されてあります
今回は東京を10 大阪を30とする

検索ワード
➀「やまだ 大阪」と入力された場合
「やまだ」「大阪」に分ける処理
また大阪をpref_id「30」にする処理

➁「〒123-4567」や「〒123-4567」と入力された場合
全角を半角にする処理・ハイフン・〒を消す処理

上記はPHP側で行っています

自分で試したこと

※実際には条件を変数に入れてwhere句をforeachで作成しています。

検索キーワード例:やまだ 大阪 〒222-3333

検証1

SELECT
    *
FROM
    master_customer as customer
LEFT JOIN
    master_address as address
ON
    customer.address_id = address.address_id
WHERE
	customer_name1 LIKE '%大阪%' OR '%やまだ%' OR '%〒222-3333%'
AND
	CONCAT( zip_code_1, zip_code_2 ) LIKE '2223333'
AND
	pref_id LIKE 30
AND
	address_1 LIKE '%大阪%' OR '%やまだ%' OR '%〒222-3333%'
AND
    address_2 LIKE '%大阪%' OR '%やまだ%' OR '%〒222-3333%'

→全てのやまだ・大阪府が住所のものすべてヒットしてしまうので×
→会社名「大阪」でも都道府県が「大阪府」とは限らないので×

検証2

WHERE
   CONCAT(customer_name1, pref_id, zip_code_1, zip_code_2, adddress_1, address2) LIKE '%やまだ%' AND '%大阪%' AND '30'

→会社名が「大阪」でも都道府県が「大阪府とは限らない」ので×

検証3

住所のみをsqlで検索し、php側で会社名に合致しているか調べる
→会社名が「大阪」でも都道府県が「大阪府とは限らない」ので×

その他

調べてみたところSQL全文検索というものがある

→テーブルが分かれていると検索できない?

一文字のみの検索だとヒットしない
とのことだった為今回は使用を見送りました。

0

1Answer

期待を守るなら、画面を再設計して、4つの入力枠(住所、得意先名、郵便前半、郵便後半)します。住所と得意先名を曖昧検索します。さらに、1つの入力枠に、スペース区切りでor関係の複雑なあいまい検索します。こうすれば、
得意先名に、「やまだ 大阪」を入力されたら、customer_name1 LIKE '%大阪%' OR '%やまだ%' の条件しか作られないで、住所のSQL文が混入されないです。

0Like

Comments

  1. @yyy4_mu

    Questioner

    ご回答ありがとうございます。
    画面再設計することにします。

Your answer might help someone💌