Developing a PHP Web Application with Oracle Database 11g

Install Oracle Database 11.2

Create a user named PHPHOL with password of 'welcome'. Install Oracle's sample
HR schema and make the following changes:

 create sequence emp_id_seq start with 400;
   create trigger my_emp_id_trigger
   before insert on employees for each row
   begin
     select emp_id_seq.nextval into :new.employee_id from dual;
   end;
   /
   --
   -- Also to simplify the example we remove this trigger otherwise
   -- records can only be updated once without violating the
   -- PYTHONHOL.JHIST_EMP_ID_ST_DATE_PK constraint
   --

   drop trigger update_job_history;

   --
   -- Allow employees to be changed when testing the lab after hours.
   --
   drop trigger secure_employees; 

Install Apache and enable UserDir module for public_html with

AllowOverride All

This allows Zend Framework to work correctly.

In httpd.conf set:

SetEnv APPLICATION_ENV development 

This is also for Zend Framework.

Install PHP 5.3.3 with the OCI8 1.4 extension.

Install Zend Framework 1.10

Install NetBeans 6.9.

In the NetBeans options, make the following adjustments:

In PHP->General, set "PHP 5 interpreter" to your php executable.

In PHP->Zend, set "Zend script" to your Zend Framework script zf.sh and click "Register Provider"

In Keymap set "Alt+Z" as a hotkey for "Zend: Run Command"

Extract these files to your $HOME location.

Start the NetBeans IDE from your desktop.

Start a new project by selecting File > New Project from the menu. Alternatively, you can click the new project icon in the toolbar, or use the keyboard shortcut Ctrl+Shift+N.

In the New Project wizard, click Next, leaving the category with the default of PHP, and the project with the default of PHP Application.

Change the Project Name to PHPLab. The sources folder will automatically change to /home/phphol/public_html/PHPLab.

Click in the PHP Version field, and select PHP 5.3.

Click Next.

Append public/ to the Project URL, making it http://localhost/~phphol/PHPLab/public/. NetBeans will warn you if the trailing slash is missing.

Click Next.

Select the Zend PHP Web Framework option.

Click Finish to complete the Wizard.

In the Projects pane, explore the files generated by Zend Framework and NetBeans.

The created skeleton includes project configuration files, a folder for "models" to handle database interaction, some "views" for display page layout, and "controllers" to manage incoming page requests. This is the MVC application model.

In the Projects pane, navigate to Source Files > public > .htaccess. Open the file and insert a new RewriteBase line so it reads:

RewriteEngine On
RewriteBase /~phphol/PHPLab/public
RewriteCond %{REQUEST_FILENAME} -s [OR]
RewriteCond %{REQUEST_FILENAME} -l [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^.*$ - [NC,L]
RewriteRule ^.*$ index.php [NC,L]

The .htaccess file causes all HTTP requests for the application to load the index.php file, which then invokes the appropriate code path. The RewriteBase rule allows Apache to correctly remap URLs in the $HOME/public_html directory which is where our application files are created and executed from.

In the Projects pane, open Source files > application > views > scripts > index > index.phtml and replace the entire contents with:

<h2>Welcome</h2>
<p>Welcome to the home page of My Company</p>

This is the main body content of the application's home page.

Click Save to save the Project files.

Run the project by clicking the green Run arrow, or by pressing the F6 function key.

A Firefox browser window will open the application homepage http://localhost/~phphol/PHPLab/public/.

By default the Zend Framework invokes its "index" controller and loads the matching index view that you just edited.

Press Alt+Z to open the Run Zend Command window. This is a GUI wrapper around Zend Framework's control and configuration script.

In the Filter field enter layout. This returns two matching Zend Framework commands in the Matching Tasks list. Select the enable layout task. Notice the command at the bottom changes to zf.sh enable layout.

Click Run.

The Netbeans Output window reports that a new layout file was created.

Open the newly created layout file in Source Files > application > layouts > scripts > layout.phtml.

Change the file to:

<?php
$this->headMeta()->appendHttpEquiv('Content-Type',
                                 'text/html;charset=utf-8');
$this->headTitle()->setSeparator(' - ');
$this->headTitle('My Company Portal');
echo $this->doctype();
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <?php
  echo $this->headMeta(),
       $this->headTitle(),
       $this->headLink()->appendStylesheet(
                                '/~phphol/css/php_hol.css');
  ?>
</head>
<body>
  <h1>My Company Portal</h1>
  <div id="menu">
    <ul>
      <li><a href="<?php echo $this->url(
            array('controller' => 'index'), 'default', true) ?>"
            >Home</a></li>
      <li><a href="<?php echo $this->url(
            array('controller' => 'employee'), 'default', true) ?>"
            >Employee Administration</a></li>
    </ul>
  </div>
  <div id="content">
    <?php echo $this->layout()->content; ?>
  </div>
