Page 1 of 1

Cascading Drop Downs

Posted: Tue Oct 27, 2009 11:00 pm
by edawson003
I want to implement a cascading down menu relationship between two drop down menus. I found this javascript code out there which seems to work ok, but I would like to incorporate php such that the values available in the drop downs are pulled from my mysql table.

Here's the HTML, Javascript and PHP code I pulled from the net:

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en"><head><meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
<title></title>
<script type="text/javascript">
var makes = new Array("BMW", "Ford");
var models = new Array();
models["BMW"] = new Array("318", "525", "650", "X5");
models["Ford"] = new Array("Bronco", "Explorer", "Focus");
function resetForm(theForm) {  /* reset makes */  theForm.makes.options[0] = new Option("Please select a make", "");  
for (var i=0; i<makes.length; i++) {    theForm.makes.options[i+1] = new Option(makes[i], makes[i]);
  }  theForm.makes.options[0].selected = true;  /* reset models */  theForm.models.options[0] = new Option("Please select a model", ""); 
   theForm.models.options[0].selected = true;
   }function updateModels(theForm) {  var make = theForm.makes.options[theForm.makes.options.selectedIndex].value;
     var newModels = models[make];
       theForm.models.options.length = 0;
         theForm.models.options[0] = new Option("Please select a model", "");
           for (var i=0; i<newModels.length; i++) {    theForm.models.options[i+1] = new Option(newModels[i], newModels[i]);
             }  theForm.models.options[0].selected = true;}
             </script></head>
             <body>
             <form name="autoSelectForm" action="" method="post">
             <select size="1" name="makes" onchange="updateModels(this.form)"></select>
             <select size="1" name="models"></select><input type="submit"></form>
             <script type="text/javascript">  
             resetForm(document.autoSelectForm);
             </script>
             <?php  $make = $_POST['makes'];
               $model = $_POST['models'];
                 if ($make && $model) {
                     echo "<p>".$make." - ".$model."</p>";  
                     }?>
drop-pict.jpg
drop-pict.jpg (26.31 KiB) Viewed 100 times

You can pretty much just copy/paste the above and generate a linked set of drop downs.

Here's the php code/mysql queries I'd like to use to generate the values in teh above code:

Code: Select all

<?
                     $musclegrpquery = "SELECT DISTINCT REPLACE(B.optiongroupname,'muscle_','') AS optiongroupname FROM `MuscleMatrix` AS A JOIN `OptionGroup` AS B ON B.optiongroupid = A.musclegrpid ORDER BY B.optiongroupname";
                     $mg_result = mysql_query($musclegrpquery); 
                     if (!$mg_result) 
                      { 
                     die("Could Not Query Database: <br />".mysql_error()); 
                       }                     
                     while ($row = mysql_fetch_array($mg_result)){
                     $muscl_grp =$row['optiongroupname'];
                     echo "$muscl_grp<br>";
                     }  
                     echo "<br>";                
                     ?>
The above code generates:
arms
back
chest
shoulder

Code: Select all

<?
                     $musclequery = "SELECT muscleid, musclename, REPLACE(B.optiongroupname,'muscle_','') AS optiongroupname FROM `MuscleMatrix` AS A JOIN `OptionGroup` AS B ON B.optiongroupid = A.musclegrpid ORDER BY B.optiongroupname";
                     $ms_result = mysql_query($musclequery);
                     if (!$ms_result) 
                      { 
                     die("Could Not Query Database: <br />".mysql_error()); 
                       }                     
                     while ($row = mysql_fetch_array($ms_result)){
                     $muscleid =$row['muscleid'];
                     $muscl_grp =$row['optiongroupname'];
                     $musclename =$row['musclename'];
                     echo "$muscl_grp - $muscleid - $musclename<br>";
                     }
                     ?>
                     </body></html>
The above code generates:
arms - 5 - long head of the biceps
arms - 6 - short head of the biceps
arms - 7 - brachialis
arms - 8 - brachioradialis
back - 3 - latissimus dorsi
back - 4 - upper trapezius
back - 9 - middle trapezius
back - 10 - lower trapezius
chest - 1 - pectoralis major
chest - 2 - pectoralis minor
shoulder - 11 - anterior (front) deltoid
shoulder - 12 - lateral (middle) deltoid
shoulder - 13 - Posterior (Rear) Deltoid

Admittedly, I am at a loss with Javascript, so I guess I need to get familiar, but aside from that, I not sure where to begin integrating the javascript and php to make this work. Any suggestions would appreciated. :)

