gary
|
| Posted: 09/09/2005, 10:20 PM |
|
The CCS search performs exact string matches. I need a more comprehensive search...one that breaks the search string into keywords, performing an OR search of all the words. I'd also like to support quoted strings.
I'm avoiding SQL's FULLTEXT feature as it seems very un-google like in format and has index maintenance issues on the SQL end. So I'm leaning towards a solution that does not use this feature, even at the cost of speed.
Does anyone have an algorithm (ASP) I could use?
much thanks,
gary
|
|
|
 |
Edd
Posts: 547
|
| Posted: 09/12/2005, 8:05 AM |
|
Gary,
If you want to perform searches beyond your database's text search capabilities, there are 3rd Party tools that (at a cost) can help you.
Google itself has an API to catalogue your data and retrieve it in a search.
But you have to weigh up the factors on whether it is worth it or whether changing your manual procedures will arrive at a better solution. If you go outside the industry solution then expect a heavy maintenance bill.
Edd
_________________
Accepting and instigating change are life's challenges.
http://www.syntech.com.au |
 |
|
gary
|
| Posted: 09/29/2005, 4:55 PM |
|
Edd,
I'm not trying to perform searches beyond my database's text search capabilities. In fact, I explicitly want to USE my database's text search capabilities. I just need front end logic that takes something like:
Search: [ fishing for trout ] (a simple vanilla CCS search textbox)
but instead of performing exact string matches on "fishing for trout", it needs to break the string into it's keywords, stuffing the WHERE clause accordingly. So even a record with "trout fishing" or "best trout fishing rivers" would be returned.
Like google, it needs to interpret quotes correctly, support parenthesis, negation (minus sign), and throw out a defined list of noise words. Google is a defacto search standard and I want to offer my users the same functionality, even thought I'm ultimately just searching against a single text field.
I'm definitely open to purchasing code as long as it can be easily integrated into CCS. I don't think this includes Google's API as I believe this parses documents, not a field in my sql database.
Hope that clarifies the situation. Any help is MUCH appreciated.
gary
|
|
|
|
marcwolf
Posts: 361
|
| Posted: 10/02/2005, 11:52 PM |
|
Hi Gary
Question: What Database are you using.
If it is MySQL then you can use a Fulltext index. However this will only work in ISAM tables - not INNOB.
But - there is no reason why you cannot mix table types by having you main data in a INNOB table and just having the fields to do a fulltext search into a ISAM.
The updates etc are a but more tricky but certainly not too hard.
Hope it helps
Dave
_________________
' Coding Coding Coding
Keep Those Keyboards Coding.
Raw Code!!!!!!!
|
|
|
|
gary
|
| Posted: 10/03/2005, 4:27 PM |
|
I'm using SQL. I looked into the FullText approach as I know it's supposed to do some fairly sophisticated things, but it had two definite disadvantages that really put me off that idea:
1) It's still very UN-google like. Presumably the interface was developed before google was around. So I would still need a fairly sophisticated piece of code to translate google-like searches into the format FullText wants.
2) As I understand it the FullText feature requires that index tables be maintained in SQL. This wouldn't bother me except that they didn't seem to be an automatic sort of thing. If a new record was inserted, it wasn't automatically reflected in the index table. Perhaps I just didn't understand the mechanism here?
So, at the risk of having not very efficient searches, I had resigned myself to just doing it with LIKE statements. I'm still a bit frustrated that the CCS people don't see a need to support more complex searches. I mean, they obviously fixed their own forum search engine! Why don't they think the rest of us need that same ability?
thanks,
gary
|
|
|
|
Oper
Posts: 1195
|
| Posted: 10/03/2005, 7:59 PM |
|
Gary is not beyond CCS it just right there (in case i dint get it )
Check
1) the URL containg the whole Frase/sentence
2) before building select
Split the URL and create new new SQL with and/or.
example: Query: "CCS Search Engine"
you could split in 3 words
and sql will run like this
meta SQL
LIKE "CCS" or
LIKE "Search" or
LIKE "Engine" or
LIKE "CCS search" or
LIKE "CCS Engine" or
LIKE "search engine" or
LIKE "ccs search engine"
we have something like that it work ok.
The hard part will be the "Do you mean" part in google search engine 
http://www.globaldevelop.com
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
gary
|
| Posted: 10/05/2005, 9:09 AM |
|
Oper,
Would you mind sharing your code?
thanks,
gary
|
|
|
|
ChipW
|
| Posted: 10/24/2005, 3:39 PM |
|
I've been wanting to do about the same thing. I'm using MS SQL Server also, but I'm unfamilar with the FULLTEXT in SQL, need to do some Googling I guess.
In addition, I'd like to save the search term(s) into a table to see what is being searched for, so I can edit/add a keywords field for things like mis-spellings, or alternate terminology. I'd also want to add the date and maybe session ID to the table so I could see how often something was searched for in a given period of time, and/or how many times or different ways did somebody search for something.
Any help, code or guidence would be greatly appreciated.
Chip
|
|
|
|
dataobjx
Posts: 181
|
| Posted: 11/10/2005, 1:00 PM |
|
Here's another way. It's simple, but works fairly well and could be extended easily.
Place a note next to the search input box that say's something like;
Separate each word or phrase you want to search for with a comma. E.g., [ trout, trout fishing,fishing rod ]
In the BeforeBuildSelect Function you'll need to add code like this.
Dim TheWords
Dim AllWords
Dim Words
Dim sWhere
TheWords = Request.Querystring("s_KeyWords")
AllWords = Split(TheWords, ",")
For Each Words In AllWords
If Len(sWhere)>0 Then sWhere = sWhere & " OR "
sWhere = " FieldToSearchIn Like '" & Words & "%' "
Next
DBConn.DataSource.Where = sWhere
I could supply you with better code, but I'm not at the machine I need to be at to do so.. but this will likely get you headed in the right direction.
_________________
www.DataObjx.net
www.mydigitalapps.com |
|
|
|
ChipW
|
| Posted: 11/10/2005, 4:37 PM |
|
I've been researching the Fulltext SQL option and it seems it will be a good enough fit for me. Gary (the original Poster) said the Catalog required maintenance, but there are 3 options for SQL Server to automatically update the catalog. I think at this point I'd just like a way to save the search term from the query so I could manually fine tune my keywords as needed, to supplement the way SQL Server Fulltext natively searches. I haven't even tried to do this yet, just a bit too busy right now.
DataObjx, I'm not confident that the average searcher/surfer, would read the instructions on how to get better results. You'd be surprised how many people won't even use a search box, they would rather call an 800 number and ask you stupid questions! You tell them "type XXXX in the search box" and the say "Oh that's easy!" So I feel asking them to put commas in is a waste of time. Search must be more intuative. I just don't have the 6 figure budget to buy the fancy search technology.
Just my 2 cents
Thanks
Chip
|
|
|
|
gary
|
| Posted: 11/11/2005, 8:10 AM |
|
ChipW and dataobjx, much thanks for the input.
ChipW, I'm glad to hear that the catalog maintenance isn't a problem for you, however I'll reserve final judgement for when you actually get the thing working ;) I didn't find it very intuitive.
dataobjx, I think you approach would work, but it still lacks support for the now standard google features (quoted strings, negation, etc).
In looking at the CONTAINS clause further, I may have misjudged it. The input string isn't that different from what google expects. The only real standout is the NOT operator. Google users (at least myself) are accustomed to using the minus ('-') sign for this.
ChipW, I'm off on other coding efforts right now, but please let us know how it works out. I still think this sort of complex searching should be better supported by CodeCharge, but I'll take what I can get.
gary
|
|
|
|
Walter Kempees
|
| Posted: 11/11/2005, 10:34 AM |
|
I am going to pick up this "glove" as well.
Later.
Walter
|
|
|
|
ChipW
|
| Posted: 11/12/2005, 9:25 PM |
|
Gary, I agree wholeheartedly, Yes could create a tutourial, or better yet a search builder that included "human friendly search". at least that's what the Search Vendors seem to call it now. That said, the combinations of databases and languages CCS supports could make that expectation unrealistic. But a tutorial for ASP with Access and/or SQL, or PHP and MySQL, should be doable. Then again I tought a graph/chart builder and WYSIWIG HTML editor built in would have been a no brainer for CCS3.
Also note the just released MS SQL 2005 has much improved Fulltext support and the setup is much more intuitive, so say the reviews I've read.
I'm starting to get caught up, so I may install Fulltext search this week, and do some playing with it with CCS3. I'll let you know how it goes.
Lets continue to collaborate on this, 2 (or more hopefully) heads are better than one.
Chip
|
|
|
|
dataobjx
Posts: 181
|
| Posted: 11/16/2005, 10:30 AM |
|
I do have the code that will parse for full text search - And - I agree ChipW - that many don't read the screens they use. In fact, I've spent hours writing something straight and to the point - that would take 10 seconds to read, and they users still do not read it.
Nevertheless, I have the routines that allow one to perform the parsed - full text searching that you mention above, but am unsure how to chop this code out for presentation to CCS users viewing this post - since the knowledgebase application using it has grown rather large.
In an attempt to focus in on your issues, I provide the following which could be written better - but at the time - time was short and then I had to move on to other things and it was left how it was since it was working properly.
Naturally, my table structures and field names will likely differ, but with a bit of persistence, (changing field and table names as applicable to your application) you should be able to get the searches going.
One more thing about this code.
I wrote it over a year ago, and am hoping that I have posted everything (all functions, etc) to enable you to get it running... but in the event that I haven't please post what's missing and I'll send it up too.
In any event, using the parsing code, you can perform simple searches by just typing in some keywords or more sophisticated searches by using boolean operators (AND, OR, NOT, NEAR), grouping words/phrases using parentheses or quotes, and using the wild card character "*". Here are some example search queries:
checks
finds records containing the word "checks"
checks AND verification
finds records containing "checks" and "verification"
checks OR verification
finds records containing "checks" or "verification"
checks NEAR verification
finds records containing both "checks" and "verification", where the two terms are located in close to each within the document
"checks systems" AND verification
NOTE: Phrases MUST be contained within quotes "".
finds records containing the exact phrase "checks verification" -- this would not match a document with the phrase 'checks systems', for example
checks NOT verification
finds records containing "checks" but not "verification"
"checks sys*"
finds records containing words starting with "'checks sys" -- the asterick (*) wildcard only works at the end of a word or phrase, for example, "checks sys*" or info*, but not "info*tion"
******************************************************************************
In an effort to provide at least something for CCS Users I've provided the code see below - and realize that it may not be easy for one to understand at first glance.... but it does contain those functions (some are recursive functions) that perform the SQL Full Text Parsing required to do what you're after.
Since the number of characters allowed within a single post prevents me from publishing everything in one long post, I'll have after a number of attempts to publish it and failed will place the code on DataObjx for those interested to pull down.
Go to: http://dmpro.dataobjx.net
Login using your login and those without logins should be able to login as guest / guest.
After logging in Click on the "Documents" button, then on "Public Files Area"
Look for the file called "FULL Text Search (SQL Server)" and download it.
Let me know if it helps and again I apologize if the code is a bit rough in some spots - but at least the logic and the code is there and you should feel free to improve upon it accordingly.
The main parsing code is an include called "KBaseParser.asp"..
Let me know how you go and if anyone improves on the code - perhaps they would consider sending it to DataObjx and I'll publish it within the public files area.
_________________
www.DataObjx.net
www.mydigitalapps.com |
|
|
|
Walter Kempees
|
| Posted: 11/16/2005, 5:43 PM |
|
Persistent as ever, thanks.
Have just completed a phonebook application with the ordinary Search/Grid
combi.
It has the classic Number, Name, street, city postalcode, housenumber
approach.
However it is a little complicated in that (virtually) it consist of two
tables, one containing the main contact data, the other extra numbers to 1
contact.
I previously posted I would take up the 'glove' for using full text search
(MySQL), but actually I will first have a look at your solution.
My Db contains 6Mio in table one and 7.5 in table 2, so it will be
interesting to see how far we'll get.
The objective of course being a single entry field (googlish) accepting
frightening combinations like:
1234567890 - indicating a phone number (reverse search) or maybe a
housenumber
NNNNXX NN could be a postalcode + housenumber
XXXXXXXXXX could be a name a streetname a cityname
and any of the above in any combination.
Of course we would like our users to write:
- Dataobjx Meriden Connecticut
but will have to respond to
- Data Meriden
- DataOb Connecticut
It will be fun to undertake, so we'll see.
Thanks for keeping this thread interesting.
Walter Kempees
Consultair Nederland BV
Capelle aan den IJssel
The Netherlands
which in itself gives a great search string : Consultair Nederland BV
Capelle aan den IJssel The Netherlands
"dataobjx" <dataobjx@forum.codecharge> schreef in bericht
news:6437b7acc9685f@news.codecharge.com...
>I do have the code that will parse for full text search - And - I agree
>ChipW -
> that many don't read the screens they use. In fact, I've spent hours
> writing
> something straight and to the point - that would take 10 seconds to read,
> and
> they users still do not read it.
>
> Nevertheless, I have the routines that allow one to perform the parsed -
> full
> text searching that you mention above, but am unsure how to chop this code
> out
> for presentation to CCS users viewing this post - since the knowledgebase
> application using it has grown rather large.
>
> In an attempt to focus in on your issues, I provide the following which
> could
> be written better - but at the time - time was short and then I had to
> move on
> to other things and it was left how it was since it was working properly.
>
> Naturally, my table structures and field names will likely differ, but
> with a
> bit of persistence, (changing field and table names as applicable to your
> application) you should be able to get the searches going.
>
> One more thing about this code.
>
> I wrote it over a year ago, and am hoping that I have posted everything
> (all
> functions, etc) to enable you to get it running... but in the event that I
> haven't please post what's missing and I'll send it up too.
>
> In any event, using the parsing code, you can perform simple searches by
> just
> typing in some keywords or more sophisticated searches by using boolean
> operators (AND, OR, NOT, NEAR), grouping words/phrases using parentheses
> or
> quotes, and using the wild card character "*". Here are some example
> search
> queries:
>
> checks
> finds records containing the word "checks"
>
> checks AND verification
> finds records containing "checks" and "verification"
>
> checks OR verification
> finds records containing "checks" or "verification"
>
> checks NEAR verification
> finds records containing both "checks" and "verification", where the two
> terms
> are located in close to each within the document
>
> "checks systems" AND verification
> NOTE: Phrases MUST be contained within quotes "".
> finds records containing the exact phrase "checks verification" -- this
> would
> not match a document with the phrase 'checks systems', for example
>
> checks NOT verification
> finds records containing "checks" but not "verification"
>
> "checks sys*"
> finds records containing words starting with "'checks sys" -- the asterick
> (*)
> wildcard only works at the end of a word or phrase, for example, "checks
> sys*"
> or info*, but not "info*tion"
>
> ******************************************************************************
> In an effort to provide at least something for CCS Users I've provided the
> code
> see below - and realize that it may not be easy for one to understand at
> first
> glance.... but it does contain those functions (some are recursive
> functions)
> that perform the SQL Full Text Parsing required to do what you're after.
>
> Since the number of characters allowed within a single post prevents me
> from
> publishing everything in one long post, I'll have after a number of
> attempts to
> publish it and failed will place the code on DataObjx for those interested
> to
> pull down.
>
> Go to: http://dmpro.dataobjx.net
> Login using your login and those without logins should be able to login as
> guest / guest.
>
> After logging in Click on the "Documents" button, then on "Public Files
> Area"
>
> Look for the file called "FULL Text Search (SQL Server)" and download it.
>
> Let me know if it helps and again I apologize if the code is a bit rough
> in
> some spots - but at least the logic and the code is there and you should
> feel
> free to improve upon it accordingly.
>
> The main parsing code is an include called "KBaseParser.asp"..
>
> Let me know how you go and if anyone improves on the code - perhaps they
> would
> consider sending it to DataObjx and I'll publish it within the public
> files
> area.
> _________________
> www.DataObjx.net
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
ChipW
|
| Posted: 12/03/2005, 7:41 AM |
|
I have SQL Fulltext search running, and have created queries with Query Analyser. After some experimentation with the different predicates (contains, freetext, etc.) it seems FreeTextTable will be the best (at least for me) for integration into a CCS search. I say this because FreeText is more fuzzy (ie less chance of null results) and automatically breaks apart a search string (phrase) into its' individual words, then ranks the phrase based on keyword density and proximity and ignors noise words. In my opinion ,and from my own Googling, a seach that doesn't yield the exact results I was looking for, may clue me in on new term to use to refine my search and then achieve the desired results. This is why I think the more "fuzzy" results(or less precise), but ranked by best match is a better approach.
Now to the problems. It seems FreeTextTable will only search on one column in the DB, whereas FreeText & Contains will search three columns. Please correct me if this is wrong, or if you know of a way around this limitation. I would to be able to search on at least 3 columns: Product name, description, and keywords, where I could include synonyms, mis-spellings, etc.
Anyone have any other ideas?
Thanks
Chip
|
|
|
|
Alistair
|
| Posted: 12/22/2005, 9:28 AM |
|
CSS is to do with styles and nothing to do with searching.
|
|
|
|
Walter Kempees
|
| Posted: 12/22/2005, 10:26 AM |
|
CCS not CSS, short for CodeCharge Studio, the subject of this Forum.
Walter
To all:
For one of our Forum members, I recently build a solution not completely
covering the Googlish Search but solving a special objective.
He uses a 'store_products' table containing several text fields containing
article_name, description, manufacturer, and so on.
What we did is we added a field 'searchterm' to this table Text(255).
In the catalog maintenance page we did an AfterInsert and AfterUpdate
routine concatenating the several text fields into 'searchterm' separated by
a space.
(in MySQL UPDATE catalog set searchterm=concat_ws('',article_name,
description, manufacturer, and so on) where catalog.id=current id )
Also we ran the UPDATE without any where clause against his table resulting
in a filled database.
The Search containes a s_keyword field, just as normally generated.
In the BeforeExecuteSelect of the Grid we put
$i = 1;
$keywords =(explode(" ", ccGetFromGet(s_keyword,"")));
foreach ($keywords as $v) {
if ($i == 1)
$store_products->ds->Where = " searchterm LIKE '%". $v ."%' ";
else
$store_products->ds->Where .= " AND searchterm LIKE '%". $v ."%' ";
$i +=1;
}
//debug echo $store_products->ds->Where;
This solved the search requirements in a Q&D way.
If more fields need to be included in to the search the concatenate only
needs to be expanded on.
Although this creates overhead in the table and could be handled even more
sophisticated, it's the result that counts.
Walter
"Alistair" <Alistair@forum.codecharge> schreef in bericht
news:643aae236bb87c@news.codecharge.com...
> CSS is to do with styles and nothing to do with searching.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
ChipW
|
| Posted: 12/27/2005, 6:15 AM |
|
Walter, that's the "work around" I came up with too. I hesitate to call it a solution, but it will work just fine for me.
Could you explain "Q&D way" and the code you posted in more detail?
Thanks
Chip
|
|
|
|
Walter Kempees
|
| Posted: 12/27/2005, 7:51 AM |
|
Chip,
Works like a charm, so why change it.
It would have been hard to explain to our mutual forum member what
fullTextSearch would imply.
His MySql version was a 3 series, so that was a problem as well.
The size of his database was small to medium, this was the fast and feasable
way to go.
RAD = Rapid Application Development
R&D Research and Development.
Q&D, Quick and Dirty.
Ciao,
Walter
"ChipW" <ChipW@forum.codecharge> schreef in bericht
news:643b14c98356a7@news.codecharge.com...
> Walter, that's the "work around" I came up with too. I hesitate to call it
> a
> solution, but it will work just fine for me.
>
> Could you explain "Q&D way" and the code you posted in more detail?
>
> Thanks
> Chip
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
ChipW
|
| Posted: 12/28/2005, 4:45 AM |
|
Ahhh, Q&D, I didn't realized there was an acronym for the way I do things 
Thanks
Chip
|
|
|
|
|
