Creating a Database Driven Application With PHP

Lesson 6: Adding a New Wish to the Database

Content on this page applies to NetBeans IDE 7.2, 7.3, 7.4 and 8.0

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

To implement this functionality, you edit the editWishList.php file and create the new file editWish.php.

Page flow diagram highlighting Lesson 6 additions

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


Application Source Code from the Previous Lesson

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

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

Submitting a New Wish

The user submits a new wish in the following steps:

  1. The user logs in, switches to the editWishList.php page, and presses the Add Wish button. The editWish.php page opens, displaying an HTML form.
  2. In the HTML form, the user enters a description of a wish and possibly the date by when he/she wants it and presses the Save Changes button.
  3. If a form is submitted without a description of the wish, the user is returned to the form to try again. If the user submitted a due date but no description, that date is redisplayed when the form reloads.

To enable this procedure for the user, you add the following functionality to the application:

Adding the User Interface Components

To add functionality for adding a new wish:

  1. Implement the Add Wish button. In the editWishList.php file, add the following HTML code beneath the PHP block:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
        <head>
    
           <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        </head>
        <body>
            <form name="addNewWish" action="editWish.php">            
                <input type="submit" value="Add Wish">
            </form>
        </body>
    </html>

    Note: You can ignore warnings from the HTML validator.

    The form contains an "Add Wish" input field of the submit type. This field implements the "Add Wish" button. When the user clicks Add Wish, they are redirected to the editWish.php page. Because no data is transferred through this form, no Server Request method is used.

  2. Add a table above the addNewWish form that displays the existing wishes for the wisher. The code is similar to wishlist.php.

    For the MySQL database:

    <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($_SESSION["user"]);
    $result = WishDB::getInstance()->get_wishes_by_wisher_id($wisherID);
    while($row = mysqli_fetch_array($result)) {
    echo "<tr><td>" . htmlentities($row['description']) . "</td>";
    echo "<td>" . htmlentities($row['due_date']) . "</td></tr>\n";
    }
    ?>
    </table>

    For the Oracle database:

    <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($_SESSION["user"]);
        $stid = WishDB::getInstance()->get_wishes_by_wisher_id($wisherID);
        while ($row = oci_fetch_array($stid)) {
    echo "<tr><td>" . htmlentities($row['DESCRIPTION']) . "</td>";
    echo "<td>" . htmlentities($row['DUE_DATE']) . "</td></tr>\n"; } ?> </table>
  3. Create the editWish.php PHP file in the Source Files folder.
  4. In editWish.php, implement the Add Wish form. Type or paste the following code below the <? php ?> block:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    
    <html>
        <head>
    
           <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        </head>
        <body>
            <form name="editWish" action="editWish.php" method="POST">
    Describe your wish: <input type="text" name="wish" value="" /><br/>
    When do you want to get it? <input type="text" name="dueDate" value=""/><br/>
    <input type="submit" name="saveWish" value="Save Changes"/>
    <input type="submit" name="back" value="Back to the List"/> </form> </body> </html>

The Add Wish form contains:

  • Two empty text fields for entering the wish description and due date.
  • The texts to be printed next to the input fields.
  • A submit field that represents a Save Changes button
  • A submit field that represents a Back to the List button for returning to the editWishList.php page

Upon pressing the Add Wish button, the form submits the entered data to the same page, editWish.php, through the Request method POST.

Redisplaying the Due Date After an Unsuccessful Submission

If the user does not fill in a description in the Add Wish form, an error message is displayed and the user returns to the editWish.php page. When the user returns to editWish.php, the Add Wish form should show the value of dueDate if it was entered. In the current implementation of the form, both fields are always empty. To keep entered values, you need to save the data of the new wish in an array. The array will consist of two elements named description and due_date. You then need to change the Add Wish form so it retrieves the value of the dueDate field from the array.

Note: The code that reloads the input form if no description is entered is included in the code that validates the data and enters it to the database. This code is not described in this section. The code in this section only preserves the value of dueDate so that it is displayed if the form is reloaded .

