Page 1 of 1

Drop down menus

Posted: Mon May 13, 2002 3:15 pm
by Crashin
I'm trying to create a form where the user can select an 'application' in one drop down menu and then a 'category' in the next drop down and I want the 'category' menu generated based on the selection made for the 'application.'

Each list is gathered from two separate tables, and I know how to code the drop downs to get the correct items. My question is how do I initiate a query for the 'categories' drop down after the application has been selected? Can JavaScript handle a PHP function, or is there another way?

Posted: Mon May 13, 2002 8:11 pm
by volka
You may use the onChange-property of the <select>-element.
<select onChange="myFunc()">.....</select>
myFunc is called as handler for every change of the current <option>-element. Use it to evaluate the current value or selectedIndex.
From myFunc you may submit the form to load the updated data or -if you've transfered all the data- replace all <option>-elements of the 'category'-selection.
i.e. (not tested at all :) ):

Code: Select all

...
function changedApp()
&#123;
 document.forms&#1111;0].hType.value="changedApp";
 document.forms&#1111;0].submit();
 return true;
&#125;
...
<form>
  <input type="hidden" name="hType" id="hType" value="none"/>
  <select name="app" onChange="changedApp()"><option>app A</option><option>app B</option></select>
	<input type="submit"/>
</form>.....
each time the user changes the app-selection your php-script will be requested with $_REQUEST['hType'] set to 'changedApp'

Posted: Mon May 13, 2002 8:40 pm
by Crashin
Thanks for the tip! I'll give it a try. :)

Posted: Mon May 20, 2002 11:20 am
by Crashin
Well, I tried the above but still haven't come up with a good solution. I'm able to call a JavaScript function using the onChange event handler, however I'm still struggling with populating the second drop-down list with options associated with the selection made by the first. Does anyone have any other ideas/input?

Posted: Mon May 20, 2002 11:28 am
by cwcollins
I'm sure i saw PHP to generate the JS. i think this might be it, but if not, check at http://www.phpclasses.org.

http://www.phpclasses.org/browse.html/package/158.html

-c.w.collins

Posted: Tue May 21, 2002 4:09 pm
by Crashin
Thanks for the post, cw. I'm still struggling with this one, though. Does anyone know of a way to just refresh the page with the first selected option getting passed as a variable that I can then perform my second select list query on? I've seen this done elsewhere on the web (of course, I can't find those pages now)...

Posted: Wed May 22, 2002 3:50 am
by mikeq
Okay, create this function

Code: Select all

<?php
function populate_dependent_list($conn,$QrySecondaryOptions,$querymaxentries,$primaryid,$secondaryid,$secondarycolumn,$formname,$primaryname,$secondaryname,$firstoption,$value,$event="",$font=array("form","form","8pt"))
{
/*
This function will dynamically populate a dropdown list (the SECONDARY select) depending on the selection made in another dropdown list (the PRIMARY select).

Firstly a primary drop down list must be created. The only stipulation with this picklist (list1) is that it must have an onChange event.
The onChange event must be of the form Updatelist2();Updatelist3();....Updatelistx(); , where list2 is the name of
the list that is directly changed by list1, list3 is the list changed by list2 and so up to listx which is changed by listx-1

The following parameters should be passed to the function:-
$conn - A valid database connection
$QrySecondaryOptions - A query which returns the names and values of the entries for the secondary list with the corresponding
                       names and values from the primary list
$querymaxentries - A query which counts the maximum number of secondary list values corresponding to primary list values
$primaryid - The column that contains the values for the primary list
$secondaryid - The column that contains the values for the secondary list
$secondarycolumn - The column that contains the text options for the secondary list
$formname - The name of the form that the list are in
$primaryname - The name of primary list
$secondaryname - The name of secondary list
$firstoption - The text to display before any choice is made
$value - The variable that holds the current value of the selected item in the secondary list
$event - Call the functions to update all lists lower in the chain than this one (similar to the top level
         onChange event but without calling functions that update lists higher in the heirarchy.
*/
  $i=0;
/*$QrySecondaryOptions = "select p.$primarytable ,s.$secondarytable,s.$secondaryid,p.$primaryid";
$QrySecondaryOptions .= " from $secondarytable s,$primarytable p, $primarysecondarytable ps";
$QrySecondaryOptions .= " where ps.$psprimaryid = p.$primaryid";
$QrySecondaryOptions .= " and ps.$pssecondaryid = s.$pssecondaryid order by p.$primarycolumn";
  */
/*$querymaxentries = "select max(count(*)) MAXITEMS from $primarysecondarytable group by $psprimaryid";*/

$SecondaryOptions = ociparse($conn,$QrySecondaryOptions);
$getmaxentries = ociparse($conn  ,$querymaxentries);



$i=0;
ociexecute($SecondaryOptions,OCI_DEFAULT);
while (ocifetch($SecondaryOptions))
{
 $secondary_primary[$i] = ociresult($SecondaryOptions,$primaryid);
 $secondary_name[$i] = ociresult($SecondaryOptions,$secondarycolumn);
 $secondary_id[$i] = ociresult($SecondaryOptions,$secondaryid);
 $i++;
}

$numSecondarys  =count($secondary_name);

ociexecute($getmaxentries,OCI_DEFAULT);
while (ocifetch($getmaxentries))
{
 $MaxItems = ociresult($getmaxentries,MAXITEMS);
 $i++;
}

echo  "<SCRIPT  Language="JavaScript">\n";
echo  "function  Update$secondaryname()\n";
echo  "{\n";
/*  clear  the  $secondaryname  selector  */
echo  "\tvar  Sec  =  document.$formname.$secondaryname.options.length;\n";
echo  "\twhile(Sec  >  0)\n";
echo  "\t{\n";
echo  "\t\tSec--;\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].text  =  '';\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].value  =  '';\n";
echo  "\t}\n\n";
echo  "\tdocument.$formname.$secondaryname.options[0].selected  =  true;\n\n";

/*  figure  out  which  function  is  selected*/
echo  "\tvar  PrimarySelected  =  0;\n";
echo  "\twhile(document.$formname.$primaryname.options[PrimarySelected].selected==false)  PrimarySelected++;\n\n";

$SecondaryPrimary  =  "";
$SecondaryCount  =  0;

$RowCount  =  0;
while($RowCount  <  $numSecondarys)  {
      $secondary_ID  =  $secondary_id[$RowCount];
      $secondary_Primary  =  $secondary_primary[$RowCount];
      $secondary_Name  =  $secondary_name[$RowCount];

      if  ($SecondaryPrimary  !=  $secondary_Primary)  {
            if  ($SecondaryPrimary  !=  "")  {
                  echo  "\t}\n";
            }

            echo  "\tif(document.$formname.$primaryname.options[PrimarySelected].value  ==  '$secondary_Primary')\n";
            echo  "\t{\n";

            echo  "\t\tdocument.$formname.$secondaryname.options[0].value  =  0;\n";
            echo  "\t\tdocument.$formname.$secondaryname.options[0].text  =  '$firstoption';\n\n";


            $SecondaryPrimary  =  $secondary_Primary;
            $SecondaryCount =1;
      }

      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].value  =  '$secondary_ID';\n";
      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].text  =  '$secondary_Name';\n\n";
      print "\t\tif('$secondary_ID'=='$value')
                {document.$formname.$secondaryname.options[$SecondaryCount].selected = true;}\n";
      $SecondaryCount ++;
      $RowCount++;
}

echo  "\t}\n";

