Chained Selects with PHP, MySQL, and JS

[rprincejr]

I am trying to create a form that has 3 drop down menus. I have gotten it partially coded but am now stuck and I dont undertand where to go from here. The 1st menu is to choose a Division. After choosing a Division, you goto the 2nd menu which is Tournaments in that Division. Lastly, you choose Teams, which are Teams loacted in the Division. So basically, The Parent is Divisions and it has two child drop downs, the Tournaments and the Teams. Any help is appreciated. Here is my code.

    // Javascript for dynamic dropdown menu
    ?>
    <script>
        var aDetail = new Array();
        function categSelected(theSel){
        theForm = theSel.form;
        opt = theForm.SubCategory.options;
        opt.length = 0;
        if(theSel.value=="") {
        opt[0] = new Option(" - Select a Tournament - ", "");
                return;
        }
        for(i=0;i<aDetail.length;i++){
        if(aDetail[i][2]==theSel.value){
                subCategoryValue = aDetail[i][1];
        for(j=0;j<opt.length;j++){
        if(opt[j].value==subCategoryValue) subCategoryValue="";
        }
      i f(subCategoryValue>""){
        opt[opt.length] = new Option(subCategoryValue, subCategoryValue);
        }
        }
        }
        }
    </script>
    <?
    echo "<center><font class=\"title\"><b>Tournament Results Administration</b></font></center>"
        ."<br>"
        ."<table width=\"90%\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">"
        ."<tr><td>"
        ."</td><td align=\"right\">"
        ."<a href=\"admin.php?op=tournaments\">[Back to Tournaments Main]</a>"
        ."</td></tr>"
        ."</table>";

$trow = $db->sql_numrows($db->sql_query("select * from " . $user_prefix . "_tourn_schedules"));

echo "<table width=\"90%\" cellspacing=\"0\" cellpadding=\"0\" border=\"0\" align=\"center\">"
    ."<tr><td colspan=\"2\">"
    ."<br><hr>"
    ."<p><font class=\"content\"><center><b>Add New Tournament Results</b><br><br></font></center>"
    ."<align=\"left\"><form method=\"post\" action=\"admin.php\">"
    ."<b>Choose a Division:</b><br><select name=\"div_id\" onchange=categSelected(this);><option> - Select a Division - </option>";

    $query = "SELECT div_id, div_name FROM " . $user_prefix . "_tourn_divisions ORDER BY div_name ASC";
    $query_result = mysql_query($query);
    $i = 0;
    $j = 0;
    $division_name = array();
    $division_value = array();
while($row = mysql_fetch_array($query_result)) {
      $division_value[$j] = $row['div_id'];
      $division_name[$j] = $row['div_name'];
      $j++;
  }
?>
        <script>
        aDetail[<?=$i?>] = new Array();
        aDetail[<?=$i?>][0] = "<?=$row['div_id']?>";
        aDetail[<?=$i?>][1] = "<?=$row['div_name']?>";
        </script>
<?
    $i++;
    for($t=0; $t<count($division_value); $t++) {
    echo "<option value='".$division_value[$t]."'>".$division_name[$t]."</option>";
  }
    echo "</select></align><br><br></td></tr><tr><td>";
    echo "<b>Choose a Tournament:</b><br><select name=\"SubCategory\" ><option> - Select a Tournament - </option>";

    $query2 = "SELECT sched_id, div_id sched_date FROM " . $user_prefix . "_tourn_schedules WHERE div_id='$row[div_id]' ORDER BY sched_date ASC";
    $query_result2 = mysql_query($query2);
    while ($row2 = mysql_fetch_array ($query_result2)) {
            echo "<option value=\"$row2[sched_id]\">$row2[sched_date]</option>";
    }
echo "</select></align><br><br></td></tr><tr><td>";
echo "<b>Choose a Team:</b><br><select name=\"team_id\" ><option> - Select a Team - </option>";

    $query3 = "SELECT team_id, div_id, partner1_firstname, partner1_lastname, partner2_firstname, partner2_lastname FROM " . $user_prefix . "_tourn_teams WHERE div_id='$row[div_id]' ORDER BY team_id ASC";
    $query_result3 = mysql_query($query3);
    while ($row3 = mysql_fetch_array ($query_result3)) {
            echo "<option value=\"$row3[team_id]\">$row3[partner1_firstname]&nbsp;$row3[partner1_lastname]&nbsp;/&nbsp;$row3[partner2_firstname]&nbsp;$row3[partner2_lastname]</option>";
    }
echo "</select></align><br><br></td></tr>";

[paulnaj]

Hi r,

Erm, it's a bit of a difficult question to answer. There are a few different ways, all of them quite involved, to do this ... depending on your 'style'.

One thing I noticed is that your sort of doing a 'half and half' job with javascript and php. I would suggest that you should try using one or the other until you're comfortable enough with both to be able to mix at will.

If you decide to go down the 'all php' route then I would suggest thinking in terms of 'states' and 'messages' ... for example:

=================================
State 1: Division not yet chosen
=> Show Division Menu, Hide other menus

State 2: Division chosen, Tournament & Teams not yet chosen
=> Show Division Choice and other menus

State 3: Division, Tournament chosen, Teams not yet chosen
=> Show Division Choice, Tournament Choice and Teams menu

State 4: Division, Teams chosen, Tournament not yet chosen
=> Show Division Choice, Teams Choice and Tournament menu

State 5: All chosen
=> Show Division Choice, Teams Choice and Tournament Choice

=================================
Then, you can think of the variables passed via a form or a link as 'messages' to select the correct state.

I won't go any further at the mo' ... you might not want to go down this road.

Paul 🙂

[rprincejr]

Will your 'style' create an auto-updated page? What I mean is will it require choosing the first menu and then the info is sent to a second page containing the next info? Sort of like a handler script. Or is your style going to auto-update the menus by a quick page refresh. I was under the impression that it took javascript to refresh the page and not use a handler script to do what I want.

Thanks for the help so far. I look forward to figuring this out so my knowledge of php is wider than when I started.