<html>
<! InsertItemRecord.php                                       >
<! Chapter 13, Oracle Database XE PHP Web Programming         >
<! by Michael McLaughlin                                      >
<!                                                            >
<! This script demonstrates how to insert a row of data by    >
<! specific column names.                                     >
<head>
<title>
  Chapter 13: InsertItemRecord.php
</title>
<style>
.e {background-color: #ccccff; font-weight: bold; color: #000000;}
.v {background-color: #cccccc; color: #000000;}
</style>
</head>
<body>
<?php
  // Connect to the database.
  if ($c = @oci_connect("php","php","xe"))
  {
    // Parse a insert to a resource statement.
    $s = oci_parse($c,"INSERT INTO item VALUES
                       ( item_s1.nextval
                       ,'9736-06125-4'
                       ,(SELECT   common_lookup_id
                         FROM     common_lookup
                         WHERE    common_lookup_type = 'DVD_WIDE_SCREEN')
                       ,'The Adventures of Indiana Jones'
                       ,''
                       ,'PG-13','21-OCT-03'
                       , 3, SYSDATE, 3, SYSDATE)");

    // Execute query without an implicit commit.
    oci_execute($s,OCI_DEFAULT);

    // Query before commit, commit insert, print line break and requery.
    query();
    oci_commit($c);
    print "Inserted [".oci_num_rows($s)."] row(s) committed.<br />";
    query();

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

  // Query results in another connection.
  function query()
  {
    // Open a new connection.
    if ($nc = @oci_new_connect("php","php","xe"))
    {
      // Parse a query to a resource statement.
      $q = oci_parse($nc,"SELECT   item_id AS ID
                          ,        item_title AS TITLE
                          ,        item_release_date AS RELEASE_DATE
                          FROM     item
                          WHERE    REGEXP_LIKE(item_title,'Indiana Jones')");

      // Query for committed records.
      oci_execute($q);

      // Assign results.
      render_query($q);

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

  // Render query results.
  function render_query($rs)
  {
    // Declare control variable.
    $no_row_fetched = true;

    // Open the HTML table.
    print '<table border="1" cellspacing="0" cellpadding="3">';

    // Read fetched data.
    while (oci_fetch($rs))
    {
      // Only print header once.
      if ($no_row_fetched)
      {
        // Read fetched headers.
        print '<tr>';
        for ($i = 1;$i <= oci_num_fields($rs);$i++)
          print '<td class="e">'.oci_field_name($rs,$i).'</td>';
        print '</tr>';

        // Disable header printing.
        $no_row_fetched = false;
      }

      // Print open and close HTML row tags and columns data.
      print '<tr>';
      for ($i = 1;$i <= oci_num_fields($rs);$i++)
        print '<td class="v">'.oci_result($rs,$i).'</td>';
      print '</tr>';
    }

    // Close the HTML table.
    print '</table>';
  }
?>
</body>
</html>
