Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Search for Multiple keywords in multiple fields

Reply
Thread Tools

Search for Multiple keywords in multiple fields

 
 
JP SIngh
Guest
Posts: n/a
 
      01-23-2006
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. 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?


 
Reply With Quote
 
 
 
 
dNagel
Guest
Posts: n/a
 
      01-23-2006
Without reading and digesting everything you've written, couldn't you
use a Union to simplify the logic?

Select [blah, blah,blah] from [TapeRecords] Where [Somecondition]
UNION
Select [blah, blah,blah] from [TapeRecords] Where [SomeOthercondition]
UNION
Select [blah, blah,blah] from [TapeRecords] Where [YetAnothercondition]

D?

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. 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?
>
>

 
Reply With Quote
 
 
 
 
dNagel
Guest
Posts: n/a
 
      01-23-2006
I meant to change the table name each tie but I forgot... D.


dNagel wrote:
> Without reading and digesting everything you've written, couldn't you
> use a Union to simplify the logic?
>
> Select [blah, blah,blah] from [TapeRecords] Where [Somecondition]
> UNION
> Select [blah, blah,blah] from [TapeRecords] Where [SomeOthercondition]
> UNION
> Select [blah, blah,blah] from [TapeRecords] Where [YetAnothercondition]
>
> D?
>
> 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. 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?
>>
>>

 
Reply With Quote
 
paul@bullschmidt.com
Guest
Posts: n/a
 
      01-23-2006
And for some related articles that might help:

Classic ASP Design Tips - Search Criteria on Multiple Fields
http://www.bullschmidt.com/devtip-searchcriteria.asp

Classic ASP Design Tips - Search For Keywords on Multiple Fields
http://www.bullschmidt.com/devtip-se...iplefields.asp

Best regards,
-Paul
www.Bullschmidt.com - Freelance Web and Database Developer
www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips

 
Reply With Quote
 
JP SIngh
Guest
Posts: n/a
 
      01-24-2006
Paul

Thanks for this but this the simplest example. What I trying to work out is
much complicated.

Here are my tables if any of your wizards can crack

TABLE - TapeRecords

Id (primary key)
ItemTitle
Location

TABLE - TapeLogDetails

TapeNumber (foreign key
Description

If the user enters Asia Burma I split the phrase into two keywords using
array.

For the above example I want to return the record where both the search
terms appear in the Item Title but also want to display the record where
"Burma" appears in Title and "Asia" appear in any of the other fields
including TapeLogDetails.Description.

Please help if you can



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...
> And for some related articles that might help:
>
> Classic ASP Design Tips - Search Criteria on Multiple Fields
> http://www.bullschmidt.com/devtip-searchcriteria.asp
>
> Classic ASP Design Tips - Search For Keywords on Multiple Fields
> http://www.bullschmidt.com/devtip-se...iplefields.asp
>
> Best regards,
> -Paul
> www.Bullschmidt.com - Freelance Web and Database Developer
> www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-24-2006
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
C++ gurus, keywords: programming,search, expertise Andrey Hristoliubov C++ 4 10-01-2008 07:46 PM
q. How Search Engines Utilize Keywords David HTML 1 04-29-2007 07:43 AM
Can we search keywords in a word document? savvy ASP .Net 4 11-25-2005 12:34 PM
Search using multiple keywords David Lozzi ASP .Net 2 06-02-2005 04:13 PM
Netscape hidden fields - array - multiple fields with same name mark.reichman@rl.af.mil Javascript 0 07-17-2003 03:05 PM



Advertisments