PHP`s Oracle Functionality: Inserting and Modifying Rows

(Page 1 of 2 )

In this fourth part of a seven-part series on using Oracle Database XE's features within PHP applications, you'll learn how to insert and modify the rows of a database. This article is excerpted from chapter 32 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Inserting Rows

Inserting data into the database is carried out very much in the same fashion as retrieving information, except that the query often contains variable data. Following an example is the best way to learn this process. Suppose that your companys inventory specialist requires a means for inserting new product information from anywhere. Not surprisingly, the most efficient way to do so is to provide him with a Web interface. Figure 32-1 depicts this Web form for which the source code, called insert_location.php, is provided in Listing 32-4 with a new location ready to insert into the database. 

Figure 32-1.  The location insertion form

Listing 32-4. Location Insertion Form Code (insert_location.php)

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
    <p>Add a Location to the LOCATIONS table.</p>
    <p>
      Location ID Number:<br />
      <input type="text" name="LocationID" size="4" maxlength="4" value="" />
    </p> 
    <p>
      Street Address:<br />
      <input type="text" name="StreetAddress" size="40" maxlength="40" value="" /> 
    </p>
    <p>
      Postal Code:<br />
      <input type="text" name="PostalCode" size="12" maxlength="12" value="" />
    </p>
    <p>
      City:<br />
      <input type="text" name="City" size="30" maxlength="30" value="" />
    </p>
    <p>
      State or Province:<br />
      <input type="text" name="StateOrProvince" size="25" maxlength="25" value="" /> 
    </p> 
    <p>
      Country Code:<br />
      <input type="text" name="CountryCode" size="2" maxlength="2" value="" />
    </p> 
    <p>
      <input type="submit" name="submit" value="Submit!" /> 
    </p>
</form>  

Listing 32-5 contains the source code for the database insertion logic.  

Listing 32-5. Inserting Form Data into an Oracle Table (db_insert_location.php)

<?php

   // If the submit button has been pressed...

   if (isset($_POST['submit']))
   {

      // Connect to the database
      $c = @oci_connect('hr', 'hr', '//localhost/xe')
                or die("Could not connect to Oracle server");

      // Retrieve the posted new location information.
      $LocationID = $_POST['LocationID'];
      $StreetAddress = $_POST['StreetAddress'];
      $PostalCode = $_POST['PostalCode'];
      $City = $_POST['City'];
      $StateOrProvince = $_POST['StateOrProvince'];
      $CountryCode = $_POST['CountryCode'];

      // Insert the location information into the LOCATIONS table
      $s = oci_parse($c, "insert into locations
                           (location_id, street_address, postal_code,
                            city, state_province, country_id)
                           values ($LocationID, '$StreetAddress', '$PostalCode',
                                   '$City', '$StateOrProvince', '$CountryCode')");
      $result = oci_execute($s);

      // Display an appropriate message on either success or failure
      if ($result)
      { 
         echo "<p>Location successfully inserted!</p>";
         oci_commit($c);
      }
      else
      {
         echo "<p>There was a problem inserting the location!</p>";
         var_dump(oci_error($s));
      }

      oci_close($c);
    }

  // Include the insertion form
  include "insert_location.php";

?>

Note the use of the include directive in the file db_insert_location.php. You can place all of the code in one file but splitting it up this way makes code maintenance easier and facilitates code reuse. Notice the oci_commit() call; this option permanently saves the inserted row, although in this example, calling oci_close() also saves the inserted row. We talk more about transactions in Chapter 33.

Querying the table from the SQL Commands page within the Oracle Database XE Web interface, you can see in Figure 32-2 that the new row exists in the LOCATIONS table.

Figure 32-2.  Updated contents of the LOCATIONS table

Next: Modifying Rows >>
 

More Database Articles Articles
More By Apress Publishing

blog comments powered by Disqus