Boolean/Logic searches

annalise

Ello,

I am trying to implement a search on specific library materials, I have given a web page (request and design by the librarians) where user simply enter a word or phrase in the find input fields (max of four) and they have a selection of searchable fields as Title, keywords, author.
Full Boolean/Logic searches are also needed the AND, OR, and NOT operators.

I have attempt many SQL statements using the post method.
I wanting to know what is the best strategy/ways accomplish this?
Should I query each find with its logic?
I also like to know how would I optimise those queries in the database end.
I have dumped the HTML code of the web page for anyone who does not understand what I am trying to get across.

Any common or suggestions are welcome.
I am using MySQL Server version 3.23.22 and php-3.0.12-6.

annalise

---the html code---

<CENTER>
<FORM METHOD ="post" ACTION ="blar.php3">
<TABLE BORDER ="0">
<TR><TH ALIGN ="CENTER" COLSPAN ="3"><B CLASS=typeC>Search</B></TH></TR>
<TR><TH ALIGN ="LEFT">&nbsp</TH><TD><B>Find :</B></TD>   <TD><B>Field:</B></TD></TR>

<TR><TD>&nbsp</TD><TD><INPUT TYPE=TEXT NAME="find1" MAXLENGHT ="40" SIZE ="35"></INPUT>  IN  </TD>
<TD><SELECT NAME ='fieldpick1'>
<OPTION SELECTED VALUE ="all"> All Fields</OPTION>
<OPTION VALUE ="keywords"> Keyword(s)</OPTION>
<OPTION VALUE ="Author"> Author</OPTION>
<OPTION VALUE ="Title"> Title</OPTION>
</SELECT>
</TD>
</TR>
<TR><TD><SELECT NAME ='logic1'>
<OPTION SELECTED VALUE ="AND"> AND</OPTION>
<OPTION VALUE ="OR"> OR</OPTION>
<OPTION VALUE ="<>"> NOT</OPTION>
</SELECT>
</TD>
<TD><INPUT TYPE=TEXT NAME="find2" MAXLENGHT ="40" SIZE ="35"></INPUT>  IN  </TD>
<TD><SELECT NAME ='fieldpick2'>
<OPTION SELECTED VALUE ="all"> All Fields</OPTION>
<OPTION VALUE ="keywords"> Keyword(s)</OPTION>
<OPTION VALUE ="Author"> Author</OPTION>
<OPTION VALUE ="Title"> Title</OPTION>
</SELECT>
</TD>
</TR>
<TR><TD><SELECT NAME ='logic2'>
<OPTION SELECTED VALUE ="AND"> AND</OPTION>
<OPTION VALUE ="OR"> OR</OPTION>
<OPTION VALUE ="<>"> NOT</OPTION>
</SELECT>
</TD>
<TD><INPUT TYPE=TEXT NAME="find3" MAXLENGHT ="40" SIZE ="35"></INPUT>  IN  </TD>
<TD><SELECT NAME ='fieldpick3'>
<OPTION SELECTED VALUE ="all"> All Fields</OPTION>
<OPTION VALUE ="keywords"> Keyword(s)</OPTION>
<OPTION VALUE ="Author"> Author</OPTION>
<OPTION VALUE ="Title"> Title</OPTION>
</SELECT>
</TD>
</TR>
<TR><TD><SELECT NAME ='logic3'>
<OPTION SELECTED VALUE ="AND"> AND</OPTION>
<OPTION VALUE ="OR"> OR</OPTION>
<OPTION VALUE ="<>"> NOT</OPTION>
</SELECT>
</TD>
<TD><INPUT TYPE=TEXT NAME="find4" MAXLENGHT ="40" SIZE ="35"></INPUT>  IN  </TD>
<TD><SELECT NAME ='fieldpick4'>
<OPTION SELECTED VALUE ="all"> All Fields</OPTION>
<OPTION VALUE ="keywords"> Keyword(s)</OPTION>
<OPTION VALUE ="Author"> Author</OPTION>
<OPTION VALUE ="Title"> Title</OPTION>
</SELECT>
</TD>
</TR>
<TR></TR>
</TABLE><BR><INPUT TYPE="submit" VALUE="Start Search"></INPUT>  
<INPUT TYPE="reset" VALUE="Clear Search Details"></INPUT>  
<INPUT TYPE="button" VALUE="Back" onclick="history.back()"></INPUT>
</FORM>
</CENTER>

hotspur

With the danger of eing a database bore....

You should design you database and let that determine how you search for your data, and design the form from that.

How is you data stored? do you have all the data in one table or do you have separate keywords, author and title tables?

If the data is in separate tables I would go for three separate searches and then use then display the data eg store the results for each search in an array and then display the array.

Also I can see the benefit of trying to use generic fields on your form but does the data really support this ie Keywords how many are there? with your form you have a maximun of four and then you can't use title or author. Then consider author - if someone enters the full name do you really want to start your search on the first name - if not how do you decide if it the first or last name and how would you split it? - better to have a dedicated author fields where the first and last names can be entered and used appropriately.

Optimising your data, you can use secondary idices to reference your data if it is stored in one table ie an index for author etc.
However my first thought are that one table would not be good especially for keywords a book many have one or many keywords this would point to having a keyowrds/book_id reference table.
Then Title - how often would you get the full title - therefore this requires pattern matching. It would be difficult to optimize when a user adds a pattern matching at the beginning, as most keys use the leftmost letters

Hope I haven't gone on too much
Mark