PHP-Tutorial using Templates and Databases

A regular task for webdevelopers is to design an application as user interface to a database. Good practise tells to seperate user interface and application logic. Therefore you'll learn in this tutorial how to

Notice: The code is supposed to be without errors. If it nevertheless crashes your system, remember it was free! It's offered as-is without any warranties.

Business Problem (Scenario)

A company called Outdoor Artists is looking for a name to its mascot bear. Therefore they want to arrange a competition on their website. Visitors should be able to suggest names for the mascot. The suggestions shall be reviewed and then published on the homepage.

Notice: The company name is randomly chosen. If there is really a company called Outdoor Artists it's pure coincidence.

I. Specification

Three pages are required:

  1. Entry page introducing competition and listing actual suggestions. The entry page introduces the visitor to the competition. The wording is fixed and won't change. Below the introduction a listing is show with:
    • real name of participant
    • e-mail address of participant
    • suggested name of the bear
    The listing is dynamically created by querying the database.
  2. Input page for visitors to enter their suggestions. The input page enables a visitor to participate in the competition by suggesting a name for the mascot. The page contains a form where all required data can be entered:
    • real name
    • e-mail address
    • name suggestion
    The input is checked for completeness. If complete an e-mail with the input will be sent to a static address. The empty form is presented again with a confirmation message. If incomplete the form is presented again with all entered information in place. A status message is indicating the reason for failure.
  3. Admin page for company staff to enter received suggestions. The administration page is protected from public access. Access control is achieved by using Apaches .htaccess files. Access is restricted to people supplying the correct username/password. The functionality equals that of the input page. But checked input is written to the database rather than sent by e-mail.

II. Preparation

1. Requirements
2. Database

You need full access to a database. This tutorial uses a mySQL database. However, you can use any of the databases supported by the dbx module:

(Source: PHP Manual at 2007/10/18)

If you use mySQL you can follow the steps below to get a database and table ready for use with this tutorial. Users of other database systems might use these steps as guideline:
  1. If you've got root access to the database system login to create a database:
    mysql -u root -p
  2. Create the database phptutor (or choose whatever name you like):
    CREATE DATABASE phptutor;
  3. Create a normal user wettbewerb with full access to the database:
    GRANT ALL ON phptutor.* TO 'wettbewerb'@'localhost' IDENTIFIED BY '1etMe1n1';
  4. Log-off from the database:
  5. If you don't have have root access, you should have received an user id. A password and the database name(s) you've got full accesss to. Now login with your normal database user:
    mysql -u wettbewerb -p
  6. Select the database:
    use phptutor;
  7. Create the table:
    CREATE TABLE competition ( name VARCHAR(40), email VARCHAR(40), bear VARCHAR(80) );
  8. For security reasons you should create a second user with read/write access privileges on table level only. You need to login agian as database admin to issue the following command:
    GRANT ALL ON phptutor.competition TO 'webserver'@'localhost' IDENTIFIED BY '1etMe1n2';
If you acounter problems, please consult the mySQL Manual.

3. Template Engine

I considered these two template engines:

I guess they are both sufficient for this project. However, I've chosen IT[X] for no special reason.

III. Implementation

The project has the following structure:
+ competition
|   + manage
|   |   + .htaccess
|   |   + index.php
|   + input.php
|   + index.php
+ security
|   + admin.pwd
+ templates
    + adminform.tmpl
    + showlist.tmpl
    + userform.tmpl
1. Entry page
The layout of the introduction page is defined in templates/showlist.tmpl. The first part of the page contains the introduction. It is done in standard HTML code. The second part of the page contains the list of names submitted by other visitors. The PHP script competition/index.php is executed if a visitor first enters the website. It contains the program logic to fill the list of name suggestions:
  1. Connect to database: The dbx interface is used to connect to the database. Three constant values are defined for database access:
    define( "USER", "<USERNAME>" );
    define( "PASSWD", "<PASSWORD>" );
    define( "DB", "<DATABASENAME>" );
    Replace <USERNAME>, <PASSWORD> and <DATABASENAME> with values from above.
  2. Load HTML template:
    $template = new HTML_Template_IT( TMPL_PATH );
    $template->loadTemplatefile( TMPL_FILE, true, true );
  3. Retrieve names from database:
    $result = dbx_query( $con, SELECT_LIST );
    The SQL statement is stored in the PHP constant SELECT_LIST as:
    SELECT name, email, bear FROM competition
  4. Replace variables in template: IT[X] iterates blocks of code. A block is defined within BEGIN and END statements. Variables are then used to fill in content dynamically. A variable is defined in curly braces. Here is the code snippet from templates/showlist.tmpl:
    <!-- BEGIN participant -->
    <!-- END participant -->
  5. Set proper page encoding in HTTP-header and hand over finished HTML page to webserver for delivery:
    header('Content-Type: text/html; charset=utf-8');
