corner imagecorner image
FeaturesPluginsDocs & SupportCommunityPartners

Creating a CRUD Application with PHP

Lesson 7: Updating and Deleting Entries in the Database

In this lesson you will expand the application's functionality with two features:

The implementation of these two features will affect the editWishList.php and editWish.php files. A new file deleteWish.php will be created.

The current document is a part of the Creating a CRUD Application in the NetBeans IDE for PHP tutorial.

Lessons in this tutorial:

Content on this page applies to NetBeans IDE 6.5
  1. Creating a CRUD Application with PHP - Main page
  2. Creating a MySQL Database
  3. Designing the Application. Reading from the Database
  4. Creating a New Application User
  5. Optimizing the Code
  6. Adding Security. Implementing Application User Logon
  7. Writing a New Entry into the Database
  8. => Updating and Deleting Entries in the Database
  9. Making the Application Look Better Using the CSS Technology
  10. Deploying the Application on a Remote Web Server

Contents of this lesson

Application Source Code from the Previous Lesson

Click here to download the source code that reflects the project state after the previous lesson is completed.

Editing a Wish

The functionality supports the following use case:
  • On the editWishList.php page, the user presses the Edit button to the right of a wish. The editWish.php page with the data of the selected wish opens.
  • The user changes the description and/or the due date of the wish and presses the Save Changes button.
  • If the description is not filled in, an error message is displayed and the user returns to the editWish.php page.
  • If the description is filled in, the application returns to the editWishList.php page, where the wish is updated.

The implementation consists of the following steps:

Implementing the Edit Button

In editWishList.php, a table with the wishes of a wisher is implemented by a loop (a while statement) that displays rows with wishes while the wishes are selected from the database. Add an Edit button as the extreme right cell in a row.

  1. To transfer the ID of a wish through the HTML input form, store it in a variable. Enter the following code line at the end of the while loop: 
    $wishID = $row["id"];
    
?>
  2. To implement the Edit button, add a table inside the "while" loop. To put an HTML table inside the loop, first delete the closing curly braket from the loop, then type or paste the following code beneath the PHP block. The while loop is now closed by the bracket in the PHP block you added after the table, after the echo "</tr>\n"; operation. 
        <td>
      <form name="editWish" action="editWish.php" method="GET">
       <input type="hidden" name="wishID" value="<?php echo $wishID; ?>"/>
       <input type="submit" name="editWish" value="Edit"/>
      </form>
    </td>
<?php
   echo "</tr>\n";
}
?>

    The entire table, including the form with the Edit button inside the while loop, now looks like this:

    <table border="black">
        <tr><th>Item</th><th>Due Date</th></tr>
        <?php
        require_once("Includes/db.php");
        $wisherID = WishDB::getInstance()->get_wisher_id_by_name(mysql_real_escape_string($_SESSION["user"]));
        $result = WishDB::getInstance()->get_wishes_by_wisher_id($wisherID);
        while($row = mysql_fetch_array($result)) {
            strip_tags($row["description"],'<br><p><h1>');
            echo "<tr><td>" . $row["description"]."</td>";
            strip_tags($row["due_date"],'<br><p><h1>');
            echo "<td>".$row["due_date"]."</td>";
            echo "</tr>\n";
            $wishID = $row["id"];
                            
            ?>
            <td>
                <form name="editWish" action="editWish.php" method="GET">
                    <input type="hidden" name="wishID" value="<?php echo $wishID; ?>"/>
                    <input type="submit" name="editWish" value="Edit"/>
                </form>
            </td>
            <?php
            echo "</tr>\n";
        }
        ?>
    </table>

    The form contains a submit field, which implements the Edit button, and a hidden field wishID for transferring the ID of the wish displayed to left of the Edit button. The ID of the wish is stored in the $wishID variable.

  3. Remove the first echo "</tr>\n"; operation, the one above $wishID = $row["id"]. Otherwise you close the row before the last </td> and your table will not display correctly.

Expanding the $wish Array

Upon pressing the Edit button on the editWishList.php page, the ID of the selected wish is transferred to the editWish.php page through the Server Request method GET. To store the id of the wish, you need to add a new element to the $wish array.

As when adding a new wish, the input form can be accessed both from the editWishList.php page and from the editWish.php page after an unsuccessful attempt to save. The cases are distinguished by the Server Request method through which the data is transferred. GET indicates that the form is displayed when the user first gets to the page by pressing Edit Wish. POST indicates that the user is redirected to the form after attempting to save a wish without a description.

Replace the PHP block in the HTML <body> above the EditWish input form with the following: 

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST")
$wish = array("id" => $_POST["wishID"], "description" => $_POST["wish"], "due_date" => $_POST["dueDate"]);
else
if (array_key_exists("wishID", $_GET))
$wish = mysql_fetch_array(WishDB::getInstance()->get_wish_by_wish_id($_GET["wishID"]));
else
$wish = array("id" => "", "description" => "", "due_date" => "");
?>

The code initializes the $wish array with three elements: id, description, and due_date. The values of these elements depend on the Server Request method. If the Server Request method is POST, the values are received from the input form. Otherwise, if the Server Request method is GET and the $_GET array contains an element with the key "wishID", the values are retrieved from the database by the function get_wish_by_wish_id. Finally, if the Server Request method is neither POST nor GET, which means the Add New Wish use case takes place, the elements are empty.

The preceding code covers the cases for creation and editing wishes. Now you need to update the input form so that it can be also used for both cases.

