Daevid, It occurs to me that what you were explaining was multi-column keyword search which MySQL does not support as of now. You may even face scenarios where you need multi-table and multi-column keyword search (even in your example, this may come up). In brief, you have a set of keywords and you don't know in which column of a table they may appear (multi-column kw-search), or you don't know in which (joined) tables they may appear (multi-table kw-search).
I am working on a generic module to do this, however not sure when that would be available. Meanwhile what you can do, given that you're doing PHP+MySQL is to use free-text indexes on multiple textual columns, then write a php module that would iterate over the columns, runs keyword search on single column, then integrates the results. This will not be efficient, but as of now, this type of logical keyword search must be handled outside the database, I guess ... --mayssam On 5/4/07, Daevid Vincent <[EMAIL PROTECTED]> wrote:
> -----Original Message----- > From: Baron Schwartz [mailto:[EMAIL PROTECTED] > > Daevid Vincent wrote: > > 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 > > I think you're describing full-text indexing. MySQL supports > it but only on MyISAM > tables. If you don't want to use MyISAM, full-text search > engines like Lucene or Sphinx may be worth looking at. I don't think I am. While full-text indexing might help since the indexes would be faster. I think this is a logic issue. The full-text index would be useful on a TEXT or BLOB or some long varchar field, but it doesn't solve that I'm trying to pull from two different tables, Product and Company and mapping the free-form string to fields that could be one of several. I think my first attempt is close, but it's something to do with all the AND and OR combinations that's not right. My version gives many results because it matches ("SONY" OR "TV" OR "20"). I need it to match ("SONY" AND "TV" AND "20") But this isn't it either (returns 0 results) because some fields, like the categories.name, products.upc and products.model don't match so the entire condition fails. 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%') AND (products.upc LIKE 'sony' OR products.upc LIKE '20' OR products.upc LIKE 'tv') AND (products.name LIKE '%sony%' OR products.name LIKE '20%' OR products.name LIKE '%tv%') AND (companies.name LIKE 'sony%' OR companies.name LIKE '20%' OR companies.name LIKE 'tv%') AND (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; Also, the 'SONY' is really the companies.name, while the '20"' _AND_ 'TV' together form '20" TV' to make the products.name. +------------------+--------------+--------------+--------------+ | name | model | upc | company_name | +------------------+--------------+--------------+--------------+ | 20" TV | STV20-KVR-HD | 097855008633 | Sony | | 36" TV | STV36-KVR-HD | 087452047023 | Sony | .... +------------------+--------------+--------------+--------------+ One way might be to do three separate queries, one for each word. Then store them in an array and compare the overlaps, removing any that aren't shared. Then a final query where product.id IN(array) That seems extremely inefficient and hackish though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
