mlapl1
Posts: 66
|
| Posted: 03/29/2004, 3:25 PM |
|
Dear Everyone
I was very fortunate that Peterr responded to my previous question.
I have another question. I want to search for keywords in a database field. At the moment, I enter keywords into a search form and the search form considers my words as a single string of characters. It then checks for that string.
Is there some builtin function that codecharge has which will split my string into "words" and search for each word in a field (and either OR or AND them)?
e.g. my field contains the keywords dog cat mouse
I enter the search string "dog mouse" . At present my search will not find a match but I would like it to do so.
Thanks for any help
Andrew
|
 |
 |
Anton
|
| Posted: 04/05/2004, 12:17 AM |
|
I believe that the answer to your problem depends on the development language you use and the use of BeforeExecute select event.
You need to split the words down into separate strings and then adjust the SQL's where clause as referenced by the DataObjectEvent in the BeforeExecute Select. In Java you would use the string tokeniser to split off each work and then iterate/merge all the words into a new Where condition for your SQL.
The count SQL will also have to be changed and you have to re-invoke the count SQL with the new where clause.
Very do-able but >very< slow due to non-optimised: x LIKE "%dog%" AND x LIKE "%mouse%".
In the past (when I used FoxPro ) there were tools which indexed upon words within a memo/long text. I do not know if your database supports such add-on features. The memo tool used a table of words built from the memo and then used a linking table to cross reference the memo to a key word. Your 'dog' could be instance 314 in the list so your linking table would be record primary key 1234, word PK of 314. Quite expensive in storage terms but a good single instance list of words to search within.
I have the same sort of problem where my television and film database uses a synopsis and I suspect that the same search requirements will exist.
If you find an answer to the work index problem please keep me posted. Until I find a performance answer I will not implement this feature.
Regards
Anton
|
|
|
|
mlapl1
Posts: 66
|
| Posted: 04/05/2004, 5:17 AM |
|
Thank you so much for your answer Anton.
Yes I knew approximately what the answer was as I have done this sort of thing before but I was hoping that Codecharge would have some sort of built-in function (as it is a such common thing to want to do). I just don't have much time to get this all done and I was hoping...
I am using mysql and php. I will let you know if I discover anything.
Thank you once again
Andrew
|
|
|
|
Anton
|
| Posted: 04/06/2004, 1:40 AM |
|
I guess that your only option ATM is to use a FULLTEXT index and the MATCH() feature in MySQL >= version 4.0 - if you have the luxury of this version.
It appears as if MySQL has implemented a SOUNDEX algorithm against an index.
Please keep me posted if you find anything.
TIA
Anton
|
|
|
|
mlapl1
Posts: 66
|
| Posted: 04/08/2004, 8:46 PM |
|
Thanks Anton
I will keep looking - have not had much time to look into it yet but will do so in due course. Interesting about the SOUNDEX....
cheers
Andrew
|
|
|
|
