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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
