0

I have a table A with two column name, description_en

Data

name 
----------------
|Hero 1|
----------------
|Hero 2|
----------------
|Hero 3|
----------------
|Hero 4|

description_en
----------------
|Hero 1, if this card|
----------------
|If this card Hero 2| 
----------------
|Hero 3 is special|
----------------
|is special card hero 4|

I use php html to make a search box, my input $_GET['search'] is: hero;if this card;is special

$WHERE = "Select * from A where";
$search = "hero;if this card;is special"; // $_GET['search']
$searchs = explode(';', $search);
 foreach ($searchs as $valuesearch) {
    $phrase= '"'.$valuesearch.'"';
    $text_search .= " MATCH(name, description_en) AGAINST ('$phrase' IN BOOLEAN MODE)" ;
}
$search = isset($text_search) ? $text_search : NULL ;
$WHERE .=  isset($search) ? $search : NULL;
echo $WHERE;

Query from echo:

Select * 
from A 
where MATCH(name, description_en) 
      AGAINST ('"hero"' IN BOOLEAN MODE) 
  AND MATCH(name, description_en) 
      AGAINST ('"if this card"' IN BOOLEAN MODE) 
  AND MATCH(name, description_en) 
      AGAINST ('"is special"' IN BOOLEAN MODE)

Result above have full 4 rows so the query have no problem, then I try with other input: ero;if this;is specia

If miss somewords in first or last like "is special" to "is specia" or "if this card" to "if this" or "hero" to "ero", then query below have no result, it should be full 4 rows too, what wrong guys?

Select * 
from A 
where MATCH(name, description_en) 
      AGAINST ('"ero"' IN BOOLEAN MODE) 
   AND MATCH(name, description_en) 
      AGAINST ('"if this"' IN BOOLEAN MODE) 
   AND MATCH(name, description_en) 
      AGAINST ('"is specia"' IN BOOLEAN MODE)
Arun
  • 1,069
  • 2
  • 12
learnincode
  • 1
  • 2
  • Words in double quotes must match exact phrase or word. To match partial words, you have to remove double quotes and add `*` before and after word or phrase. Something like this: `AGAINST ('*ero*' IN BOOLEAN MODE)`. Please see this: https://stackoverflow.com/questions/28278150/mysql-efficient-search-with-partial-word-match-and-relevancy-score-fulltext and read mysql documentation to find out more about operators: https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html – Robert Dec 02 '21 at 08:16

0 Answers0