Page 1 of 4

Confused about default values when using WHERE statement.

Posted: Sat Aug 07, 2004 3:00 pm
by WLW
My SQL database has a table called members, which contains 3 columns that I need to run searches on.

Each of the 3 columns has 3 possible choices:

Columns headings are:

Code: Select all

SAG           AFTRA           AEA

Data Choices: 
n/a           n/a             n/a
SAG           AFTRA           AEA
SAG Eligible  AFTRA Eligible  AEA Eligible
A record can have one choice from each column.

I need to design a query that SHOWS ALL when no selection on a form is made, but can produce any combination otherwise. That is, using checkboxes, except for the n/a, I can pass any combination of the other six choices.

I can't figure out a query that will allow this. I assume that the SQL for each column would be something like:
WHERE (members.SAG = 'sag1' OR members.SAG = 'sagEL')

sag1 and sagEL are the variable names used on the form.

The default should be a SHOW ALL if no selection is passed from the form.
I can't figure out what term to use as a default. Originally, I was using WHERE (members.SAG LIKE 'sag1' OR members.SAG LIKE 'sagEL') and % as the default. When all variables have %, it does show all. When all variables have choices other than %, the data is properly filtered. But, if any one variable is % and the others are something else, it returns a show all instead of filtering on the selected variable(s).

When the form is submitted, any unchecked option is filled by the SQL default, which is %.

Basically, using the variables 'sag1' and 'sagEL', the possible combinations would be:
1. Show all - This works if both 'sag1' and 'sagEL' are LIKE '%'
2. Show SAG only - Does not work if 'sagEL' is LIKE % - it forces a show all
3. Show SAG Eligible only - Does not work if 'sag1' is LIKE % - it forces a show all
4. Show SAG or SAG Eligible - This works if both 'sag1' and 'sagEL' are NOT LIKE %

Can anyone help this newbie??

feyd | fixed formatting to look like intended

Posted: Mon Aug 09, 2004 8:36 am
by lostboy
Build the statement dynamically.

Code: Select all

if (isset($_POST['submit'])){
  $x = 0; //counter
   $sql = "select * from members ";

  if (isset($_POST['sag'])){
    $sag = $_POST['sag'];
    $sql .= " where sag1 = '$sag' ";
   $x = 1;
  }
  
   //here are the tricky ones since you need to check for both the existance of $sag and how many elements there are
  if (isset($_POST['sagEL')){
    $sagEL = $_POST['sagEL'];
    if ($x == 1){
        $sql .=  " AND sagEL like '$sagEL%' ";
    }else{
        $sql .= " WHERE sagEL like '$sagEL%' ";
    }//end if
  }//end if

  //execute statement
  $result = mysql_query($sql);

Posted: Mon Aug 09, 2004 3:16 pm
by WLW
Thanks for taking the time to help me. I think I understand (more or less) what you are doing. Is it something like this: Depending on the choice(s) made on the form, the SQL statement may get appended with an appropriate WHERE statement?

I think you missed a few things mentioned in my original post that will probably have an effect on the coding. 1) There is already a lengthy WHERE statement in my SQL. So, I believe I can replace WHERE in your code with AND (or OR). 2) In addition to the column (members.SAG), there are 2 additional columns (members.AFTRA and members.AEA) that are part of this particular structure. I think your code only addresses the (members.SAG) column.

My form contains 6 checkboxes (to represent the 3 columns of 2 choices each). Below is the SQL that works as intended by me when all 6 checkboxes have been selected:

WHERE... AND ((members.SAG = 'SAG') OR (members.SAG = 'SAG Eligible') OR (members.AFTRA = 'AFTRA') OR (members.AFTRA = 'AFTRA Eligible') OR (members.AEA = 'AEA') OR (members.AEA = 'AEA Eligible'))

which is similar to having nothing selected, except that it excludes the default "n/a" that could also appear in any of the 3 columns.

The 6 checkboxes on the form are named: sag1, sagEL, aftra1, aftraEL, aea1, aeaEL.

I believe then, that there are 720 (6 factorial) possible combinations available on the form. Is there some PHP code that can account for all of them, and only append the correct one to the SQL?