To redisplay the input form after the user submits it unsuccessfully:

  1. Type or paste the following code block inside the HTML <body> element of editWish.php, directly above the input form:
    <?php 
    if ($_SERVER["REQUEST_METHOD"] == "POST")
    $wish = array("description" => $_POST["wish"],
    "due_date" => $_POST["dueDate"]);
    else
    $wish = array("description" => "",
    "due_date" => ""); ?>

    The code checks which Request Server method was used for transferring the data and creates an array named $wish. If the method is POST, which means that the input form is displayed after an unsuccessful attempt to save a wish with an empty description, the elements description and due_date accept the values transferred through POST.

    If the method is not POST, which means that the input form is displayed for the first time after redirection form the editWishList.php page, the elements description and due_date are empty.

    Note: In either case the description is empty. The difference is only in the dueDate.

  2. Update the Add Wish form so that the values of its input fields are retrieved from the $wish array. Replace the lines in the Add Wish form:
    Describe your wish: <input type="text" name="wish"  value="" /><br/>
    When do you want to get it? <input type="text" name="dueDate" value=""/><br/>
    with:
    Describe your wish: <input type="text" name="wish"  value="<?php echo $wish['description'];?>" /><br/>
    When do you want to get it? <input type="text" name="dueDate" value="<?php echo $wish['due_date']; ?>"/><br/>

Verifying the Wisher's Logon

In the editWish.php file, enter the following session handling code inside the <? php ?> block at the top of the file:
session_start();
if (!array_key_exists("user", $_SESSION)) {
    header('Location: index.php');
    exit;
}
The code:
  • Opens the $_SESSION array for retrieving data..
  • Verifies that the array $_SESSION contains an element with the identifier "user".
  • If the check fails, which means that the user is not logged on, redirects the application to the front index.php page and cancels the PHP processing.

To check that session handling works correctly, run the editWish.php file from the IDE. The index.php page opens, because no user has been transferred to the editWish.page through a session.

Inserting the New Wish to the Database

After the user submits a new wish, the application needs to add the wish to the "wishes" database. To enable this functionality, add the following code to the application:

  • Add two more auxiliary functions to the WishDB class in db.php.
    • One function adds a new record to the wishes table.
    • The other function converts dates into the format that the MySQL databases server supports.
  • Add code to editWish.php that will use the new auxilliary functions in WishDB to enter the new wish into the database.

Adding the insert_wish Function to WishDB

This function requires the wisher's id, a description of the new wish, and the due date of the wish as the input parameters and enters this data to the database in a new record. The function does not return any values.

Open db.php and add the function insert_wish into the WishDB class:

For the MySQL database

function insert_wish($wisherID, $description, $duedate){
    $description = $this->real_escape_string($description);
if ($this->format_date_for_sql($duedate)==null){
$this->query("INSERT INTO wishes (wisher_id, description)" .
" VALUES (" . $wisherID . ", '" . $description . "')");
} else
$this->query("INSERT INTO wishes (wisher_id, description, due_date)" .
" VALUES (" . $wisherID . ", '" . $description . "', "
. $this->format_date_for_sql($duedate) . ")"); }

For the Oracle database:

function insert_wish($wisherID, $description, $duedate) {
  $query = "INSERT INTO wishes (wisher_id, description, due_date) VALUES (:wisher_id_bv, :desc_bv, to_date(:due_date_bv, 'YYYY-MM-DD'))"; 
  $stid = oci_parse($this->con, $query);
  oci_bind_by_name($stid, ':wisher_id_bv', $wisherID);
  oci_bind_by_name($stid, ':desc_bv', $description);
  oci_bind_by_name($stid, ':due_date_bv', $this->format_date_for_sql($duedate));
  oci_execute($stid);
  oci_free_statement($stid);
}

The code calls the function format_date_for_sql to convert the entered due date into a format that can be processed by the database server. Then the query INSERT INTO wishes (wisher_id, description, due_date) is executed to enter the new wish to the database.

Adding the format_date_for_sql Function to WishDB

Add the function format_date_for_sql to the WishDB class in db.php. The function requires a string with a date as the input parameter. The function returns a date in the format that can be processed by the database server or null if the input string is empty.

Note: The function in this example uses the PHP date_parse function. This function works only with English-language dates, such as December 25, 2010, and only Arabic numerals. A professional web site would use a date picker.

For the MySQL database:
function format_date_for_sql($date){
if ($date == "")
return null;
else {
$dateParts = date_parse($date);
return $dateParts["year"]*10000 + $dateParts["month"]*100 + $dateParts["day"];
}
}

For the Oracle database:

function format_date_for_sql($date){
    if ($date == "")
        return null;
    else {
        $dateParts = date_parse($date);
        return $dateParts['year']*10000 + '-' + $dateParts['month']*100 + '-' + $dateParts['day'];
   }
}

If the input string is empty, the code returns NULL. Otherwise, the internal date_parse function is called with the $date as the input parameter. The date_parse function returns an array that consists of three elements named $dateParts["year"], $dateParts["month"], and $dateParts["day"]. The final output string is constructed of the elements of the $dateParts array.

