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

• access a mySQL database
• use a template engine to separate layout (HTML) and logic (PHP)

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

• Apache (virtual) webserver with mod_php and htaccess enabled.
• mySQL database for availability and ease of use.
• Pear HTML Template engine

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:

• FrontBase (available from PHP 4.1.0)
• Microsoft SQL Server
• MySQL
• ODBC
• PostgreSQL
• Sybase-CT (available from PHP 4.2.0)
• Oracle (oci8) (available from PHP 4.3.0)
• SQLite (PHP 5)

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:

   quit;

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';

I considered these two template engines:

• The Integrated Template [Extension] (IT[X]) is part of the PHP Extension and Application Repository (PEAR). It is part of the PHP distribution on Debian as package php-html-template-it.
• The HTMLTMPL is a small template engine available for PHP and Python. Thus you could use templates with both languages.

III. Implementation

phptutor
+ competition
|   + manage
|   |   + .htaccess
|   |   + index.php
|   + input.php
|   + index.php
+ security
|   + admin.pwd
+ templates
    + adminform.tmpl
    + showlist.tmpl
    + userform.tmpl

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 -->
   {name}
   {email}
   {bear}
   <!-- 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');
   $template->show();

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:

   $_SERVER["REQUEST_METHOD"] == "POST"

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 );
   $template->parseCurrentBlock();

   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.

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.

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!