<html>
<! BindFetchAllSQL.php                                        >
<! Chapter 13, Oracle Database XE PHP Web Programming         >
<! by Michael McLaughlin                                      >
<!                                                            >
<! This script demonstrates how to return rows of data with a >
<! single oci_fetch_all() call.                               >
<head>
<title>
  Chapter 13: FetchAll.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['limit']))   ? $limit = (int) $_GET['limit']
                              : $limit = 5;
    (isset($_GET['param']))   ? $flag = (int) $_GET['param']
                              : $flag = 0;

    // Parse a query through the connection to a statement.
    $s = oci_parse($c,"SELECT   cr.full_name
                       ,        cr.title
                       FROM     current_rental cr
                       ,       (SELECT   cr.account_number
                                FROM     current_rental cr
                                GROUP BY cr.account_number
                                HAVING   COUNT(account_number) >= :limit) lim
                       WHERE    cr.account_number = lim.account_number");

    // Bind the array variable to the statement resource.
    oci_bind_by_name($s,":limit",$limit);

    // Execute the parsed query without a commit.
    oci_execute($s,OCI_DEFAULT);

    // Declare variables to countrol HTML header formatting.
    $dimensions = array("Column","Row");
    $dimension1 = "";
    $dimension2 = "";
    $index = 0;

    // Print open HTML table tag.
    print '<table border="1" cellspacing="0" cellpadding="3">';

    // Dynamically alter array organization flags.
    switch($flag)
    {
      case 0:
        // Primary index is column name, secondary index is row number.
        oci_fetch_all($s,$array_out,0,-1,OCI_FETCHSTATEMENT_BY_COLUMN|OCI_ASSOC);
        $dimension1 = $dimensions[0];
        $dimension2 = $dimensions[1];
        break;
      case 1:
        // Primary index is column number, secondary index is row number.
        oci_fetch_all($s,$array_out,0,-1,OCI_FETCHSTATEMENT_BY_COLUMN|OCI_NUM);
        $dimension1 = $dimensions[0];
        $dimension2 = $dimensions[1];
        break;
      case 2:
        // Primary index is row number, secondary index is column name.
        oci_fetch_all($s,$array_out,0,-1,OCI_FETCHSTATEMENT_BY_ROW|OCI_ASSOC);
        $dimension1 = $dimensions[1];
        $dimension2 = $dimensions[0];
        break;
      case 3:
        // Primary index is row number, secondary index is column number.
        oci_fetch_all($s,$array_out,0,-1,OCI_FETCHSTATEMENT_BY_ROW|OCI_NUM);
        $dimension1 = $dimensions[1];
        $dimension2 = $dimensions[0];
        break;
      default:
        // Primary index is column name, secondary index is row number.
        oci_fetch_all($s,$array_out,0,-1);
        $dimension1 = $dimensions[0];
        $dimension2 = $dimensions[1];
        break;
    }

    // Print 1st dimension header.
    print '<tr>';
    print '<td class="e">'.$dimension1.'<br />Index</td>';

    // Set initial index, string or zero.
    foreach ($array_out as $name => $value)
    {
      if (!is_numeric($name)) $index = $name;
      break;
    }

    // Print nested array headers.
    for ($i = 0;$i < count($array_out[$index]);$i++)
    {
      print '<td class="e">'.$dimension2.'<br />Index</td>';
      print '<td class="e">'.$dimension2.'<br />Value</td>';
    }

    // Close HTML header row.
    print '</tr>';

    // Read through the first dimension of the record set.
    foreach ($array_out as $name => $value)
    {
      print '<tr>';
      print '<td class="e">'.$name.'</td>';

      // Read through the second dimension of the record set.
      foreach ($value as $subname => $subvalue)
      {
        print '<td class="e">'.$subname.'</td>';
        print '<td class="v">'.$subvalue.'</td>';
      }
      print '</tr>';
    }

    // Print the close HTML table tag.
    print '</table>';

    // 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 />";
  }
?>
</body>
</html>
