Lesson two: Designing the Application. Reading from the Database

  • Creating a PHP Project
  • Defining a Folio Flow Diagram
  • Adding a Form To index.php
  • Creating wishlist.php and Testing the Application
  • Establishing the Connexion and Getting the Wisher ID
  • Displaying a Table of Wishes
  • Application Source Code later the Current Lesson Is Completed
  • Next Step

In this lesson you lot create and configure the PHP project to develop your application, create a list of pages in the awarding, and ascertain the relations between them. Y'all besides develop basic application functionality and examination it against the data you entered in the sample database in lesson 1.

The PHP code you write in this lesson performs the following functions:

  1. Gets the name of a person that the user types in.

  2. Checks whether that person is really in the database. Exits with an error message if the person is not in the database.

  3. Displays a table of that person's wishes.

The current document is a role of the Creating a Database-Driven Application in the NetBeans IDE for PHP tutorial.

Creating a PHP Projection

Select File > New Projection (Ctrl-Shift-Northward on Windows and Linux, ⌘-Shift-N on MacOS). Create a new PHP project named "wishlist". When you create a PHP project, it contains the index file index.php by default. For information on creating and configuring a PHP project, come across Setting Up a PHP Projection.

Defining a Page Menstruation Diagram

The scope of your awarding covers the post-obit use cases:

  1. The user views the wish list of a person.

  2. The user registers as a new wisher.

  3. The user logs in and creates her/his wish list.

  4. The user logs in and edits his/her wish list.

To comprehend this bones functionality, yous will demand to implement the following PHP files:

  1. The "forepart" page index.php for logging in, registering, and switching to wish lists of other users.

  2. The wishlist.php page for viewing the wish list of a particular wisher.

  3. The createNewWisher.php page for registering as a wisher.

  4. The editWishList.php page for editing a wish listing by its possessor.

  5. The editWish.php page for creating and editing wishes.

page flow diagram

Now that you take finished the preliminary steps, you can starting time implementing the basic functionality of your application. Start with viewing the wish list of a wisher. This characteristic does not involve any validations and can exist hands tested as you accept already entered the test data into the database. The feature'southward functionality will be implemented on two pages, index.php and wishlist.php.

Adding a Form To index.php

The index.php file will not contain whatever PHP lawmaking and then y'all can hands remove the following cake:

The index.php file is used for ii purposes:

  • Displaying a page with controls for entering data.

  • Transferring the entered data to another PHP file, where the data is processed. In this tutorial, the data is passed to a file named wishlist.php , which you create and code in the next section.

These actions are performed using an HTML course. Each HTML form contains:

  • A gear up of fields that correspond to the controls on the folio.

  • The "action" that is performed later the user submits the data on the form. The action is represented by the path to the page that processes the data.

To add a class to index.php:

  1. Switch to the Projects window, expand your project node and the Source Files node, and double click the index.php file. The index.php file opens in the chief IDE editor area. The file contains a template for inbound HTML and PHP code.

You tin can ignore warnings from the HTML validator.

  1. Remove the PHP block. The index.php file will not contain whatever PHP code.

remove php block

  1. Open the Palette from the Window carte du jour or by pressing Ctrl-Shift-viii.

  2. From the Palette's* HTML Forms* section, drag and drop a Form into the <body> section of alphabetize.php .

form dnd

  1. The Insert Form dialog opens. In the Action field, type the path to the file to which the form will transfer information. In this case, type in wishlist.php . (You will create this file in the same location as index.php . Meet Creating wishlist.php and Testing the Application.) Select the GET method for transferring information. Give the form an arbitrary proper noun, such equally wishList . Click OK when you are done.

insert form dialog

The file now looks like this:

blank form

  1. Between the opening and endmost tags of the course, type the text "Show wish listing of: ".

  2. Drag a Text Input component from the HTML Forms section of the Palette to the space afterward the text "Prove wish list of: ". The Insert Text Input dialog opens.

  3. Name the input user . Select input type text . Leave all other fields empty and click OK.

insert text input

The file now looks similar this:

