FormAPI cannot get checkboxes return value from AJAX call via external database query

Question

I have not been able to get the return #options value when using AJAX for a dependent checkbox after selecting a value from select. My AJAX isn't great, and I've searched all over, I'm hoping someone can point me in the right direction.

There have been many iterations and I have moved things around, used drupal_map_assoc() with $options2 and without, tried just pulling the COURSE_NAME as a single value and mapping it with drupal_map_assoc() and without. I put the line $options2 = array('' => 'No Courses Available'); in as a test to see what it would return on the form, and it will create a single checkbox with the value of "No Courses Available". I also included an array that did work, the original $options that is commented out toward the bottom of the code snippet. If I enable that, it will show those two courses every time. Even when I do dvr($options2), the response text shows an array similar to the hardcoded $options array but no checkboxes will display on the form whatsoever.

I have sanitized the code snippet and changed the name of the Oracle view I am querying, it is not really called oracle.view. Can anybody please tell me where I am going wrong?

Thank You!

$form['xyz']['existing_credits']=array(
    '#title' => t('How many credits do you have?'),
    '#type' => 'select',
    '#options' => drupal_map_assoc(array('' => '-- select # of credits --','0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15')),
  );

  $db = "(DESCRIPTION=
        (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=192.168.1.1)
          (PORT=8003)
        )
        (CONNECT_DATA=
          (SERVER=dedicated)
          (SERVICE_NAME=TEST)
        )
      )";

  $conn = oci_connect('username', 'password', $db);
  if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
  }

  $stid = oci_parse($conn, "select * from oracle.view order by course_title, registration_start_date");
  oci_execute($stid);

  $options1 = array();
  $options1[] = '- select a school -';
  $xyz = array();
  $selected = isset($form_state['values']['school']) ? $form_state['values']['school']: key($options1);
  while (($schools = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    // put the contents of the db records in a persistent array
    $xyz[] = $schools;
  }
  // looping through the $hsa array, pulling out DISTINCT school names and
  // adding them to the School select field
  for ($x = 0; $x <count($xyz); $x++) {
    $school = $xyz[$x]["SCHOOL_NAME"];
    if(!in_array($school, $options1)){
      $options1[$school] = $school;
    }
  }
  $form['xyz']['school'] = array(
    '#title' => t('School'),
    '#type' => 'select',
    '#options' => $options1,
    '#default_value' => $selected,
    '#ajax' => array(
      'callback' => 'xyz_ajax_callback',
      'event' => 'change',
      'wrapper' => 'xyz-courses',
    ),
    '#states' => array(
      'invisible' => array(
        ':input[name="existing_credits"]' => array(
            array('value' => t('-- select # of credits --')),
            array('value' => t('13')),
            array('value' => t('14')),
            array('value' => t('15')),
        ),
      ),
    ),
  );

  $form['xyz']['courses'] = array(
      '#type' => 'checkboxes',
      '#title' => t('Select Course(s)'),
      '#options' => _xyz_get_courses($selected),
      '#default_value' => isset($form_state['values']['courses']) ? $form_state['values']['courses'] : '',
      '#prefix' => '<div id="xyz-courses">',
      '#suffix' => '</div>',
      '#states' => array(
        'invisible' => array(
          ':input[name="school"]' => array('value' => '0'),
        ),
      ),
    );

function xyz_ajax_callback($form, &$form_state) {
  return $form['courses'];
}

function _xyz_get_courses($key=NULL) {
  $options2 = array('' => 'No Courses Available');

  $db = "(DESCRIPTION=
        (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=192.168.1.1)
          (PORT=8003)
        )
        (CONNECT_DATA=
          (SERVER=dedicated)
          (SERVICE_NAME=TEST)
        )
      )";

  $conn = oci_connect('username', 'password', $db);
  if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
  }

  $stid = oci_parse($conn, "select COURSE, COURSE_TITLE from oracle.view WHERE school_name = '$key'");
  oci_execute($stid);

  while (($course_list = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {

    $course_id = $course_list["COURSE"];
    $course_title = $course_list["COURSE_TITLE"];
    $options[$course_id] = $course_title;

  }

  //$options = drupal_map_assoc(array('EDUC648GUB' => 'Dmsns of Lrng & Ltrcy', 'EDUC532GUL' => 'Tchng Stdnts with Dsblts 7-12'));

  $options2 = drupal_map_assoc($options);
  //dvr($options2);
  return $options2;
}

Answer 1

So, I had a difficult time finding examples that showed me how to interact with an external database like Oracle. I wound up solving my own problem and wanted to post my corrected code here in case anybody ever wants to use it for reference.

$form['xyz']['existing_credits']=array(
    '#title' => t('How many credits do you have?'),
    '#type' => 'select',
    '#options' => drupal_map_assoc(array('' => '-- select # of credits --','0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15')),
  );

  $db = "(DESCRIPTION=
        (ADDRESS=
          (PROTOCOL=TCP)
          (HOST=192.168.1.1)
          (PORT=8003)
        )
        (CONNECT_DATA=
          (SERVER=dedicated)
          (SERVICE_NAME=TEST)
        )
      )";

  $conn = oci_connect('username', 'password', $db);
  if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
  }

  $stid = oci_parse($conn, "select * from oracle.view order by course_title, registration_start_date");
  oci_execute($stid);

  $options1 = array();
  $options1[] = '- select a school -';
  $xyz = array();
  $selected = isset($form_state['values']['school']) ? $form_state['values']['school']: key($options1);
  while (($schools = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) != false) {
    // put the contents of the db records in a persistent array
    $xyz[] = $schools;
  }
  // looping through the $hsa array, pulling out DISTINCT school names and
  // adding them to the School select field
  for ($x = 0; $x <count($xyz); $x++) {
    $school = $xyz[$x]["SCHOOL_NAME"];
    if(!in_array($school, $options1)){
      $options1[$school] = $school;
    }
  }
  $form['xyz']['school'] = array(
    '#title' => t('School'),
    '#type' => 'select',
    '#options' => $options1,
    '#default_value' => $selected,
    '#ajax' => array(
      'callback' => 'xyz_ajax_callback',
      'event' => 'change',
      'wrapper' => 'xyz-courses',
    ),
    '#states' => array(
      'invisible' => array(
        ':input[name="existing_credits"]' => array(
            array('value' => t('-- select # of credits --')),
            array('value' => t('13')),
            array('value' => t('14')),
            array('value' => t('15')),
        ),
      ),
    ),
  );

  $form['xyz']['courses'] = array(
      '#type' => 'checkboxes',
      '#title' => t('Select Course(s)'),
      '#options' => _xyz_get_courses($selected),
      '#default_value' => isset($form_state['values']['courses']) ? $form_state['values']['courses'] : '',
      '#prefix' => '<div id="xyz-courses">',
      '#suffix' => '</div>',
      '#states' => array(
        'invisible' => array(
          ':input[name="school"]' => array('value' => '0'),
        ),
      ),
    );

function xyz_ajax_callback($form, &$form_state) {
  return $form['uft']['courses'];
}

function _xyz_get_courses($key=NULL) {
    $course_id = array();
    $course_title = array();

      $db = "(DESCRIPTION=
            (ADDRESS=
              (PROTOCOL=TCP)
              (HOST=192.168.1.1)
              (PORT=8003)
            )
            (CONNECT_DATA=
              (SERVER=dedicated)
              (SERVICE_NAME=TEST)
            )
          )";

  $conn = oci_connect('username', 'password', $db);
  if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
  }

  $query = "SELECT * FROM oracle.view WHERE school_name = '$key'";

  $stid = oci_parse($conn, $query);

  oci_execute($stid);

  $courses[] = oci_fetch_all($stid, $res);
  $course_id = $res["COURSE"];
  $course_title = $res["COURSE_TITLE"];
  $course_options = array_combine($course_id, $course_title);
  $options = drupal_map_assoc($course_options);

  oci_free_statement($stid);
  oci_close($conn);

  return $options;
}