> -----Original Message----- > From: Iain Alexander [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 09, 2007 3:11 PM > To: mysql@lists.mysql.com > 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. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