form with text input

  1. Add a blank line above the </class> tag. Into this blank line, drag and drop a Push component from the* HTML Forms* section of the Palette.

  2. The Insert Button dialog opens. Type Go into the Label field and click OK.

insert button dialog

  1. The form now looks like the code below, with ane difference. In the lawmaking below, the method attribute is explicit in the <form> tag. NetBeans IDE did not add together a method attribute to your grade because Go is the default value of this aspect. However, yous might understand the code more than easily if the method attribute is explicit.

                <form activeness="wishlist.php" method="Get" name="wishList">     Show wish list of:     <input type="text" proper name="user" value=""/>     <input type="submit" value="Become" /> </form>

Note the following elements of the grade:

  • The opening <form> tag contains the action attribute. The activity attribute specifies the file to which the form transfers information. In this case, the file is named wishlist.php and it is in the same folder as alphabetize.php . (You will create this file in the section Creating wishlist.php and Testing the Application.)

  • The opening <form> tag also contains the method to exist practical to transferring information (GET). PHP uses a $_GET or $_POST array for the values passed by this course depending on the value of the method aspect. In this case, PHP uses $_GET .

  • A text input component. This component is a text field for entering the proper noun of the user whose wish listing one wants to view. The starting value of the text field is an empty string. The name of this field is user . PHP uses the name of the field when creating an assortment for the values of the field. In this instance, the array for the values of this field is htmlentities($_GET["user"]) .

  • A submit input component with the value "Become". The "submit" type ways that the input field appears on the folio every bit a button. The value "Become" is the label of the button. When the user clicks the push button, the information in the text component is transferred to the file specified in the activeness aspect.

Creating wishlist.php and Testing the Awarding

In Adding a Grade To index.php you created a form in which the user submits the name of someone whose wish listing the user wants to see. The name is passed to the page wishlist.php . However, this page does not exist. If you run index.php , you lot volition get a 404: File Not Found error when you lot submit a name. In this section, yous create wishlist.php then test the application.

To create wishlist.php and examination the application:

  1. Inside the "wishlist" project you created, click the right mouse button on the Source files node and from the context menu cull New > PHP Web Page. The New PHP Web Page sorcerer opens.

  2. Type wishlist in the File Name field and press Finish.

  3. Click the right mouse button on the Sources node and choose Run Project from the context bill of fare or click the Run Main Project icon run main project button on the toolbar if you have fix your project every bit Chief.

index php works

  1. In the Show wish listing of: edit box, enter Tom and click Become. An empty page with the post-obit URL appears: http://localhost:90/Lesson2/wishlist.php?user=tom. This URL indicates that your chief page works properly.

Establishing the Connection and Getting the Wisher ID

In this department, you first add code to wishlist.php that creates a connection to the database. You and then add code to retrieve the ID number of the wisher whose name was typed into the index.php form.

  1. Double click the wishlist.php file. The template that opens is different from index.php. Begin and cease the file with <html></html> and <body></torso> tags equally the file will contain HTML code too.

                <!DOCTYPE html> <html>     <head>         <meta http-equiv="Content-Type" content="text/html; charset=UTF-eight">         <title></title>     </head>     <body>         <?php             // put your lawmaking here         ?>     </trunk> </html>

  1. To display the title, enter the following code block immediately subsequently the opening <body> tag, before the generated <?php tag:

                                  Wish List of <?php echo htmlentities($_GET["user"])."<br/>";?>

The code at present looks like this:

                <body>     Wish Listing of <?php echo htmlentities($_GET["user"])."<br/>"; ?>     <?php         // put your lawmaking here     ?> </body>

The PHP lawmaking cake displays the information that is received through the method Make it the field "user". This data is transferred from index.php where the proper name of the wish list possessor Tom was entered in the text field "user". Echo the steps from Testing index.php to see that wishlist.php works properly.

wishlist php title works

  1. Delete the commented department in the template PHP cake. In its identify, type or paste in the following code. This lawmaking opens the connexion to the database.