2. Input page
The layout of the input page for visitors is defined in templates/userform.tmpl. Visitors enter their name, e-mail address and the name suggestion into a HTML form. The input is sent back to the server for validation and further action. The preparation of the form and processing of the input is done by the PHP script competition/input.php:
  1. Load template file.
  2. Four variables will be used for input processing and are initialised empty:
    $name = "";
    $email = "";
    $bear = "";
    $status = "";
    $name, $email and $bear will be holding the user input from the form. $status will be set during input validation.
  3. First we are checking whether the script is called by a HTTP POST request or not:
  4. If it's not a HTTP POST we assume it's a first visit to the site. Thus we are going to present an empty form. This needs no further action to be taken by now. The variables will be holding their initialised values.
  5. If the script is called by a HTTP POST request, we are receiving data from the HTML form (or being attacked). In a second step we are now checking whether all fields from the HTML form are available:
    !($_POST["name"] && $_POST["email"] && $_POST["bear"])
  6. If one of the required fields is missing, they were not filled in the HTML form. Now we'll find out which field(s) were missing and append a message to the status variable:
    if ( $_POST["name"] ) $name .= $_POST["name"];
    else $status .= "Your name is missing.<br>";
    if ( $_POST["email"] ) $email .= $_POST["email"];
    else $status .= "Your e-mail address is missing.<br>";
    if ( $_POST["bear"] ) $bear .= $_POST["bear"];
    else $status .= "Which name do you suggest for our mascot?<br>";
    $status .= "Please fill in the required information:";
  7. If the form was filled completely we'll sent an e-mail to someone from the marketing department. The e-mail address and subject are defined in the PHP constants MAIL_ADDR and MAIL_SUBJ. You need to replace <E-MAIL> with some proper e-mail address. The $status variable will indicating whether the e-mail was successfully sent or not. Here is the code:
    $formulardaten = "Name: " . $_POST["name"] . "\nE-Mail: " . $_POST["email"] . "\nSuggestion: " . $_POST["bear"];
    if ( mail (MAIL_ADDR, MAIL_SUBJ, $formulardaten) ) {
    	$name = "";
    	$email = "";
    	$bear = "";
    	$status .= "Your suggestion has been received. Thank you!";
    } else {
    	$status .= "Sorry, an error occurred during message delivery!";
  8. A HTML file is created by replacing all template parameters with values from the PHP variables:
    $template->setVariable( "name", $name );
    $template->setVariable( "email", $email );
    $template->setVariable( "bear", $bear );
    $template->setVariable( "status", $status );
    Depending on the program flow different values will be set. If everything is alright $name, $email and $bear will be empty. If we are not processing a HTTP POST request, $status will be empty, else it contains some message. If the form was missing some values, the available values are placed back into the form, such that user input won't get lost. This is achived in the template by setting the attribute value to the received user input:
    <input name="name" size="40" maxlength="40" value="{name}" type="text">
  9. Finally the HTML page is transferred to the server for delivery.
3. Admin page
The layout of the admin page is defined in templates/adminform.tmpl. It is basically the same form used for the input page. Also the program logic in competition/manage/index.php follows the one of competition/input.php. Except that data is not sent by e-mail but rather written to the database:
  1. Load template.
  2. Initialise variables.
  3. Check whether script is called by a HTTP POST request or not.
  4. If script is not called by a HTTP POST request present empty form.
  5. If it is a HTTP POST request check for completness.
  6. If form is missing information present it again to the user.
  7. If form is complete, connect to database:
    $con = dbx_connect( DBX_MYSQL, HOST, DB, USER, PASSWD ) or die( "Error connecting to database!" );
    Constant values are used to hold access information to the database:
    define( "HOST", "localhost" );
    define( "USER", "<username>" );
    define( "PASSWD", "<password>" );
    define( "DB", "<databasename>" );
    Replace <USERNAME>, <PASSWORD> and <DATABASENAME> with values from above.
  8. Prevent SQL injection by escaping user input:
    $name .= dbx_escape_string( $con, $_POST["name"] );
    $email .= dbx_escape_string( $con, $_POST["email"] );
    $bear .= dbx_escape_string( $con, $_POST["bear"] );
  9. Insert data into database with an SQL insertion statement:
    $result = dbx_query( $con, "INSERT INTO competition (name, email, bear) VALUES ('".$name."','".$email."','".$bear."')" );
  10. Set status message according to result of database operation and disconnect from database.
  11. Create HTML page from template.
  12. Transfer HTML page is transferred to the server for delivery.
4. Security

Last but not least access to the Admin page needs to be restricted. The Apache web-server provides a simple and secure mechanism to achieve a basic level of security: Hypertext Access (htaccess). Access control is achieved by placing a properly set-up .htaccess file into a directory. In order for .htaccess files to be evaluated, Apache must be told to allow the configuration of authorisation being changed per directory: AllowOverride AuthConfig. This must be set by the system administrator. Now there are two steps left for the user to set-up access restriction:

  1. Create password file security/admin.pwd with one entry for the user admin: htpasswd -c security/admin.pwd admin
  2. Create Hypertext Access file competition/manage/.htaccess with your favourite text editor. You need to replace <ABSOLUT_PATH_TO> with the absolute path to admin.pwd.

For more information on web-server security, read the manual for version 1.3 or the manual for version 2.2.

IV. Epilogue

The best resource to start learning PHP and to find help while developing is the PHP Manual.

While writing and testing this tutorial I came across an issue with wrong character encodings. I found this page to be a very informative source about PHP web-development and character encoding.

Feedback and suggestions are very welcome!