List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 4 2007 8:21am
Subject:How do I find products when a user types freeform strings like 'Sony 20" TV' or '20" Sony TV'?
View as plain text  
I'm having trouble figuring out the logic/query I want.
I know that all those "OR"s are not right.
I'm doing this in PHP and mySQL (of course), 
so if it can't be done with a single query, I can split it up.

Here's the challenge, given a text field search box, someone enters:

	Sony 20" TV

How do I search for that, not knowing which fields are which?
For example, they could have also entered:

	20" Sony TV

This is the one I have now, but (as you probably noticed), it will return many rows,
I expect that most of the time > 1 row will be returned, but I'm getting a grip more
than I want (or the customer would want), and
also rows that have nothing to do with the search terms.

SELECT products.*, companies.name AS company_name, categories.name AS category_name 
FROM 	 products 
	 LEFT JOIN companies ON company_id = companies.id 
	 LEFT JOIN categories ON category_id = categories.id 
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%') 
	) 
ORDER BY categories.name DESC, products.name ASC, companies.name ASC;

(and that just gets uglier the more words in the search)

+----+------------------+--------------+--------------+---------------+
| id | name             | model        | company_name | category_name |
+----+------------------+--------------+--------------+---------------+
|  1 | 20" TV           | STV20-KVR-HD | Sony         | Tube          | <---
|  2 | 36" TV           | STV36-KVR-HD | Sony         | Tube          | 
|  4 | Ultra-Plasma 62" | UP62F900     | Sony         | Plasma        | 
|  5 | Value Plasma 38" | VPR542_38    | Sony         | Plasma        | 
|  6 | Power-MP3 5gb    | 09834wuw34   | Sony         | MP3 Players   | 
|  3 | Super-LCD 42"    | SLCD42hd002  | Sony         | LCD           | 
|  7 | Super-Player 1gb | SP1gb        | Sony         | Flash         | 
|  8 | Porta CD         | pcd500       | Sony         | CD Players    | 
......
+----+------------------+--------------+--------------+---------------+

Obviously the person wanted id = 1 in this case.

Unrelated, is there any speed improvement using JOIN instead of LEFT JOIN ?
Think millions of products.

Thanks for help and suggestions...

Daevid.

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