At 23:59 -0500 10/26/01, BadgerBay wrote:
>How do I avoid search problems when a user leaves a search box empty on an
>Ultradev search page running jserv and attached to a MySQL database?
>I have three search boxes: Author, Title, and Keyword
>The user enters the search data into one, two, or all three of the fields,
>and presses "SUBMIT"
>(I use JSP), so the variables for the recordset are (ex. Author) :
>Default Value: % [I also tried 1 ]
>Run-Time Value: request.getParameter("txtAuthor")
>The search uses this SQL logic:
>SELECT Author, Title, Keyword
>WHERE Author LIKE '%varAuthor%' OR Title LIKE '%varTitle%' OR Keyword LIKE
>The search works perfectly , UNLESS A USER LEAVES ONE BOX EMPTY, then the
>search RETURNS ALL RECORDS IN THE DATABASE,
>whether the default value is % or is 1.
>I do not want this to happen.
>How might I modify the code? I am a SQL beginner so I need explicit
If the user leaves a box empty, then omit the test for the corresponding
variable from your WHERE clause.
If the user leaves all the boxes empty, omit the WHERE clause entirely.
This means you'll need to construct your query conditionally based on
the values of the parameters you receive. This principle is true for
whatever API language you use.