Search function query

--=-QBTrAoXxEgBscmmv8ocu
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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

--=-QBTrAoXxEgBscmmv8ocu--

Re: Search function query

--=-PlFcTnw7aXDuaHzaf7FZ
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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
Reply-to: ron.php@actsministries.org
To: PHP DB
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

--=-PlFcTnw7aXDuaHzaf7FZ--

Re: 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
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
> Reply-to: ron.php@actsministries.org
> To: PHP DB
> 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: Re: Search function query

--=-/oM8A2mSEKNQFqmsfZtV
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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
To: ron.piggott@actsministries.org
Cc: PHP DB
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
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
> Reply-to: ron.php@actsministries.org
> To: PHP DB
> 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

--=-/oM8A2mSEKNQFqmsfZtV--

Re: Re: Search function query

--Apple-Mail-1--384566322
Content-Type: text/plain;
charset=us-ascii;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

Have you tried removing the HAVING clause?

Sent from my iPhone

On 02/11/2009, at 3:51 AM, Ron Piggott
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
> To: ron.piggott@actsministries.org
> Cc: PHP DB
> 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
> 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
> > Reply-to: ron.php@actsministries.org
> > To: PHP DB
> > 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

--Apple-Mail-1--384566322--

Re: Re: Search function query

--Apple-Mail-2--379830621
Content-Type: text/plain;
charset=us-ascii;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit


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
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
> To: ron.piggott@actsministries.org
> Cc: PHP DB
> 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 )
>

--Apple-Mail-2--379830621--