> -----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. :)
