designing a search page for database

[Millie Niss]

I am new to PHP and mySQL, and I am trying to build a database of
services (on a service table) provided by agencies (another table),
where one agency can provide many services, but a service belongs to a
single agency. There are about 10 fields in the services table,
including a comments field which needs to be searchable. I don't know
how to design a search page that will be easy to use for so many
fields, and I don't want to actually enter the data until I am sure
that my database design will support the searches that I need to do.
People should be able to search by agency and also by the fields in
the services table, but I need to get results that include the partial
matches to the search fields, for example where some but not all of
the fields match the serach terms. Plus I'd like to have searches
with "and" and "or". I don't know how to design the search page that
I need for this. Any suggestions would be welcome!

A further complication is that the database needs to be usable by
people with limited education/intellectual level and those with
cognitive impairments, so I need an easy-to-understand method of
searching (possibly with a more complicated "advanced search" optons
as well).

Are there books on designing search interfaces/algorithms that you can
recommend? I'm more confused about the design than about the actual
code, which I think I can figure out once I design the page...

Please send me a copy of replies via email to men2@columbia.e du as I
don't always read the group. Thanks!

Millie

[Geoff Berrow]

Re: designing a search page for database

I noticed that Message-ID:
<3ff69e4e.03110 60959.4ef9779e@ posting.google. com> from Millie Niss
contained the following:
[color=blue]
>Please send me a copy of replies via email to men2@columbia.e du as I
>don't always read the group. Thanks![/color]

The nerve of some people!
--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[renster]

Re: designing a search page for database

sounds like you need

http://aspn.activestate.com/ASPN/Cookbook/PHP/Recipe/125901

[Wes Bailey]

Re: designing a search page for database

This class is not the best for doing keyword searches on values. The
person who wrote it is not using SQL to its fullest capability in
limiting the result set to only include relevant matches using a WHERE
clause. Depending on the type of search (wide or narrow) the
following constructs can be used on a varchar datatype:

select name from table where name like '%key1%key2%'

will return all records containing the string in key1 and key2. This
is commonly referred to a narrow search since it really means "find
names where key1 AND key2 are in the name". The next construct is

select name from table where name like '%key1%' or name like '%key2%'

This commonly referred to as a wide search since a name containing
both or only one of the key strings is returned.
[color=blue]
> renster <renster> wrote in message news:<Xns942D48 4E01A3Frenster@ 203.59.27.131>. ..
> sounds like you need
>
> http://aspn.activestate.com/ASPN/Coo.../Recipe/125901[/color]

[renster]

Re: designing a search page for database

> This class is not the best for doing keyword searches on values.

do you have an alternative class that you can point me to?

[renster]

Re: designing a search page for database

> This class is not the best for doing keyword searches on values. The[color=blue]
> person who wrote it is not using SQL to its fullest capability in
> limiting the result set to only include relevant matches using a WHERE
> clause.[/color]

Now that I've looked at the class a little more closely I don't see a where
clause anywhere in the class. I'm not an expert or anything, maybe I have
missed it.

I see that he has put th keywords into an array and does a select statement
for the columns specified returning an array. Then the array of keywords is
shuffled through with the result array to determine matches. Wouldn't this
in effect return a wide search as each element of the array is returned?
Not as efficient as it could be with respect to returning all rows that
match the specified columns (without the search terms). I agree with not
making the best use of SQL.