I'm creating a search page similiar to many we've done here
as our work as students at the college. The search form
consists of multiple textboxes that the user can fill out.
They must fill in at least 1 but can fill in more or all of
them. I want the search to pull all matching records, if they
put in last name Oxford but put in a wrong first name, say Sally,
then I want the result to be all the records with first names
Sally and all last names Oxford (not look for records with both
Sally as a first name and Oxford as a last name).
In the past we've handled this with a messy if statement in our class
file (all database interaction occurs in our
java class files not on the
JSP page).
I.E.
// if only FirstName textbox filled
if( FirstName.length() != 0 && LastName.length() == 0 && StudentID.length() == 0 )
{
Condition = "FirstName LIKE '%" + FirstName + "%'";
}
// if only LastName textbox filled
else if( FirstName.length() == 0 && LastName.length() != 0 && StudentID.length() == 0 )
{
Condition = "LastName = '" + LastName + "'";
}
- Condition is what I pass a stored procedure
- The stored procedure has something like SELECT * FROM tblPersons
WHERE 0=0 AND <Condition I pass goes here>
So basically I would create variables and use request.getParameter to pull the values directly from the form's textbox. Then I would have to
test for every single possible combination the user may have entered information for. In one search it was first name, last name and id#, so test if they entered only first name or first name and last name or first name and id# or last name and id# or just id#....my if/else statement was 7 tests long.
Now I have search page with 5 textboxes, so I do not want to have to resort to such a large cumbersome testing method.
Using SQL Query Analyzer I tested my query to see if I could get around this. With this statement:
select DISTINCT * from tblPersons
WHERE 0=0
AND
FirstName LIKE '%Mike%' OR
ContactName LIKE '%Mike%' OR
SurName LIKE '%Dietrick%' OR
RefNo = 500 OR
Address1 LIKE '%Meadowlane%' OR
Address2 LIKE '%Meadowlane%' OR
HomePhoneNo LIKE '%7224242%' OR
AltPhoneNo LIKE '%5606000%'
I got exactly what I wanted, all the records that have any of these matching values with no repeats. But I hardcoded the values in, when I try this in my java class file and enter only 1 value, leaving the rest of the textboxes blank, I got 269 results returned instead of the 7 I got in the query analyzer. But if I change all the OR's to AND's then each condition must be met and if they made a mistake with one field then they would get nothing returned even if they gave some right information, like the person's last name.
Oh and since we keep a home phone number and an alternate one, like for a cell phone or work number, I don't want a textbox for 2 phone numbers. I want to take the one phone number they gave me and test both fields to see if it shows up there. Same with contact name. Usually ppl will give a first name for a contact, so when the user enters a first name I want to test both the first name field and the contact name field (but the name doesn't have to appear in both fields, just one or the other or both).
Is there an easier way to code this? All I want is to search the db and return all results that have any of these values without saying it has to have all of them. Does SQL have an AND/OR option? I thought about encasing them in brackets but I couldn't get that to work either.
Any advice would be greatly appreciated!
[ March 22, 2005: Message edited by: Linda Thomas ]