// 
//     Licensed to the Apache Software Foundation (ASF) under one
//     or more contributor license agreements.  See the NOTICE file
//     distributed with this work for additional information
//     regarding copyright ownership.  The ASF licenses this file
//     to you under the Apache License, Version 2.0 (the
//     "License"); you may not use this file except in compliance
//     with the License.  You may obtain a copy of the License at
// 
//       http://www.apache.org/licenses/LICENSE-2.0
// 
//     Unless required by applicable law or agreed to in writing,
//     software distributed under the License is distributed on an
//     "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
//     KIND, either express or implied.  See the License for the
//     specific language governing permissions and limitations
//     under the License.
//

:jbake-type: tutorial
:jbake-tags: tutorials 
:jbake-status: published
:syntax: true
:icons: font
:source-highlighter: pygments
:toc: left
:toc-title:
:description: Creating a Database Driven Application With PHP - Apache NetBeans
:keywords: Apache NetBeans, Tutorials, Creating a Database Driven Application With PHP

= Lesson 6: Adding a New Wish to the Database
:jbake-type: tutorial
:jbake-tags: tutorials 
:jbake-status: published
:syntax: true
:icons: font
:source-highlighter: pygments
:toc: left
:toc-title:
:description: Lesson 6: Adding a New Wish to the Database - Apache NetBeans
:keywords: Apache NetBeans, Tutorials, Lesson 6: Adding a New Wish to the Database


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

* <<_submitting_a_new_wish,Adding a new wish>>
* <<_returning_to_the_front_index_php_page,Returning to the front index.php page>>

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

image::images/page-flow-diagram-l6.png[]

The current document is a part of the link:wish-list-tutorial-main-page.html[+Creating a CRUD Application in the NetBeans IDE for PHP+] tutorial.


[[_application_source_code_from_the_previous_lesson]]
== Application Source Code from the Previous Lesson

MySQL users: Click link:https://netbeans.org/files/documents/4/1931/lesson5.zip[+here+] to download the source code that reflects the project state after the previous lesson is completed.

Oracle Database users: Click link:https://netbeans.org/projects/www/downloads/download/php%252Foracle-lesson5.zip[+here+] to download the source code that reflects the project state after the previous lesson is completed.

[[_submitting_a_new_wish]]
== 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:

* <<add-wish-ui-elements,User interface components>>, consisting of an HTML form for adding wishes and a button in  `editWishList.php ` that redirects the user to the form.
* Code for <<_redisplaying_the_due_date_after_an_unsuccessful_submission,redisplaying the due date>> if an incomplete form is submitted.


[[add-wish-ui-elements]]
=== 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:

[source,xml]
----

<!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.


[start=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*:


[source,php]
----

<table border="black">
    <tr>
        <th>Item</th>
        <th>Due Date</th>
    </tr>
    <?php
    $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";
    }
    mysqli_free_result($result);
    ?>
</table>
----

*For the Oracle database:*


[source,php]
----

<table border="black">
    <tr>
        <th>Item</th>
        <th>Due Date</th>
    </tr>
    <?php
    $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";
    }
    oci_free_statement($stid);
    ?>
</table>
----

[start=3]
. Create the  `editWish.php`  PHP file in the Source Files folder.

[start=4]
. In  `editWish.php` , implement the Add Wish form. Type or paste the following code below the <? php ?> block:

[source,xml]
----

<!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]]
=== 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 <<_verifying_the_wisher_s_logon,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:

[source,php]
----

<?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` .


[start=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:

[source,php]
----

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:

[source,php]
----

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]]
== 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:


[source,php]
----

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.


[[insert-new-wish]]
== 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.


[[add-insert-wish]]
=== 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*


[source,php]
----

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:*


[source,php]
----

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.


[[add-format-date-for-sql]]
=== 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:*


[source,php]
----

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:*


[source,php]
----

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.


[[validateAndEnterWishToDatabase]]
=== 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 <<_returning_to_the_front_index_php_page,developed earlier>>.

Enter the following code inside the top <? php?> block of  `editWish.php` , below the session handling code:


[source,php]
----

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]]
=== 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:


[source,php]
----

<?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]]
== 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:


[source,xml]
----

<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]]
== 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".
image::images/user-logon-to-edit-wish-list.png[]

[start=2]
. Press the Edit My Wish List button. The  `editWishList.php`  page opens. 
image::images/edit-wish-list-add-wish.png[]

[start=3]
. Press the Back to Main Page button. The  `index.php`  page opens.

[start=4]
. Logon as Tom and press the Edit My Wish List button again. The  `editWishList.php`  page opens.

[start=5]
. Press the Add Wish button. The  `editWish.php`  page opens. Fill in the form.

image::images/new-wish.png[] 

Press the Back to the List button. The  `editWishList.php`  page opens but the entered wish is not added.

[start=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.

[start=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. 

image::images/edit-wish-list-updated.png[]

[[_application_source_code_after_the_current_lesson_is_completed]]
== Application Source Code after the Current Lesson Is Completed

MySQL users: Click link:https://netbeans.org/files/documents/4/1932/lesson6.zip[+here+] to download the source code that reflects the project state after the lesson is completed.

Oracle Database users: Click link:https://netbeans.org/projects/www/downloads/download/php%252Foracle-lesson6.zip[+here+] to download the source code that reflects the project state after the lesson is completed.

[[_next_steps]]
== Next Steps

link:wish-list-lesson5.html[+<< Previous lesson+]

link:wish-list-lesson7.html[+Next lesson >>+]

link:wish-list-tutorial-main-page.html[+Back to the Tutorial main page+]