A Sample PHP Application

This small sample has a really big brother which can be downloaded from www.radicore.org

Introduction

Entity-Relationship Diagram

Online demonstration

Downloadable code

Installation instructions

A Challenge

Have the software generate the code for you

Amendment History

Comments

Introduction

Since I started developing in PHP I have assembled a collection of standard reusable modules which enable me to create quality software in very short timescales. As a veteran developer who has experienced a variety of 2nd, 3rd and 4th generation languages, and a variety of development methodologies, I have used my experience and skill to create a development environment which encompasses quite a few different disciplines and techniques. These I have documented in the following articles:

• Using PHP 4's DOM XML functions to create XML files from SQL data which describes how to take the data which you have just extracted from your database and write it to an XML document.
• Using PHP 4's Sablotron extension to perform XSL Transformations which describes how to convert an XML document into HTML so that it can be output to the client's browser.
• Using PHP 5's DOM functions to create XML files from SQL data which describes how to use the alternative functions in PHP 5.
• Using PHP 5's XSL functions to perform XSL Transformations which describes how to use the alternative functions in PHP 5.
• Generating dynamic web pages using XSL and XML which describes how to construct XSL stylesheets which will transform XML into HTML. This also shows how to run common code from separate and reusable templates.
• Reusable XSL Stylesheets and Templates which describes a technique which reduces the need for a customised stylesheet for each screen by moving the list of fields from the stylesheet to the XML data. This means that a single generic stylesheet can now be used for many different screens.
• Using PHP Objects to access your Database Tables (Part 1) which describes how to create an abstract class for use with every database table within your application.
• Using PHP Objects to access your Database Tables (Part 2) which shows how to extend the database table class to perform basic validation using field specifications defined within each class, how you can have standard code which deals with candidate keys, and how to deal with referential integrity.
• What is/is not considered to be good OO programming which documents some criticisms of my approach, and my responses.
• In the world of OOP am I Hero or Heretic? which documents more criticisms of my approach.
• Customising the PHP error handler which describes how to trap and filter various errors, output a meaningful message to the user, send an email to the system administrator, and record the error in a log file.
• A Development Infrastructure for PHP which describes how I have implemented the 3 tier architecture by separating the code for the presentation layer (user interface), the business layer and the data access layer.
• UML diagrams for the Radicore Development Infrastructure which shows the communication between my controller, abstract table superclass, database table subclass and data access object using UML diagrams.
• The Model-View-Controller (MVC) Design Pattern for PHP which describes how I have implemented this pattern within my development infrastructure.
• Transaction Patterns for Web Applications which describes how to create a large library of reusable scripts based on structure and behaviour (transaction pattern) so that your application need only consist of small component scripts which simply identify the content for a particular transaction pattern.
• A Flexible Tree Structure which explains the design philosophy behind the tree structure and how it can be maintained using the components provided.

Entity-Relationship Diagram

The database used by this software has the following structure:

Figure 1 - Entity-Relationship Diagram (ERD)

PERS-TYPE to PERSON is a one-to-many relationship.

PERSON to X-OPTION is a many-to-many relationship with PERS-OPT-XREF as the intersection. This enables the following views:

• PERSON to X-OPTION (one-to-many)
• X-OPTION to PERSON (one-to-many)

TREE-TYPE to TREE-LEVEL is a one-to-many relationship.

TREE-LEVEL to TREE-NODE is a one-to-many relationship.

TREE-NODE(SNR) to TREE-NODE(JNR) is a one-to-many relationship. This enables nodes to be related to other nodes. A node can only have one parent, but it can have any number of children.

TREE-NODE to PERSON is a one-to-many relationship.

Online demonstration

In order to demonstrate that all these different disciplines and techniques manage to work together quite nicely I have put together a small set of screens which you can run online here.

The maintenance functions for each database table all start with a LIST screen which will show all available rows in the database. Rather than show all rows at once they are split into smaller 'pages', and hyperlinks are provided at the bottom of the screen which will allow you to step forwards or backwards through all the available pages. These are known as pagination options. You can use the hyperlinks in the column headings to sort the data by that column, toggling between ascending and descending sequence.

The column headings in the LIST screens are hyperlinks which will cause the data to be sorted on that column. This will be all data which satisfies the current selection criteria, not just the contents of the current page. The sort sequence will toggle between 'ascending' and 'descending', with an image being displayed next to the column heading to indicate the current setting.

The buttons at the bottom of the screen are know as action buttons as they perform some sort of action within the current screen.

• The CANCEL/CLOSE button will quit the current screen and return to the previous screen.
• The RESET button will clear any sort, search or pagination settings and restart the screen from the first page.

The buttons just below the screen title are known as navigation buttons as they pass control to separate scripts in order to perform whatever processing is contained within those scripts.

• The INSERT button will show the detail screen and allow a new row to be inserted.
• The UPDATE button will show the detail screen and allow an existing row to be updated.
• The ENQUIRE button will show the detail screen and allow an existing row to be viewed.
• The DELETE button will show the detail screen and allow an existing row to be deleted.
• The SEARCH button will show the detail screen and allow various selection criteria to be entered before returning to the LIST screen which will act upon that selection criteria.

The checkboxes at the start of each row in the LIST screen are for selecting rows which are to be passed to a child screen (from a navigation button) for processing. Note that it is possible to select multiple rows before pressing a navigation button as the detail screen will offer a set of scrolling options (similar to the pagination options) to allow the user to move backwards and forwards through the selected rows.

There is a help button in each screen which will display online help text.

Downloadable Code

You can download all the relevant code from here so that you can run it in your own environment and examine the source code at your leisure. The zip file contains the following:

• A CLASSES folder containing all database table classes.
• A CSS folder containing all CSS files and images.
• An IMAGES folder containing standard images.
• A PICTURES folder containing images of people.
• A SCREENS folder which contains all the necessary screen definitions.
• An SQL folder (with subfolders for MySQL and PostgreSQL) containing sql scripts which load the SAMPLE database with data.
• An XSL folder which contains all the necessary XSL stylesheets and templates.
  • STD.DETAIL1.XSL, STD.LIST1.XSL, STD.LIST2.XSL which are generic XSL stylesheets.
  • STD.???.XSL which are a collection of XSL templates.
• DB.INC contains the username and password to connect to the database. Please amend as necessary
• DML.MYSQL.CLASS.INC is the class which issues all DML/SQL statements. This version is for MySQL versions before 4.1.x. Other versions can be created for alternative DBMS engines. Each table subclass will identify its DBMS engine name so it will know which DML class to instantiate at runtime. It is therefore possible for different database tables to be accessed through different DBMS engines within the same transaction.
• DML.MYSQLI.CLASS.INC is the class which issues all DML/SQL statements for MySQL version 4.1.x or higher. This uses the 'improved' functions with the 'mysqli_' prefix.
• DML.PGSQL.CLASS.INC is the class which issues all DML/SQL statements for PostgreSQL.
• DML.ORACLE.PHPx.CLASS.INC are the classes which issue all DML/SQL statements for Oracle. There is one version for PHP 4 and another for PHP 5 as the api names are different between the two versions.
• DML.SQLSRV.CLASS.INC is the class which issues all DML/SQL statements. This version is for Microsoft SQL Server version 2008 R2 and above.
• ERROR.INC contains standard code for dealing with errors.
• INCLUDE.GENERAL.INC manages the includes of other files and overrides standard functions.
• INCLUDE.LIBRARY.INC contains my library of standard functions.
• INCLUDE.XML.PHPx.INC contains my library of standard XML/XSL functions. There are separate versions for PHP 4 and PHP 5.
• INDEX.HTML is the main index page.
• MENU.INC contains settings for the menu and navigation bars.
• STD.TABLE.CLASS.INC is my abstract database table class.
• *.CLASS.INC extend the abstract table class to contain the specific code for dealing with physical tables within the application database.
• HELP_TEXT.CLASS.INC is for the database table which contains all help text.
• STD.DATEVALIDATION.CLASS.INC is the standard class which I use when I want to check that the user has input a valid date.
• STD.VALIDATION.CLASS.INC is the class which performs all basic validation before rows are inserted or updated. It uses the contents of the $fieldspec array within each class to determine how to validate each field.
• A collection of component scripts:
  • PERS_TYPE_*.PHP which deal with the PERS_TYPE table.
  • PERSON_*.PHP which deal with the PERSON table.
  • OPTION_*.PHP which deal with the OPTION table.
  • PERS_OPT_XREF_LINK(A).PHP which will maintain PERS_OPT_XREF by PERSON.
  • PERS_OPT_XREF_LINK(B).PHP which will maintain PERS_OPT_XREF by OPTION.
  • HELP.PHP for online help.
  • TREE_TYPE_*.PHP which will deal with the TREE_TYPE table.
  • TREE_LEVEL_*.PHP which deal with the TREE_LEVEL table.
  • TREE_NODE_*.PHP which deal with the TREE_NODE table.