</body>
</html>

This page mixes PHP and HTML code. The PHP code is executed and any output is sent to the browser inline with the HTML code.

The page prints an appropriate HTML prologue which includes a link to a cascading style sheet pre-created prior to the tutorial. More typically the CSS would be located within the application code tree.

The body of the page displays a heading "My Company Portal" and two sections. The first section is a navigation menu containing two links. The links are generated by the framework to load the default actions of the "index" and the "employee" controllers.

The second section of the body prints page specific content using:

echo $this->layout()->content;

For the application's home page this will be the content of views > scripts > index > index.phtml.

Save the Project files.

Run the project by pressing F6 or simply reload the web page http://localhost/~phphol/PHPLab/public/ in the browser. PHP does not need to be compiled.

If you don't see this page, make sure you saved the project.

Beneath the title, the CSS positions the menu to the left of the page and the content of the default index page to the right.

Hover over the menu links. The home page link is for the current page. This allows every part of the application to return here. The Employee Administration link goes to http://localhost/~phphol/PHPLab/public/employee. This URL is for the employee controller which we will create next. It will give a Page not found error if clicked now.

Press Alt+Z to open the Run Zend Command window.

In the Filter field enter controller. Make sure create controller is selected in the Matching Tasks list.

In the Parameters field above the tasks enter Employee (take care with the capitalization and do not add a plural 's'). The command shown will be zf.sh create controller Employee. You cannot enter the argument directly in the Command field.

Click Run.

The Netbeans Output pane will show the controller was created. The files generated and added to the project are controllers > EmployeeController.php and views > scripts > employee > index.phtml.

Run the application (or return to the application home page in the browser) and click the Employee Administration link.

At runtime, Zend Framework recognizes the URL http://localhost/~phphol/PHPLab/public/employee is handled by the Employee controller and invokes controllers > EmployeeController.php and its associated view in views > scripts > employee > index.phtml. The view content is "injected" into the common layout.phtml page. By default, index.phtml displays the controller and action names.

Now we have the base to code a page listing employees.

From the Projects pane, open Source Files > application > configs > application.ini. This file contains all the Zend Framwork configuration for the project. Separate sections allow the application to be configured differently during the development and deployment phases.

At the end of application.ini under the section with the heading [development : production] add the following database connection information:

resources.db.adapter = "Oracle"
resources.db.params.dbname = "localhost/orcl"
resources.db.params.username = "phphol"
resources.db.params.password = "welcome"

This tells the application to use Zend Framework's Oracle database layer which calls PHP's OCI8 extension to connect to the Oracle Database. The database connected to is called orcl and runs on the local machine. The database schema is phphol.

Press Alt+Z to open the Run Zend Command window. In the Filter field enter db.

In the Matching Tasks list select the create db-table task. In the Parameters field enter Employee EMPLOYEES (take care with the capitalization and pluralization). The command shown will be zf.sh create db-table Employee EMPLOYEES.

Click Run.

Press Alt+Z to open the Run Zend Command window. In the Filter field enter model.

In the Matching Tasks list select the create model task. In the Parameters field enter Employee (take care with the capitalization and pluralization). The command shown will be zf.sh create model Employee.

Click Run.

Press Alt+Z to open the Run Zend Command window. In the Filter field enter model.

In the Matching Tasks list select the create model task. In the Parameters field enter EmployeeMapper (take care with the capitalization and pluralization). The command shown will be zf.sh create model EmployeeMapper.

Click Run.

You should see in the Project pane the generated files for the 3 model objects you created (Db Table, Employee, EmployeeMapper):

Change the Application_Model_Employee class in Source Files > application > models > Employee.php. It was created as an empty class. Open a terminal window and execute:

cd $HOME/public_html/PHPLab/application/models
cp $HOME/samples/models_Employee.php Employee.php 

Note: If you accidentally named the project something other than PHPLab then replace PHPLab with your project name in the cd command above.

If you have this file open in NetBeans the screen will automatically reload after a short delay. Do not confuse this file with the file of the same name in DbTable just above it in the Project file list!

The class now has an attribute for each column in the EMPLOYEES table, and has getters and setters for those attribute values. There is no logic in this class to retrieve or save that data. Instances of the class will simply hold data corresponding to a row of the EMPLOYEES table.

Change the Application_Model_EmployeeMapper class in Source Files > application > models > EmployeeMapper.php.

Open a terminal window and execute:

