List:General Discussion« Previous MessageNext Message »
From:Iain Alexander Date:May 9 2007 10:11pm
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  
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.
-- 
Iain Alexander      ia@stripped


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