echo  "}\n</script>";
         //Print the HTML for the drop down
         print  "<td class=$font[1]><SELECT  style="font-size:$font[2]" NAME=$secondaryname value="$$secondaryname" size="1" width=200 onChange="$event" >\n ";
                  $RowCount  =  0;

                  while  ($RowCount  <=  $MaxItems)
                  {
                          print  "<OPTION  VALUE=$RowCount>";
                          print  "$firstoption \n";
                          $RowCount++;
                  }
         print  "</SELECT></td>\n";
  print "<SCRIPT  Language="JavaScript">Update$secondaryname();\n</SCRIPT>\n";

}

?>
you can call it from your PHP script like this

Code: Select all

<?php

$querydepartments = "select f.functional ,d.department,d.department_id,f.functional_id";
$querydepartments .= " from department d,functional f, function_department fd";
$querydepartments .= " where fd.functional_id = f.functional_id";
$querydepartments .= " and fd.department_id = d.department_id order by f.functional";

$querymaxdepartments = "select max(count(*)) MAXITEMS from function_department group by functional_id";

        populate_dependent_list($c1,$querydepartments,$querymaxdepartments,FUNCTIONAL_ID,DEPARTMENT_ID,DEPARTMENT,advanced_search,functionalid,departmentid,"Choose  a  Department","","Updatesectionid();");

?>
The first list is created with this function

Code: Select all

