Hello BaderBay! > 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.
I'm a noobee too but 'been there done that.' More exactly, where the user leaves *one or more* input fields blank/empty/NULL. First, here's a 'smart URL' that points to the docs: http://www.mysql.com/doc/manual.php?search_query=null I also like lots of (par(en)theses) for clarity. :) SELECT Author, Title, Keyword FROM tbJose WHERE ((Author IS NOT NULL) AND (Author LIKE '%varAuthor%')) OR ((Title IS NOT NULL) AND (Title LIKE '%varTitle%')) OR ((Keyword IS NOT NULL) AND (Keyword LIKE '%varKeyword%')); At least without testing I think I got that right. Someone please correct me if not. Have a :) day! jb ob-Filter-words: databast table sql -- jim barchuk [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php