PHPでレコードの検索

Question

質問を訂正しました。

PHPとMySQLを使って以下のような機能を実装したいです。

難易度、科目名、講師名の３つのカラムを持つテーブルがあり、いくつかのレコードが格納されています。

ここで、このテーブルからレコードを検索して、一致するものを抽出したいです。

ちなみに、「難易度」は<select>タグでプルダウンメニューとして設定し、
(「易」「普通」「難」から選ぶ)
「科目名」と「講師名」は<input type="text">で設定します。

検索の条件は

 検索者は「難易度」の選択のみが必須で、「科目名」と「講師名」の入力は任意。

できなくて困っていることは

例えば「数学」として格納されているデータを抽出するのに、
「数」では引っかかってくれるのですが、「数学者」では引っかからないことです。

ポストされたデータは、難易度が$difficulty、科目名が$subject、講師名が$teacherに代入してあります。。テーブル名はtableです。

汚いコードですが書いてみたものです。

    if($difficulty=='易'){

    $sql = "SELECT * FROM table
     WHERE (difficulty='易') AND (subject LIKE(:subject)) AND (teacher 
     LIKE(:teacher))";

    $stm = $pdo->prepare($sql);
    $stm->bindValue(':subject', "%{$subject}%", PDO::PARAM_STR);
    $stm->bindValue(':teacher', "%{$teacher}%", PDO::PARAM_STR);

  }else if($difficulty=='普通'){
  //上と同じ処理
   }else{
 //上と同じ処理
   }

WHEREをどのようにすれば、入力文字が実際のデータの文字列を超えていても抽出できるのでしょうか？
また選択必須の難易度$difficultyは条件分岐しなくてもできるでしょうか？

複雑で面倒な質問で本当にすみません。もしわかる方がいれば知恵を拝借させてください。

よろしくお願いします。

以下追記です。________________________________________________________________________

$stm->bindValue(':subject1', "%{$subject}%", PDO::PARAM_STR);
$stm->bindValue(':subject2', "{$subject}%", PDO::PARAM_STR);

のように、subject1とsubject2をORではできないのでしょうか？(試しましたが動作しませんでした。)

"{$subject}%"とすれば格納されている文字列に余計なものが続いて入力されていても抽出されると思ったのですが、、

Answer 1

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

$options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        , PDO::ATTR_EMULATE_PREPARES => true
];
$pdo = new PDO($dsn, $username, $password, $options);

$sql = "SELECT * FROM table WHERE 1 ";

$data = [];

if(!is_null($difficulty)){
  $sql.="and difficulty = :difficulty ";
    $data[':difficulty'] = $difficulty;
}

if(!is_null($subject)){
  $sql.="and (subject like CONCAT('%', :subject, '%') OR CONCAT('%', subject, '%') like :subject)  ";
  $data[':subject'] = $subject;
}

if(!is_null($teacher)){
  $sql.="and (teacher like CONCAT('%', :teacher, '%') OR CONCAT('%', teacher, '%') like :teacher)  ";
  $data[':teacher'] = $teacher;
}

$stmt = $pdo->prepare($sql);
$stmt->execute($data);

Comments

[gsuisk]

Invalid parameter number: parameter was not defined

というエラーがでてしまいます...

[退会済みユーザー]

少しは調べましょうね。

[gsuisk]

すみません、、参考になりました。ありがとうございました。

Answer 2

流れ的にはこうします
※追記：スペースの抜けだけ修正しました

$sql = "SELECT * FROM table WHERE 1 ";
$data=[];
if(!is_null($difficulty)){
  $sql.="and difficulty=? ";
  $data[]=$difficulty;
}
if(!is_null($subject)){
  $sql.="and subject like ? ";
  $data[]="%".$subject."%";
}
if(!is_null($teacher)){
  $sql.="and teacher like ? ";
  $data[]="%".$teacher."%";
}
$stmt = $pdo->prepare($sql);
$stmt->execute($data);

ただし前方後方一致はパフォーマンスがでないので注意が必要

修正版