Re: Cascading Drop Downs

Posted: Sat Oct 31, 2009 10:26 am
by edawson003
I took some hours to crack the code :) , but I figured it out.

Code: Select all

 
 
<?
include("../database.php");
?>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en"><head><meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
<title></title>
<?
/* echo first part of javascript */              
echo "<script type=text/javascript>
  var musclegrps = new Array(
       ";              
                     [color=#0000FF]/* Q1BEGIN - Query for the last muscle group in the table by alphabetical order with a PHP array and while loop */[/color]
                     $lstmsclegrpquery = "SELECT MAX(B.optiongroupname) AS optiongroupname, MAX(REPLACE(B.optiongroupname,'muscle_','')) AS loptiongroupname FROM `MuscleMatrix` AS A JOIN `OptionGroup` AS B ON B.optiongroupid = A.musclegrpid ORDER BY B.optiongroupname";
                     $lsmg_result = mysql_query($lstmsclegrpquery);
                     while ($row = mysql_fetch_array($lsmg_result)){
                     $lsmuscl_grp = $row['optiongroupname'];
                     [color=#0000FF]/* this variable is set to store the muscle group name with 'muscle_' prefix stripped out -- see above select statement */[/color]                     $llsmuscl_grp = $row['loptiongroupname'];
                     }  
                     [color=#0000FF]/* Q1END */[/color]
                     
                     [color=#0000FF]/* Q2BEGIN - Query for muscle groups in the table by alphabetical order accept the last muscle group established in the above query with a PHP array and while loop */[/color]
                     $musclegrpquery = "SELECT DISTINCT REPLACE(B.optiongroupname,'muscle_','') AS optiongroupname FROM `MuscleMatrix` AS A JOIN `OptionGroup` AS B ON B.optiongroupid = A.musclegrpid WHERE B.optiongroupname != '$lsmuscl_grp' ORDER BY B.optiongroupname";
                     $mg_result = mysql_query($musclegrpquery); 
                     if (!$mg_result) 
                      { 
                     die("Could Not Query Database: <br />".mysql_error()); 
                       }                     
                     while ($row = mysql_fetch_array($mg_result)){
                     $muscl_grp = $row['optiongroupname'];
                     
                     [color=#0000FF]/* Q2COMMENT - Below echo statements purposely includes quotes as they are required syntax in javascript */[/color]
                     echo "\"";
                     echo "$muscl_grp";
                     echo "\"";
                     
                     [color=#0000FF]/* Q2COMMENT - Notice comma is included in the below echo loop */[/color]
                     echo ",";
                     } 
                     
                     [color=#0000FF]/* Q2COMMENT - Below echo statements includes the last muscle group to be included in the javascript array. */[/color]
                     [color=#0000FF]/* Being the last item in the array, a comma cannot be included in echo group */[/color]
                     echo "\"";
                     echo "$llsmuscl_grp";   
                     echo "\");\n";
                     [color=#0000FF]/* Q2END */[/color]
                     ?>
<?
[color=#0000FF]/* Echoing a one liner piece of javascript code */[/color]
echo "var muscles = new Array();\n";
 
                     [color=#0000FF]/* Q3BEGIN - another query, PHP array and while loop for muscle group to build out this javascript component: muscles["muscle group"] = new Array */[/color]
                     $lstmsclegrpquery = "SELECT DISTINCT B.optiongroupname, REPLACE(B.optiongroupname,'muscle_','') AS loptiongroupname FROM `MuscleMatrix` AS A JOIN `OptionGroup` AS B ON B.optiongroupid = A.musclegrpid ORDER BY B.optiongroupname";
                     $lsmg_result = mysql_query($lstmsclegrpquery);
                     while ($row = mysql_fetch_array($lsmg_result)){
                     $lsmuscl_grp = $row['optiongroupname'];
                     $llsmuscl_grp = $row['loptiongroupname'];
                     echo "muscles[\"";
                     echo "$llsmuscl_grp";
                     echo "\"]";
                     echo " = new Array(";
                     [color=#0000FF]/* Q3END */[/color]
                     
                     [color=#0000FF]/* The below lines of code Q4 and Q5 are for building out the second piece of the javascript component: ("some muscle name","brachioradialis","long head of the biceps","short head of the biceps"); */[/color]
                     
                     [color=#0000FF]/* Q4BEGIN - Query, PHP array and loop to establish last muscle within each muscle group within table. Similar to Q1 above */[/color]
                     $lstloopquery = "SELECT MAX(musclename) AS musclename FROM `MuscleMatrix` AS A JOIN `OptionGroup` AS B ON B.optiongroupid = A.musclegrpid WHERE B.optiongroupname = '$lsmuscl_grp' ORDER BY A.musclename";
                     $lstloop_result = mysql_query($lstloopquery);
                     while ($row = mysql_fetch_array($lstloop_result)){
                     $lstmuscl_names = $row['musclename'];                   
                     }
                    [color=#0000FF] /* Q4END */[/color]                     
                     
                     [color=#0000FF]/* Q5BEGIN Query for muscle in the table by alphabetical order accept the last muscle established in the above query with a PHP array and while loop */[/color]
                     $loopquery = "SELECT DISTINCT musclename FROM `MuscleMatrix` AS A JOIN `OptionGroup` AS B ON B.optiongroupid = A.musclegrpid WHERE B.optiongroupname = '$lsmuscl_grp' AND A.musclename != '$lstmuscl_names' ORDER BY A.musclename";
                     $loop_result = mysql_query($loopquery);
                     while ($row = mysql_fetch_array($loop_result)){
                     $muscl_names = $row['musclename'];
                     echo "\"";                 
                     echo $muscl_names;
                     echo "\"";
                     echo ",";                   
                     } 
                     /* Q5COMMENT - Below echo statements includes the last muscle to be included in the javascript array. */
                     /* Being the last item in the array, a comma cannot be included in echo group */
                     echo "\"";                     
                     echo $lstmuscl_names;
                     echo "\""; 
                     echo "); ";
                     [color=#0000FF]/* Q5END */[/color]
                     
     [color=#0000FF]/*
     Q4 and Q5 are set up build the below bits of javacode dynamically based on what is available in the table
     muscles["arms"] = new Array("brachialis","brachioradialis","long head of the biceps","short head of the biceps"); 
     muscles["back"] = new Array("latissimus dorsi","lower trapezius","middle trapezius","upper trapezius");
     etc. etc. etc. */ [/color]                         
                     }
 
echo "
[color=#0000FF]/* just echoing the rest of the javascript code */[/color]
function resetForm(theForm) {  /* reset musclegrps */  theForm.musclegrps.options[0] = new Option(\"Select a muscle group\", \"\");
for (var i=0; i<musclegrps.length; i++) {    theForm.musclegrps.options[i+1] = new Option(musclegrps[i], musclegrps[i]);
  }  theForm.musclegrps.options[0].selected = true;  /* reset muscles */  theForm.muscles.options[0] = new Option(\"Select...\", \"\");
   theForm.muscles.options[0].selected = true;
   }function updateMuscles(theForm) {  var musclegrp = theForm.musclegrps.options[theForm.musclegrps.options.selectedIndex].value;
     var newMuscles = muscles[musclegrp];
       theForm.muscles.options.length = 0;
         theForm.muscles.options[0] = new Option(\"Select...\", \"\");
           for (var i=0; i<newMuscles.length; i++) {    theForm.muscles.options[i+1] = new Option(newMuscles[i], newMuscles[i]);
             }  theForm.muscles.options[0].selected = true;}
             </script>
             ";
             ?>
             </head>
             <body>
             <form name="autoSelectForm" action="" method="post">
             <select size="1" name="musclegrps" onchange="updateMuscles(this.form)"></select>
             <select size="1" name="muscles"></select><input value="Submit" type="submit"></form>
             <script type="text/javascript">  
             resetForm(document.autoSelectForm);
             </script>
             <?php  $musclegrps = $_POST['musclegrps'];
               $model = $_POST['muscles'];
                 if ($musclegrps && $model) {
                     echo "<p>".$musclegrps." - ".$model."</p>";  
                     }?>
 
Not half bad I say, but if anyone knows of a more streamlined approach be my guest.

For the muscle drop down part, the above basically sets up:

<select>
<option>Muscle1</option>
<option>Muscle2</option>
<option>Muscle3</option>
<option>Muscle4</option>
</select>


However, I rather also also pull in each muscleid from my table and set it as the value for each option, like so:

<select>
<option value="34">Muscle1</option>
<option value="23">Muscle2</option>
<option value="12">Muscle3</option>
<option value="5">Muscle4</option>
</select>


Not sure how to incorporate that.