boolean search expressions?

[David]

Hi,

I'm trying to add a search facility to a page that looks for matches in one,
other or both memo fields of a database. The code below works fine if the
visitor types in one word, or the term just happens to exist in one of the
queried fields.

What I'd really like is for a visitor to type in an expression, or query in
the same format as you would use in a search engine and it would find
appropriate matches. Any ideas how I can modify the code below to do this?

Thanks

set rsData = con.execute("select topic, title, ID, description from journals
where articletext LIKE '%" & searchstring & "%' OR description Like '%" &
searchstring & "%' ORDER BY dateno DESC")

 

[Bob Barrows]

Hi,

I'm trying to add a search facility to a page that looks for matches
in one, other or both memo fields of a database. The code below
works fine if the visitor types in one word, or the term just happens
to exist in one of the queried fields.

What I'd really like is for a visitor to type in an expression, or
query in the same format as you would use in a search engine and it
would find appropriate matches. Any ideas how I can modify the code
below to do this?

Thanks

set rsData = con.execute("select topic, title, ID, description from
journals where articletext LIKE '%" & searchstring & "%' OR
description Like '%" & searchstring & "%' ORDER BY dateno DESC")

"Memo" implies Access, right? Please don't make us guess. Always tell us the
type and version of database you are using. It is almost always relevant.

Anyways, I think we need to see specs for the expression syntax you want the
visitor to use. Depending on the search engine you are talking about, the
syntax can be very simple, or very complex. So tell us what you mean by "the
same format as you would use in a search engine".

Bob Barrows

 

[David]

"Memo" implies Access, right? Please don't make us guess. Always tell us the
type and version of database you are using. It is almost always relevant.

I'm sorry - I forgot that bit. Yes, the database is Access (office2000
version)

Anyways, I think we need to see specs for the expression syntax you want the
visitor to use. Depending on the search engine you are talking about, the
syntax can be very simple, or very complex. So tell us what you mean by "the
same format as you would use in a search engine".

Ideally I'd like the visitor to be able to type in an expression such as:-
model railway in england
into the search box. If they want to type in all the " ' + type characters
you can use in a search engine I'd strip them from the querystring.

So, if the visitor types in model railway in england, then the search would
look in both memo fields to see if any of the words in that expression
appeared in either field of the database.

I'd go down the route of the code stripping common words like "of at and in"
etc from the querystring.

Hope this provides more information. I don't want a fully featured search
engine - just the ability to see if any of the words in a search string are
in the database.

Thanks

 

[Bob Barrows]

I'm sorry - I forgot that bit. Yes, the database is Access (office2000
version)

Ideally I'd like the visitor to be able to type in an expression such
as:- model railway in england
into the search box. If they want to type in all the " ' + type
characters you can use in a search engine I'd strip them from the
querystring.

So, if the visitor types in model railway in england, then the search
would look in both memo fields to see if any of the words in that
expression appeared in either field of the database.

I'd go down the route of the code stripping common words like "of at
and in" etc from the querystring.

Hope this provides more information. I don't want a fully featured
search engine - just the ability to see if any of the words in a
search string are in the database.

Thanks

That's a relief: you're not going to allow the use of "exclusion words".

You're options are very limited, since you are not limiting the number of
words being typed. I see no way to avoid concatenating a potentially long
dynamic sql statement, which will probably perform horribly. If you were
using SQL Server, you could use the Full Text Search functionality, but ....

Anyways, you'll need to carry out your plan to break up the search string
into discrete words (you can use Split for this):
wordarray = split(searchstring, " ")

and then loop through the array, adding an OR clause to the query for each
word in the array:
where articletext LIKE '%" & wordarray(0) & "%' OR description Like '%" & _
wordarray(0) & "%' OR articletext LIKE '%" & wordarray(1) & _
"%' OR description Like '%" & wordarray(1) & "%' etc.

Did I mention that this will probably perform horribly?

HTH,
Bob Barrows

 

[Bob Barrows]

Anyways, you'll need to carry out your plan to break up the search
string into discrete words (you can use Split for this):
wordarray = split(searchstring, " ")

and then loop through the array, adding an OR clause to the query for
each word in the array:
where articletext LIKE '%" & wordarray(0) & "%' OR description Like
'%" & _ wordarray(0) & "%' OR articletext LIKE '%" & wordarray(1) & _
"%' OR description Like '%" & wordarray(1) & "%' etc.

Did I mention that this will probably perform horribly?

Hmm, I wonder if a UNION query would perform better ... It can't hurt to
test it:

sSQL = "select dateno, topic, title, ID, description from journals " & _
"where articletext LIKE '%" & searchstring1 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where description Like '%" & searchstring1 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where articletext LIKE '%" & searchstring2 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where description Like '%" & searchstring2 & "%' " & _
"ORDER BY dateno DESC")

HTH,
Bob Barrows

 

[David]

Hmm, I wonder if a UNION query would perform better ... It can't hurt to
test it:

sSQL = "select dateno, topic, title, ID, description from journals " & _
"where articletext LIKE '%" & searchstring1 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where description Like '%" & searchstring1 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where articletext LIKE '%" & searchstring2 & "%' " & _
"UNION select dateno, topic, title, ID, description from journals " & _
"where description Like '%" & searchstring2 & "%' " & _
"ORDER BY dateno DESC")

Thanks Bob - I'll try this.

David