cd $HOME/public_html/PHPLab/application/models
cp $HOME/samples/EmployeeMapper.php EmployeeMapper.php

If you have this file open in NetBeans the screen will automatically reload after a short delay.

This is where the basic interaction with the database and the business logic occurs. It maps our model Application_Model_Employee (in application > models > Employee.php) to the data source Application_Model_DbTable_Employee (in application > models > DbTable > Employee.php). The Application_Model_DbTable_Employee class appears empty but it inherits database interaction logic from Zend Framework's parent class.

The mapper class contains methods for setting and getting the type of data to be used, and a fetchAll() method to retrieve all rows from the EMPLOYEES table.

In Netbeans, edit Source Files > application > controllers > EmployeeController.php and add two lines to the indexAction() method. This queries the list of employees. The file should look like:

<?php

class EmployeeController extends Zend_Controller_Action
{

    public function init()
    {
        /* Initialize action controller here */
    }

    public function indexAction() {
        $employee = new Application_Model_EmployeeMapper();
        $this->view->entries = $employee->fetchAll();
    }

}

The indexAction() of the Employee controller is invoked when the URL http://localhost/~phphol/PHPLab/public/employee is loaded. It simply fetches all the rows from the EMPLOYEES table using the new fetchAll() method we created in EmployeeMapper.php. We'll change the corresponding view next to display those rows.

Note there is no closing ?> PHP tag in the file. The tag is optional in PHP scripts and many coding standards suggest omitting it. This prevents whitespace that accidentally follows the tag from being sent to the browsers and interferring with the page layout.

Edit Source Files > application > views > scripts > employee > index.phtml to render the code. Change the file to look like:

<table id="employee_list_table">
  <tr>
    <th>Email</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>ID</th>
  </tr>

<?php foreach ($this->entries as $entry): ?>
  <tr>
    <td><?php echo $this->escape($entry->email) ?></td>
    <td><?php echo $this->escape($entry->firstname) ?></td>
    <td><?php echo $this->escape($entry->lastname) ?></td>
    <td><?php echo $this->escape($entry->employeeid) ?></td>
  </tr>
<?php endforeach ?>

</table>

Save the Project files.

Reload the web page or run the project by pressing F6 in NetBeans. In the browser click the Employee Administration link. The list of employees is shown.

You can easily navigate back and forth between the home page and the employee list.

Run Alt+Z to open the Run Zend Command window.

In the Filter field enter action. Make sure the create action task is selected in the Matching Tasks list. In the Parameters field enter new Employee. The command shown will be zf.sh create action new Employee.

Click Run.

This creates a newAction() method in the existing EmployeController.php file and a corresponding view in Source Files > application > views > scripts > employee > new.phtml. These are used when the URL http://localhost/~phphol/PHPLab/public/employee/new is called.

Run Alt+Z to open the Run Zend Command window.

Make sure the Matching Tasks list has the create action task selected. In the Parameters field change the text to be edit Employee. The command shown will be zf.sh create action edit Employee.

Click Run.

This creates an editAction() method in the existing EmployeController.php file and a corresponding view in Source Files > application > views > scripts > employee > edit.phtml. These are used when the URL http://localhost/~phphol/PHPLab/public/employee/edit is called.

Note: If you run into a duplicate action error it may be because you forgot to change the Zend Framework parameter. It might also occur if you have multiple NetBeans projects open. Be sure to click on the correct project before opening the Run Zend Command window. The error might be like:

An Error Has Occurred
 This controller (Employee) already has an action named (edit)

Zend Framework Command Line Console Tool v1.10.3
Details for action "Create" and provider "Action"
  Action
    zf create action name controller-name[=Index]
                                       view-included[=1] module

Run Alt+Z to open the Run Zend Command window.

Make sure the Matching Tasks list has the create action task selected. In the Parameters field change the text to be delete Employee. The command shown will be zf.sh create action delete Employee.

Click Run.

This creates an editAction() method in the existing EmployeController.php file and a corresponding view in Source Files > application > views > scripts > employee > edit.phtml. These are used when the URL http://localhost/~phphol/PHPLab/public/employee/edit is called.

Creation of three actions added corresponding methods to EmployeeController.php and the three created views:

Edit Source Files > application > views > scripts > employee > index.phtml to modify the employee list and add the link for the new employee action.

Add the following text at the top, above the <table id="employee_list_table"> line:

<a href="<?php echo $this->url(
      array('controller' => 'employee','action' => 'new'),
     'default', true) ?>">Add New Employee</a>

Save the Project files.

In the browser, reload the Employee Administration page http://localhost/~phphol/PHPLab/public/employee. It shows the Add New Employee link above the table.