&lt;?php
  function populate_list($Caption,$name,$query,$conn,$column,$value,$default,$firstoption="",$font="",$event="")
{

        //parse the query and return statement
        $stmt = ociparse($conn,$query);
        // execute query and put results into $stmt
        ociexecute($stmt,OCI_DEFAULT);
        //create pfunction

Code: Select all

<?php
function populate_dependent_list($conn,$QrySecondaryOptions,$querymaxentries,$primaryid,$secondaryid,$secondarycolumn,$formname,$primaryname,$secondaryname,$firstoption,$value,$event="",$font=array("form","form","8pt"))
{
/*
This function will dynamically populate a dropdown list (the SECONDARY select) depending on the selection made in another dropdown list (the PRIMARY select).

Firstly a primary drop down list must be created. The only stipulation with this picklist (list1) is that it must have an onChange event.
The onChange event must be of the form Updatelist2();Updatelist3();....Updatelistx(); , where list2 is the name of
the list that is directly changed by list1, list3 is the list changed by list2 and so up to listx which is changed by listx-1

The following parameters should be passed to the function:-
$conn - A valid database connection
$QrySecondaryOptions - A query which returns the names and values of the entries for the secondary list with the corresponding
                       names and values from the primary list
$querymaxentries - A query which counts the maximum number of secondary list values corresponding to primary list values
$primaryid - The column that contains the values for the primary list
$secondaryid - The column that contains the values for the secondary list
$secondarycolumn - The column that contains the text options for the secondary list
$formname - The name of the form that the list are in
$primaryname - The name of primary list
$secondaryname - The name of secondary list
$firstoption - The text to display before any choice is made
$value - The variable that holds the current value of the selected item in the secondary list
$event - Call the functions to update all lists lower in the chain than this one (similar to the top level
         onChange event but without calling functions that update lists higher in the heirarchy.
*/
  $i=0;
/*$QrySecondaryOptions = "select p.$primarytable ,s.$secondarytable,s.$secondaryid,p.$primaryid";
$QrySecondaryOptions .= " from $secondarytable s,$primarytable p, $primarysecondarytable ps";
$QrySecondaryOptions .= " where ps.$psprimaryid = p.$primaryid";
$QrySecondaryOptions .= " and ps.$pssecondaryid = s.$pssecondaryid order by p.$primarycolumn";
  */
/*$querymaxentries = "select max(count(*)) MAXITEMS from $primarysecondarytable group by $psprimaryid";*/

$SecondaryOptions = ociparse($conn,$QrySecondaryOptions);
$getmaxentries = ociparse($conn  ,$querymaxentries);



$i=0;
ociexecute($SecondaryOptions,OCI_DEFAULT);
while (ocifetch($SecondaryOptions))
{
 $secondary_primary&#1111;$i] = ociresult($SecondaryOptions,$primaryid);
 $secondary_name&#1111;$i] = ociresult($SecondaryOptions,$secondarycolumn);
 $secondary_id&#1111;$i] = ociresult($SecondaryOptions,$secondaryid);
 $i++;
}

$numSecondarys  =count($secondary_name);

ociexecute($getmaxentries,OCI_DEFAULT);
while (ocifetch($getmaxentries))
{
 $MaxItems = ociresult($getmaxentries,MAXITEMS);
 $i++;
}

echo  "&lt;SCRIPT  Language="JavaScript"&gt;\n";
echo  "function  Update$secondaryname()\n";
echo  "{\n";
/*  clear  the  $secondaryname  selector  */
echo  "\tvar  Sec  =  document.$formname.$secondaryname.options.length;\n";
echo  "\twhile(Sec  &gt;  0)\n";
echo  "\t{\n";
echo  "\t\tSec--;\n";
echo  "\t\tdocument.$formname.$secondaryname.options&#1111;Sec].text  =  '';\n";
echo  "\t\tdocument.$formname.$secondaryname.options&#1111;Sec].value  =  '';\n";
echo  "\t}\n\n";
echo  "\tdocument.$formname.$secondaryname.options&#1111;0].selected  =  true;\n\n";

/*  figure  out  which  function  is  selected*/
echo  "\tvar  PrimarySelected  =  0;\n";
echo  "\twhile(document.$formname.$primaryname.options&#1111;PrimarySelected].selected==false)  PrimarySelected++;\n\n";

$SecondaryPrimary  =  "";
$SecondaryCount  =  0;

$RowCount  =  0;
while($RowCount  &lt;  $numSecondarys)  {
      $secondary_ID  =  $secondary_id&#1111;$RowCount];
      $secondary_Primary  =  $secondary_primary&#1111;$RowCount];
      $secondary_Name  =  $secondary_name&#1111;$RowCount];

      if  ($SecondaryPrimary  !=  $secondary_Primary)  {
            if  ($SecondaryPrimary  !=  "")  {
                  echo  "\t}\n";
            }

            echo  "\tif(document.$formname.$primaryname.options&#1111;PrimarySelected].value  ==  '$secondary_Primary')\n";
            echo  "\t{\n";

            echo  "\t\tdocument.$formname.$secondaryname.options&#1111;0].value  =  0;\n";
            echo  "\t\tdocument.$formname.$secondaryname.options&#1111;0].text  =  '$firstoption';\n\n";


            $SecondaryPrimary  =  $secondary_Primary;
            $SecondaryCount =1;
      }

      echo  "\t\tdocument.$formname.$secondaryname.options&#1111;$SecondaryCount].value  =  '$secondary_ID';\n";
      echo  "\t\tdocument.$formname.$secondaryname.options&#1111;$SecondaryCount].text  =  '$secondary_Name';\n\n";
      print "\t\tif('$secondary_ID'=='$value')
                {document.$formname.$secondaryname.options&#1111;$SecondaryCount].selected = true;}\n";
      $SecondaryCount ++;
      $RowCount++;
}

echo  "\t}\n";

echo  "}\n&lt;/script&gt;";
         //Print the HTML for the drop down
         print  "&lt;td class=$font&#1111;1]&gt;&lt;SELECT  style="font-size:$font&#1111;2]" NAME=$secondaryname value="$$secondaryname" size="1" width=200 onChange="$event" &gt;\n ";
                  $RowCount  =  0;

                  while  ($RowCount  &lt;=  $MaxItems)
                  {
                          print  "&lt;OPTION  VALUE=$RowCount&gt;";
                          print  "$firstoption \n";
                          $RowCount++;
                  }
         print  "&lt;/SELECT&gt;&lt;/td&gt;\n";
  print "&lt;SCRIPT  Language="JavaScript"&gt;Update$secondaryname();\n&lt;/SCRIPT&gt;\n";

}

?&gt;
you can call it from your PHP script like this

Code: Select all

<?php

$querydepartments = "select f.functional ,d.department,d.department_id,f.functional_id";
$querydepartments .= " from department d,functional f, function_department fd";
$querydepartments .= " where fd.functional_id = f.functional_id";
$querydepartments .= " and fd.department_id = d.department_id order by f.functional";

$querymaxdepartments = "select max(count(*)) MAXITEMS from function_department group by functional_id";

        populate_dependent_list($c1,$querydepartments,$querymaxdepartments,FUNCTIONAL_ID,DEPARTMENT_ID,DEPARTMENT,advanced_search,functionalid,departmentid,"Choose  a  Department","","Updatesectionid();");

?>
The first list is created with this function

Code: Select all

<?php
  function populate_list($Caption,$name,$query,$conn,$column,$value,$default,$firstoption="",$font="",$event="")
{

        //parse the query and return statement
        $stmt = ociparse($conn,$query);
        // execute query and put results into $stmt
        ociexecute($stmt,OCI_DEFAULT);
        //create picklist object with caption $Caption formatted as in telephone.css
        print "<TD class="$font[0]">$Caption</TD><TD class="$font[1]">
               <select style="font-size:$font[2]" id="id$name" name="$name" onChange="$event">";
        //Creates a blank option if an $event parameter is supplied
        if($firstoption!="")
        {print"<option value="0">$firstoption</option>";}
        //fetches the next row (for SELECT statements) into the internal result-buffer
        while (ocifetch($stmt))
        {
         //put the data for column $value into $checkvalue
         $checkvalue = ociresult($stmt,$value);
               if ($checkvalue==$default)
               {
                //insert all values into option tags for picklist, formatted as Initcap
                print "<option value="".ociresult($stmt,$value)."" selected>".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
               else
               {
                print "<option value="".ociresult($stmt,$value)."">".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
        }
         print"</select></TD>";
}
?>
and is used like

Code: Select all

<?php
        print populate_list("Function:","functionalid",$queryfunction,$c1,FUNCTIONAL,FUNCTIONAL_ID,"","Choose A Function",$stylearray,"Updatedepartmentid();Updatesectionid();");

?>
This constructs a drop down list which has 2 onChange events "Updatedepartmentid()" and "Updatesectionid()", it also constructs the javascript to go with them.

Please read the comments in the code before asking any questions.

Hope this helps
Mike

This wasfunction

Code: Select all

<?php
function populate_dependent_list($conn,$QrySecondaryOptions,$querymaxentries,$primaryid,$secondaryid,$secondarycolumn,$formname,$primaryname,$secondaryname,$firstoption,$value,$event="",$font=array("form","form","8pt"))
{
/*
This function will dynamically populate a dropdown list (the SECONDARY select) depending on the selection made in another dropdown list (the PRIMARY select).

Firstly a primary drop down list must be created. The only stipulation with this picklist (list1) is that it must have an onChange event.
The onChange event must be of the form Updatelist2();Updatelist3();....Updatelistx(); , where list2 is the name of
the list that is directly changed by list1, list3 is the list changed by list2 and so up to listx which is changed by listx-1

The following parameters should be passed to the function:-
$conn - A valid database connection
$QrySecondaryOptions - A query which returns the names and values of the entries for the secondary list with the corresponding
                       names and values from the primary list
$querymaxentries - A query which counts the maximum number of secondary list values corresponding to primary list values
$primaryid - The column that contains the values for the primary list
$secondaryid - The column that contains the values for the secondary list
$secondarycolumn - The column that contains the text options for the secondary list
$formname - The name of the form that the list are in
$primaryname - The name of primary list
$secondaryname - The name of secondary list
$firstoption - The text to display before any choice is made
$value - The variable that holds the current value of the selected item in the secondary list
$event - Call the functions to update all lists lower in the chain than this one (similar to the top level
         onChange event but without calling functions that update lists higher in the heirarchy.
*/
  $i=0;
/*$QrySecondaryOptions = "select p.$primarytable ,s.$secondarytable,s.$secondaryid,p.$primaryid";
$QrySecondaryOptions .= " from $secondarytable s,$primarytable p, $primarysecondarytable ps";
$QrySecondaryOptions .= " where ps.$psprimaryid = p.$primaryid";
$QrySecondaryOptions .= " and ps.$pssecondaryid = s.$pssecondaryid order by p.$primarycolumn";
  */
/*$querymaxentries = "select max(count(*)) MAXITEMS from $primarysecondarytable group by $psprimaryid";*/

$SecondaryOptions = ociparse($conn,$QrySecondaryOptions);
$getmaxentries = ociparse($conn  ,$querymaxentries);



$i=0;
ociexecute($SecondaryOptions,OCI_DEFAULT);
while (ocifetch($SecondaryOptions))
{
 $secondary_primary[$i] = ociresult($SecondaryOptions,$primaryid);
 $secondary_name[$i] = ociresult($SecondaryOptions,$secondarycolumn);
 $secondary_id[$i] = ociresult($SecondaryOptions,$secondaryid);
 $i++;
}

$numSecondarys  =count($secondary_name);

ociexecute($getmaxentries,OCI_DEFAULT);
while (ocifetch($getmaxentries))
{
 $MaxItems = ociresult($getmaxentries,MAXITEMS);
 $i++;
}

echo  "<SCRIPT  Language="JavaScript">\n";
echo  "function  Update$secondaryname()\n";
echo  "{\n";
/*  clear  the  $secondaryname  selector  */
echo  "\tvar  Sec  =  document.$formname.$secondaryname.options.length;\n";
echo  "\twhile(Sec  >  0)\n";
echo  "\t{\n";
echo  "\t\tSec--;\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].text  =  '';\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].value  =  '';\n";
echo  "\t}\n\n";
echo  "\tdocument.$formname.$secondaryname.options[0].selected  =  true;\n\n";

/*  figure  out  which  function  is  selected*/
echo  "\tvar  PrimarySelected  =  0;\n";
echo  "\twhile(document.$formname.$primaryname.options[PrimarySelected].selected==false)  PrimarySelected++;\n\n";

$SecondaryPrimary  =  "";
$SecondaryCount  =  0;

$RowCount  =  0;
while($RowCount  <  $numSecondarys)  {
      $secondary_ID  =  $secondary_id[$RowCount];
      $secondary_Primary  =  $secondary_primary[$RowCount];
      $secondary_Name  =  $secondary_name[$RowCount];

      if  ($SecondaryPrimary  !=  $secondary_Primary)  {
            if  ($SecondaryPrimary  !=  "")  {
                  echo  "\t}\n";
            }

            echo  "\tif(document.$formname.$primaryname.options[PrimarySelected].value  ==  '$secondary_Primary')\n";
            echo  "\t{\n";

            echo  "\t\tdocument.$formname.$secondaryname.options[0].value  =  0;\n";
            echo  "\t\tdocument.$formname.$secondaryname.options[0].text  =  '$firstoption';\n\n";


            $SecondaryPrimary  =  $secondary_Primary;
            $SecondaryCount =1;
      }

      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].value  =  '$secondary_ID';\n";
      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].text  =  '$secondary_Name';\n\n";
      print "\t\tif('$secondary_ID'=='$value')
                {document.$formname.$secondaryname.options[$SecondaryCount].selected = true;}\n";
      $SecondaryCount ++;
      $RowCount++;
}

echo  "\t}\n";

echo  "}\n</script>";
         //Print the HTML for the drop down
         print  "<td class=$font[1]><SELECT  style="font-size:$font[2]" NAME=$secondaryname value="$$secondaryname" size="1" width=200 onChange="$event" >\n ";
                  $RowCount  =  0;

                  while  ($RowCount  <=  $MaxItems)
                  {
                          print  "<OPTION  VALUE=$RowCount>";
                          print  "$firstoption \n";
                          $RowCount++;
                  }
         print  "</SELECT></td>\n";
  print "<SCRIPT  Language="JavaScript">Update$secondaryname();\n</SCRIPT>\n";

}

?>
you can call it from your PHP script like this

Code: Select all

<?php

$querydepartments = "select f.functional ,d.department,d.department_id,f.functional_id";
$querydepartments .= " from department d,functional f, function_department fd";
$querydepartments .= " where fd.functional_id = f.functional_id";
$querydepartments .= " and fd.department_id = d.department_id order by f.functional";

$querymaxdepartments = "select max(count(*)) MAXITEMS from function_department group by functional_id";

        populate_dependent_list($c1,$querydepartments,$querymaxdepartments,FUNCTIONAL_ID,DEPARTMENT_ID,DEPARTMENT,advanced_search,functionalid,departmentid,"Choose  a  Department","","Updatesectionid();");

?>
The first list is created with this function

Code: Select all

<?php
  function populate_list($Caption,$name,$query,$conn,$column,$value,$default,$firstoption="",$font="",$event="")
{

        //parse the query and return statement
        $stmt = ociparse($conn,$query);
        // execute query and put results into $stmt
        ociexecute($stmt,OCI_DEFAULT);
        //create picklist object with caption $Caption formatted as in telephone.css
        print "<TD class="$font[0]">$Caption</TD><TD class="$font[1]">
               <select style="font-size:$font[2]" id="id$name" name="$name" onChange="$event">";
        //Creates a blank option if an $event parameter is supplied
        if($firstoption!="")
        {print"<option value="0">$firstoption</option>";}
        //fetches the next row (for SELECT statements) into the internal result-buffer
        while (ocifetch($stmt))
        {
         //put the data for column $value into $checkvalue
         $checkvalue = ociresult($stmt,$value);
               if ($checkvalue==$default)
               {
                //insert all values into option tags for picklist, formatted as Initcap
                print "<option value="".ociresult($stmt,$value)."" selected>".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
               else
               {
                print "<option value="".ociresult($stmt,$value)."">".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
        }
         print"</select></TD>";
}
?>
and is used like

Code: Select all

<?php
        print populate_list("Function:","functionalid",$queryfunction,$c1,FUNCTIONAL,FUNCTIONAL_ID,"","Choose A Function",$stylearray,"Updatedepartmentid();Updatesectionid();");

?>
This constructs a drop down list which has 2 onChange events "Updatedepartmentid()" and "Updatesectionid()", it also constructs the javascript to go with them.

Please read the comments in the code before asking any questions.

Hofunction

Code: Select all

<?php
function populate_dependent_list($conn,$QrySecondaryOptions,$querymaxentries,$primaryid,$secondaryid,$secondarycolumn,$formname,$primaryname,$secondaryname,$firstoption,$value,$event="",$font=array("form","form","8pt"))
{
/*
This function will dynamically populate a dropdown list (the SECONDARY select) depending on the selection made in another dropdown list (the PRIMARY select).

Firstly a primary drop down list must be created. The only stipulation with this picklist (list1) is that it must have an onChange event.
The onChange event must be of the form Updatelist2();Updatelist3();....Updatelistx(); , where list2 is the name of
the list that is directly changed by list1, list3 is the list changed by list2 and so up to listx which is changed by listx-1

The following parameters should be passed to the function:-
$conn - A valid database connection
$QrySecondaryOptions - A query which returns the names and values of the entries for the secondary list with the corresponding
                       names and values from the primary list
$querymaxentries - A query which counts the maximum number of secondary list values corresponding to primary list values
$primaryid - The column that contains the values for the primary list
$secondaryid - The column that contains the values for the secondary list
$secondarycolumn - The column that contains the text options for the secondary list
$formname - The name of the form that the list are in
$primaryname - The name of primary list
$secondaryname - The name of secondary list
$firstoption - The text to display before any choice is made
$value - The variable that holds the current value of the selected item in the secondary list
$event - Call the functions to update all lists lower in the chain than this one (similar to the top level
         onChange event but without calling functions that update lists higher in the heirarchy.
*/
  $i=0;
/*$QrySecondaryOptions = "select p.$primarytable ,s.$secondarytable,s.$secondaryid,p.$primaryid";
$QrySecondaryOptions .= " from $secondarytable s,$primarytable p, $primarysecondarytable ps";
$QrySecondaryOptions .= " where ps.$psprimaryid = p.$primaryid";
$QrySecondaryOptions .= " and ps.$pssecondaryid = s.$pssecondaryid order by p.$primarycolumn";
  */
/*$querymaxentries = "select max(count(*)) MAXITEMS from $primarysecondarytable group by $psprimaryid";*/

$SecondaryOptions = ociparse($conn,$QrySecondaryOptions);
$getmaxentries = ociparse($conn  ,$querymaxentries);



$i=0;
ociexecute($SecondaryOptions,OCI_DEFAULT);
while (ocifetch($SecondaryOptions))
{
 $secondary_primary[$i] = ociresult($SecondaryOptions,$primaryid);
 $secondary_name[$i] = ociresult($SecondaryOptions,$secondarycolumn);
 $secondary_id[$i] = ociresult($SecondaryOptions,$secondaryid);
 $i++;
}

$numSecondarys  =count($secondary_name);

ociexecute($getmaxentries,OCI_DEFAULT);
while (ocifetch($getmaxentries))
{
 $MaxItems = ociresult($getmaxentries,MAXITEMS);
 $i++;
}

echo  "<SCRIPT  Language="JavaScript">\n";
echo  "function  Update$secondaryname()\n";
echo  "{\n";
/*  clear  the  $secondaryname  selector  */
echo  "\tvar  Sec  =  document.$formname.$secondaryname.options.length;\n";
echo  "\twhile(Sec  >  0)\n";
echo  "\t{\n";
echo  "\t\tSec--;\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].text  =  '';\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].value  =  '';\n";
echo  "\t}\n\n";
echo  "\tdocument.$formname.$secondaryname.options[0].selected  =  true;\n\n";

/*  figure  out  which  function  is  selected*/
echo  "\tvar  PrimarySelected  =  0;\n";
echo  "\twhile(document.$formname.$primaryname.options[PrimarySelected].selected==false)  PrimarySelected++;\n\n";

$SecondaryPrimary  =  "";
$SecondaryCount  =  0;

$RowCount  =  0;
while($RowCount  <  $numSecondarys)  {
      $secondary_ID  =  $secondary_id[$RowCount];
      $secondary_Primary  =  $secondary_primary[$RowCount];
      $secondary_Name  =  $secondary_name[$RowCount];

      if  ($SecondaryPrimary  !=  $secondary_Primary)  {
            if  ($SecondaryPrimary  !=  "")  {
                  echo  "\t}\n";
            }

            echo  "\tif(document.$formname.$primaryname.options[PrimarySelected].value  ==  '$secondary_Primary')\n";
            echo  "\t{\n";

            echo  "\t\tdocument.$formname.$secondaryname.options[0].value  =  0;\n";
            echo  "\t\tdocument.$formname.$secondaryname.options[0].text  =  '$firstoption';\n\n";


            $SecondaryPrimary  =  $secondary_Primary;
            $SecondaryCount =1;
      }

      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].value  =  '$secondary_ID';\n";
      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].text  =  '$secondary_Name';\n\n";
      print "\t\tif('$secondary_ID'=='$value')
                {document.$formname.$secondaryname.options[$SecondaryCount].selected = true;}\n";
      $SecondaryCount ++;
      $RowCount++;
}

echo  "\t}\n";

echo  "}\n</script>";
         //Print the HTML for the drop down
         print  "<td class=$font[1]><SELECT  style="font-size:$font[2]" NAME=$secondaryname value="$$secondaryname" size="1" width=200 onChange="$event" >\n ";
                  $RowCount  =  0;

                  while  ($RowCount  <=  $MaxItems)
                  {
                          print  "<OPTION  VALUE=$RowCount>";
                          print  "$firstoption \n";
                          $RowCount++;
                  }
         print  "</SELECT></td>\n";
  print "<SCRIPT  Language="JavaScript">Update$secondaryname();\n</SCRIPT>\n";

}

?>
you can call it from your PHP script like this

Code: Select all

<?php

$querydepartments = "select f.functional ,d.department,d.department_id,f.functional_id";
$querydepartments .= " from department d,functional f, function_department fd";
$querydepartments .= " where fd.functional_id = f.functional_id";
$querydepartments .= " and fd.department_id = d.department_id order by f.functional";

$querymaxdepartments = "select max(count(*)) MAXITEMS from function_department group by functional_id";

        populate_dependent_list($c1,$querydepartments,$querymaxdepartments,FUNCTIONAL_ID,DEPARTMENT_ID,DEPARTMENT,advanced_search,functionalid,departmentid,"Choose  a  Department","","Updatesectionid();");

?>
The first list is created with this function

Code: Select all

<?php
  function populate_list($Caption,$name,$query,$conn,$column,$value,$default,$firstoption="",$font="",$event="")
{

        //parse the query and return statement
        $stmt = ociparse($conn,$query);
        // execute query and put results into $stmt
        ociexecute($stmt,OCI_DEFAULT);
        //create picklist object with caption $Caption formatted as in telephone.css
        print "<TD class="$font[0]">$Caption</TD><TD class="$font[1]">
               <select style="font-size:$font[2]" id="id$name" name="$name" onChange="$event">";
        //Creates a blank option if an $event parameter is supplied
        if($firstoption!="")
        {print"<option value="0">$firstoption</option>";}
        //fetches the next row (for SELECT statements) into the internal result-buffer
        while (ocifetch($stmt))
        {
         //put the data for column $value into $checkvalue
         $checkvalue = ociresult($stmt,$value);
               if ($checkvalue==$default)
               {
                //insert all values into option tags for picklist, formatted as Initcap
                print "<option value="".ociresult($stmt,$value)."" selected>".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
               else
               {
                print "<option value="".ociresult($stmt,$value)."">".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
        }
         print"</select></TD>";
}
?>
and is used like

Code: Select all

<?php
        print populate_list("Function:","functionalid",$queryfunction,$c1,FUNCTIONAL,FUNCTIONAL_ID,"","Choose A Function",$stylearray,"Updatedepartmentid();Updatesectionid();");

?>
This constructs a drop down list which has 2 onChange events "Updatedepartmentid()" and "Updatesectionid()", it also constructs the javascript to go with them.

Please read the comments in the code before asking any questions.

Hope this helps
Mike

This was developed to retrieve frofunction

Code: Select all

<?php
function populate_dependent_list($conn,$QrySecondaryOptions,$querymaxentries,$primaryid,$secondaryid,$secondarycolumn,$formname,$primaryname,$secondaryname,$firstoption,$value,$event="",$font=array("form","form","8pt"))
{
/*
This function will dynamically populate a dropdown list (the SECONDARY select) depending on the selection made in another dropdown list (the PRIMARY select).

Firstly a primary drop down list must be created. The only stipulation with this picklist (list1) is that it must have an onChange event.
The onChange event must be of the form Updatelist2();Updatelist3();....Updatelistx(); , where list2 is the name of
the list that is directly changed by list1, list3 is the list changed by list2 and so up to listx which is changed by listx-1

The following parameters should be passed to the function:-
$conn - A valid database connection
$QrySecondaryOptions - A query which returns the names and values of the entries for the secondary list with the corresponding
                       names and values from the primary list
$querymaxentries - A query which counts the maximum number of secondary list values corresponding to primary list values
$primaryid - The column that contains the values for the primary list
$secondaryid - The column that contains the values for the secondary list
$secondarycolumn - The column that contains the text options for the secondary list
$formname - The name of the form that the list are in
$primaryname - The name of primary list
$secondaryname - The name of secondary list
$firstoption - The text to display before any choice is made
$value - The variable that holds the current value of the selected item in the secondary list
$event - Call the functions to update all lists lower in the chain than this one (similar to the top level
         onChange event but without calling functions that update lists higher in the heirarchy.
*/
  $i=0;
/*$QrySecondaryOptions = "select p.$primarytable ,s.$secondarytable,s.$secondaryid,p.$primaryid";
$QrySecondaryOptions .= " from $secondarytable s,$primarytable p, $primarysecondarytable ps";
$QrySecondaryOptions .= " where ps.$psprimaryid = p.$primaryid";
$QrySecondaryOptions .= " and ps.$pssecondaryid = s.$pssecondaryid order by p.$primarycolumn";
  */
/*$querymaxentries = "select max(count(*)) MAXITEMS from $primarysecondarytable group by $psprimaryid";*/

$SecondaryOptions = ociparse($conn,$QrySecondaryOptions);
$getmaxentries = ociparse($conn  ,$querymaxentries);



$i=0;
ociexecute($SecondaryOptions,OCI_DEFAULT);
while (ocifetch($SecondaryOptions))
{
 $secondary_primary[$i] = ociresult($SecondaryOptions,$primaryid);
 $secondary_name[$i] = ociresult($SecondaryOptions,$secondarycolumn);
 $secondary_id[$i] = ociresult($SecondaryOptions,$secondaryid);
 $i++;
}

$numSecondarys  =count($secondary_name);

ociexecute($getmaxentries,OCI_DEFAULT);
while (ocifetch($getmaxentries))
{
 $MaxItems = ociresult($getmaxentries,MAXITEMS);
 $i++;
}

echo  "<SCRIPT  Language="JavaScript">\n";
echo  "function  Update$secondaryname()\n";
echo  "{\n";
/*  clear  the  $secondaryname  selector  */
echo  "\tvar  Sec  =  document.$formname.$secondaryname.options.length;\n";
echo  "\twhile(Sec  >  0)\n";
echo  "\t{\n";
echo  "\t\tSec--;\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].text  =  '';\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].value  =  '';\n";
echo  "\t}\n\n";
echo  "\tdocument.$formname.$secondaryname.options[0].selected  =  true;\n\n";

/*  figure  out  which  function  is  selected*/
echo  "\tvar  PrimarySelected  =  0;\n";
echo  "\twhile(document.$formname.$primaryname.options[PrimarySelected].selected==false)  PrimarySelected++;\n\n";

$SecondaryPrimary  =  "";
$SecondaryCount  =  0;

$RowCount  =  0;
while($RowCount  <  $numSecondarys)  {
      $secondary_ID  =  $secondary_id[$RowCount];
      $secondary_Primary  =  $secondary_primary[$RowCount];
      $secondary_Name  =  $secondary_name[$RowCount];

      if  ($SecondaryPrimary  !=  $secondary_Primary)  {
            if  ($SecondaryPrimary  !=  "")  {
                  echo  "\t}\n";
            }

            echo  "\tif(document.$formname.$primaryname.options[PrimarySelected].value  ==  '$secondary_Primary')\n";
            echo  "\t{\n";

            echo  "\t\tdocument.$formname.$secondaryname.options[0].value  =  0;\n";
            echo  "\t\tdocument.$formname.$secondaryname.options[0].text  =  '$firstoption';\n\n";


            $SecondaryPrimary  =  $secondary_Primary;
            $SecondaryCount =1;
      }

      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].value  =  '$secondary_ID';\n";
      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].text  =  '$secondary_Name';\n\n";
      print "\t\tif('$secondary_ID'=='$value')
                {document.$formname.$secondaryname.options[$SecondaryCount].selected = true;}\n";
      $SecondaryCount ++;
      $RowCount++;
}

echo  "\t}\n";

echo  "}\n</script>";
         //Print the HTML for the drop down
         print  "<td class=$font[1]><SELECT  style="font-size:$font[2]" NAME=$secondaryname value="$$secondaryname" size="1" width=200 onChange="$event" >\n ";
                  $RowCount  =  0;

                  while  ($RowCount  <=  $MaxItems)
                  {
                          print  "<OPTION  VALUE=$RowCount>";
                          print  "$firstoption \n";
                          $RowCount++;
                  }
         print  "</SELECT></td>\n";
  print "<SCRIPT  Language="JavaScript">Update$secondaryname();\n</SCRIPT>\n";

}

?>
you can call it from your PHP script like this

Code: Select all

<?php

$querydepartments = "select f.functional ,d.department,d.department_id,f.functional_id";
$querydepartments .= " from department d,functional f, function_department fd";
$querydepartments .= " where fd.functional_id = f.functional_id";
$querydepartments .= " and fd.department_id = d.department_id order by f.functional";

$querymaxdepartments = "select max(count(*)) MAXITEMS from function_department group by functional_id";

        populate_dependent_list($c1,$querydepartments,$querymaxdepartments,FUNCTIONAL_ID,DEPARTMENT_ID,DEPARTMENT,advanced_search,functionalid,departmentid,"Choose  a  Department","","Updatesectionid();");

?>
The first list is created with this function

Code: Select all

<?php
  function populate_list($Caption,$name,$query,$conn,$column,$value,$default,$firstoption="",$font="",$event="")
{

        //parse the query and return statement
        $stmt = ociparse($conn,$query);
        // execute query and put results into $stmt
        ociexecute($stmt,OCI_DEFAULT);
        //create picklist object with caption $Caption formatted as in telephone.css
        print "<TD class="$font[0]">$Caption</TD><TD class="$font[1]">
               <select style="font-size:$font[2]" id="id$name" name="$name" onChange="$event">";
        //Creates a blank option if an $event parameter is supplied
        if($firstoption!="")
        {print"<option value="0">$firstoption</option>";}
        //fetches the next row (for SELECT statements) into the internal result-buffer
        while (ocifetch($stmt))
        {
         //put the data for column $value into $checkvalue
         $checkvalue = ociresult($stmt,$value);
               if ($checkvalue==$default)
               {
                //insert all values into option tags for picklist, formatted as Initcap
                print "<option value="".ociresult($stmt,$value)."" selected>".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
               else
               {
                print "<option value="".ociresult($stmt,$value)."">".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
        }
         print"</select></TD>";
}
?>
and is used like

Code: Select all

<?php
        print populate_list("Function:","functionalid",$queryfunction,$c1,FUNCTIONAL,FUNCTIONAL_ID,"","Choose A Function",$stylearray,"Updatedepartmentid();Updatesectionid();");

?>
This constructs a drop down list which has 2 onChange events "Updatedepartmentid()" and "Updatesectionid()", it also constructs the javascript to go with them.

Please read the comments in the code before asking any questions.

Hope this helps
Mike

This was developed tofunction

Code: Select all

<?php
function populate_dependent_list($conn,$QrySecondaryOptions,$querymaxentries,$primaryid,$secondaryid,$secondarycolumn,$formname,$primaryname,$secondaryname,$firstoption,$value,$event="",$font=array("form","form","8pt"))
{
/*
This function will dynamically populate a dropdown list (the SECONDARY select) depending on the selection made in another dropdown list (the PRIMARY select).

Firstly a primary drop down list must be created. The only stipulation with this picklist (list1) is that it must have an onChange event.
The onChange event must be of the form Updatelist2();Updatelist3();....Updatelistx(); , where list2 is the name of
the list that is directly changed by list1, list3 is the list changed by list2 and so up to listx which is changed by listx-1

The following parameters should be passed to the function:-
$conn - A valid database connection
$QrySecondaryOptions - A query which returns the names and values of the entries for the secondary list with the corresponding
                       names and values from the primary list
$querymaxentries - A query which counts the maximum number of secondary list values corresponding to primary list values
$primaryid - The column that contains the values for the primary list
$secondaryid - The column that contains the values for the secondary list
$secondarycolumn - The column that contains the text options for the secondary list
$formname - The name of the form that the list are in
$primaryname - The name of primary list
$secondaryname - The name of secondary list
$firstoption - The text to display before any choice is made
$value - The variable that holds the current value of the selected item in the secondary list
$event - Call the functions to update all lists lower in the chain than this one (similar to the top level
         onChange event but without calling functions that update lists higher in the heirarchy.
*/
  $i=0;
/*$QrySecondaryOptions = "select p.$primarytable ,s.$secondarytable,s.$secondaryid,p.$primaryid";
$QrySecondaryOptions .= " from $secondarytable s,$primarytable p, $primarysecondarytable ps";
$QrySecondaryOptions .= " where ps.$psprimaryid = p.$primaryid";
$QrySecondaryOptions .= " and ps.$pssecondaryid = s.$pssecondaryid order by p.$primarycolumn";
  */
/*$querymaxentries = "select max(count(*)) MAXITEMS from $primarysecondarytable group by $psprimaryid";*/

$SecondaryOptions = ociparse($conn,$QrySecondaryOptions);
$getmaxentries = ociparse($conn  ,$querymaxentries);



$i=0;
ociexecute($SecondaryOptions,OCI_DEFAULT);
while (ocifetch($SecondaryOptions))
{
 $secondary_primary[$i] = ociresult($SecondaryOptions,$primaryid);
 $secondary_name[$i] = ociresult($SecondaryOptions,$secondarycolumn);
 $secondary_id[$i] = ociresult($SecondaryOptions,$secondaryid);
 $i++;
}

$numSecondarys  =count($secondary_name);

ociexecute($getmaxentries,OCI_DEFAULT);
while (ocifetch($getmaxentries))
{
 $MaxItems = ociresult($getmaxentries,MAXITEMS);
 $i++;
}

echo  "<SCRIPT  Language="JavaScript">\n";
echo  "function  Update$secondaryname()\n";
echo  "{\n";
/*  clear  the  $secondaryname  selector  */
echo  "\tvar  Sec  =  document.$formname.$secondaryname.options.length;\n";
echo  "\twhile(Sec  >  0)\n";
echo  "\t{\n";
echo  "\t\tSec--;\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].text  =  '';\n";
echo  "\t\tdocument.$formname.$secondaryname.options[Sec].value  =  '';\n";
echo  "\t}\n\n";
echo  "\tdocument.$formname.$secondaryname.options[0].selected  =  true;\n\n";

/*  figure  out  which  function  is  selected*/
echo  "\tvar  PrimarySelected  =  0;\n";
echo  "\twhile(document.$formname.$primaryname.options[PrimarySelected].selected==false)  PrimarySelected++;\n\n";

$SecondaryPrimary  =  "";
$SecondaryCount  =  0;

$RowCount  =  0;
while($RowCount  <  $numSecondarys)  {
      $secondary_ID  =  $secondary_id[$RowCount];
      $secondary_Primary  =  $secondary_primary[$RowCount];
      $secondary_Name  =  $secondary_name[$RowCount];

      if  ($SecondaryPrimary  !=  $secondary_Primary)  {
            if  ($SecondaryPrimary  !=  "")  {
                  echo  "\t}\n";
            }

            echo  "\tif(document.$formname.$primaryname.options[PrimarySelected].value  ==  '$secondary_Primary')\n";
            echo  "\t{\n";

            echo  "\t\tdocument.$formname.$secondaryname.options[0].value  =  0;\n";
            echo  "\t\tdocument.$formname.$secondaryname.options[0].text  =  '$firstoption';\n\n";


            $SecondaryPrimary  =  $secondary_Primary;
            $SecondaryCount =1;
      }

      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].value  =  '$secondary_ID';\n";
      echo  "\t\tdocument.$formname.$secondaryname.options[$SecondaryCount].text  =  '$secondary_Name';\n\n";
      print "\t\tif('$secondary_ID'=='$value')
                {document.$formname.$secondaryname.options[$SecondaryCount].selected = true;}\n";
      $SecondaryCount ++;
      $RowCount++;
}

echo  "\t}\n";

echo  "}\n</script>";
         //Print the HTML for the drop down
         print  "<td class=$font[1]><SELECT  style="font-size:$font[2]" NAME=$secondaryname value="$$secondaryname" size="1" width=200 onChange="$event" >\n ";
                  $RowCount  =  0;

                  while  ($RowCount  <=  $MaxItems)
                  {
                          print  "<OPTION  VALUE=$RowCount>";
                          print  "$firstoption \n";
                          $RowCount++;
                  }
         print  "</SELECT></td>\n";
  print "<SCRIPT  Language="JavaScript">Update$secondaryname();\n</SCRIPT>\n";

}

?>
you can call it from your PHP script like this

Code: Select all

<?php

$querydepartments = "select f.functional ,d.department,d.department_id,f.functional_id";
$querydepartments .= " from department d,functional f, function_department fd";
$querydepartments .= " where fd.functional_id = f.functional_id";
$querydepartments .= " and fd.department_id = d.department_id order by f.functional";

$querymaxdepartments = "select max(count(*)) MAXITEMS from function_department group by functional_id";

        populate_dependent_list($c1,$querydepartments,$querymaxdepartments,FUNCTIONAL_ID,DEPARTMENT_ID,DEPARTMENT,advanced_search,functionalid,departmentid,"Choose  a  Department","","Updatesectionid();");

?>
The first list is created with this function

Code: Select all

<?php
  function populate_list($Caption,$name,$query,$conn,$column,$value,$default,$firstoption="",$font="",$event="")
{

        //parse the query and return statement
        $stmt = ociparse($conn,$query);
        // execute query and put results into $stmt
        ociexecute($stmt,OCI_DEFAULT);
        //create picklist object with caption $Caption formatted as in telephone.css
        print "<TD class="$font[0]">$Caption</TD><TD class="$font[1]">
               <select style="font-size:$font[2]" id="id$name" name="$name" onChange="$event">";
        //Creates a blank option if an $event parameter is supplied
        if($firstoption!="")
        {print"<option value="0">$firstoption</option>";}
        //fetches the next row (for SELECT statements) into the internal result-buffer
        while (ocifetch($stmt))
        {
         //put the data for column $value into $checkvalue
         $checkvalue = ociresult($stmt,$value);
               if ($checkvalue==$default)
               {
                //insert all values into option tags for picklist, formatted as Initcap
                print "<option value="".ociresult($stmt,$value)."" selected>".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
               else
               {
                print "<option value="".ociresult($stmt,$value)."">".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
        }
         print"</select></TD>";
}
?>
and is used like

Code: Select all

<?php
        print populate_list("Function:","functionalid",$queryfunction,$c1,FUNCTIONAL,FUNCTIONAL_ID,"","Choose A Function",$stylearray,"Updatedepartmentid();Updatesectionid();");

?>
This constructs a drop down list which has 2 onChange events "Updatedepartmentid()" and "Updatesectionid()", it also constructs the javascript to go with them.

Please read the comments in the code before asking any questions.

Hope this helps
Mike

This was developed to retrieve from an Oracle database, amp;lt;?php
function populate_dependent_list($conn,$QrySecondaryOptions,$querymaxentries,$primaryid,$secondaryid,$secondarycolumn,$formname,$primaryname,$secondaryname,$firstoption,$value,$event="",$font=array("form","form","8pt"))
{
/*
This function will dynamically populate a dropdown list (the SECONDARY select) depending on the selection made in another dropdown list (the PRIMARY select).

Firstly a primary drop down list must be created. The only stipulation with this picklist (list1) is that it must have an onChange event.
The onChange event must be of the form Updatelist2();Updatelist3();....Updatelistx(); , where list2 is the name of
the list that is directly changed by list1, list3 is the list changed by list2 and so up to listx which is changed by listx-1

The following parameters should be passed to the function:-
$conn - A valid database connection
$QrySecondaryOptions - A query which returns the names and values of the entries for the secondary list with the corresponding
names and values from the primary list
$querymaxentries - A query which counts the maximum number of secondary list values corresponding to primary list values
$primaryid - The column that contains the values for the primary list
$secondaryid - The column that contains the values for the secondary list
$secondarycolumn - The column that contains the text options for the secondary list
$formname - The name of the form that the list are in
$primaryname - The name of primary list
$secondaryname - The name of secondary list
$firstoption - The text to display before any choice is made
$value - The variable that holds the current value of the selected item in the secondary list
$event - Call the functions to update all lists lower in the chain than this one (similar to the top level
onChange event but without calling functions that update lists higher in the heirarchy.
*/
$i=0;
/*$QrySecondaryOptions = "select p.$primarytable ,s.$secondarytable,s.$secondaryid,p.$primaryid";
$QrySecondaryOptions .= " from $secondarytable s,$primarytable p, $primarysecondarytable ps";
$QrySecondaryOptions .= " where ps.$psprimaryid = p.$primaryid";
$QrySecondaryOptions .= " and ps.$pssecondaryid = s.$pssecondaryid order by p.$primarycolumn";
*/
/*$querymaxentries = "select max(count(*)) MAXITEMS from $primarysecondarytable group by $psprimaryid";*/

$SecondaryOptions = ociparse($conn,$QrySecondaryOptions);
$getmaxentries = ociparse($conn ,$querymaxentries);



$i=0;
ociexecute($SecondaryOptions,OCI_DEFAULT);
while (ocifetch($SecondaryOptions))
{
$secondary_primaryї$i] = ociresult($SecondaryOptions,$primaryid);
$secondary_nameї$i] = ociresult($SecondaryOptions,$secondarycolumn);
$secondary_idї$i] = ociresult($SecondaryOptions,$secondaryid);
$i++;
}

$numSecondarys =count($secondary_name);

ociexecute($getmaxentries,OCI_DEFAULT);
while (ocifetch($getmaxentries))
{
$MaxItems = ociresult($getmaxentries,MAXITEMS);
$i++;
}

echo "&lt;SCRIPT Language="JavaScript"&gt;\n";
echo "function Update$secondaryname()\n";
echo "{\n";
/* clear the $secondaryname selector */
echo "\tvar Sec = document.$formname.$secondaryname.options.length;\n";
echo "\twhile(Sec &gt; 0)\n";
echo "\t{\n";
echo "\t\tSec--;\n";
echo "\t\tdocument.$formname.$secondaryname.optionsїSec].text = '';\n";
echo "\t\tdocument.$formname.$secondaryname.optionsїSec].value = '';\n";
echo "\t}\n\n";
echo "\tdocument.$formname.$secondaryname.optionsї0].selected = true;\n\n";

/* figure out which function is selected*/
echo "\tvar PrimarySelected = 0;\n";
echo "\twhile(document.$formname.$primaryname.optionsїPrimarySelected].selected==false) PrimarySelected++;\n\n";

$SecondaryPrimary = "";
$SecondaryCount = 0;

$RowCount = 0;
while($RowCount &lt; $numSecondarys) {
$secondary_ID = $secondary_idї$RowCount];
$secondary_Primary = $secondary_primaryї$RowCount];
$secondary_Name = $secondary_nameї$RowCount];

if ($SecondaryPrimary != $secondary_Primary) {
if ($SecondaryPrimary != "") {
echo "\t}\n";
}

echo "\tif(document.$formname.$primaryname.optionsїPrimarySelected].value == '$secondary_Primary')\n";
echo "\t{\n";

echo "\t\tdocument.$formname.$secondaryname.optionsї0].value = 0;\n";
echo "\t\tdocument.$formname.$secondaryname.optionsї0].text = '$firstoption';\n\n";


$SecondaryPrimary = $secondary_Primary;
$SecondaryCount =1;
}

echo "\t\tdocument.$formname.$secondaryname.optionsї$SecondaryCount].value = '$secondary_ID';\n";
echo "\t\tdocument.$formname.$secondaryname.optionsї$SecondaryCount].text = '$secondary_Name';\n\n";
print "\t\tif('$secondary_ID'=='$value')
{document.$formname.$secondaryname.optionsї$SecondaryCount].selected = true;}\n";
$SecondaryCount ++;
$RowCount++;
}

