Select using multiple dropdown boxes problem

adwfun

Hi, I setup 5 dropdown boxes for user to search data using them individually or combinations of two or more, upto all 5 parameters.

This/these parameter(s) is/are then "$_POST" to a mysql_query statement as follows:

$s1 = $POST["dropdown1"];
$s2 = $POST["dropdown2"];
$s3 = $POST["dropdown3"];
$s4 = $POST["dropdown4"];
$s5 = $_POST["dropdown5"];

$query=mysql_query( "Select * from Table Where field1='$s1' and field2='$s2' and field3='$s3' and field4='$s4' and field5='$s5' ");

I know this will give me result which fits all 5 parameters only. But I want to get result according to whichever dropdown boxes selected (may be any 1, 2, 3, 4 or all 5) by user.

Anyone has any idea how to rewrite the "Select" statement ?

Please Help !!

big_nerd

adwfun,

I Recommend You have change the HTML a bit, I had this same problem before, I resolved by changing my drop down menu's a bit.

<select name="test_box">
<option value="0" selected="selected">Select</option>
<option>Option One</option>
<!-- or you can use -->
<option value="1">Option 1</option>
<!-- .... -->
</select>

When you get it into the PHP Side of things, I would recommend using an IF statement to see if the value is 0 and build your query that way.

$query = "SELECT * from table WHERE ";
if ($_POST['value1'] != 0) {
     $query = $query."field1 = '$s1'," ;
} 
// Continue on with all..

$query = substr_replace($query,strlen($query),'',-1);