If you hover over the link, its URL is http://localhost/~phphol/PHPLab/public/employee/new.

Clicking the link shows the contents of the file Source Files > application > views > scripts > employee > new.phtml

Run Alt+Z to open the Run Zend Command window.

In the Filter field enter form. Make sure the create form task is selected in the Matching Tasks list. In the Parameters field change the text to be Employee (singular). The command shown will be zf.sh create form Employee.

Click Run.

Update the new file Source Files > application > forms > Employee.php. Open a terminal window and execute:

cd $HOME/public_html/PHPLab/application/forms
cp $HOME/samples/forms_Employee.php Employee.php

If you have this file open in NetBeans the screen will automatically reload after a short delay.

The file:

1. Uses POST for the method.
2. Adds a hidden field for the employee identifier.
3. Uses an addElement() method to create each form field with appropriate validation.
4. To simplify the tutorial, jobs (President and Vice President) are hardcoded.

Edit the view script Source Files > application > views > scripts > employee > new.phtml. Replace the automatically generated file with:

<div id="formdiv">
  <h1>Add New Employee</h1>
  <p>Use this form to manage your employees.</p>
  <?php
    $this->form->setAction($this->url());
    echo $this->form;
  ?>
</div>

Save the file.

Edit the employee controller script Source Files > application > controllers > EmployeeController.php. Replace the empty newAction() method with the following code. Make sure to leave the other methods unchanged:

public function newAction()
{
  $request = $this->getRequest();
  $form    = new Application_Form_Employee();

  if ($this->getRequest()->isPost()) {
    if ($form->isValid($request->getPost())) {
      $employee = new Application_Model_Employee(
                                    $form->getValues());
      $mapper   = new Application_Model_EmployeeMapper();
      $mapper->save($employee);
      return $this->_helper->redirector('index');
    }
  }

  // Associate a link with the cancel button
  $form->getElement('cancel')->setAttrib('onclick',
	  "window.open('".
	  $this->view->url( array(
	  'controller' => 'employee',
	  'action'     => 'index'
	  ), 'default', true).
	  "','_self')");

  $this->view->form = $form;
}

This code is invoked when http://localhost/~phphol/PHPLab/public/employee/new is called. It instantiates a form, saves any data if it was invoked as a post request, and sets the appropriate URL for when the cancel button is clicked.

Save the project files and reload the application in the browser.

Navigate to the Add New Employee page. Leave the form fields empty and click the Create Employee button.

Validation supplied by Zend Framework prevents invalid data being submitted.

Clicking the Cancel Action button will return back to the employee list page.

If you try to insert a valid record right now you will get an error that Application_Model_EmployeeMapper::save() is missing. In a few more steps, this method will be created.

Note: If you made the change to the new.phtml view without changing the newAction() controller action you may get an error similar to:

Fatal error: Call to a member function setAction() on a
non-object in PHPLab/application/views/scripts/employee/new.phtml

Edit Source Files > application > models > EmployeeMapper.php to add the logic to save a new employee's data.

Add a new save() method at the top of the class, leaving the rest of the file unchanged. This method saves the employee on the EmployeeMapper class:

public function save(Application_Model_Employee $employee)
{
  $data = array(
      'EMAIL'          => $employee->getEmail(),
      'FIRST_NAME'     => $employee->getFirstName(),
      'LAST_NAME'      => $employee->getLastName(),
      'PHONE_NUMBER'   => $employee->getPhoneNumber(),
      'HIRE_DATE'      => $employee->getHireDate(),
      'JOB_ID'         => $employee->getJobId(),
      'SALARY'         => $employee->getSalary(),
      'COMMISSION_PCT' => $employee->getCommissionPct(),
  );

  if (null === ($id = $employee->getEmployeeId())) {
    unset($data['EMPLOYEE_ID']);
    $this->getDbTable()->insert($data);
  } else {
    $this->getDbTable()->update($data,
                         array('EMPLOYEE_ID = ?' => $id));
  }
}

Save the Project files.

Reload the web page (or run the application) and navigate to the Add New Employee page. Enter the following values:

  Chris
  Jones
  cj@example.com
  20-JAN-09
  Vice President

Click the Create Employee button. The new record will be inserted into the database and will be displayed at the end of the employee list.

Edit the employee list page Source Files > application > views > scripts > employee > index.phtml.

Change the $entry->email column to be a link for editing an employee:

<td><a href="<?php echo $this->url(
	  array('controller' => 'employee',
		'action'     => 'edit',
		'id'=> $this->escape($entry->employeeid)),
		'default',
		true) ?>">
	 <?php echo $this->escape($entry->email) ?></a></td>

