HOME / Docs & Support

Creating a CRUD Application with PHP

Lesson 2: Designing the Application. Reading from the Database

In this lesson you create and configure the PHP project to develop your application, create a list of pages in the application, and define the relations between them. You will also develop a piece of the basic application functionality and test it against the data you entered in the sample database in lesson 1.

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

Creating a CRUD Application with PHP - Main page
Creating a MySQL Database
=> Designing the Application. Reading from the Database
Creating a New Application User
Optimizing the Code
Adding Security. Implementing Application User Logon
Writing a New Entry into the Database
Updating and Deleting Entries in the Database
Making the Application Look Better Using the CSS Technology
Deploying the Application on a Remote Web Server

Contents of this lesson

Creating a PHP Project
Defining a Page Flow Diagram
Basic Functionality: Viewing the Wish List of a Person
Application Source Code after the Current Lesson Is Completed

Creating a PHP Project

For information on creating and configuring a PHP project, see Setting Up a PHP Project.

Defining a Page Flow Diagram

The scope of your application covers the following use cases:

The user views the wish list of a person.
The user registers as a new wisher.
The user logs in and creates her/his wish list.
The user logs in and edits her/her wish list.

To cover this basic functionality, you will need to implement the following pages:

The "front" page index.php for logging in, registering, and switching to wish lists of other users.
The wishlist.php page for viewing the wish list of a particular wisher.
The createNewWisher.php for registering as a wisher.
The editWishList.php page for editing a wish list by its owner.
The editWish.php page for creating and editing wishes.

The diagram shows the planned switches between the pages in various use cases

Basic Functionality: Viewing the Wish List of a Person

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

Create the wishlist.php File

Start the NetBeans IDE.
Click the right mouse button on the Source files node and from the context menu choose New > PHP File.
On the New PHP File panel, in the File Name: edit box, type wishlist and press Finish.
According to your project setup, the index.php file is already created. If not, create it as described above.
Switch to the Projects window, expand your project node, and double click the index.php file. The index.php file opens in the main IDE editor area. The file contains a template for entering HTML and PHP code.
.

Transferring Data from index.php to wishlist.php

The index.php file will not contain any PHP code so you can easily remove the following block:
An empty PHP block that can be removed

This file is used for two purposes:

Displaying a page with controls for entering data.
Transferring the entered data to another page.

These actions are performed using HTML forms. Each form contains:

A set of fields that correspond to the controls on the page with their types specified
The destination URL address that corresponds to the page where the entered data must be transferred.

The data is received and processed on the destination page. In our example, the data is entered on the index page (index.php) and transferred to the wishlist.php page. You need to implement data transferring in index.php and data reception in wishlist.php.

Internationalizing the Form

To use international UTF-8 characters, insert the following element inside the <head> element, if it is not present.

<meta http-equiv="content-type" content="text/html; charset=UTF-8">

HTML Form in index.php

Enter the following code block into the body of index.php:

<form action="wishlist.php" method="GET" name="wishList">
      Show wish list of: <input type="text" name="user"/>
		<input type="submit" value="Go" />
</form>

The above piece of code consists of the following elements:

Enclosing tags <form></form>
The opening <form> tag that contains the action field for entering the name of the file where the data must be transferred (wishlist.php) and the method to be applied to transferring data (GET). PHP creates a special array $_GET and populate there values of the fields from the original form.
The text that appears on the page: Show wish list of:
A text input field for entering the name of the user whose wish list one wants to view. The name ("user") is the key to pick the data on the destination form.
An input field of the "submit" type with the text "Go". The type "submit" means that the input field appears on the page as a button and the data is transferred when exactly this control is affected.

Testing index.php

To test the front index.php page of your application:

Click the right mouse button on the Sources node and choose Run Project from the context menu or click the Run Main Project icon on the toolbar if you have set your project as Main.
In the Show wish list of: edit box, enter Tom and click Go. An empty page with the following URL appears: http://localhost:90/Lesson3/wishlist.php?user=tom. This URL indicates that your main page works properly.

Receiving and Processing Data in wishlist.php

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

<html>
    <body>
        <?php
        /*
         * To change this template, choose Tools | Templates
         * and open the template in the editor.
         */

        ?>
    </body>
