SQL Query help

[tonynoriega]

i have 3 form fields...that my agents use to search records in my table for.

<select name="agent" id="agent">
<option value=" " selected>Select your name</option>
<option value="agent1">agent2</option>
<option value="agent2">AGent2</option>
</select>

<input type="submit" name="submit" value="Search"></td>
<input name="lname" type="text" class="text" id="lname" size="20" />

<select name="community" id="community">
<option value="" selected>Select Community</option>
<option value="Sonata">Sonata</option>
<option value="Hazelwood Village">Hazelwood Village</option>
<option value="RockHampton">RockHampton</option>
</select>

my query is as follows

$sql = "SELECT * FROM registration_table WHERE lname LIKE '%$lname%' AND community='$community' AND agent='$agent'";

what is happening is that even if you put the value of "Curtis" in the last name field, nothing returns becuase its trying to match the community and agent names....

i dont know if im asking this right...but...

how do i say that if the user only enters a value for last name, it will return the records regardless of what the community and agent name are...

i hope that makes sense...

[Demaestro]

If I understand you correctly i think you want this...

$sql = "SELECT * FROM registration_table WHERE lname LIKE '%$lname%' OR community='$community' OR agent='$agent'";

All I did was change the ANDs to ORs

I would add this as well to remove case sensitive searches "WHERE lower(lname) LIKE lower('%$lname%')"

That way if they search 'Curtis' but in the DB it is 'curtis' it will still return the value so they can type CURTIS and it won't matter.

$sql = "SELECT * FROM registration_table WHERE lower(lname) LIKE lower('%$lname%') OR community='$community' OR agent='$agent'";

Hope this helps.. if not post back and I will take another stab at it with you.

[tonynoriega]

ok, that works great.

Now, if i may....

what i did was put the value "curtis" in the lname field, and it returned the record with the last name of "Curtiss" so not only did it catch the case of the first letter, it also caught the extra "S" on the end and still returned the record...

great.

But when i selected "agent1" in the first selection box, and put "curtis" in the lname input field, it returned all of the records for "agent1" ...even though there was a value for the lname field.

I can see it is because of the OR operator...i think it is querying the first option, which is "agent" and only using that in the query...right?

to make it say for instance, "AND OR" , is that a pivot table?

again, i hope i explained my thoughts right...

basically no matter if you select an agent, lname, or community, or two of three, or three of three....it should return the records...

your assistance is greatly appreciated.

[rocknbil]

what i did was put the value "curtis" in the lname field, and it returned the record with the last name of "Curtiss"

In your form,

Match on:
<input type="radio" name="fname_match_on" value="match_exact" checked>Exact
<input type="radio" name="fname_match_on" value="match_start">Starts with
<input type="radio" name="fname_match_on" value="match_end">Ends with
<input type="radio" name="fname_match_on" value="match_any">Anywhere

if ($fname_match_on == 'match_end") { $fname_match=" like \"\%$fname\""; }
else if ($fname_match_on == 'match_start") { $fname_match="like \"$fname\%"; }
else if ($fname_match_on == 'match_any") { $fname_match="like \"\%$fname"\%\""; }
else { $fname_match="=\"$fname\""; }

$select = "select from table where fname $fname_match;";

Note there's no "=" or "like..." in the select - this is contained in $fname_match.

But when i selected "agent1" in the first selection box, and put "curtis" in the lname input field, it returned all of the records for "agent1"

If you don't like this behavior, you're going to have to put something in the form, like a radio button, so they can only select one or the other. Or, go back to using "and" - it makes more sense to use and anyway from an intuitive standpoint. Without hints on the form about what it's doing, someone doing a search is going to expect every form field to further restrict their search.

OR will always add more records, AND will always restrict the records (that is, if there are values in all three fields.)
A possible solution,

to make it say for instance, "AND OR" , is that a pivot table?

Not directly, but in your form,
[first name fields and match radios as above]
<input type="radio" name="and_or_lname" value="and" checked> AND
<input type="radio" name="and_or_lname" value="OR" checked> OR

You then perform the same logic on the last name to decide whether it's exact or partial matched to populate "$lname_match."

$select = "select from table where fname=$fname_match"; 
if ($lname) { 
  $select .= " $and_or_lname lname $lname_match"; 
} 

(Remembering, of course, $lname_match will contain the operators = or "like %" as required.)

This may seem tedious to apply so much coding to all form fields and select statements - and it is. What you do is create subroutines that build the form fields (perl-y example:)

$fname_radios = &match_radios('fname');
$lname_radios = &match_radios('lname');

sub match_radios { 
 my ($radios,$fieldname,$radioname,$radiovalue,%labels); 
 $fieldname=shift(@_); ## Stores 'fname'; 
 $radioname = $fieldname . '_match_on'; 
 %labels = ('exact'=>'Exact Match', 
      'start' => 'Starts with', 
      'end' => 'Ends with', 
      'all' => 'Anywhere' 
      ); 
 foreach $f ('exact','start','end','all') { 
   ## Creates "match_exact", etc. 
   $radiovalue = 'match_' . $f; 
   $radios .= "<input type=\"radio\" name=\"$radioname\""; 
   if ($f eq 'exact') { $radios .= " checked"; } 
   $radios .= "> $labels{$f}\n"; 
 } 
 return $radios; 
} 

The previous routine would create a set of radio buttons for **any** field you want to set matching options on.

You would do the same thing in building your select statements - create small subroutines that parse through the radio values and decide whether to add an AND or an OR, and decide which matching options to use on each field, without repeating the same code over and over changing the names.