I may be mistaken with the substr_replace (I don't use it often) but it should take the last comma out.

You can then execute your query and it will only select IF it doesn't equal 0 (which will be the first option of your drop down menus)

Hope this helps..

big.nerd

adwfun

Thanks big.nerd.

But what do you mean by continue on with all...

Do you mean I have to write out a IF statement for each possible combinations of dropdown selected ?

Something like this :

if ($s1 != 0) {
$query = $query."field1 = '$s1'," ;
}

if ($s1 && $s2 != 0) {
$query = $query."field1 = '$s1' and field2 = '$s2'" ;
}

etc...

It must be at least 20+ different combinations here (i.e $s1, $s2, ... , $s1+$s2, $s1+$s3.... $s1+$s2+$s3, $s1+$s2+$s4..... etc. etc. etc.) and corresponding If statements.

Any idea to avoid such long scripts...

thanks again...

big_nerd

adwfun,

No I don't mean code for each possible combination.

Since Drop down boxes have only 1 "answer" dispite the fact they can have a HUGE list.

What I recommended is this; if you have 106 drop boxes (for 106 columns for instance) you would have to do the if statement 106 times.

You don't have to use 0 for the variable.. you can do it like this..

<select name="test_box">
<option value="XXXX" selected="selected">Select</option>
<option>Option One</option>
<!-- or you can use -->
<option value="1">Option 1</option>
<!-- .... -->
</select>

And the option labelled "Select" will carry the value of XXXX, if it is XXXX, then you won't have to insert that into the query.

What the script does is this; it starts with a base query:

$query = "SELECT * from table WHERE ";

This is the static portion of the query, it doesn't need to change based on the options the user has selected.

if ($_POST['value1'] != 0) { 
     $query = $query."field1 = '$s1'," ; 
}

This is just an example, it is of course vulnerable to SQL injection, but I am making it simple to understand the process.

Try This (below):

<!-- Test.html-->
<body>
<form id="optFrm" name="optFrm" method="post" action="optform.php">
  <p>
    <select name="op1">
      <option value="XXXX" selected="selected">Select</option>
      <option>Howdie Doodie</option>
      <option>Special Day</option>
      <option>big.nerd rocks</option>
    </select>
  </p>
  <p>
    <select name="op2">
      <option value="XXXX" selected="selected">Select</option>
      <option>Howdie Doodie</option>
      <option>Special Day</option>
      <option>big.nerd rocks</option>
            </select>
  </p>
  <p>
    <select name="op3">
      <option value="XXXX" selected="selected">Select</option>
      <option>Howdie Doodie</option>
      <option>Special Day</option>
      <option>big.nerd rocks</option>
        </select>
  </p>
  <p>
    <select name="op4">
      <option value="XXXX" selected="selected">Select</option>
      <option>Howdie Doodie</option>
      <option>Special Day</option>
      <option>big.nerd rocks</option>
        </select>
  </p>
  <p>
    <select name="op5">
      <option value="XXXX" selected="selected">Select</option>
      <option>Howdie Doodie</option>
      <option>Special Day</option>
      <option>big.nerd rocks</option>
        </select>
  </p>
  <p>
    <select name="op6">
      <option value="XXXX" selected="selected">Select</option>
      <option>Howdie Doodie</option>
      <option>Special Day</option>
      <option>big.nerd rocks</option>
        </select>
</p>
</form>
</body>

Then the code for: optform.php


$op1 = $_POST['op1'];
$op2 = $_POST['op2'];
$op3 = $_POST['op3'];
$op4 = $_POST['op4'];
$op5 = $_POST['op5'];
$op6 = $_POST['op6'];

$found = 0;


$query = "SELECT * from table WHERE ";

if ($op1 != "XXXX") { 
     $query = $query."field1 = '$op1'," ; 
     $found++;
} 
if ($op2 != "XXXX") { 
     $query = $query."field1 = '$op1'," ; 
     $found++;
} 
if ($op3 != "XXXX") { 
     $query = $query."field1 = '$op1'," ; 
     $found++;
} 
if ($op4 != "XXXX") { 
     $query = $query."field1 = '$op1'," ; 
     $found++;
} 
if ($op5 != "XXXX") { 
     $query = $query."field1 = '$op1'," ; 
     $found++;
} 
if ($op6 != "XXXX") { 
     $query = $query."field1 = '$op1'," ; 
     $found++;
} 


if ($found > 0) {
    $query = substr_replace($query,strlen($query),'',-1); 
    echo "The Query is: $query";
} else {
    echo "You left all of them at select! How Come??";
}

Try using different changes in the Drop Down Boxes, and see how it changes the query.

big.nerd

adwfun

I got you now. Big.nerd. Much Thanks.

But I need to add a GROUP BY and ORDER BY at the end of Select statement. I tried the following but query not working, any idea what's wrong with the syntax ?

$s1 = $POST["dropdown1"];
$s2 = $POST["dropdown2"];
$s3 = $POST["dropdown3"];
$s4 = $POST["dropdown4"];
$s5 = $_POST["dropdown5"];

$query = "Select * from table1 t1, table t2, table t3 Where t1.FieldID=t2.FieldID and t2.primaryID=t3.primaryID";

if($s1 !="select") { $query = $query." and t1.Field1='$s1' " };
if($s2 !="select") { $query = $query." and t2.Field2='$s2' " };
if($s3 !="select") { $query = $query." and t3.Field3='$s3' " };
if($s4 !="select") { $query = $query." and t3.Field4='$s4' " };
if($s5 !="select") { $query = $query." and t3.Field5='$s5' " };

$query= $query." Group By t1.FieldID Order By t1.FieldName";

$sql=mysql_query($query);

while($result=mysql_fetch_array($sql)) {
echo $result[........].......
}

Bunkermaster

output the SQL and test it in your console before trying to sort it in PHP. You are debugging 2 languages at the same time.
Also for those cases I suggest you use arrays in your dropdown names with the field name or an easy key

... name="dropdown[field1]" ...
... name="dropdown[field2]" ...

which allows you to factorize your code and avoid repeating the same code over and over again with only marginal changes (making maintainance a lot easier)

...
foreach( $_POST[dropdown] as $fieldname => $fieldfilter ){
    $query .= " and $fieldname='$fieldfilter' \n";
}
// debugging
echo "<pre>$query</pre>";
// debugging
...

note the \n giving you line number errors when you paste the SQL in phpmyadmin.

if you want multiple tables just do a name="dropdown[table1_field1]" and explode.