Re: Re: Search function query

[Ron Piggott <ron.piggott@xxxxxxxxxxxxxxxxxx>]

I just made a FULLTEXT index on the 3 fields I want to search
(product_name, product_description and keywords) and then created the
mySQL query below with a search for the word "postcards".  It didn't
have any results though.  I don't understand why because the word
'postcard' exists in the product_name field a few times.  Any idea what
I have done wrong / or why this is happening?  Ron

SELECT MATCH (
`product_name` , `product_description` , `keywords` 
)
AGAINST (
'postcard'
) AS Relevance
FROM store_product_profile
WHERE MATCH (
`product_name` , `product_description` , `keywords` 
)
AGAINST (
'postcard'
IN BOOLEAN
MODE 
)
HAVING Relevance > 0.2
ORDER BY Relevance DESC

-----Original Message-----
From: Kesavan Rengarajan <k7@xxxxxxxx>
To: ron.piggott@xxxxxxxxxxxxxxxxxx <ron.piggott@xxxxxxxxxxxxxxxxxx>
Cc: PHP DB <php-db@xxxxxxxxxxxxx>
Subject: Re:  Re: Search function query
Date: Mon, 2 Nov 2009 03:21:56 +1100


Please have a look at this page: http://imysql.cn/docs/MySQL_51_en/ch12s07.html 
  .
Hope that is helpful.

Sent from my iPhone

On 02/11/2009, at 1:45 AM, Ron Piggott  
<ron.piggott@xxxxxxxxxxxxxxxxxx> wrote:

> I have found the concept I am looking for on the mySQL web site:
>
> SELECT MATCH('Content') AGAINST ('keyword1 keyword2') as Relevance  
> FROM
> table WHERE MATCH ('Content') AGAINST('+keyword1 +keyword2' IN BOOLEAN
> MODE) HAVING Relevance > 0.2 ORDER BY Relevance DESC
>
> I don't know how to apply this to the 3 fields in the data I want to
> search with the users search string ( product_name ,
> product_description , keywords )  The search string in the form is
> "search_string"
>
> Could someone show me how to make this work with what I have described
> please?
>
> Ron
>
> -----Original Message-----
> From: Ron Piggott <ron.php@xxxxxxxxxxxxxxxxxx>
> Reply-to: ron.php@xxxxxxxxxxxxxxxxxx
> To: PHP DB <php-db@xxxxxxxxxxxxx>
> Subject: Search function query
> Date: Sun, 01 Nov 2009 08:38:13 -0500
>
> I am writing a search function.  Is there a way to enhance this  
> query so
> it will display the best matches first down to the least matches?  As
> well I don't know how to handle if the user types in more than 1  
> word to
> search.
>
> SELECT * FROM `store_product_profile` WHERE `product_name` LIKE '%
> $search_string%' AND `product_description` LIKE '%$search_string%' AND
> `keywords` LIKE '%$search_string%'
>
> Ron