Posted: Mon Aug 09, 2004 3:50 pm
by pickle
First, I'd make the selections pulldown menus rather than checkboxes. Much easier to deal with.

Second, this should work:

Code: Select all

//assume the three pulldown menus are called choice1,choice2,choice3
//and that -1 is the n/a value
$where_clause = "WHERE ";
//first pulldown
if($_POST[choice1] != -1)
{
   $where_clause += "$_POST[choice1] AND ";
   $condition_found = true;
}

//second pulldown
if($_POST[choice2] != -1)
{
   $where_clause += "$_POST[choice2] AND ";
   $condition_found = true;
}

//third pulldown
if($_POST[choice3] != -1)
{
   $where_clause += "$_POST[choice3] AND ";
   $condition_found = true;
}

$where_clause = rtrim($where_clause," AND ");

//only add where clause if there is something to sort on,
//otherwise, no where clause = return everything.
if($condition_found)
{
  $query += $where_clause;
}
You might want to change the "AND" to an "OR", but that's kind of besides the point.

Posted: Mon Aug 09, 2004 3:58 pm
by WLW
Pickle, thanks for the reply.

I cannot use pull downs, because that would only allow 3 of the possible 6 choices to be submitted. I need the possibility of all 6. I guess I could use 3 list boxes, each allowing multiple selections...?

Also, I am purposely excluding the "n/a" as a form option for now. It is only used during data input.

Posted: Mon Aug 09, 2004 4:55 pm
by pickle
See, checkboxes suck because you can't send along a value with them. How about this? Warning: It's ugly.

Code: Select all

//once again, assume checkboxes are choice1....choice6
//put in an array that maps checkboxes to values to search for
$values["choice1"] = array("column"=>"SAG",
                                        "search_value"="sagEL");
.
.
.
. //on and on for each choice