$sql = "SELECT * FROM table WHERE 1 ";
$data=[];
if(!is_null($difficulty)){
  $sql.="and difficulty=? ";
  $data[]=$difficulty;
}
if(!is_null($subject)){
  $sql.="and ( 0 ";
  $sql.="or subject like concat('%',?,'%') ";
  $data[]=$subject;
  $sql.="or ? like concat('%',subject,'%') ";
  $data[]=$subject;
  $sql.=") ";
}
if(!is_null($teacher)){
  $sql.="and ( 0 ";
  $sql.="or teacher like concat('%',?,'%') ";
  $data[]=$teacher;
  $sql.="or ? like concat('%',teacher,'%') ";
  $data[]=$teacher;
  $sql.=") ";
}
$stmt = $pdo->prepare($sql);
$stmt->execute($data);

Comments

[gsuisk]

SQLSTATE[42000]: Syntax error or access violation: 1064
...
use near 'table WHERE 1 and difficulty=?and subject like ?and teacher like ?' at line 1

こんなエラーが出てしまいます。

[yambejp]

とりあえずスペースの抜けがあったので追記しました。
テーブル名はtableになっていますが、正しいものを利用してください
また、もしテーブル名に「table」という予約語を使っている場合は
from `table`のようにバックスラッシュでかこってください

[gsuisk]

ありがとうございます。

訂正されたもので試してみたのですが、やはり「数学」を抽出するのに「数学者」や「数学１」など余計な文字列を入力すると抽出してくれません、、、

[gsuisk]

実現したいことのイメージを質問に追記したのでよければ読んでいただきたいです。

[yambejp]

すみません、発想が逆だったみたいです。
「数」から「数学」でも、「数学者」から「数学」でも
どちらもヒットするような内容を追記しておきました

[gsuisk]

ありがとうございます。
何度も本当に申し訳ないのですが、、

use near 'subject like concat('%',?,'%') or concat('%',subject,'%') like ? ) and ( 0or teacher' at line 1

というエラーが出てしまいます...

[yambejp]

こちらこそナン度も同じミスで申し訳ないです
0の後ろに半角スペースが抜けてたので足しときました
再チャレンジお願いします

[gsuisk]

エラーはでないのですが、やはり「数学者」や「数学1」等と入力すると引っかかりません...

他の回答者さんのでもうまくいかなくて...SQL文でないところで不具合が起こっているのですかね...？

私の知識不足で他の不具合を発見できずにいるのかもしれません(汗)

[yambejp]

すみません、完全に記載ミスがありました
修正しました

[gsuisk]

できました！！助かります！何度も親切に本当にありがとうございました！

Answer 3

MySQLでしか使えないと思いますが、下記のようにCONCAT（文字列結合）SQL文を使って書くことができます。しかし、決まった仕様なのであればテキストボックスではなく、セレクトボックスのような選択肢にすればこのようなことをしなくても良いと思います。

• subject LIKE '%数%' OR
• '数学者' LIKE '%数学(subject)%'

SELECT * FROM table
WHERE (
  difficulty = :difficulty AND 
  subject LIKE CONCAT('%',:subject,'%') AND 
  teacher LIKE CONCAT('%',:teacher,'%')
) OR (
  difficulty = :difficulty AND 
  :subject LIKE CONCAT('%',subject,'%') AND 
  :teacher LIKE CONCAT('%',teacher,'%')
)

PHPのプレースホルダのバインドは、下記のようにします。テキストボックスが空の時は、条件分岐でSQL文とプレースホルダのバインドを分けて書きます。

    $stm->bindValue(':difficulty', $difficulty, PDO::PARAM_STR);
    $stm->bindValue(':subject',    $subject,    PDO::PARAM_STR);
    $stm->bindValue(':teacher',    $teacher,    PDO::PARAM_STR);

きちんとやるには、日本語の形態要素解析か単語辞書検索のようなロジックが必要になります。

Comments

[yambejp]

LIKE '%:subject%'は、よくないかもしれませんね
LIKE CONCAT('%',:subject,'%') とするか
bidValue側で先に’％’をつけたほうがよいでしょう

[Tomak]

＞LIKE '%:subject%'は、よくないかもしれませんね
＞LIKE CONCAT('%',:subject,'%') とするか
＞bidValue側で先に’％’をつけたほうがよいでしょう

確かにそうですね。。。(汗;;)
回答を修正しておきます。

[gsuisk]

エラーはでないのですが、何を入力しても検索結果なしになってしまいます、、、

[gsuisk]

解決しました。ありがとうございました。

[Tomak]

修正が遅くなりました。。。解決したということで良かったです。