List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 9 2007 10:41pm
Subject:RE: How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'?
View as plain text  
> -----Original Message-----
> From: Iain Alexander [mailto:ia@stripped] 
> Sent: Wednesday, May 09, 2007 3:11 PM
> To: mysql@stripped
> Subject: Re: How do I find products when a user types 
> freeform strings like 'Sony 20" TV' or '20" Sony TV'?
> 
> On 4 May 2007 at 1:21, Daevid Vincent wrote:
> 
> > I'm having trouble figuring out the logic/query I want.
> > I know that all those "OR"s are not right.
> [snip]
> > WHERE  products.enabled = 1 
> > 	AND( 
> > 	    (products.model LIKE 'sony%'   OR products.model 
> LIKE '20%'  OR products.model LIKE 'tv%') 
> > 	 OR (products.upc LIKE 'sony'      OR products.upc LIKE 
> '20'     OR products.upc LIKE 'tv') 
> > 	 OR (products.name LIKE '%sony%'   OR products.name 
> LIKE '20%'   OR products.name LIKE '%tv%') 
> > 	 OR (companies.name LIKE 'sony%'   OR companies.name 
> LIKE '20%'  OR companies.name LIKE 'tv%') 
> > 	 OR (categories.name LIKE '%sony%' OR categories.name 
> LIKE '20%' OR categories.name LIKE '%tv%') 
> > 	) 
> [snip]
> 
> It seems to me that the logic you're looking for is something 
> more like
> 
> (products.model LIKE 'sony%'   OR products.upc LIKE 'sony'      OR
>   products.name LIKE '%sony%'   OR companies.name LIKE 'sony%'   OR
>   categories.name LIKE '%sony%'
> ) AND (
>   products.model LIKE '20%'  OR products.upc LIKE '20'     OR
>   products.name LIKE '20%'   OR companies.name LIKE '20%'  OR
>   categories.name LIKE '20%'
> ) AND (
>   products.model LIKE 'tv%' OR products.upc LIKE 'tv' OR
>   products.name LIKE '%tv%' OR companies.name LIKE 'tv%' OR
>   categories.name LIKE '%tv%'
> )
> 
> so that each of the search terms appears in at least one of 
> the relevant columns.

OMG! I think you are on to something. I just tried this, and I got one row. Exactly what I
wanted. I'll have to poke at this some
more, and tweak my PHP that autogenerates the SQL, but I may just be naming my first born
Iain. :) 

Thread
How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'?Daevid Vincent4 May
  • Re: How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'?Mogens Melander4 May
  • Re: How do I find products when a user types freeform strings like'Sony 20" TV' or '20" Sony TV'?Baron Schwartz4 May
    • RE: How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'?Daevid Vincent4 May
      • Re: How do I find products when a user types freeform strings like'Sony 20" TV' or '20" Sony TV'?Baron Schwartz4 May
      • Re: How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'?Mayssam Sayyadian7 May
  • Re: How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'?Iain Alexander10 May
    • RE: How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'?Daevid Vincent10 May
  • RE: How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'? [SOLVED]Daevid Vincent11 May