Drop down question

[27groo27]

New at this. This code below which I have included the relevant snippets from generates for me 2 drop down boxes based on 2 fields from a table. Basically the code is working but there is still a major problem. The first drop down box allows the user to pick a value from table1 for that field (field1) and then when user clicks submit, they see matching rows where field1 has the value selected. Likewise, the second drop down box is doing the same for field2. The problem is that the first drop box shows not only the values for field1 but also all the values for field2. Ideally it should only contain the values for field1. The same problem applies for the second drop down box.

php

$sql = "SELECT Distinct Field1 FROM Table1 Order by Field1
";

$sql_result = mysql_query($sql,$conx);
while ($row = mysql_fetch_array($sql_result)) {
$Field1 = $row["Field1"];
$option_block .= "<OPTION value=\"$Field1\">$Field1</OPTION>";
}

$xyz = "SELECT Distinct Field2 FROM Table1 Order by Field2
";
$xyz_result = mysql_query($xyz,$conx);

while ($a_row = mysql_fetch_array($xyz_result)) {

$Field2 = $a_row["Field2"];

$option_block .= "<OPTION value=\"$Field2\">$Field2</OPTION>";
}

?>

<FORM method="get" action="getinfo.php">
<P>Field1br>
<SELECT name="Field1">
<? echo "$option_block"; ?>
</SELECT>
<P><INPUT type="submit" value="submit"></p>
</FORM>

<FORM method="get" action="getinfo.php">
<P>Field2br>
<SELECT name="Field2">
<? echo "$option_block"; ?>
</SELECT>
<P><INPUT type="submit" value="submit"></p>
</FORM>
</BODY> </HTML>


getinfo.php:

<?php

$sql = "SELECT * FROM Table1 WHERE Field1 = '". $_GET['Field1'] ."'
OR Field2 = '". $_GET['Field2'] ."'
";


Thanks for help

[jetboy]

That's because you're looping through Field1 and adding the options to the variable $option_block, and then looping through Field2 and adding the options to $option_block. At the end of this, $option_block contains the options for both fields. You then display this same variable in both dropdowns.

No. Use $option_block_1 for anything relating to Field1, and $option_block_2 for anything related to Field2.

[27groo27]

Thanks. Different question: Is there any way to set it up so user selects values for both Field1 and Field2, and there would be only 1 submit button which would pass both of those values selected?

[jetboy]

Only have one form on your page, with both the selects inside it, and only one submit button. This will work with your existing getinfo.php

[27groo27]

Ok thanks. If getinfo.php were to be set up as an AND condition:
$sql = "SELECT * FROM Table1 WHERE Field1 = '". $_GET['Field1'] ."'
AND Field2 = '". $_GET['Field2'] ."'
";



not an OR conditon, I'm guessing the rows selected would be those where both values from the two drop down boxes are met;this might be a desirable search for a user; but then, if this is so, how to handle the problem where the user only selects from the Field1 drop down box, and doesn't make any Field2 choice;then it seems the results shown would not even be all the rows from the Field1 value chosen. It would be nice to have a solution where the user could choose values from both Field1 and Field2 ,or if they prefer from just Field1 or Field2. Thanks for help.