FullText Search 2

[WilliamMute007]

Hi all,

I am incredibly lost here. I am trying to implement a fulltext search on my mysql table. I think I have succefully done it but every time I query the table it returns unexpected result. i.e My table has the following data (Shiny Shoes Ltd). When I search for shoes, it returns no result but when I search for Shiny it gives result. so its very tricky. I just wanted it to return result if Shiney or Shoes or Ltd is typed in. Please can anybody help?

I have also included all my code to help diagnose the problem below.

Create table businesses(
  id integer not null primary key auto_increment,
  Company varchar(100),
  ContactName varchar(100),
  ServicesSummary varchar(50),
  ServicesDetailed varchar(150),
  fulltext(Company, ServicesDetailed)
) engine = MyISAM;

My Query is as followed

$sql = "Select * from `businesses3` where match(Company,ServicesDetailed) against ('".$keyword." with query expansion')";

Thank you so much for you help.

 

[Kozusnik]

If it were me, I'd just simplify the query.

$sql = "Select * from businesses where Company LIKE '%$keyword%' OR ServicesDetailed LIKE '%$keyword%'";

Mark

 

[jpadie]

I think your query is incorrect. the quotes are in the wrong place. try this instead

$sql = "Select * from `businesses3` WHERE match(Company,ServicesDetailed) AGAINST ('$keyword' with query expansion)";

if your returned results are not as expected, try re-indexing the table and then run the query again.

 

[WilliamMute007]

Thanks Kozusnik & Justin for your suggestions am not at home now until later tonight so I will try it and let you know. Hopefully these my just do it.

 

[jpadie]

@Kozusnik

the full text search within mysql does a lot more than just a LIKE comparison. particularly if one includes the "with query expansion" qualifier. typically a full text search will first reduce all words down to their root forms (i.e. books -> book; persuaded -> persuade) and then perform the search on those root forms (minus stop words etc).

the 'with query expansion qualifier' performs a second pass as well, taking cogent words from the first pass results and using them as the input variables for the second path.

 

[Foamcow]

Out of interest, would you also use the IN BOOLEAN MODE qualifier with 'WITH QUERY EXPANSION' and what would be the syntax?

For instance, would this work?

MATCH (col1, col2) AGAINST('$terms' WITH QUERY EXPANSION IN BOOLEAN MODE)
[code]

--
[COLOR=red][b]Tek-Tips Forums is Member Supported. [url=http://www.tek-tips.com/supportus.cfm]Click Here[/url] to donate[/b][/color]

<honk>*:[COLOR=red]O[/color])</honk>

[i]Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril. [/i]

 

[jpadie]

@Foamcow

I fear not. although i have not tried it the manual is clear that the search modifiers are alternatives.

It feels like a bad idea too. kind of like combining fuzzy logic with something precise; or buttering a cat's back before dropping it out of the window. the world might end or a perpetual motion machine might spring into existence.

what i'd like to see is an easy way to do 'within' or 'near' queries like we used to have on lexis all those years ago. ie.

select * when textfield has "negligence" within 5 words of "liability"

or somesuch neater syntax. I know we can create these constructs with regex but that's not for ordinary users and it's not straightforward to build an engine that will create regex on the fly from a search form.

 

[WilliamMute007]

Thank you all for your contribution. the code

$sql = "Select * from `businesses3` WHERE match(Company,ServicesDetailed) AGAINST ('$keyword' with query expansion)";

works! there's only one flaw tho, it picks up only two characters within a given column. i.e James Osbourne William it will only pick up Osbourne William if I typed either of the keyword. So in that example, if I typed James it returns no result. Not sure why that is.

Thanks for your help thus far, it is greatly appreciated.

 

[jpadie]

fulltext searching is slightly arcane. but i'm surprised that James should not be recognised.

suggest that you delete the index and then recreate it (this is often quicker than trying to repair the table).

 

[WilliamMute007]

Ok Justin... thanks for you help as usual. You are always amazing. Thank You.

 

[Foamcow]

Fulltext searches by default only work on strings of 3 characters or more.

This can be over-ridden though, so ensure you have not got a longer minimum string length set.

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.