Re: [PHP-DB] Re: Search function query
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.pigg...@actsministries.org 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@actsministries.org
Reply-to: ron@actsministries.org
To: PHP DB php-db@lists.php.net
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Search function query
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 k...@trk7.com
To: ron.pigg...@actsministries.org ron.pigg...@actsministries.org
Cc: PHP DB php-db@lists.php.net
Subject: Re: [PHP-DB] 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.pigg...@actsministries.org 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@actsministries.org
Reply-to: ron@actsministries.org
To: PHP DB php-db@lists.php.net
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
Re: [PHP-DB] Re: Search function query
Have you tried removing the HAVING clause?
Sent from my iPhone
On 02/11/2009, at 3:51 AM, Ron Piggott
ron.pigg...@actsministries.org wrote:
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 k...@trk7.com
To: ron.pigg...@actsministries.org ron.pigg...@actsministries.org
Cc: PHP DB php-db@lists.php.net
Subject: Re: [PHP-DB] 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.pigg...@actsministries.org 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@actsministries.org
Reply-to: ron@actsministries.org
To: PHP DB php-db@lists.php.net
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
Re: [PHP-DB] Re: Search function query
The '0' relevance is probably because 'postcard' is present in a lot of rows . Try adding more rows with different content and also try searching for different products. Your query looks fine to me. Sent from my iPhone On 02/11/2009, at 4:53 AM, Ministry Office off...@actsministries.org wrote: Yes. All the search results have a '0' relevance result. When I created the FULLTEXT index I made all three fields one index with the keyname customer_search --- is my query wrong, should I be using 'customer_search'? Ron -Original Message- From: Kesavan Rengarajan k...@trk7.com To: ron.pigg...@actsministries.org ron.pigg...@actsministries.org Cc: PHP DB php-db@lists.php.net Subject: Re: [PHP-DB] Re: Search function query Date: Mon, 2 Nov 2009 04:11:45 +1100 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 )