echo "\t}\n";

echo "}\n&lt;/script&gt;";
//Print the HTML for the drop down
print "&lt;td class=$fontї1]&gt;&lt;SELECT style="font-size:$fontї2]" NAME=$secondaryname value="$$secondaryname" size="1" width=200 onChange="$event" &gt;\n ";
$RowCount = 0;

while ($RowCount &lt;= $MaxItems)
{
print "&lt;OPTION VALUE=$RowCount&gt;";
print "$firstoption \n";
$RowCount++;
}
print "&lt;/SELECT&gt;&lt;/td&gt;\n";
print "&lt;SCRIPT Language="JavaScript"&gt;Update$secondaryname();\n&lt;/SCRIPT&gt;\n";

}

?&gt;

you can call it from your PHP script like this

Code: Select all

<?php

$querydepartments = "select f.functional ,d.department,d.department_id,f.functional_id";
$querydepartments .= " from department d,functional f, function_department fd";
$querydepartments .= " where fd.functional_id = f.functional_id";
$querydepartments .= " and fd.department_id = d.department_id order by f.functional";

$querymaxdepartments = "select max(count(*)) MAXITEMS from function_department group by functional_id";

        populate_dependent_list($c1,$querydepartments,$querymaxdepartments,FUNCTIONAL_ID,DEPARTMENT_ID,DEPARTMENT,advanced_search,functionalid,departmentid,"Choose  a  Department","","Updatesectionid();");

