5 Updating Data

In this chapter, you extend the Anyco HR application with forms that enable you to insert, update, and delete an employee record.

Building the Basic Employees page
Extending the Basic Employees Page
Combining Departments and Employees
Adding Error Recovery
Further Error Handling

Building the Basic Employees page

In this section, you will extend your application to include a basic employees page.

To display employee records, perform the following tasks:

Create the chap5 directory, copy the application files from chap4, and change to the newly created directory:

On Windows:

mkdir c:\program files\Apache Group\Apache2\htdocs\chap5
cd c:\program files\Apache Group\Apache2\htdocs\chap5
copy ..\chap4\* .

On Linux:

mkdir $HOME/public_html/chap5
cd $HOME/public_html/chap5
cp ../chap4/* .

Edit the anyco.php file. Add a construct_employees() function. This function constructs the employee query, calls the db_do_query() function to execute the query, and prints the results using the ui_print_employees() function:

function construct_employees()
{
  $query =
  "SELECT employee_id,
    substr(first_name,1,1) || '.  '|| last_name as employee_name,
    hire_date,
    to_char(salary, '9999G999D99') as salary,
    nvl(commission_pct,0) as commission_pct
   FROM   employees
   ORDER BY employee_id asc";

  $conn = db_connect();
  $emp = db_do_query($conn, $query);

  ui_print_header('Employees');
  ui_print_employees($emp);
  ui_print_footer(date('Y-m-d H:i:s'));
}

There is no need to pass a $bindargs parameter to the db_do_query() call because this query does not use bind variables. The db_do_query() declaration will provide a default value of an empty array automatically. PHP allows functions to have variable numbers of parameters.

Edit the anyco.php file. Replace the call to construct_departments() with a call to construct_employees():

<?php // File: anyco.php

require('anyco_cn.inc');
require('anyco_db.inc');
require('anyco_ui.inc');

session_start();
construct_employees();
...
?>

Edit the anyco_ui.inc file. Implement the presentation of employee data in an HTML table by adding a ui_print_employees() function:

function ui_print_employees($employeerecords)
{
  if (!$employeerecords) {
    echo '<p>No Employee found</p>';
  }
  else {
    echo <<<END
  <table>
  <tr>
    <th>Employee<br>ID</th>
    <th>Employee<br>Name</th>
    <th>Hiredate</th>
    <th>Salary</th>
    <th>Commission<br>(%)</th>
  </tr>
END;
    // Write one row per employee
    foreach ($employeerecords as $emp) {
      echo '<tr>';
      echo '<td align="right">'.
            htmlentities($emp['EMPLOYEE_ID']).'</td>';
      echo '<td>'.htmlentities($emp['EMPLOYEE_NAME']).'</td>';
      echo '<td>'.htmlentities($emp['HIRE_DATE']).'</td>';
      echo '<td align="right">'.
            htmlentities($emp['SALARY']).'</td>';
      echo '<td align="right">'.
            htmlentities($emp['COMMISSION_PCT']).'</td>';
      echo '</tr>';
    }
    echo <<<END
  </table>
END;
  }
}

Save the changes to the anyco.php and anyco_ui.inc files. Test the result of these changes by entering the following URL in your Web browser:

On Windows:
```
http://localhost/chap5/anyco.php
```
On Linux:
```
http://localhost/~<username>/chap5/anyco.php
```
Examine the result page, and scroll down to view all the employee records displayed in the page:

Description of the illustration chap5_basic_emp_001.gif

Extending the Basic Employees Page

In this section, you will extend the basic employees page to include the ability to manipulate employee records.

To enable employee records to be manipulated, perform the following tasks:

Edit the anyco.php file. Replace the construct_employees() call with the form handler control logic to manage the requests for showing, inserting, updating, and deleting employee records:

<?php // File: anyco.php

require('anyco_cn.inc');
require('anyco_db.inc');
require('anyco_ui.inc');

session_start();
// Start form handler code
if (isset($_POST['insertemp'])) {
  construct_insert_emp();
}
elseif (isset($_POST['saveinsertemp'])) {
  insert_new_emp();
}
elseif (isset($_POST['modifyemp'])) {
  construct_modify_emp();
}
elseif (isset($_POST['savemodifiedemp'])) {
  modify_emp();
}
elseif (isset($_POST['deleteemp'])) {
  delete_emp();
}
else {
  construct_employees();
}

...

Edit the anyco.php file. Add the construct_insert_emp() function:
```
function construct_insert_emp()
{
  $conn = db_connect();

  $query = "SELECT job_id, job_title
            FROM jobs
            ORDER BY job_title ASC";
  $jobs = db_do_query($conn, $query,
                      OCI_FETCHSTATEMENT_BY_COLUMN);

  $query = "SELECT sysdate FROM dual";
  $date = db_do_query($conn, $query,
                      OCI_FETCHSTATEMENT_BY_COLUMN);
  $emp = array(
    'DEPARTMENT_ID' => 10,      // Default to department 10
    'HIRE_DATE' => $date['SYSDATE'][0],
    'ALLJOBIDS' => $jobs['JOB_ID'],
    'ALLJOBTITLES' => $jobs['JOB_TITLE']
    );

  ui_print_header('Insert New Employee');
  ui_print_insert_employee($emp, $_SERVER['SCRIPT_NAME']);
  // Note: The two kinds of date used:
  // 1) SYSDATE for current date of the database system, and
  // 2) The PHP date for display in the footer of each page
  ui_print_footer(date('Y-m-d H:i:s'));
}
```
The construct_insert_emp() function executes two queries to obtain default data to be used to populate the Insert New Employee form, which is displayed by the ui_print_insert_employee() function.

The $query of the JOBS table obtains a list of all the existing job IDs and their descriptions in order to build a list for selecting a job type in the HTML form generated by the ui_print_insert_employee() function.

The $query using SYSDATE obtains the current database date and time for setting the default hire date of the new employee.

There are two kinds of date used in the application code, the PHP date() function for printing the date and time in the page footer, and the Oracle SYSDATE function to obtain the default date and time for displaying in the hire date field of the Employees page and to ensure that text is entered in the correct database format.

The two db_do_query() function calls provide an additional parameter value OCI_FETCHSTATEMENT_BY_COLUMN to specify that the return type for the query is an array of column values.

Edit the anyco.php file. Add the insert_new_emp() function to insert an employee record into the EMPLOYEES table:

function insert_new_emp()
{
  $newemp = $_POST;
  $statement =
    "INSERT INTO employees
        (employee_id, first_name, last_name, email, hire_date,
         job_id, salary, commission_pct, department_id)
     VALUES (employees_seq.nextval, :fnm, :lnm, :eml, :hdt, :jid,
             :sal, :cpt, :did)";

  $conn = db_connect();
  $emailid = $newemp['firstname'].$newemp['lastname'];

  $bindargs = array();
  array_push($bindargs, array('FNM', $newemp['firstname'], -1));
  array_push($bindargs, array('LNM', $newemp['lastname'], -1));
  array_push($bindargs, array('EML', $emailid, -1));
  array_push($bindargs, array('HDT', $newemp['hiredate'], -1));
  array_push($bindargs, array('JID', $newemp['jobid'], -1));
  array_push($bindargs, array('SAL', $newemp['salary'], -1));
  array_push($bindargs, array('CPT', $newemp['commpct'], -1));
  array_push($bindargs, array('DID', $newemp['deptid'], -1));

  $r = db_execute_statement($conn, $statement, $bindargs);
  construct_employees();
}

The return value from the db_execute_statement() function is ignored and not even assigned to a variable, because no action is performed on its result.

Edit the anyco.php file. Add the construct_modify_emp() function to build the HTML form for updating an employee record.

function construct_modify_emp()
{
  $empid = $_POST['emprec'];
  $query =
    "SELECT employee_id, first_name, last_name, email, hire_date,
            salary, nvl(commission_pct,0) as commission_pct
     FROM   employees
     WHERE  employee_id = :empid";

  $conn = db_connect();
  $bindargs = array();
  array_push($bindargs, array('EMPID', $empid, -1));

  $emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, 
                                      $bindargs);

  ui_print_header('Modify Employee ');
  ui_print_modify_employee($emp[0], $_SERVER['SCRIPT_NAME']);
  ui_print_footer(date('Y-m-d H:i:s'));
}

Edit the anyco.php file. Add the modify_emp() function to update the employee record in the EMPLOYEES table, using the update form field values:

function modify_emp()
{
  $newemp = $_POST;
  $statement =
    "UPDATE employees
     SET   first_name = :fnm, last_name = :lnm, email = :eml,
           salary = :sal, commission_pct = :cpt
     WHERE employee_id = :eid";

  $conn = db_connect();
  $bindargs = array();
  array_push($bindargs, array('EID', $newemp['empid'], -1));
  array_push($bindargs, array('FNM', $newemp['firstname'], -1));
  array_push($bindargs, array('LNM', $newemp['lastname'], -1));  
  array_push($bindargs, array('EML', $newemp['email'], -1));
  array_push($bindargs, array('SAL', $newemp['salary'], -1));
  array_push($bindargs, array('CPT', $newemp['commpct'], -1));

  $r = db_execute_statement($conn, $statement, $bindargs);
  construct_employees();
}

Edit the anyco.php file. Add the delete_emp() function to delete an employee record from the EMPLOYEES table:

function delete_emp()
{
  $empid = $_POST['emprec'];
  $statement = "DELETE FROM employees
                WHERE employee_id = :empid";
    $conn = db_connect();
  $bindargs = array();
  array_push($bindargs, array('EMPID', $empid, 10));
  $r = db_execute_statement($conn, $statement, $bindargs);

  construct_employees();
}

Edit the anyco.php file. In the construct_employees() function, modify the db_do_query() call to supply OCI_FETCHSTATEMENT_BY_ROW as the last parameter, and provide $_SERVER['SCRIPT_NAME'] as second parameter in the ui_print_employees() call. The function becomes:

function construct_employees()
{
  $query =
  "SELECT employee_id,
    substr(first_name,1,1) || '.  '|| last_name as employee_name,
    hire_date,
    to_char(salary, '9999G999D99') as salary,
    nvl(commission_pct,0) as commission_pct
   FROM   employees
   ORDER BY employee_id asc";

  $conn = db_connect();
  $emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW);

  ui_print_header('Employees');
  ui_print_employees($emp, $_SERVER['SCRIPT_NAME']);
  ui_print_footer(date('Y-m-d H:i:s'));
}

Edit the anyco_db.inc file. Add $resulttype as a third parameter to the db_do_query() function. Replace the last parameter value, OCI_FETCHSTATEMENT_BY_ROW, in the oci_fetch_all() call with a variable, so that callers can choose the output type.
```
function db_do_query($conn, $statement, $resulttype,
                     $bindvars = array())
{
  $stid = oci_parse($conn, $statement);

  ...

  $r = oci_fetch_all($stid, $results, null, null, $resulttype);
  return($results);
}
```

Edit the anyco_db.inc file. Inside the db_get_page_data() function, insert OCI_FETCHSTATEMENT_BY_ROW as the third parameter value in the db_do_query() call:

function db_get_page_data($conn, $q1, $current = 1,
                          $rowsperpage = 1, $bindvars = array())
{
 
  ...

  $r = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $bindvars);
  return($r);
}

Edit the anyco_db.inc file. Add a db_execute_statement() function to execute data manipulation statements such as INSERT statements:

function db_execute_statement($conn, $statement, $bindvars = array())
{
  $stid = oci_parse($conn, $statement);
  if (!$stid) {
    db_error($conn, __FILE__, __LINE__);
  }
    // Bind parameters
  foreach ($bindvars as $b) {
    // create local variable with caller specified bind value 
    $$b[0] = $b[1];
    $r = oci_bind_by_name($stid, ":$b[0]", $$b[0], $b[2]);
    if (!$r) {
      db_error($stid, __FILE__, __LINE__);
    }
  }

  $r = oci_execute($stid);
  if (!$r) {
    db_error($stid, __FILE__, __LINE__);
  }
  return($r);
}

Edit the anyco_ui.inc file. Change the ui_print_employees() function to produce an HTML form containing the employee rows. The function becomes:

function ui_print_employees($employeerecords, $posturl)
{
  if (!$employeerecords) {
    echo '<p>No Employee found</p>';
  }
  else {
    echo <<<END
  <form method="post" action="$posturl">
  <table>
  <tr>
    <th>&nbsp;</th>
    <th>Employee<br>ID</th>
    <th>Employee<br>Name</th>
    <th>Hiredate</th>
    <th>Salary</th>
    <th>Commission<br>(%)</th>
  </tr>
END;
    // Write one row per employee
    foreach ($employeerecords as $emp) {
      echo '<tr>';
      echo '<td><input type="radio" name="emprec" value="'.
             htmlentities($emp['EMPLOYEE_ID']).'"></td>';
      echo '<td align="right">'.
                htmlentities($emp['EMPLOYEE_ID']).'</td>';
      echo '<td>'.htmlentities($emp['EMPLOYEE_NAME']).'</td>';
      echo '<td>'.htmlentities($emp['HIRE_DATE']).'</td>';
      echo '<td align="right">'.
                htmlentities($emp['SALARY']).'</td>';
      echo '<td align="right">'.
                htmlentities($emp['COMMISSION_PCT']).'</td>';
      echo '</tr>';
    }
    echo <<<END
  </table>
  <input type="submit" value="Modify" name="modifyemp">
  <input type="submit" value="Delete" name="deleteemp">
  &nbsp;&nbsp;
  <input type="submit" value="Insert new employee"
         name="insertemp">
  </form>
END;
  }
}

A radio button is displayed in the first column of each row to enable you to select the record to be modified or deleted.

Edit the anyco_ui.inc file. Add the ui_print_insert_employee() function to generate the form to input new employee data:

function ui_print_insert_employee($emp, $posturl)
{
  if (!$emp) {
    echo "<p>No employee details found</p>";
  }
  else {
    $deptid = htmlentities($emp['DEPARTMENT_ID']);
    $hiredate = htmlentities($emp['HIRE_DATE']);

    echo <<<END
  <form method="post" action="$posturl">
  <table>
    <tr>
      <td>Department ID</td>
      <td><input type="text" name="deptid" value="$deptid" 
                 size="20"></td>
    </tr>
    <tr>
      <td>First Name</td>
      <td><input type="text" name="firstname" size="20"></td>
    </tr>
    <tr>
      <td>Last Name</td>
      <td><input type="text" name="lastname" size="20"></td>
    </tr>
    <tr>
      <td>Hiredate</td>
      <td><input type="text" name="hiredate" value="$hiredate" 
                 size="20"></td>
    </tr>
    <tr>
      <td>Job</td>
       <td><select name="jobid">
END;
    // Write the list of jobs
    for ($i = 0; $i < count($emp['ALLJOBIDS']); $i++)
    {
      echo '<option
             label="'.htmlentities($emp['ALLJOBTITLES'][$i]).'"'.
           ' value="'.htmlentities($emp['ALLJOBIDS'][$i]).'">'.
           htmlentities($emp['ALLJOBTITLES'][$i]).'</option>';
    }
    echo <<<END
      </select>
      </td>
    </tr>
    <tr>
      <td>Salary</td>
      <td><input type="text" name="salary" value="1" 
                 size="20"></td>
    </tr>
    <tr>
      <td>Commission (%)</td>
      <td><input type="text" name="commpct" value="0" 
                 size="20"></td>
    </tr>
  </table>
    <input type="submit" value="Save" name="saveinsertemp">
    <input type="submit" value="Cancel" name="cancel">
  </form>
END;
  }
}

Edit the anyco_ui.inc file. Add the ui_print_modify_employee() function to generate the form to update an employee record:

function ui_print_modify_employee($empdetails, $posturl)
{
  if (!$empdetails) {
    echo '<p>No Employee record selected</p>';
  }
  else {
    $fnm = htmlentities($empdetails['FIRST_NAME']);
    $lnm = htmlentities($empdetails['LAST_NAME']);
    $eml = htmlentities($empdetails['EMAIL']);
    $sal = htmlentities($empdetails['SALARY']);
    $cpt = htmlentities($empdetails['COMMISSION_PCT']);
    $eid = htmlentities($empdetails['EMPLOYEE_ID']);

    echo <<<END
  <form method="post" action="$posturl">
  <table>
    <tr>
      <td>Employee ID</td>
      <td>$eid</td></tr>
    <tr>
      <td>First Name</td>
      <td><input type="text" name="firstname" value="$fnm"></td>
    </tr>
    <tr>
      <td>Last Name</td>
      <td><input type="text" name="lastname" value="$lnm"></td>
    </tr>
    <tr>
      <td>Email Address</td>
      <td><input type="text" name="email" value="$eml"></td>
    </tr>
    <tr>
      <td>Salary</td>
      <td><input type="text" name="salary" value="$sal"></td>
    </tr>
    <tr>
      <td>Commission (%)</td>
      <td><input type="text" name="commpct" value="$cpt"></td>
    </tr>
  </table>
  <input type="hidden" value="{$empdetails['EMPLOYEE_ID']}" 
         name="empid">
  <input type="submit" value="Save" name="savemodifiedemp">
  <input type="submit" value="Cancel" name="cancel">
  </form>
END;
  }
}

Save the changes to your Anyco application files, and test the changes by entering the following URL in your Web browser:

On Windows:
```
http://localhost/chap5/anyco.php
```
On Linux:
```
http://localhost/~<username>/chap5/anyco.php
```
The list of all employees is displayed with a radio button in each row.

Description of the illustration chap5_test_emp_001.gif

Scroll to the bottom of the Employees page to view the Modify, Delete, and Insert new employee buttons:

Description of the illustration chap5_test_emp_002.gif
To insert a new employee record, click Insert new employee:

Description of the illustration chap5_test_emp_003.gif

When you create or modify employee records, you will see that the database definitions require the salary to be greater than zero, and the commission to be less than 1. The commission will be rounded to two decimal places. In the Insert New Employee page, the Department ID field contains 10 (the default), Hiredate contains the current date (in default database date format), Salary contains 1, and Commission (%) contains 0. Enter the following field values:

First Name: James

Last Name: Bond

Job: Select Programmer from the list.

Salary: replace the 1 with 7000

Click Save.

Description of the illustration chap5_test_emp_004.gif
When the new employee record is successfully inserted, the Web page is refreshed with the form listing all employees. Scroll the Web page to the last record and check that the new employee record is present. The employee ID assigned to the new record on your system may be different than the one shown in the following example:

Description of the illustration chap5_test_emp_005.gif
To modify the new employee record, select the radio button next to the new employee record, and click Modify:

Description of the illustration chap5_test_emp_006.gif
In the Modify Employee page, modify the Email Address field to JBOND, increase the Salary to 7100, and click Save:

Description of the illustration chap5_test_emp_007.gif
Successfully updating the employee record causes the Employees page to be redisplayed. Scroll to the last employee record and confirm that the salary for James Bond is now 7,100:

Description of the illustration chap5_test_emp_008.gif
To remove the new employee record, select the radio button for the new employee record, and click Delete:

Description of the illustration chap5_test_emp_009.gif

On successful deletion, the deleted row does not appear in the list of employee records redisplayed in the Employees page:

Description of the illustration chap5_test_emp_010.gif

Combining Departments and Employees

In this section, you will modify your application to enable access to both Employees and Departments pages.

To combine the Departments and Employees pages, perform the following tasks:

Edit the anyco.php file. Modify the query in the construct_employees() function to include a WHERE clause to compare the department_id with a value in a bind variable called :did. This makes the page display employees in one department at a time. Get the deptid session parameter value to populate the bind variable:

$query =
 "SELECT employee_id, 
         substr(first_name,1,1) || '.  '|| last_name as employee_name,
         hire_date,
         to_char(salary, '9999G999D99') as salary, 
         nvl(commission_pct,0) as commission_pct
  FROM   employees
  WHERE  department_id = :did
  ORDER BY employee_id asc";

$deptid = $_SESSION['deptid'];

Edit the anyco.php file. In the construct_employees() function, update the call to the db_do_query() function to pass the bind information:

$conn = db_connect();

$bindargs = array();
array_push($bindargs, array('DID', $deptid, -1));

$emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $bindargs);

Edit the anyco.php file. In the construct_departments() function, save the department identifier in a session parameter:
```
$_SESSION['currentdept'] = $current;
$_SESSION['deptid'] = $deptid;
```
This saves the current department identifier from the Departments page as a session parameter, which is used in the Employees page.

Edit the anyco.php file. Create a function get_dept_name() to query the department name for printing in the Departments and Employees page titles:

function get_dept_name($conn, $deptid)
{
  $query =
    'SELECT department_name
     FROM   departments
     WHERE  department_id = :did';

  $conn = db_connect();
  $bindargs = array();
  array_push($bindargs, array('DID', $deptid, -1));
  $dn = db_do_query($conn, $query,OCI_FETCHSTATEMENT_BY_COLUMN, $bindargs);

  return($dn['DEPARTMENT_NAME'][0]);
}

Edit the anyco.php file. Modify the construct_employees() function to print the department name in the Employees page heading:
```
$deptname = get_dept_name($conn, $deptid);
ui_print_header('Employees: '.$deptname);
```
Edit the anyco.php file. Modify the construct_departments() function to print the department name in the Departments page heading:
```
$deptname = get_dept_name($conn, $deptid);
ui_print_header('Department: '.$deptname);
```

Edit the anyco.php file. Modify the construct_insert_emp() function so that the default department is obtained from the session parameter passed in the $emp array to the ui_print_insert_employee() function. The function becomes:

function construct_insert_emp()
{
  $deptid = $_SESSION['deptid'];

  $conn = db_connect();
  $query = "SELECT job_id, job_title FROM jobs ORDER BY job_title ASC";
  $jobs = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN);
  $query = "SELECT sysdate FROM dual";
  $date = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN);
  $emp = array(
    'DEPARTMENT_ID' => $deptid,
    'HIRE_DATE' => $date['SYSDATE'][0],
    'ALLJOBIDS' => $jobs['JOB_ID'],
    'ALLJOBTITLES' => $jobs['JOB_TITLE']
    );
  ui_print_header('Insert New Employee');
  ui_print_insert_employee($emp, $_SERVER['SCRIPT_NAME']);
  ui_print_footer(date('Y-m-d H:i:s'));
}

Edit the anyco.php file. Modify the final else statement in the HTML form handler. The handler becomes:

// Start form handler code
if (isset($_POST['insertemp'])) {
  construct_insert_emp();
}
elseif (isset($_POST['saveinsertemp'])) {
  insert_new_emp();
}
elseif (isset($_POST['modifyemp'])) {
  construct_modify_emp();
}
elseif (isset($_POST['savemodifiedemp'])) {
  modify_emp();
}
elseif (isset($_POST['deleteemp'])) {
  delete_emp();
}
elseif (   isset($_POST['showemp'])) {
  construct_employees();
}
elseif (   isset($_POST['nextdept'])
        || isset($_POST['prevdept'])
        || isset($_POST['firstdept'])
        || isset($_POST['showdept'])) {
  construct_departments();
}
else {
  construct_departments();
}

Edit the anyco_ui.inc file. In the ui_print_department() function, change the HTML form to enable it to call the Employees page:

...
<form method="post" action="$posturl">
<input type="submit" value="First" name="firstdept">
<input type="submit" value="< Previous" name="prevdept">
<input type="submit" value="Next >" name="nextdept">
&nbsp;&nbsp;&nbsp;
<input type="submit" value="Show Employees" name="showemp">
</form>
...

Edit the anyco_ui.inc file. In the ui_print_employees() function, change the HTML form to enable it to call the Departments page:

...
</table>
<input type="submit" value="Modify" name="modifyemp">
<input type="submit" value="Delete" name="deleteemp">
&nbsp;&nbsp;
<input type="submit" value="Insert new employee" name="insertemp">
&nbsp;&nbsp;
<input type="submit" value="Return to Departments" name="showdept">
</form>
...

Save the changes to your PHP files. In your browser, test the changes by entering the following URL:

On Windows:
```
http://localhost/chap5/anyco.php
```
On Linux:
```
http://localhost/~<username>/chap5/anyco.php
```
The Departments page is displayed.

Description of the illustration chap5_combine_deptemp_001.gif

To display a list of employees in the department, click the Show Employees button.

Description of the illustration chap5_combine_deptemp_002.gif

You can return to the Departments page by clicking the Return to Departments button. Experiment by navigating to another department and listing its employees to show the process of switching between the Departments and Employees pages.

Adding Error Recovery

Error management is always a significant design decision. In production systems, you might want to classify errors and handle them in different ways. Fatal errors could be redirected to a standard "site not available" page or home page. Data errors for new record creation might return to the appropriate form with invalid fields highlighted.

In most production systems, you would set the display_errors configuration option in the php.ini file to off, and the log_errors configuration option to on.

You can use the PHP output buffering functionality to trap error text when a function is executing. Using ob_start() prevents text from displaying on the screen. If an error occurs, the ob_get_contents() function allows the previously generated error messages to be stored in a string for later display or analysis.

Now you change the application to display error messages and database errors on a new page using a custom error handling function. Errors are now returned from the db* functions keeping them silent.

Edit the anyco_db.inc file. Change the db_error() function to return the error information in an array structure, instead of printing and quitting. The function becomes:

function db_error($r = false, $file, $line)
{
  $err =  $r ? oci_error($r) : oci_error();

  if (isset($err['message'])) {
    $m = htmlentities($err['message']);
    $c = $err['code'];
  }
  else {
    $m = 'Unknown DB error';
    $c = null;
  }

  $rc = array(
    'MESSAGE' => $m,
    'CODE'    => $c,
    'FILE'    => $file,
    'LINE'    => $line
    );
  return $rc;
}

Edit the anyco_db.inc file. For every call to the db_error() function, assign the return value to a variable called $e and add a return false; statement after each call:
```
if (<error test>)
{
  $e = db_error(<handle>, __FILE__, __LINE__);
  return false;
}
```
Make sure to keep the <error test> and <handle> parameters the same as they are currently specified for each call. Remember that the __FILE__ and __LINE__ constants help to pinpoint the location of the failure during development. This is useful information to log for fatal errors in a production deployment of an application.

Edit the anyco_db.inc file. Add a $e parameter to every function to enable the return of error information. Use the & reference prefix to ensure that results are returned to the calling function. Each function declaration becomes:

function db_connect(&$e) {...}

function db_get_page_data($conn, $q1, $currrownum = 1, $rowsperpage = 1,
                          &$e, $bindvars = array()) {...}

function db_do_query($conn, $statement, $resulttype, &$e,
                     $bindvars = array()) {...}

function db_execute_statement($conn, $statement, &$e,
                              $bindvars = array()) {...}

Edit the anyco_db.inc file. In the db_get_page_data() function, change the call to the db_do_query() function to pass down the error parameter $e:
```
$r = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $e, $bindvars);
```
Edit the anyco_db.inc file. Add an @ prefix to all oci_* function calls. For example:
```
@ $r = @oci_execute($stid);
```
The @ prefix prevents errors from displaying because each return result is tested. Preventing errors from displaying can hide incorrect parameter usage, which may hinder testing the changes in this section. You do not need to add @ prefixes, but it can effect future results when errors are displayed.

Edit the anyco.php file. Create a function to handle the error information:

function handle_error($message, $err)
{
  ui_print_header($message);
  ui_print_error($err, $_SERVER['SCRIPT_NAME']);
  ui_print_footer(date('Y-m-d H:i:s'));
}

Edit the anyco.php file. Modify all calls to db_* functions to include the additional error parameter:

Steps 8 to 15 show the complete new functions, so the code changes in this step can be skipped.
- Change all db_connect() calls to db_connect($err).
- Change all db_do_query() calls and insert a $err parameter as the fourth parameter. For example, the call in construct_employees() becomes:
```
$emp = db_do_query($conn, $query, 
                   OCI_FETCHSTATEMENT_BY_ROW, $err, $bindargs);
```
  Change the other four db_do_query() calls in anyco.php remembering to keep the existing parameter values of each specific call.
- Change the db_get_page_data() call and insert a $err parameter as the fifth parameter:
```
$dept = db_get_page_data($conn, $query, $current, 1, $err);
```
- Change the db_execute_statement() calls and insert a $err parameter as the third parameter, for example:
```
$r = db_execute_statement($conn, $statement, $err, $bindargs);
```

Edit the anyco.php file. Modify the construct_departments() function to handle errors returned. The function becomes:

function construct_departments()
{
  if (isset($_SESSION['currentdept']) && isset($_POST['prevdept']) && 
            $_SESSION['currentdept'] > 1)
     $current = $_SESSION['currentdept'] - 1;
  elseif (isset($_SESSION['currentdept']) && isset($_POST['nextdept']))
     $current = $_SESSION['currentdept'] + 1;
  elseif (isset($_POST['showdept']) && isset($_SESSION['currentdept']))
     $current = $_SESSION['currentdept'];
  else
     $current = 1;

  $query =
    "SELECT d.department_id, d.department_name,
            substr(e.first_name,1,1)||'. '|| e.last_name as manager_name,
            c.country_name, count(e2.employee_id) as number_of_employees
     FROM   departments d, employees e, locations l, 
            countries c, employees e2
     WHERE  d.manager_id    = e.employee_id
     AND    d.location_id   = l.location_id
     AND    d.department_id = e2.department_id
     AND    l.country_id    = c.country_id
     GROUP BY d.department_id, d.department_name,
              substr(e.first_name,1,1)||'. '||e.last_name, c.country_name
     ORDER BY d.department_id ASC";

  $conn = db_connect($err);

  if (!$conn) {
    handle_error('Connection Error', $err);
  }
  else {
    $dept = db_get_page_data($conn, $query, $current, 1, $err);
    if ($dept === false) {  
      // Use === so empty array at end of fetch is not matched
      handle_error('Cannot fetch Departments', $err);
    } else {

      if (!isset($dept[0]['DEPARTMENT_ID']) && $current > 1) {  
        // no more records so go back one

        $current--;
        $dept = db_get_page_data($conn, $query, $current, 1, $err);
      }

      $deptid = $dept[0]['DEPARTMENT_ID'];

      $_SESSION['deptid'] = $deptid;
      $_SESSION['currentdept'] = $current;

      $deptname = get_dept_name($conn, $deptid);
      ui_print_header('Department: '.$deptname);
      ui_print_department($dept[0], $_SERVER['SCRIPT_NAME']);
      ui_print_footer(date('Y-m-d H:i:s'));
    }
  }
}

Edit the anyco.php file. Modify the construct_employees() function to handle errors. The function becomes:

function construct_employees()
{
  $query =
    "SELECT employee_id,
            substr(first_name,1,1) || '.  '|| last_name as employee_name,
            hire_date,
            to_char(salary, '9999G999D99') as salary,
            nvl(commission_pct,0) as commission_pct
     FROM   employees
     WHERE  department_id = :did
     ORDER BY employee_id asc";

  $deptid = $_SESSION['deptid'];

  $conn = db_connect($err);

  if (!$conn) {
    handle_error('Connection Error', $err);
  }
  else {
    $bindargs = array();
    array_push($bindargs, array('DID', $deptid, -1));
    $emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $err,
    $bindargs);

    if (!$emp) {
      handle_error('Cannot fetch Employees', $err);
    }
    else {
      $deptname = get_dept_name($conn, $deptid);
      ui_print_header('Employees: '.$deptname);
      ui_print_employees($emp, $_SERVER['SCRIPT_NAME']);
      ui_print_footer(date('Y-m-d H:i:s'));
    }
  }
}

Edit the anyco.php file. Modify the construct_insert_emp() function to handle errors. The function becomes:

function construct_insert_emp()
{
  $deptid = $_SESSION['deptid'];
  $conn = db_connect($err);
  if (!$conn) {
    handle_error('Connection Error', $err);
  }
  else {
    $query = "SELECT job_id, job_title FROM jobs ORDER BY job_title ASC";
    $jobs = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN, $err);
    $query = "SELECT sysdate FROM dual";
    $date = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN, $err);

    $emp = array(
      'DEPARTMENT_ID' => $deptid,
      'HIRE_DATE' => $date['SYSDATE'][0],
      'ALLJOBIDS' => $jobs['JOB_ID'],
      'ALLJOBTITLES' => $jobs['JOB_TITLE']
      );

    ui_print_header('Insert New Employee');
    ui_print_insert_employee($emp, $_SERVER['SCRIPT_NAME']);
    ui_print_footer(date('Y-m-d H:i:s'));
  }
}

Edit the anyco.php file. Modify the insert_new_emp() function to handle errors. The function becomes:

function insert_new_emp()
{
  $statement =
    'INSERT INTO employees
                 (employee_id, first_name, last_name, email, hire_date,
                 job_id, salary, commission_pct, department_id)
     VALUES (employees_seq.nextval, :fnm, :lnm, :eml, :hdt,
            :jid, :sal, :cpt, :did)';

  $newemp = $_POST;

  $conn = db_connect($err);
  if (!$conn) {
    handle_error('Connect Error', $err);
  }
  else {
    $emailid = $newemp['firstname'].$newemp['lastname'];

    $bindargs = array();
    array_push($bindargs, array('FNM', $newemp['firstname'], -1));
    array_push($bindargs, array('LNM', $newemp['lastname'], -1));
    array_push($bindargs, array('EML', $emailid, -1));
    array_push($bindargs, array('HDT', $newemp['hiredate'], -1));
    array_push($bindargs, array('JID', $newemp['jobid'], -1));
    array_push($bindargs, array('SAL', $newemp['salary'], -1));
    array_push($bindargs, array('CPT', $newemp['commpct'], -1));
    array_push($bindargs, array('DID', $newemp['deptid'], -1));

    $r = db_execute_statement($conn, $statement, $err, $bindargs);
    if ($r) {
      construct_employees();
    }
    else {
      handle_error('Cannot insert employee', $err);
    }
  }
}

Edit the anyco.php function. Modify the construct_modify_emp() function to handle errors. The function becomes:

function construct_modify_emp()
{
  if (!isset($_POST['emprec'])) { // User did not select a record
    construct_employees();
  }
  else {
    $empid = $_POST['emprec'];

    $query =
      "SELECT employee_id, first_name, last_name, email, hire_date,
              salary, nvl(commission_pct,0) as commission_pct
       FROM   employees
       WHERE  employee_id = :empid";

    $conn = db_connect($err);
    if (!$conn) {
      handle_error('Connect Error', $err);
    }
    else {
      $bindargs = array();
      array_push($bindargs, array('EMPID', $empid, -1));

      $emp = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_ROW, $err,
             $bindargs);

      if (!$emp) {
        handle_error('Cannot find details for employee '.$empid, $err);
      }
      else {
        ui_print_header('Modify Employee ');
        ui_print_modify_employee($emp[0], $_SERVER['SCRIPT_NAME']);
        ui_print_footer(date('Y-m-d H:i:s'));
      }
    }
  }
}

Edit the anyco.php file. Change the modify_emp() function to handle errors. The function becomes:

function modify_emp()
{
  $newemp = $_POST;

  $statement =
    "UPDATE employees
     SET    first_name = :fnm, last_name = :lnm, email = :eml,
            salary = :sal, commission_pct = :cpt
     WHERE  employee_id = :eid";

  $conn = db_connect($err);
  if (!$conn) {
    handle_error('Connect Error', $err);
  }
  else {
    $bindargs = array();
    array_push($bindargs, array('EID', $newemp['empid'], -1));
    array_push($bindargs, array('FNM', $newemp['firstname'], -1));
    array_push($bindargs, array('LNM', $newemp['lastname'], -1));
    array_push($bindargs, array('EML', $newemp['email'], -1));
    array_push($bindargs, array('SAL', $newemp['salary'], -1));
    array_push($bindargs, array('CPT', $newemp['commpct'], -1));

    $r = db_execute_statement($conn, $statement, $err, $bindargs);

    if (!$r) {
      handle_error('Cannot update employee '.$newemp['empid'], $err);
    }
    else {
      construct_employees();
    }
  }
}

Edit the anyco.php file. Modify the delete_emp() function to handle errors. The function becomes:

function delete_emp()
{
  if (!isset($_POST['emprec'])) { // User did not select a record
    construct_employees();
  }
  else {
    $empid = $_POST['emprec'];

    $conn = db_connect($err);
    if (!$conn) {
      handle_error('Connection Error', $err);
    }
    else {
      $statement = "DELETE FROM employees WHERE employee_id = :empid";
      $bindargs = array();
      array_push($bindargs, array('EMPID', $empid, -1));
      $r = db_execute_statement($conn, $statement, $err, $bindargs);

      if (!$r) {
        handle_error("Error deleting employee $empid", $err);
      }
      else {
        construct_employees();
      }
    }
  }
}

Edit the anyco.php file. Modify the get_dept_name() function to handle errors. The function becomes:

function get_dept_name($conn, $deptid)
{
  $query =
    'SELECT department_name
     FROM   departments
     WHERE  department_id = :did';

  $conn = db_connect($err);
  if (!$conn) {
    return ('Unknown');
  }
  else {
    $bindargs = array();
    array_push($bindargs, array('DID', $deptid, -1));
    $dn = db_do_query($conn, $query, OCI_FETCHSTATEMENT_BY_COLUMN,
                      $err, $bindargs);
    if ($dn == false)
      return ('Unknown');
    else
      return($dn['DEPARTMENT_NAME'][0]);
  }
}

Edit the anyco_ui.inc file. Add a new function ui_print_error():

function ui_print_error($message, $posturl)
{
  if (!$message) {
    echo '<p>Unknown error</p>';
  }
  else {
    echo "<p>Error at line {$message['LINE']} of "
         ."{$message['FILE']}</p>";  // Uncomment for debugging
    echo "<p>{$message['MESSAGE']}</p>";
  }
  echo <<<END
  <form method="post" action="$posturl">
  <input type="submit" value="Return to Departments" name="showdept">
END;
}

Remember not to put leading spaces in the END; line. Leading spaces in the END;line cause the rest of the document to be treated as part of the text to be printed.

Save the changes to your application files. Test the changes by entering the following URL in your browser:

On Windows:
```
http://localhost/chap5/anyco.php
```
On Linux:
```
http://localhost/~<username>/chap5/anyco.php
```
The Departments page is displayed:

Description of the illustration chap5_err_handling_001.gif
Click Next to navigate to the last department record, the Accounting department with ID 110. Try to navigate past the last department record by clicking Next.

Description of the illustration chap5_err_handling_002.gif

The error handling prevents navigation past the last department record.
If a new employee is inserted with a salary of 0, or the department ID is changed to one that does not exist, the new error page is shown with the heading "Cannot insert employee".

Further Error Handling

Specific Oracle errors can be handled individually. For example, if a new employee record is created by clicking the Insert new employee button on the Employees page, and the Department ID is changed to a department that does not exist, you can trap this error and display a more meaningful message:

Edit the anyco.php file. Change the error handling in the insert_new_emp() function:

    $r = db_execute_statement($conn, $statement, $err, $bindargs);
    if ($r) {
      construct_employees();
    }
    else {
      if ($err['CODE'] == 2291) {  // Foreign key violated
        handle_error("Department {$newemp['deptid']} does not yet exist",
        $err);
      } 
      else {
        handle_error('Cannot insert employee', $err);
      }
    }

Save the changes to your application files. Test the changes by entering the following URL:

On Windows:
```
http://localhost/chap5/anyco.php
```
On Linux:
```
http://localhost/~<username>/chap5/anyco.php
```
In the Departments page, click Show Employees.

Description of the illustration chap5_err_handling_003.gif
In the Employees page, click Insert new employee.

Description of the illustration chap5_err_handling_004.gif
In the Insert New Employee page, enter employee details as shown, setting the Department ID to 99, and click Save.

Description of the illustration chap5_err_handling_005.gif

The following error page is displayed:

Description of the illustration chap5_err_handling_006.gif

You can click Return to Departments to return to the Departments page and then click Show Employees to verify that the new employee record has not been added to the Administration department.