Updating the HTML Input Form - Unifying the Implementation of Creation and Editing

Currently the input form works for creation of a new wish when the wish has no id. To unify the tasks of creating and editing wishes, you need to add a hidden field for transferring the ID of a wish. The value of the hidden field must be retrieved from the $wish array. The value must be an empty string during the creation of a new wish. If the wish is edited, the value of the hidden field must change to the ID of the wish. To create this hidden field, add the following line to the top of the EditWish input form: 
<input type="hidden" name="wishID" value="<?php echo $wish["id"];?>" />

Updating the Wish in the Database

Now you need to update the code that verifies the input data and inserts the wish to the database. The current code does not distinguish between creating a new wish case and updating an existing one. In the current implementation, a new record is always added to the database because the code does not verify the value of the wish ID transferred from the input form.

You need to add the following functions:

  • If the transferred element "wishID" is an empty string, create a new wish.
  • Otherwise, if the element "wishID" is not an empty string, update the wish.

To update editWish.php so that it verifies if a wish is new and updates it if it is not new:

  1. Add the update_wish function to db.php. 
    public function update_wish($wishID, $description, $duedate){
    $description = mysql_real_escape_string($description);
        return mysql_query("UPDATE wishes SET description = '" . $description . "',
                       due_date = " . $this->format_date_for_sql($duedate) 
                           . " WHERE id =" . $wishID);
    }
  2. Add the get_wish_by_wish_id function to db.php. 
    public function get_wish_by_wish_id ($wishID) {
        return mysql_query("SELECT * FROM wishes WHERE id = " . $wishID);
    }
  3. In the main PHP block of editWish.php, add a condition to the else statement that inserts the wish to the database, changing it to an else if statement: 
    else if ($_POST["wishID"]=="") {
        WishDB::getInstance()->insert_wish($wisherId, $_POST["wish"], $_POST["dueDate"]);
        header('Location: editWishList.php' );
        exit;
    }
  4. Type or paste another else if statement below the one you just edited: 
    else if ($_POST["wishID"]!="") {
        WishDB::getInstance()->update_wish($_POST["wishID"], $_POST["wish"], $_POST["dueDate"]);
        header('Location: editWishList.php' );
        exit;
}

The code checks that the wishID element in the $_POST array is not an empty string, which means that the user was redirected from the editWishList.php page by pressing the Edit button and that the user has filled in the description of the wish. If the check is successful, the code calls the function update_wish with the input parameters wishID, description, and dueDate. These parameters are received from the HTML input form through the POST method. After update_wish is called, the application is redirected to the editWishList.php page and the PHP processing is canceled.

Testing the Edit Wish Functionality

  1. Run the application. On the index.php page, fill in the fields: in the Username field, enter "Tom", in the Password field, enter "tomcat".
    The user logs on to edit their wish list
  2. Press the Edit My Wish List button. The editWishList.php page opens.
    The Edit button is added to the <tt>editWishList.php</tt> page
  3. Click Edit next to Icecream. The editWish.php page opens.
    The <tt>editWish.php</tt> page with a form for editing a wish. The fields are filled in with the wish data.
  4. Edit the fields and press Back to the List. The editWishList.php page opens but the changes are not saved.
  5. Press Edit next to Icecream. Clear the Describe your wish field and press Save Changes. An error message is displayed.
    The form for editing wish shows anerror message: the description is not filled in
  6. Enter Chocolate icecream in the Describe your wish field and press Save Changes. The editWishList.php page opens with the updated list.
    editWishList.php page: the updatedwish is on the list

Deleting a Wish

Now that you can create, read, and update wishes, add functionality for deleting a wish.

To enable the user to delete wishes:

  1. Add a delete_wish function to db.php. 
    function delete_wish ($wishID){
        return mysql_query("DELETE FROM wishes WHERE id = " . $wishID);
    }
  2. Create a new PHP file named deleteWish.php and enter the following code into the <? php ?> block: 
    require_once("Includes/db.php");
    WishDB::getInstance()->delete_wish ($_POST["wishID"]);
    header('Location: editWishList.php' );
    The code enables the use of the db.php file. It then calls the function delete_wish from an instance of WishDB, with the wishID as the input parameter. Finally, the application is redirected to the editWishList.php page.
  3. To implement the Delete button, enter the following code block inside the while loop in editWishList.php, directly below the code block for the editWish button: 
    <td>
    <form name="deleteWish" action="deleteWish.php" method="POST">
        <input type="hidden" name="wishID" value="<?php echo $wishID; ?>"/>
        <input type="submit" name="deleteWish" value="Delete"/>
    </form>
</td>
    The HTML input form contains a hidden field for the wishID and a submit button labelled Delete.

Testing the Delete Wish Functionality

To check that the functionality is implemented correctly, press Delete next to any item on the editWishList.php page. The item is no longer on the list.

editWishList.php page: the wish is deleted

Application Source Code after the Current Lesson Is Completed

Click here to download the source code that reflects the project state after the lesson is completed

Next Steps

<< Previous lesson

Next lesson >>

Back to the Tutorial main page



To send comments and suggestions, get support, and keep informed on the latest developments on the NetBeans IDE PHP development features, join the mailing list.

Back to the PHP Learning Trail
Companion
Projects: 		MySQL Database Server   GlassFish Community: an Open Source Application Server   Open Solaris  Open JDK: an Open SourceJDK   Mobile & Embedded Community     Sponsored by 
Sponsored by Sun Microsystems