?>
The first list is created with this function

Code: Select all

<?php
  function populate_list($Caption,$name,$query,$conn,$column,$value,$default,$firstoption="",$font="",$event="")
{

        //parse the query and return statement
        $stmt = ociparse($conn,$query);
        // execute query and put results into $stmt
        ociexecute($stmt,OCI_DEFAULT);
        //create picklist object with caption $Caption formatted as in telephone.css
        print "<TD class="$font[0]">$Caption</TD><TD class="$font[1]">
               <select style="font-size:$font[2]" id="id$name" name="$name" onChange="$event">";
        //Creates a blank option if an $event parameter is supplied
        if($firstoption!="")
        {print"<option value="0">$firstoption</option>";}
        //fetches the next row (for SELECT statements) into the internal result-buffer
        while (ocifetch($stmt))
        {
         //put the data for column $value into $checkvalue
         $checkvalue = ociresult($stmt,$value);
               if ($checkvalue==$default)
               {
                //insert all values into option tags for picklist, formatted as Initcap
                print "<option value="".ociresult($stmt,$value)."" selected>".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
               else
               {
                print "<option value="".ociresult($stmt,$value)."">".ucwords(strtolower(ociresult($stmt,$column)))."</option>\n";
               }
        }
         print"</select></TD>";
}
?>
and is used like

Code: Select all

<?php
        print populate_list("Function:","functionalid",$queryfunction,$c1,FUNCTIONAL,FUNCTIONAL_ID,"","Choose A Function",$stylearray,"Updatedepartmentid();Updatesectionid();");

?>
This constructs a drop down list which has 2 onChange events "Updatedepartmentid()" and "Updatesectionid()", it also constructs the javascript to go with them.

Please read the comments in the code before asking any questions.ySecondaryOptions,$querymaxentries,$primaryid,$secondaryid,$secondarycolumn,$formname,$primaryname,$secondaryname,$firstoption,$value,$event="",$font=array("form","form","8pt"))
{
/*
This function will dynamically populate a dropdown list (the SECONDARY select) depending on the selection made in another dropdown list (the PRIMARY select).

Firstly a primary drop down list must be created. The only stipulation with this picklist (list1) is that it must have an onChange event.
The onChange event must be of the form Updatelist2();Updatelist3();....Updatelistx(); , where list2 is the name of
the list that is directly changed by list1, list3 is the list changed by list2 and so up to listx which is changed by listx-1

The following parameters should be passed to the function:-
$conn - A valid database connection
$QrySecondaryOptions - A query which returns the names and values of the entries for the secondary list with the corresponding
names and values from the primary list
$querymaxentries - A query which counts the maximum number of secondary list values corresponding to primary list values
$primaryid - The column that contains the values for the primary list
$secondaryid - The column that contains the values for the secondary list
$secondarycolumn - The column that contains the text options for the secondary list
$formname - The name of the form that the list are in
$primaryname - The name of primary list
$secondaryname - The name of secondary list
$firstoption - The text to display before any choice is made
$value - The variable that holds the current value of the selected item in the secondary list
$event - Call the functions to update all lists lower in the chain than this one (similar to the top level
onChange event but without calling functions that update lists higher in the heirarchy.
*/
$i=0;
/*$QrySecondaryOptions = "select p.$primarytable ,s.$secondarytable,s.$secondaryid,p.$primaryid";
$QrySecondaryOptions .= " from $secondarytable s,$primarytable p, $primarysecondarytable ps";
$QrySecondaryOptions .= " where p

Posted: Wed May 22, 2002 9:46 am
by Crashin
Mikeq...thank you SO much for taking the time to make that post!!! I'll give it a try! :D