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
