boolean search expressions?

Discussion in 'ASP General' started by David, Feb 8, 2004.

  1. David

    David Guest

    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")
    David, Feb 8, 2004
    #1
    1. Advertising

  2. David

    Bob Barrows Guest

    David wrote:
    > 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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #2
    1. Advertising

  3. David

    David Guest


    > "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
    David, Feb 8, 2004
    #3
  4. David

    Bob Barrows Guest

    David wrote:
    >> "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


    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
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #4
  5. David

    Bob Barrows Guest

    Bob Barrows wrote:

    >
    > 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
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows, Feb 8, 2004
    #5
  6. David

    David Guest

    "Bob Barrows" <> wrote in message
    news:...
    > Bob Barrows wrote:
    >
    > >
    > > 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")


    Thanks Bob - I'll try this.

    David
    David, Feb 8, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. webposter
    Replies:
    2
    Views:
    637
    Peter Shaggy Haywood
    Sep 14, 2004
  2. J Leonard
    Replies:
    4
    Views:
    12,625
    Mark Space
    Jan 19, 2008
  3. Gustavo Narea
    Replies:
    4
    Views:
    1,875
    Arnaud Delobelle
    Apr 28, 2009
  4. Gunnar
    Replies:
    3
    Views:
    252
    Robert Klemme
    May 3, 2005
  5. Metre Meter
    Replies:
    7
    Views:
    355
    Metre Meter
    Aug 6, 2010
Loading...

Share This Page