0

I'm coding a search function using full-text-search and I want result to be shown only if the words typed are contained in table.

$search_value = $db->mysqli->real_escape_string("lana born to die sdkjfhsdkjfhdsjkfhdsjkfhd");

$query = "select * from `music` where match(`title`, `artist`) against ('$search_value')";



artist               title
----------------------------------
lana del rey    west coast
lana del rey    born to die
lana del rey    summertime sadness
lana del rey    blue jeans

For ex. if I search: lana born to die sdkjfhsdkjfhdsjkfhdsjkfhd it shows me lana del rey born to die as a result. This should not happen. I want it to show results only if you type for ex. lana, lana del rey born and so on.

Any ideas.

M1X
  • 4,971
  • 10
  • 61
  • 123
  • Why shouldn't that happen? It search term contains valid text, it just doesn't have an exact match. – Jay Blanchard Sep 24 '14 at 13:21
  • 1
    It doesn't have to be exact match but if a particualr word is not containded in that result, the result should not be shown. @JayBlanchard – M1X Sep 24 '14 at 13:23
  • This may help - http://stackoverflow.com/questions/16130526/sql-server-full-text-search-for-exact-match-with-fallback – Jay Blanchard Sep 24 '14 at 13:26

2 Answers2

3

You could transform the query to have a boolean full-text search. This is done by adding a + or - in front of every word within the search query.

In this case, we add a + as this stands as an AND whereas a - stands as a NOT.

As such if you want to have it such that every word becomes required you could add the following code:

PHP:

$search_value = $db->mysqli->real_escape_string("lana born to die sdkjfhsdkjfhdsjkfhdsjkfhd");
$search_value = str_replace(" ", " +", $search_value);
$query = "select * from `music` where match(`title`, `artist`) against ('$search_value' IN BOOLEAN MODE)";
Cedric Le Varlet
  • 99
  • 1
  • 3
0

You need to correct your query :

$query = "select * from `music` where `title` LIKE '%$search_value%'  
          OR `artist` LIKE '%$search_value%'";

I hope that will be help for you.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Hardik Solanki
  • 3,153
  • 1
  • 17
  • 28
  • 1
    well searching with `LIKE` is not even close to `full-text-search` as performance keep that in mind. With MyISAM engine it could couse some table locks and more – Santa's helper Sep 24 '14 at 13:36
  • This doesn't solve the OP's issue as the query results that he doesn't want would still be returned. – Jay Blanchard Sep 24 '14 at 13:37