Important: The date_parse function recognizes only English dates. For example, it parses "February 2, 2016" but not "2 Unora, 2016".

Note to Oracle Database users: The only format requirement is that the format of the date in the return $dateParts... statement matches the date format in the to_date SQL function in the insert_wish query.

Entering the New Wish Record in the Database

Now that you have developed the auxiliary functions, add code to validate the new wish data and enter the data to the database if it is valid. If the data is not valid, the code must reload the Add Wish form. If the data is invalid because no description has been entered but there is a due date, the due date is saved and redisplayed when the form reloads, thanks to code you developed earlier.

Enter the following code inside the top <? php?> block of editWish.php, below the session handling code:
require_once("Includes/db.php");
    $wisherID = WishDB::getInstance()->get_wisher_id_by_name($_SESSION['user']);

    $wishDescriptionIsEmpty = false;
    if ($_SERVER['REQUEST_METHOD'] == "POST"){
        if (array_key_exists("back", $_POST)) {
           header('Location: editWishList.php' ); 
           exit;
        } else
        if ($_POST['wish'] == "") {
            $wishDescriptionIsEmpty =  true;
        } 
		 else {
           WishDB::getInstance()->insert_wish($wisherID, $_POST['wish'], $_POST['dueDate']);
           header('Location: editWishList.php' );
           exit;
        }
    }
	

The code performs the following functions:

  • Enables the use of the db.php file
  • Gets or creates an instance of the class WishDB
  • Retrieves the id of the wisher who is attempting to add a wish by calling the function get_wisher_id_by_name
  • Initializes the $wishDescriptionIsEmpty flag, which will be used later for showing error messages.
  • Checks that the Request method is POST, which means that the data was submitted from the form for entering the wish data on the editWish.php page itself.
  • Checks whether the $_POST array contains an element with the "back" key

If the $_POST array contains an element with the "back" key, the Back to the List button was pressed before submitting the form. In this case the code redirects the user to the editWishList.php without saving any data that was entered in the fields and stops PHP processing.

If the $_POST array does not contain an element with the "back" key, the data was submitted by pressing the Save Changes button. In this case the code validates whether the wish description is filled in. The code does it by checking whether the element with the "wish" key in the $_POST array is empty and, if the key is empty, changes the $wishDescriptionIsEmpty flag to true. Note that with no further code executed in the PHP block, the Add Wish form reloads.

If the Back to the List button was not pressed and the wish description is filled in, the code calls the function insert_wish with the wisher's id, the description, and the due date for the wish as the input parameters. The code then redirects the user to the editWishList.php page and stops the PHP processing.

Displaying Error Messages

If the user attempts to save a wish but has not entered a description for it, an error message must be displayed.
Enter the following <? php?> block inside the HTML input form, below the "Describe your wish" input field:
<?php
if ($wishDescriptionIsEmpty) echo "Please enter description<br/>";
?>
The error message is displayed if the $wishDescriptionIsEmpty flag is true. The flag is processed during the input form validation.

Returning to the Front index.php Page

The user should be able to return to the front page of the application at any time by pressing a button.
To implement this functionality, enter the following HTML input form in the editWishList.php file, before the closing </body> tag:
<form name="backToMainPage" action="index.php">
<input type="submit" value="Back To Main Page"/>
</form>
The form redirects the user to the front index.php page upon pressing the Back to Main Page button.

Testing the Add 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".
    User Logs on to Edit Wish List
  2. Press the Edit My Wish List button. The editWishList.php page opens.
    Edit Wish List with the Add button added
  3. Press the Back to Main Page button. The index.php page opens.
  4. Logon as Tom and press the Edit My Wish List button again. The editWishList.php page opens.
  5. Press the Add Wish button. The editWish.php page opens. Fill in the form.
    The form for a new wish is filled in
    Press the Back to the List button. The editWishList.php page opens but the entered wish is not added.
  6. Press the Add Wish button again. The editWish.php page opens. Fill in the due date and leave the description empty. Press the Save Changes button. The editWish.php page displays the input form with an error message and filled in due date.
  7. Press the Add Wish button again. The editWish.php page opens. Fill in the form and press the Save Changes button. The editWishList.php page shows an updated list of wishes.
    The new wish is added to the Wish List

Application Source Code after the Current Lesson Is Completed

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

Oracle Database users: 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

get support for the NetBeans

Support


By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2013, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo