Help a newbie: Keyword searches w/ IDC

[Richard Friesen]

Hi everyone.

Like the title says, I'm a newbie when it comes to SQL. Maybe I'm
missing something obvious, but it seems to me that it's not very easy
to do a substring search from an HTML form using IDC.

I'm trying to pass a search-word from an HTML form, through IDC to
SQL, and have it do a SUBSTRING search. The statement in the idc file
looks something like:

select firstname,lastname
from nametable
where keywords like '%searchword%'

The problem is that the user is required to type a "%" on either side
of the search word if he wants to do a substring search. I can't seem
to figure out any way to tack on a "%" before and after the
'%searchword%' in the select statement.

Would it be better to do this with a CGI script or an ISAPI dll file
instead of IDC? Which method would give the fastest results? Which
method would affect SQL performance the least on a busy web site? How
would you do a search for multiple words and list the hits from
best-matching to worst-matching? I would think that would require a
separate script, right?

I'm surprised that something as complete (and expensive) as SQL server
doesn't have that sort of stuff built in. Oh well, hack hack hack.

I eagerly await any and all suggestions from the gurus.

Thanks!

-Richard Friesen
(rfri...@smartt.com)

[Quah Siew Thiam]

hi,

if i understood idc correctly, you need '%%%searchword%%%' instead of just
one % at each end.

idc will interpret '%searchword%' as a parameter, thus, the sql statement
will not be executed correctly.

--
Regards
Quah Siew Thiam

(Temasek Polytechnic, Singapore)
http://www.tp.ac.sg

[Richard Friesen]

On 6 Dec 1996 07:31:41 GMT, "Quah Siew Thiam" <qua...@tp.ac.sg> wrote:

>if i understood idc correctly, you need '%%%searchword%%%' instead of just
>one % at each end.
>
>idc will interpret '%searchword%' as a parameter, thus, the sql statement
>will not be executed correctly.

Thanks, Quah. You're right, of course. Like I said I'm a newbie ;-)

Now for a tougher question:

How about a TRUE keyword search, like the professional search engines
do (Yahoo, Lycos, Excite...)? One where the user can type several
words, and it will do a search and list the results from best-match to
worst-match. Would you have to:

1. Have a separate table of keywords and their associated items
2. Search through the keyword table for each search-word separately
3. Put the results into a temporary table
4. List the result from the temporary table in proper order

Is there an easier way to do this? I would think this sort of thing
would require a CGI script using ODBC, or else an ISAPI dll. Both
methods scare me a bit, since I've never done ODBC or ISAPI before.

Thanks for the help!

-Richard Friesen
(rfri...@smartt.com)

[Michael howard]

> select firstname,lastname
> from nametable
> where keywords like '%searchword%'
>
> The problem is that the user is required to type a "%" on either side
> of the search word if he wants to do a substring search. I can't seem
> to figure out any way to tack on a "%" before and after the
> '%searchword%' in the select statement.

...

> I eagerly await any and all suggestions from the gurus.

Not a guru but ...

you need to double up the % signs in the idc to get them to pass
through. So, if you want a 'contains' search you need to say
where keywords like '%%%searchword%%%'
to do a 'starts with' you need to say
where keywords like '%searchword%%%'

this should do the trick.

Michael
mho...@activework.com

[Quah Siew Thiam]

hi,

i'm probably going to face the same situation.

we are now looking into the index server from microsoft to see how it can
help us.

but i think it can be defined by you, say if i enter "key words", it would
imply i want results pertaining to "key words" and not "key" and "words";
but entering "key,words", it means both "key" and "words".

having a table for keywords might not help, what if that database command a
huge disk space ?

i don't have any idea here, i hope that the index server might be able to
do something.

--
regards

[Quah Siew Thiam]

further to my other posting, as far as i know, by using a index server, it
basically allows you to maintain a index of any words of all htm pages in
your web server.

so far, i don't think it's able to access your database/tables to generate
an index.

that's also why we are looking into what Microsoft Index Server can do.

as for Yahoo, Lycos, ... , they probably have the links from registered
users and create an index based on the Title or something and a hyper link
to that page.
also, the current index server (any name), i think they are not able to
access across servers.

so, for now, it seems like you need to maintain an index file/table with
the respective hyperlinks and counters...whatever you want, they are not
able to dynamically access your tables.

thinking out loud.(tol)

--
Regards
Quah Siew Thiam (qua...@tp.ac.sg)

[Richard Friesen]

Thanks for the information, Quah and Michael.

I'll check out MS Index Server also. It may be of some use to me,
even if it can't access database tables.

-Richard Friesen
(rfri...@smartt.com)