In this fifth part of a seven-part article series, you will learn how to use PHP's Oracle functionality to delete and count selected rows in an Oracle 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).
Deleting Rows
Like data modification, data deletion is a three-step process involving target data selection, the deletion request, and the delete operation itself. You can use many different form types to delete items, such as multivalued form components introduced in Chapter 13. In this example, however, we use the code examples in Listings 32-6 and 32-7 as the basis for the delete version of the scripts in Listings 32-8 and 32-9.
Listing 32-8.Selecting the Location Number for Deletion (delete_location.php)
<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post"> <p>Delete a Location from the LOCATIONS table.</p> <p> Location ID Number:<br /> <input type="text" name="LocationID" size="4" maxlength="4" value="" /> </p> <p> <input type="submit" name="submit" value="Submit!" /> </p> </form>
Listing 32-9 contains the database delete step invoked once the location ID has been selected for deletion.
Listing 32-9.Deleting a Location (db_delete_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 existing location information // and delete the row. $LocationID = $_POST['LocationID'];
// Update the Postal Code information into the LOCATIONS table $s = oci_parse($c, "delete from locations where location_id = $LocationID");
// Display an appropriate message if ($result) { echo "<p>Locations deleted: " . $rows_affected . "</p>"; oci_commit($s); } else { echo "<p>There was a problem deleting a location!</p>"; var_dump(oci_error($s)); }
oci_close($c); }
// Include the deletion form include "delete_location.php";
?>
As you can see, the deletion process is like all the other processes described thus far. Note that we use oci_num_rows() to provide feedback to the user that the row or rows in question are properly deleted. If this function returns 0, no rows were found; if it returns -1, an error occurred. Otherwise, it returns the total number of rows affected by the DELETE command, which in this situation should always be 1. You'll learn more about this function in the next section.