For the MySQL database: 

                $con = mysqli_connect("localhost", "phpuser", "phpuserpw"); if (!$con) {     exit('Connect Mistake (' . mysqli_connect_errno() . ') '. mysqli_connect_error()); } //set the default client grapheme fix mysqli_set_charset($con, 'utf-8');

For the Oracle database: 

                $con = oci_connect("phpuser", "phpuserpw", "localhost/XE", "AL32UTF8"); if (!$con) {     $thousand = oci_error();     exit('Connect Error ' . $k['message']); }

The code attempts to open a connection to the database and gives an error message if there is a failure.

You may need to alter the database connection in the oci_connect command. The standard syntax is "hostname/service name". The connectedness to an Oracle XE database in this snippet is "localhost/XE" to follow that syntax.
You can use NetBeans IDE's lawmaking completion for either mysqli or OCI8 functions.

]image::images/codecompletion-oci.png[

  1. Beneath the code to open the connexion to the database, in the same PHP block, type or paste the following code. This code retrieves the ID of the wisher whose wish list was requested. If the wisher is not in the database, the code kills/exits the process and displays an error message.

For the MySQL database: 

                mysqli_select_db($con, "wishlist"); $user = mysqli_real_escape_string($con, htmlentities($_GET["user"])); $wisher = mysqli_query($con, "SELECT id FROM wishers WHERE name='" . $user . "'"); if (mysqli_num_rows($wisher) < 1) {     exit("The person " . htmlentities($_GET["user"]) . " is not found. Delight check the spelling and try again"); } $row = mysqli_fetch_row($wisher); $wisherID = $row[0]; mysqli_free_result($wisher);

For the Oracle database: (Notation that oci8 does not have an equivalent to mysqli_num_rows )

                $query = "SELECT id FROM wishers WHERE NAME = :user_bv"; $stid = oci_parse($con, $query); $user = $_GET['user'];  oci_bind_by_name($stid, ':user_bv', $user); oci_execute($stid);  //Because user is a unique value I simply expect ane row $row = oci_fetch_array($stid, OCI_ASSOC); if (!$row) {     exit("The person " . $user . " is not found. Please check the spelling and endeavour once more" ); } $wisherID = $row['ID']; oci_free_statement($stid);

The information is selected from the wishlist database through the $con connection. The selection criterion is the name received from the index.php as "user".

The syntax of a SELECT SQL argument can be briefly described as follows:

  • After SELECT, specify the fields from which you want to become data. An asterisk (*) stands for all fields.

  • After FROM clause, specify the name of the table from which the data must be retrieved.

  • The WHERE clause is optional. Specify the filter conditions in it.

The mysqli query returns a result object. OCI8 returns an executed statement. In either instance, you fetch a row from the upshot of the executed query and extract the value of the ID row, storing it in the variable $wisherID .

Lastly, yous free the mysqli upshot or OCI8 statement. Yous demand to free all resources that use a connection before the connection volition physically be airtight. Otherwise PHP'due south internal refcounting organization will keep the underlying DB connection open even if $con is not usable following a mysqli_close() or oci_close() call.

Security Note: For MySQL, the htmlentities($_GET["user"]) parameter is escaped in order to forestall SQL injection attacks. See Wikipedia on SQL injections and the mysql_real_escape_string documentation. Although in the context of this tutorial y'all are not at chance of harmful SQL injections, it is best practise to escape strings in MySQL queries that would exist at risk of such an assault. OCI8 avoids this through bind variables.

This PHP cake is now complete. If you lot are using a MySQL database, the wishlist.php file now looks like this:

                Wish List of <?php echo htmlentities($_GET["user"]) . "<br/>"; ?><?php$con = mysqli_connect("localhost", "phpuser", "phpuserpw");   if (!$con) {      go out('Connect Mistake (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());   }//set the default client character set   mysqli_set_charset($con, 'utf-viii');   mysqli_select_db($con, "wishlist");   $user = mysqli_real_escape_string($con, htmlentities($_GET["user"]));   $wisher = mysqli_query($con, "SELECT id FROM wishers WHERE proper name='" . $user . "'");   if (mysqli_num_rows($wisher) < 1) {      leave("The person " . htmlentities($_GET["user"]) . " is not found. Please bank check the spelling and endeavour again");   }   $row = mysqli_fetch_row($wisher);   $wisherID = $row[0];   mysqli_free_result($wisher);   ?>

If you are using an Oracle database, the wishlist.php file now looks like this:

                Wish List of <?php echo htmlentities($_GET["user"]) . "<br/>"; ?>   <?php   $con = oci_connect("phpuser", "phpuserpw", "localhost/XE", "AL32UTF8");   if (!$con) {      $m = oci_error();      exit('Connect Mistake ' . $m['bulletin'];      leave;   }   $query = "SELECT id FROM wishers WHERE name = :user_bv";   $stid = oci_parse($con, $query);   $user = htmlentities($_GET["user"]);   oci_bind_by_name($stid, ':user_bv', $user);   oci_execute($stid);//Because user is a unique value I just expect one row   $row = oci_fetch_array($stid, OCI_ASSOC);   if (!$row) {      exit("The person " . $user . " is not found. Delight bank check the spelling and try over again" );   }   $wisherID = $row["ID"];   oci_free_statement($stid);   ?>

If you exam the awarding and enter an invalid user, the post-obit message appears.

wishlist php title user not found works

Displaying a Table of Wishes

In this department, you add code that displays an HTML table of the wishes associated with the wisher. The wisher is identified by the ID yous retrieved in the lawmaking in the previous section.

  1. Beneath the PHP block, type or paste the following HTML code block. This lawmaking opens a tabular array, specifies the color of its borders (black), and "draws" the table header with the columns "Item" and "Due Date."

                <table border="black">     <tr>         <th>Particular</th>         <th>Due Appointment</th>     </tr> </table>

The </table> tag closes the table.

  1. Enter the following PHP lawmaking cake higher up the endmost </tabular array> tag.

For the MySQL database: 

                <?php     $event = mysqli_query($con, "SELECT description, due_date FROM wishes WHERE wisher_id=" . $wisherID);     while ($row = mysqli_fetch_array($result)) {         repeat "<tr><td>" . htmlentities($row["clarification"]) . "</td>";         repeat "<td>" . htmlentities($row["due_date"]) . "</td></tr>\n";     }     mysqli_free_result($result);mysqli_close($con); ?>

For the Oracle database: 

                <?php     $query = "SELECT description, due_date FROM wishes WHERE wisher_id = :id_bv";     $stid = oci_parse($con, $query);     oci_bind_by_name($stid, ":id_bv", $wisherID);     oci_execute($stid); 	while ($row = oci_fetch_array($stid)) {         repeat "<tr><td>" . htmlentities($row["DESCRIPTION"]) . "</td>"; 	    echo "<td>" . htmlentities($row["DUE_DATE"]) . "</td></tr>\due north"; 	}     oci_free_statement($stid); 	oci_close($con); ?>

Within the lawmaking:

  • The SELECT query retrieves the wishes with their due dates for the specified wisher by his ID, which was retrieved in step iv, and stores the wishes and due dates in an array $result.

  • A loop displays the items of the $result array equally rows in the table while the array is non empty.

  • The <tr></tr> tags form rows, the <td></td> tags grade cells within rows, and \n starts a new line.

  • The htmlentities office converts all characters that have HTML entity equivalents into HTML entities. This helps to preclude cross-site scripting.

  • Functions at the end complimentary all resource (mysqli results and OCI8 statements) and close the database connexion. Note y'all demand to free all resource that use a connection before the connection can physically be closed. Otherwise PHP's internal refcounting arrangement keeps the underlying DB connectedness open even if the connection is not usable following an oci_close() or mysqli_close() call.

Make sure you type the names of database fields exactly equally they are specified during the database table creation. For Oracle, column names are returned as uppercase past default.

  1. To exam the application, run the project as described in the section Testing alphabetize.php.

wishlist php works

Application Source Lawmaking after the Electric current Lesson Is Completed

MySQL users: Click + here+ to download the source lawmaking that reflects the project country after the lesson is completed.

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

Next Pace