• A collection of reusable controller scripts:
  • STD.ADD1.INC which will add a single row.
  • STD.ADD3.INC which will add several rows.
  • STD.DELETE1.INC which will delete a single row.
  • STD.ENQUIRE1.INC which will display a single row.
  • STD.FILEPICKER1.INC which will allow the user to pick from a selection of file names.
  • STD.HELP.INC which retrieves text from the HELP_TEXT table and formats it into an HTML document.
  • STD.LINK1.INC which which deals with a MANY-to-MANY relationship.
  • STD.LIST1.INC which will display multiple rows.
  • STD.LIST2.INC which will display multiple rows for a table in a ONE-to-MANY relationship.
  • STD.LINK1.INC which will insert/delete multiple rows for a link table in a MANY-to-MANY relationship.
  • STD.MULTI2.INC which will allow several occurrences in a ONE-to-MANY relationship to be modified.
  • STD.SEARCH1.INC which will accept new selection criteria before re-retrieving records from the database.
  • STD.UPDATE1.INC which will update a single row.
  • STD.UPDATE2.INC which will update several rows.
• README.TXT is a brief version of this document.
• DEFAULT.CSS and CUSTOM.CSS contain the cascading style sheet info for this software.

This code has been tested with the following PHP versions:

• Version 4.4.9 using the DOM XML and Sablotron XSLT extensions.
• Version 5.2.17 using the DOM and XSL extensions.

It has also been tested with the following MySQL versions:

• Up to version 4.0 which uses the original MySQL extension.
• Version 4.1 and above which uses the 'improved' MySQL extension.

It has also been tested with PostgreSQL version 8, Oracle 10g Express Edition and SQL Server 2008 R2 Express Edition.

When you run each script it will dump its XML data into a separate file in the XSL folder so that you can see exactly what is being input into the XSL transformation process. I have found this to be very useful when debugging.

In case you are interested the IDE that I use was PHPEdit, then I switched to Zend Studio, but I now use Nusphere PhpED.

Enjoy! Don't applaud, just throw money!

Installation instructions

I am assuming that you already have a working PHP installation in which you have identified a directory as the "document root" (<docroot>). The zip file starts with a directory called "sample". You need to perform the following steps:

• Unzip the zip file into your document root so that you end up with <docroot>/sample
• Go to the sample/sql/mysql folder (or its equivalent if you are not using MySQL), fire up your database client and do the following:
  • Create the 'sample' database and use the sample-schema.sql file to construct its contents.
  • Use the sample-data.sql file to load the initial data.
  • Update the db.inc file with your chosen username and password to access the 'sample' database.
• Open your browser and enter http://localhost/sample into your address bar, which should automatically run http://localhost/sample/index.html
• You can also use the URL http://localhost/sample/index.php

A Challenge

I believe that I have created a development environment which allows a programmer to create working components quite rapidly. If you do not believe me then I invite you to take this challenge. Create your own family of forms by taking the following steps:

1. Create a test database table with a reasonable selection of columns. You can use my PERSON table as an example as this contains a mixture of string, date and numeric fields. It also has a candidate key as well as a primary key.
2. Create a class for this table named <tablename>.class.inc. This must be a subclass of my default_table as defined in std.table.class.inc.
3. Create a screen structure file for the List View called <tablename>.list.class.inc and the Detail View called <tablename>.detail.class.inc. You will find it easier to copy some existing files and change them.
4. Create 6 component scripts named <tablename>_X.php where X is add, del, enq, list, search and upd. Use my person_X.php scripts as examples.
5. Add the list screen as a hyperlink to my index page and off you go!

If you take a look in the infrastructure FAQ you will see an example laid out for you.

Unless you take too many coffee breaks you should be able to complete all these tasks in 60 minutes. That makes an average of 10 minutes per component. Is your current development methodology as productive as that?

Have the software generate the code for you

If you wish to see something even faster then you should check out this sample's big brother at http://www.radicore.org which has code generation facilities built into the Data Dictionary. With this you can perform the following:

• Import your database schema into the data dictionary.
• Edit the dictionary details to include all relationships.
• Export the details by database table to create the class file and structure file for that database table.
• Generate the PHP scripts to maintain each database table by selecting from the catalog of Transaction Patterns.

This means that starting with nothing more than a database schema you can create working transactions to maintain the contents of each of those tables without having to write a single line of code. Is your current development methodology as productive as that?

Amendment history: