Sql Where clause and/or issues

[Linda Thomas]

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 ]

[Carol Enderlin]

Why won't AND work? Try using parens if you need to combine AND and OR clauses.

If you always include a line without a value and it says like '%%' and you include it with an OR you will get too many rows like you said.
Where
FirstName LIKE '%Mike%' OR
ContactName LIKE '%%' OR

If they fill something out wrong and don't get any results, wouldn't they just modify what they put in?

[Linda Thomas]

I think what its doing is seeing "%%" and thinking it is in every value. If I change one of my hard coded values to "%^%", which is not in any person's name in the database, I get the correct values.

Ok I just removed the %% and if I pass no value it works. It has something to do with the wild cards in my statement. But if I remove the wildcards (%%) then the user has to make no mistakes in spelling.

I.E.
(SurName LIKE 'Walker') works
(SurName LIKE '%Walker%') works
(SurName LIKE 'Walk') doesn't work
(SurName LIKE '%Walk%') works

[Shailesh Chandra]

Originally posted by Linda Thomas:

(SurName LIKE 'Walker') works
(SurName LIKE '%Walker%') works
(SurName LIKE 'Walk') doesn't work
(SurName LIKE '%Walk%') works

did you try SurName LIKE 'Walk%' it will also work

[Linda Thomas]

It does work with 'Walk%' but if I pass an empty string it would be '%' which gives me all the records in the database still.

I think I'll encase it in brackets and test it that way.
(SurName != "" AND LIKE '%Walk%') is the idea but I have to play with that syntax a bit. But only letting it test the LIKE if the value is not an empty string.

Think the syntax should be
((SurName != "") AND (SurName LIKE '%Walk%'))
[ March 22, 2005: Message edited by: Linda Thomas ]

[Shailesh Chandra]

// 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 + "'";
}

linda I would recommend that you make a logic of condition like

 
String Condition  = "";
// if FirstName textbox filled
if( FirstName != null && !"".equals(FirstName))
{
  Condition += "FirstName LIKE '%" + FirstName + "%' and ";
}
 
// if LastName textbox filled
if( LastName!= null && !"".equals(LastName))
{
  Condition += "LastName LIKE '%" + LastName + "%' and ";
}
 
// if StudentID textbox filled
if( StudentID!= null && !"".equals(StudentID))
{
  Condition += "StudentID LIKE '%" + StudentID + "%' and ";
}
 
Condition = Condition.trim();
//pass this condition to your code
Condition  = Condition.subString(0,Condition.length()-3);
 

Shailesh

[Linda Thomas]

Gives me an error:
Server: Msg 1038, Level 15, State 3, Line 6
Cannot use empty object or column names. Use a single space if necessary.

[Linda Thomas]

That was my other thought Shailesh. Since the people who've worked here have all been students we are encouraged to use code from workig projects but then I see big ugly code that I just can't stand, like my example.

My thought was to try to handle this with the sql statement without if statements and if unable to do so then to move onto your idea of just concatenating the Condition clause so I don't have to test for every single possibility.

I was just hoping to come up with a clever, elegant solution. But in the end all that matters is if it works.

Still...Granny's words speak softly in the back of my mind:
'Any fool can write code that a computer can understand. Good programmers write code that humans can understand. (Fowler)'

Thanks for all the help!
[ March 22, 2005: Message edited by: Linda Thomas ]

[Shailesh Chandra]

every one learns by practice

Gives me an error:
Server: Msg 1038, Level 15, State 3, Line 6
Cannot use empty object or column names. Use a single space if necessary.

any way are you able to solve this problem

Shailesh

[Steve Lee]

Linda,

Building the code as Shailesh suggests would work, but it needs to be built differently if performance is an issue (I don�t know Java that well, but I can help on the SQL). You need to eliminate the opening %, as it forces the SQL to look at every row, even if it isn�t what we�re after (%Smith% will only return those that begin with �Smith�, due to the capital �S�, even though it looked at every LastName field in the table, rather than just Smith<lowValues> through Smith<highValues>. Also, OR forces him to look at every row, which we can reduce if we use AND with any indexed fields available to us. This would give us those with last name LIKE 'Smith%' ANDed with the other criteria. Just use the OR logic in the lastName if, and delete both the AND logic and the parenthesis logic to OR all the fields. Assuming an index on LastName, and ANDing on LastName if possible, we have:


Shailesh�s code modified (hope you don�t mind, and apologies for any poor Java on my part!):

 
string condition  = "";
boolean needAnd = false; // Need an AND?
boolean needOr = false; // Need an OR?
boolean haveOpenParen = false; // Have an opening paren?
 
// Any field with an index on it, using AND is an advantage�
// if lastName textbox filled
if( lastName!= null && !"".equals(lastName))
{
  condition += "LastName LIKE '" + lastName + "%' ";
  needAnd = true;
}
 
// if firstName textbox filled
if( firstName != null && !"".equals(firstName))
{
  if (needAnd)
  {
    condition += � AND �;
    needAnd = false; 
  }
  if (needOr)
  {
    condition += � OR (�;
    haveOpenParen = true; 
    needOr = false; 
  }
  condition += "FirstName LIKE '" + firstName + "%' ";
  needOr = true;
}
 
// if studentID textbox filled
if( studentID!= null && !"".equals(studentID))
{
  if (needAnd)
  {
    condition += � AND �;
    needAnd = false; 
  }
  if (needOr)
  {
    condition += � OR �;
    if !(haveOpenParen) // Do we already have an open paren?
    {
      condition += �(�;
    }
    needParen = true;
    needOr = false; 
  }
  condition += "studentID LIKE '" + studentID + "%' ";
  needOr = true;
}
 
// Do we need a closing paren?
if (haveOpenParen)
{
  condition += �)�;
}
 
condition = condition.trim();

Hope this helps!
Steve
[ March 23, 2005: Message edited by: Steve Lee ]

[Linda Thomas]

Thanks for the help!

I decided to do it like Shailesh suggested and do the testing then
pass the condition but I had to change it because a Name textbox can
be found in multiple fields in the database.

Thank you for all the input guys/gals!!