Save the Project files.

Reload the employee list page http://localhost/~phphol/PHPLab/public/employee in your browser. The new edit links are shown for the email addresses.

Hover over the edit links and see how the URLs relate to the code we just modified. If you click any of the links you are shown the default template text in the related view for the edit "action".

Edit the edit-action view script Source Files > application > views > scripts > employee > edit.phtml and replace its contents with:

<div id="formdiv">
  <h1>Edit Employee</h1>
  <p>Use this form to manage your employees.</p>

  <?php
  $this->form->setAction($this->url());
  // Overwrite the default value used in 
  // the New Employee form for the submit button
  $this->form->getElement("submit")->setLabel("Update Employee");

  echo $this->form;
  ?>
</div>

This will reuse the form previously created for adding new employees, but uses different submit button text. Save the file.

Edit Source Files > application > models > EmployeeMapper.php.

Add a new find() method at the top of the Application_Model_EmployeeMapper class. This will be used to populate the form with data for an existing employee. Leave the other classes in the file unchanged.

The new method is:

public function find($employee_id, Application_Model_Employee $employee) {
  $result = $this->getDbTable()->find($employee_id);
  if (0 == count($result)) {
    return;
  }
  $row = $result->current();
  $employee->setEmployeeId($row->EMPLOYEE_ID)
	  ->setFirstName($row->FIRST_NAME)
	  ->setLastName($row->LAST_NAME)
	  ->setEmail($row->EMAIL)
	  ->setPhoneNumber($row->PHONE_NUMBER)
	  ->setHireDate($row->HIRE_DATE)
	  ->setJobId($row->JOB_ID)
	  ->setSalary($row->SALARY)
	  ->setCommissionPct($row->COMMISSION_PCT);
}

Update the editAction() method in Source Files > application > controllers > EmployeeController.php.

Open a terminal window and execute:

cd $HOME/public_html/PHPLab/application/controllers
cp $HOME/samples/EmployeeController.php EmployeeController.php

If you have this file open in NetBeans the screen will automatically reload after a short delay.

This method registers the form in the controller editAction() method and add the business logic calling EmployeeMapper's find() and save() methods.

Save the Project files.

In your browser, navigate to the Employee Administration page.

Locate the recently added entry for Chris Jones at the bottom.

Click on the cj@example.com edit link. Change the First Name from Chris to Christopher and click the Update Employee button.

The new value will be stored in the database and shown in the employee list.

Edit Source Files > application > views > scripts > employee > index.phtml.

Change the column header from ID to Action:

<th>Action</th>

Change the $entry->employeeid column output into a deletion action link:

<td><a href="<?php echo $this->url(
	   array('controller' => 'employee',
		 'action'     => 'delete',
		 'id'=> $this->escape($entry->employeeid)),
		 'default', true) ?>"
      >Delete</a></td>

Edit the view script Source Files > application > views > scripts > employee > delete.phtml. Change the default contents of the file to:

<div id="formdiv">
<h1>Delete Employee</h1>
<p>Use this form to confirm deletion of an employee.</p>

<?php
$this->form->setAction($this->url());
// Overwrite the default value used in
// the New Employee form
$this->form->getElement("submit")->setLabel("Delete Employee");

echo $this->form;
?>
</div>

This will reuse the form previously created form for adding and editing new employees, with appropriate text for the submit button.

Edit Source Files > application > models > EmployeeMapper.php. Add a delete() method to the Application_Model_EmployeeMapper class, leaving the other code unchanged.

public function delete($employee_id)
{
  $this->getDbTable()->delete(
             array('EMPLOYEE_ID = ?' => $employee_id));
}

Update the deleteAction() method in Source Files > application > controllers > EmployeeController.php.

Open a terminal window and execute:

cd $HOME/public_html/PHPLab/application/controllers
cp $HOME/samples/EmployeeController_del.php EmployeeController.php

If you have this file open in NetBeans the screen will automatically reload after a short delay.

The logic for deletion is very similar to the edit logic. The difference is that there is no need to validate the data, and the setAttrib() method is used in a loop to make all fields read-only.

Save the Project files .

Run the application, or reload the page in your browser.

Navigate to the Employee Administration page and delete the employee Christopher Jones that you previously added.

If you try to delete a manager such as SKING you will get the following SQL error:

Message: 2292 ORA-02292: integrity constraint (PHPHOL.DEPT_MGR_FK) violated - 
child record found *DELETE FROM "EMPLOYEES" WHERE (EMPLOYEE_ID = '100')           

In future, the application business logic could be extended to only enable the 'delete' action on non-managerial employees.