<html>
<! CollectionNestedTable.php                                  >
<! Chapter 14, Oracle Database XE PHP Web Programming         >
<! by Michael McLaughlin                                      >
<!                                                            >
<! This demonstrates how to return a set of rows by using     >
<! parallel nested tables from a stored procedure.            >
<head>
<title>
  Chapter 14: CollectionNestedTable.php
</title>
<style>
.e {background-color: #ccccff; font-weight: bold; color: #000000;}
.v {background-color: #cccccc; color: #000000;}
</style>
</head>
<body>
<?php
  // Return successful attempt to connect to the database.
  if ($c = @oci_connect("php","php","xe"))
  {
    // Declare input variables.
    (isset($_GET['begin']))   ? $t_start = (int) $_GET['begin']
                              : $t_start = 1787;
    (isset($_GET['end']))     ? $t_end = (int) $_GET['end']
                              : $t_end = (int) date("Y",time());
    (isset($_GET['country'])) ? $country = $_GET['country']
                              : $country = "USA";

    // Declare a PL/SQL execution command.
    $stmt = "BEGIN
               world_leaders.get_presidents(:t_start
                                           ,:t_end
                                           ,:country
                                           ,:r_president_id
                                           ,:r_president_name
                                           ,:r_tenure
                                           ,:r_party);
             END;";

    // Strip special characters to avoid ORA-06550 and PLS-00103 errors.
    $stmt = strip_special_characters($stmt);

    // Parse a query through the connection.
    $s = oci_parse($c,$stmt);

    // Define variables based on the Oracle VARRAY Collection type.
    $r_president_id = oci_new_collection($c,'PRESIDENT_ID_NTABLE');
    $r_president_name = oci_new_collection($c,'PRESIDENT_NAME_NTABLE');
    $r_tenure = oci_new_collection($c,'TENURE_NTABLE');
    $r_party = oci_new_collection($c,'PARTY_NTABLE');

    // Bind PHP variables to the OCI types.
    oci_bind_by_name($s,':t_start',$t_start);
    oci_bind_by_name($s,':t_end',$t_end);
    oci_bind_by_name($s,':country',$country);
    oci_bind_by_name($s,':r_president_id',$r_president_id,-1,SQLT_NTY);
    oci_bind_by_name($s,':r_president_name',$r_president_name,-1,SQLT_NTY);
    oci_bind_by_name($s,':r_tenure',$r_tenure,-1,SQLT_NTY);
    oci_bind_by_name($s,':r_party',$r_party,-1,SQLT_NTY);

    // Execute the PL/SQL statement.
    if (oci_execute($s))
    {
      // Declare variable and open HTML table.
      $out = '<table border="1" cellpadding="3" cellspacing="0">';

        $out .= '<tr>';
        $out .= '<td class="e">#</td>';
        $out .= '<td class="e">President Name</td>';
        $out .= '<td class="e">Tenure</td>';
        $out .= '<td class="e">Party</td>';
        $out .= '</tr>';

      // Read parallel collections.
      for ($i = 0;$i < $r_president_id->size();$i++)
      {
        $out .= '<tr>';
        $out .= '<td class="v">'.$r_president_id->getelem($i).'</td>';
        $out .= '<td class="v">'.$r_president_name->getElem($i).'</td>';
        $out .= '<td class="v">'.$r_tenure->getElem($i).'</td>';
        $out .= '<td class="v">'.$r_party->getElem($i).'</td>';
        $out .= '</tr>';
      }

      // Close HTML table.
      $out .= '</table>';

    }

    // Print the HTML table.
    print $out;

    // Disconnect from database.
    oci_close($c);
  }
  else
  {
    // Assign the OCI error and format double and single quotes.
    $errorMessage = oci_error();
    print htmlentities($errorMessage['message'])."<br />";
  }

  // Strip special characters, like carriage or line returns and tabs.
  function strip_special_characters($str)
  {
    $out = "";
    for ($i = 0;$i < strlen($str);$i++)
      if ((ord($str[$i]) != 9) && (ord($str[$i]) != 10) &&
          (ord($str[$i]) != 13))
        $out .= $str[$i];

    // Return pre-parsed SQL statement.
    return $out;
  }
?>
</body>
</html>
