• From: Herts - UK
  • Registered: 2005-09-11
  • Posts: 842

Topic: MySQL problem (Full text searching)

Ok... I have this statement...

SELECT * FROM cache WHERE MATCH(keywords) AGAINST ('".$searchString."*' IN BOOLEAN MODE) ORDER BY ref ASC

I have two questions...

1) how do I ORDER BY relevance?

2) how do I multi search... explained below....?
Basically once they have entered one word they get the results from above. Now what I need... If the enter a second word, I want that searching only the results from above.
Make sense?

Me: Blog | Twitter | TwitPic Exporter

StevenBullen's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: MySQL problem (Full text searching)

1.

select *, MATCH(keywords) AGAINST ('".$searchString."*' IN BOOLEAN MODE) AS relevance from tablename ORDER BY relevance

2. The way I thought of first would be to get a list of IDs from the original query and use those in the second query.
So the first query would look like

select *, MATCH(keywords) AGAINST ('".$searchString."*' IN BOOLEAN MODE) AS relevance from tablename ORDER BY relevance

And the second would look like:

select *, MATCH(keywords) AGAINST ('".$searchString."*' IN BOOLEAN MODE) AS relevance from tablename WHERE id IN ($id_nums) ORDER BY relevance

where $id_nums is a comma-seperated list of ID numbers from the previous query

3 Reply by StevenBullen (edited by StevenBullen 2006-10-03 17:10)

  • From: Herts - UK
  • Registered: 2005-09-11
  • Posts: 842

Re: MySQL problem (Full text searching)

Smartys wrote:

1.

select *, MATCH(keywords) AGAINST ('".$searchString."*' IN BOOLEAN MODE) AS relevance from tablename ORDER BY relevance

Cheers smartys...It sort of works

But now its returning null records amongst my normal results, where as the orignal statement did not. sad

Me: Blog | Twitter | TwitPic Exporter

StevenBullen's Website

  • Registered: 2006-06-12
  • Posts: 22

Re: MySQL problem (Full text searching)

http://lists.mysql.com/mysql/183836

  • From: Herts - UK
  • Registered: 2005-09-11
  • Posts: 842

Re: MySQL problem (Full text searching)

Cheers. Will have a go at that this evening. big_smile

Me: Blog | Twitter | TwitPic Exporter

StevenBullen's Website