JP SIngh wrote:
Hi All
This is a complicated one, not for the faint hearted :) :) :)
Please help if you can how to achieve this search.
We have a freetext search entry box to allow users to search the
database.
What database?
I am searching two tables.
SELECT TapeRecords.Id, TapeRecords.ItemTitle, TapeRecords.SourceRef,
TapeRecords.ItemYear, TapeRecords.TapeNumber FROM TapeRecords WHERE
TapeRecords.Id In (select tapenumber from TapeLogDetails where
TapeLogDetails.Description LIKE '%%asia%%' ) OR
(TapeRecords.ItemTitle LIKE '%%asia%%' ) OR (TapeRecords.Location
LIKE '%%asia%%') OR (TapeRecords.Country LIKE '%%asia%%') OR
(TapeRecords.Keywords LIKE '%%asia%%') ORDER BY TapeRecords.Id DESC;
The search logic also needs to allow users to search for multiple
entries
for example if I was to type Asia Burma
it should find all records where asia and burma appears. Sound simple
, well it is not.
Because my researchers requires the search to work where the either
of the two words (i.e. Asia and Burma) were present in any
combination of the fields.
i.e Asia might be in description and Burma might be in any of the
other fields, i.e. to find the two keywords in any combination.
can anyone help?
It sounds as if you need a full-text index. If you are using SQL Server,
this is built in and i suggest you go to microsoft.public.sqlserver.fulltext
for help with this.
If you are using Access, then my first suggestion is to migrate to SQL
Server so you can use full-text indexing. If that's not possible, then you
are going to need to roll your own ... trust me, this is not a task for the
faint-hearted.
The task (which I have never done so I cannot provide any details) involves
creating a separate table containing the record ID's and the keywords
contained in the records (you may also need to store the field names from
which the keywords came so that you can narrow down your search to specific
fields). Something like this:
TapeRecordIndex:
Id
Keyword
Fieldname
All three fields should be combined into a unique index.
You will also need a table containing "nuisance" words, i.e., words to be
avoided when generating the index. Words such as "and", "the", etc.
You will need an offline scheduled task to periodically generate/refresh the
index. The program will go through each record, looping through the fields
and splitting the text contained in the fields into individual keywords,
generating individual index records containing the index entries (idnoring
the list of nuisance words). For example, if you had these records
TapeRecord
1 Mission to Burma Asia
TapeLogDetails
1 Sample description of the Mission to Burma tape
You would wind up with:
TapeRecordIndex
1 Mission Title
1 Burma Title
1 Asia Location
1 Sample Description
etc.
This allows:
SELECT Id From TapeRecordIndex
WHERE keyword in (Burma, Asia)
This can be joined to the source tables to extract the source data.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.