I need to be able to search by db by both text matches in, say an item's name AND a preset keyword.
for instance, the NAME of an item might be: "Big Red Wax Lips", while another item is "Fake Dog Poop"
and if someone enters the words "funny red" into my search field then submits, I want any items with any of these words to match and be brought up in the search results.
however, let's say that the item mentioned above has a keyword associated with it (much like a category cross-table association). The keyword is "funny" and 'Fake Dog Poop' is associated with the keyword funny. So in my search result I should get BOTH ITEMS, since one matched for literal text and the other matches as a keyword.
Now I have a single keyword search function working, and I have an idea of how to work an exploded text search, but the following search is giving me way too many results...It should give me 5 results and instead gives me 266. There's a problem of recursively matching the same items as well as non-matching items.
I have a rough prelim counting query below (it's easier to look at the query logic without the data recovery query)... is there any way it can be divided using parens to make the text searching functions more precise by keeping the keyword table functions (ie: KX and K tables) more separated from the searches in the Inventory (I) table? Is there some usage of DISTINCT that I am overlooking?
SELECT Count(*) FROM Inventory I, Keywords K, key_xref KX WHERE (I.Name LIKE '%funny%') OR (I.Name LIKE '%red%') OR (K.keyword LIKE '%funny%') OR (K.keyword LIKE '%red%') AND (KX.key_id = K.key_id) AND (KX.inv_id = I.inv_id);
I can provide the code that does the word explosion and query building if needed.
Thanks!
Kev
