What's the best and easiest way to Populate a

Question

Very simply, I have one dropdown menu dynamically populated with data:

SQL Code

$querycourse = "SELECT course, COUNT(course) AS count FROM acme WHERE course IS NOT NULL GROUP BY course ";
$procc = mysqli_prepare($link, $querycourse);
$queryc =  mysqli_query($link, $querycourse) or die(mysqli_error($link));

PHP Code

echo "<select name='course[]' value='' multiple='multiple' size=10>";
            // printing the list box select command
            echo "<option value=''>All</option>";
            while($ntc=mysqli_fetch_array($queryc)){//Array or records stored in $nt
            echo "<option value=\"$ntc[course]\">\"$ntc[course]\"</option>";
            /* Option values are added by looping through the array */
            }
            echo "</select>";// Closing of list box 

What I need is another dropdown that is populated with data based on a selection from the first dropdown box.

I am using MySQL, PHP, Javascript and can also (at a push) use jQuery. I have no experience in Ajax.

Would anyone be kind enough to point me in the right direction?!

Thanks in advance, as always,

Homer.

Answer 1

First and Best Method (If you have or may have enough option specific data)
Use AJAX. It is the easiest way, I think, compared to the other ways to implement the same. Use Jquery to implement AJAX. It makes AJAX a piece of cake! Here I share my piece of cake for you -

Following is roughly the complete code you need -

• Call a javascript function populateSecondDropdown() on your first select like this -

      echo "<select  name='course[]' value='' multiple='multiple' size=10 onchange='populateSecondDropdown(this, 'http://yourprojectUrl','Any Subject');'>";
              // printing the list box select command
              echo "<option value=''>All</option>";
              while($ntc=mysqli_fetch_array($queryc))
              {//Array or records stored in $nt
                  echo "<option value=\"$ntc[course]\">\"$ntc[course]\"</option>";
                  /* Option values are added by looping through the array */
              }
              echo "</select>";// Closing of list box 
  

• Define an ajax call inside inside the populateSecondDropdown() function -

  <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>
  
  <script  type="text/javascript">  
      function populateSecondDropdown(object,baseUrl)
      {
          $.ajax({
          type: "POST", 
          url: baseUrl+"/ajax/fetchOptions.php", 
          data: { id_option: $(object).val(), operation: 'get_subjects' },
          dataType: "json",
          success: function(data) 
          {
              //Clear options corresponding to earlier option of first dropdown
             $('select#secondDropdown').empty(); 
             $('select#secondDropdown').append('<option value="0">Select Option</option>');
                     //Populate options of the second dropdown
             $.each( data.subjects, function() 
             {    
                 $('select#secondDropdown').append('<option value="'+$(this).attr('option_id')+'">'+$(this).attr('option_name')+'</option>');
             });
             $('select#secondDropdown').focus();
          },
              beforeSend: function() 
              {
                  $('select#secondDropdown').empty();
                  $('select#secondDropdown').append('<option value="0">Loading...</option>');
              },
              error: function() 
             {
                $('select#secondDropdown').attr('disabled', true);
                $('select#secondDropdown').empty();
                 $('select#secondDropdown').append('<option value="0">No Options</option>');
            }
          });
       }
  </script>
  

  • And finally the query to fetch 2nd dropdown's options in the AJAX processor file fetchOptions.php. You can use $_POST['id_option'] here to fetch the options under it. The database query here should fetch the option_id and option_name fields for every option (as expected by the jquery code inside $.each) and return a json encoded array like this:-

    return json_encode(array("subjects" => $resultOfQuery));
    

Second Method (Using only javascript)

• Fetch all the data for the second dropdown grouped by the field of the first dropdown. E.g. let's take courses displayed in the first dropdown and subjects under courses displayed in the 2nd

  • Create all the options of the 2nd dropdown. Assign classes equal to the courses while creating the options like this:-

    $querycourse = "SELECT course, subject FROM subjects WHERE subject IS NOT NULL GROUP BY course ";
    $procc = mysqli_prepare($link, $querycourse);
    $queryc =  mysqli_query($link, $querycourse) or die(mysqli_error($link));
    
    echo "<select  name='subjects[]' value='' multiple='multiple' size=100>";
    echo "<option value=''>All</option>";
                while($ntc=mysqli_fetch_array($queryc))
                {//Array or records stored in $nt
                    echo "<option value=\"$ntc[subject]\" class=\"$ntc[course]\">\"$ntc[subject]\"</option>";
                }
                echo "</select>";
    

  • Then define onchange="displaySubjectsUnderThisCourse(this);" for the first dropdown and write this javascript :-

     function displaySubjectsUnderThisCourse(object)
     {
         var selectedCourse = $(object).val();
        //Display the options with class = the selected option from first dropdown
        $("."+selectedCourse).removeClass("hidden"); //define a class hidden with display:none;
    
       $('option:not(.selectedCourse)').hide();  // hide all options whose class is other than selectedCourse - Not sure whether this will work or not, though
    
      //Deselect any previous selections
      //If single option selection is allowed
      $('select#secondDropdown option').attr('selected', false); 
      // or following if multiple selection is allowed (needed for IE)
      $('select#secondDropdown option').attr('selectedIndex', -1); 
    
    
    }
    

    The basic idea here is to hide/display option groups but my code may have errors.

Finally, please note, the second method (fetching all the option values) would be better only if you have limited small amount of data and are very sure there will always be less data in future. But, since nobody can be so certain about the future, it is advisable to use the AJAX method always.

Answer 2

There are two methods:

• First, you can load all choices for the second select list into a JavaScript array. When an option is selected in the first select, populate the second with the appropriate options.
• Second, you can use Ajax to make a call to the server and fetch the options for the second select based on the choice of the first. The server would then return a list of options (one per line, tab delimited is how I do it) that you parse and use to populate the second select.

The first option is very fast and easy, but may take a while to load if you have a large list of options for the second select.

The second option is more complicated, but much more flexible.

Here's some Ajax code to get you started:

Create a request:

var HTTP_UNINITIALIZED  = 0;
var HTTP_SETUP_NOTSENT  = 1;
var HTTP_PROCESSING     = 2;
var HTTP_PARTIAL_RESULT = 3;
var HTTP_COMPLETE       = 4;

function createRequest()
{
  var request = null;

  try
  {
    request = new XMLHttpRequest();
  }
  catch( failed_once )
  {
    try
    {
      request = new ActiveXObject( "Msxml2.XMLHTTP" );
    }
    catch( failed_twice )
    {
      try
      {
        request = new ActiveXObject( "Microsoft.XMLHTTP" );
      }
      catch( failed_thrice )
      {
        request = null;
      }
    }
  }

  return( request );
}

Send the request:

var request = createRequest();
function doSearch( value )
{
  getURL = "<url to get list>?Value=" + value;

  request.open( "POST", getURL, true );
  request.onreadystatechange = showResults;
  request.send( null );
}

Use the results:

function showResults()
{
  if( request.readyState == HTTP_COMPLETE && request.status == 200 )
  {
    if( request.responseText != "" )
    {
      var lines = request.responseText.split( "\n" );
      for( i = 0 ; i < lines.length ; i++ )
      {
        var parts = lines[i].split( "\t" );
        // populate the second select
      }
    }
  }
}

How you handle the server side portion is up to you.