$where_clause = "WHERE ";
foreach($values as $choice=>$properties)
{
   if(isset($_POST[$choice])
   {
     $where_clause += "$properties[column] = '$properties[search_value]' AND ";
     $condition_found = true;
   }
}

$where_clause = rtrim($where_clause," AND ");

//only add where clause if there is something to sort on,
//otherwise, no where clause = return everything.
if($condition_found)
{
  $query += $where_clause;
}
Also, I noticed my previous code is broken in that it'll make a funky where clause.

Posted: Tue Aug 10, 2004 8:30 am
by WLW
Thanks again pickle.

I am new to PHP, just starting to study it, so I'm not quite sure how to interpret what your code does. I am having a hard time figuring out how to implement it.

I do have one question: Will your code dynamically generate the SQL chunk below (in bold) when all 6 checkboxes are selected (and of course any combination of those 6 elements when less than 6 boxes are checked)??

WHERE... AND ((members.SAG = 'SAG') OR (members.SAG = 'SAG Eligible') OR (members.AFTRA = 'AFTRA') OR (members.AFTRA = 'AFTRA Eligible') OR (members.AEA = 'AEA') OR (members.AEA = 'AEA Eligible'))

An example of checking less than 6 boxes could be: If only sag1 and aftra1 are checked, the dynamically generated SQL fragment would then be:
AND ((members.SAG = 'SAG') OR (members.AFTRA = 'AFTRA'))

The fragment must ALWAYS start with AND, and the choices are ALWAYS seperated by OR. I don't think you need to include a WHERE, since my existing code already has a WHERE statement. I need code that generates only the part starting with AND in the above examples, and adds it to my existing WHERE. In addition, I think the choices need to be grouped using the outer parenthesis, because there will be more SQL after it - an ORDER BY statement, which should always be there even if the form sends NO choices for these 6 checkboxes.

The 6 checkboxes on the form are named: sag1, sagEL, aftra1, aftraEL, aea1, aeaEL.

I really appreciate your time.

Posted: Tue Aug 10, 2004 9:25 am
by pickle
Ok, well then just change

Code: Select all

$where_clause += "$properties[column] = '$properties[search_value]' AND ";
to

Code: Select all

$where_clause += "($properties[column] = '$properties[search_value]') OR ";
, get rid of that initial $where_clause definition, and tack an "AND" onto $where_clause after the loop.

Posted: Tue Aug 10, 2004 9:37 am
by WLW
pickle wrote:

Code: Select all

//once again, assume checkboxes are choice1....choice6
//put in an array that maps checkboxes to values to search for
$values["choice1"] = array("column"=>"SAG",
                                        "search_value"="sagEL");
.
Is this what you are asking me to do??

Code: Select all

$values["choice1"] = array("column"=>"SAG",
                                        "search_value"="sag1");
$values["choice2"] = array("column"=>"SAG",
                                        "search_value"="sagEL");
$values["choice3"] = array("column"=>"AFTRA",
                                        "search_value"="aftra1");
$values["choice4"] = array("column"=>"AFTRA",
                                        "search_value"="aftraEL");
$values["choice5"] = array("column"=>"AEA",
                                        "search_value"="aea1");
$values["choice6"] = array("column"=>"AEA",
                                        "search_value"="aeaEL");
I am looking up how arrays work, and am totally confused.

Posted: Tue Aug 10, 2004 9:41 am
by pickle
Yep, the way my code works, for each choice selected, it checks if the column specified, has the value specified. So, if choice1 is selected, the query is modified to ensure that column 'SAG' has value 'sag1'.

This is a multi-dimensional array, in that a value of an array element is itself an array.

Posted: Tue Aug 10, 2004 9:52 am
by WLW
pickle wrote:, get rid of that initial $where_clause definition, and tack an "AND" onto $where_clause after the loop.

Code: Select all

$where_clause = "WHERE ";
foreach($values as $choice=>$properties)
{
   if(isset($_POST[$choice])
   {
     $where_clause += "$properties[column] = '$properties[search_value]' AND ";
     $condition_found = true;
   }
}

$where_clause = rtrim($where_clause," AND ");

//only add where clause if there is something to sort on,
//otherwise, no where clause = return everything.
if($condition_found)
{
  $query += $where_clause;
}
Is this what you mean?

Code: Select all

$and_clause = "AND "; 
foreach($values as $choice=>$properties) 
{ 
   if(isset($_POST[$choice]) 
   { 
     $and_clause += "($properties[column] = '$properties[search_value]') OR "; 
     $condition_found = true; 
   } 
} 

$and_clause = rtrim($and_clause," OR "); 

//only add "and" clause if there is something to sort on, 
//otherwise, no "and" clause = return everything. 
if($condition_found) 
{ 
  $query += $and_clause; 
}
If my change to your original code is correct, it seems like after the last element is appended, there will be an OR dangling at the end of the statement. Is that true?? Or does this statement remove it:

Code: Select all

$and_clause = rtrim($and_clause," OR ");
As you can tell, I have not yet tried to insert the code into my page for testing.

Also, at what point in the existing PHP code would I actually insert your code?

Posted: Tue Aug 10, 2004 10:18 am
by feyd

Code: Select all

$where_clause += "$properties[column] = '$properties[search_value]' AND ";
you're going to want

Code: Select all

$where_clause .= "$properties[column] = '$properties[search_value]' AND ";

Posted: Tue Aug 10, 2004 10:39 am
by WLW
What is the difference between "+=" and ".="?

Posted: Tue Aug 10, 2004 11:25 am
by pickle
feyd wrote:

Code: Select all

$where_clause += "$properties[column] = '$properties[search_value]' AND ";
you're going to want

Code: Select all

$where_clause .= "$properties[column] = '$properties[search_value]' AND ";
Oh yah, whoops :oops:

+= adds a number, where as .= adds a string

Code: Select all

$number = 1;
$text = one;

$number += 2;
$text .= "two";
//$number now == 3
//$test now == onetwo
Other than that little snafoo, your code seems correct. Your correct in thinking that rtrim removes the trailing OR, Manual: [php_man]rtrim[/php_man]

I don't know where you'd put it in your code, it could go pretty much anywhere really, but I'd put it close to your actual call so the code layout makes sense.

Posted: Tue Aug 10, 2004 12:29 pm
by WLW
I added the script, and got the following error:

Parse error: parse error, unexpected '=', expecting ')' in c:\inetpub\wwwroot\hp4-php\TMP1djkf28r2m.php on line 51

Line 51 contains:

Code: Select all

"search_value"="sag1");
How do I correct this error?