List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 27 2001 3:35pm
Subject:Re: Simple problem: Blank search box returns all records
View as plain text  
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) :
>
>Name: varAuthor
>Default Value: %   [I also tried 1 ]
>Run-Time Value: request.getParameter("txtAuthor")
>
>The search uses this SQL logic:
>
>SELECT Author, Title, Keyword
>FROM tbJose
>WHERE Author LIKE '%varAuthor%' OR Title LIKE '%varTitle%' OR Keyword LIKE
>'%varKeyword%';
>
>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
>instructions.

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.
Thread
Simple problem: Blank search box returns all recordsBadgerBay27 Oct
  • Re: Simple problem: Blank search box returns all recordsjim barchuk27 Oct
  • Re: Simple problem: Blank search box returns all recordsPaul DuBois27 Oct
RE: Simple problem: Blank search box returns all recordsAndrew Murphy29 Oct