</html>

To display the title, enter the following code block immediately after the opening <body> tag, before the generated <?php tag:
```
 Wish List of <?php echo $_GET["user"]."<br/>";?>
```
The code now looks like this:
```
<html>
    <body>Wish List of <?php echo $_GET["user"]."<br/>";?>

        <?php

        /*

        * To change this template, choose Tools | Templates

        * and open the template in the editor.

        */
                    ?>

    </body>
</html>
```
The PHP code block displays the data that is received through the method GET in the field "user". This data is transferred from index.php where the name of the wish list owner Tom was entered in the text field "user". Repeat the steps from Testing index.php to see that wishlist.php works properly.
Delete the commented section in the template PHP block. Paste the following code block where the comments were. This code opens the connection to the database.
```
$con = mysql_connect("localhost", "phpuser", "!phpuser");
if (!$con) {
    die('Could not connect: ' . mysql_error());
}
mysql_query("SET NAMES 'utf8'");               
```
The code attempts to open a connection to the database and gives an error message if there is a failure.
Beneath the code to open the connection 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:
```
mysql_select_db("wishlist", $con);
$wisher = mysql_query("SELECT ID FROM wishers WHERE name='".mysql_real_escape_string($_GET["user"])."'");
$wisherID = mysql_result($wisher, 0);  
```
The data 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 statement can be briefly described as follows:
- After SELECT, specify the fields from which you want to get 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.
Security Note: The $_GET["user"] parameter is escaped in order to prevent SQL injection attacks. See Wikipedia on SQL injections and the mysql_real_escape_string documentation. Although in the context of this tutorial you are not at risk of harmful SQL injections, it is best practice to escape strings in MySQL queries that would be at risk of such an attack.

Beneath the code to retrieve the ID of the wisher, type or paste the following code. This code displays an error message if the requested wisher is not found in the database:

if (!$wisherID) {
    die("The person " .$_GET["user"]. " is not found. Please check the spelling and try again" );
}

This PHP block is now complete. The wishlist.php file now looks like this:

<html>

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

        <?php

        $con = mysql_connect("localhost", "phpuser", "!phpuser");

        if (!$con) {

            die('Could not connect: ' . mysql_error());

        }

        mysql_query("SET NAMES 'utf8'");

        mysql_select_db("wishlist", $con);

        $wisher = mysql_query("SELECT ID FROM wishers WHERE name='".mysql_real_escape_string($_GET["user"])."'");

        $wisherID = mysql_result($wisher, 0);

        if (!$wisherID) {

            die("The person " .$_GET["user"]. " is not found. Please check the spelling and try again" );

        } 

        ?>

    </body>

</html>

If you test the application and enter an invalid user, the following message appears.

The wishlist.php page shows an error message: The user is not found

Beneath the PHP block, type or paste the following HTML code block. This code opens a table, specifies the color of its borders (black), and "draws" the table header with the columns "Item" and "Due Date."
```
<table border="black">
    <tr>
        <th>Item</th>
        <th>Due Date</th>
    </tr>
</table>
```
The </table> tag closes the table.
Enter the following PHP code block above the closing </table> tag:
```
<?php
    $result = mysql_query("SELECT * FROM wishes WHERE 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></tr>\n";
    }
    mysql_close($con);
?>
```
Within the code:
- The SELECT query retrieves the wishes with their due dates for the specified wisher by his ID, which was retrieved in step 4, and stores the wishes and due dates in an array $result.
- A loop displays the items of the $result array as rows in the table while the array is not empty.
- The <tr></tr> tags form rows, the <td></td> tags form cells within rows, and \n starts a new line.
- The $con connection to the database is closed.
Note: By default, MySQL is configured to be case sensitive. Make sure you type the names of database fields exactly as they are specified during the database table creation.

Security note: Strip tags from database entries before displaying them in order to prevent cross-site scripting. Although this is not a risk in the context of this tutorial, it is best practise to always write code that is free of vulnerabilities.
To test the application, run the project as described in section Testing index.php.

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 Step

<< Previous lesson

Next lesson >>

Back to the Tutorial Main page

Useful Links

Find more information on using HTML, PHP, and MySQL here:

Send Us Your Feedback

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