Merable Celebrity & Model: Navigating Through Database Records......

Adding navigation through the database records requires several important changes to the application logic. The modifications require the combination of:

Including an HTML form to provide Next and Previous navigation buttons to step through database records.
Detecting if the HTTP request for the page was posted by clicking the Next or Previous button.
Tracking the last row queried by using the HTTP session state. A PHP session is started to maintain state information for a specific client between HTTP requests. The first HTTP request will retrieve the first data row and initialize the session state. A subsequent request, initiated with navigation buttons, combined with the session state from a previous HTTP request, enables the application to set variables that control the next record retrieved by the query.
Writing a query that returns a subset of rows based on a set of conditions whose values are determined by the application state.

To add navigation through database rows, perform the following steps:

Edit the anyco_ui.inc file. Add Next and Previous navigation buttons to the Departments page. Change the ui_print_department() function to append a second parameter called $posturl that supplies the value for the form attribute action. After printing the tag include HTML form tags for the Next and Previous buttons:
```
...

function ui_print_department($dept, $posturl)

{

 ...

   echo << 

 

  

  

  "     name="nextdept">

  

END;

 }

}



?>
```
Edit the anyco.php file. To detect if the Next or Previous button was used to invoke the page and track the session state, call the PHP function session_start(), and create a function named construct_departments():

Move and modify the database access logic into a new construct_departments() function, which detects if navigation has been performed, manages the session state, defines a subquery for the database access layer to process, and connects and calls a function db_get_page_data(). The file becomes:
```
require('anyco_cn.inc');

require('anyco_db.inc');

require('anyco_ui.inc');



session_start();

construct_departments();



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 department_id, department_name, 

                   manager_id, location_id

            FROM   departments

            ORDER BY department_id asc';



  $conn = db_connect();



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

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



  $_SESSION['currentdept'] = $current;



  ui_print_header('Department');

  ui_print_department($dept[0], $_SERVER['SCRIPT_NAME']);

  ui_print_footer(date('Y-m-d H:i:s'));

}



?>
```
The if and elseif construct at the start of the construct_departments() function is used to detect if a navigation button was used with an HTTP post request to process the page, and tracks if the currentdept number is set in the session state. Depending on the circumstances, the variable $current is decremented by one when the previous button is clicked, $current is incremented by one when the Next button is clicked, otherwise $current is set to the current department, or initialized to one for the first time through.

A query is formed to obtain all the department rows in ascending sequence of the department_id. The ORDER BY clause is an essential part of the navigation logic. The query is used as a subquery inside the db_get_page_data() function to obtain a page of a number of rows, where the number of rows per page is specified as the fourth argument to the db_get_page_data() function. After connecting to the database, db_get_page_data() is called to retrieve the set of rows obtained for the specified query. The db_get_page_data() function is provided with the connection resource, the query string, a value in $current specifying the first row in the next page of data rows required, and the number of rows per page (in this case one row per page).

After db_get_page_data()has been called to obtain a page of rows, the value of $current is stored in the application session state.

Between printing the page header and footer, the ui_print_department() function is called to display the recently fetched department row. The ui_print_department() function uses $_SERVER['SCRIPT_NAME'] to supply the current PHP script name for the $posturl parameter. This sets the action attribute in the HTML form, so that each Next or Previous button click calls the anyco.php file.
Edit the anyco_db.inc file. Implement the db_get_page_data() function to query a subset of rows:
```
// Return subset of records

function db_get_page_data($conn, $q1, $current = 1,

                 $rowsperpage = 1, $bindvars = array())

{

  // This query wraps the supplied query, and is used

  // to retrieve a subset of rows from $q1

  $query = 'SELECT *

            FROM (SELECT A.*, ROWNUM AS RNUM

                  FROM ('.$q1.') A

                  WHERE ROWNUM <= :LAST)

            WHERE :FIRST <= RNUM';



  // Set up bind variables.

  array_push($bindvars, array('FIRST', $current, -1));

  array_push($bindvars,

             array('LAST', $current+$rowsperpage-1, -1));



  $r = db_do_query($conn, $query, $bindvars);

  return($r);

}
```
The structure of the query in the db_get_page_data() function enables navigation through a set (or page) of database rows.

The query supplied in $q1 is nested as a subquery inside the following subquery:
```
SELECT A.*, ROWNUM AS RNUM FROM $q1 WHERE ROWNUM <= :LAST
```
Remember that the query supplied in $q1 retrieves an ordered set of rows, which is filtered by its enclosing query to return all the rows from the first row to the next page size ($rowsperpage) of rows. This is possible because the Oracle ROWNUM function (or pseudocolumn) returns an integer number starting at 1 for each row returned by the query in $q1.

The set of rows, returned by the subquery enclosing query $q1, is filtered a second time by the condition in the following outermost query:
```
WHERE :FIRST <= RNUM
```
This condition ensures that rows prior to the value in :FIRST (the value in $current) are excluded from the final set of rows. The query enables navigation through a set rows where the first row is determined by the $current value and the page size is determined by the $rowsperpage value.

The $current value is associated with the bind variable called :FIRST. The expression $current+$rowsperpage-1 sets the value associated with the :LAST bind variable.
To test the changes made to your application, save the changed files, and enter the following URL in your Web browser:

On Windows:
```
http://localhost/chap4/anyco.php
```
On Linux:
```
http://localhost/~/chap4/anyco.php
```
When you request the anyco.php page, the first DEPARTMENT table record, the Administration department, is displayed:

Description of the illustration chap4_db_nagivation_001.gif
To navigate to the next department record (Marketing), click Next:

Description of the illustration chap4_db_nagivation_002.gif
To navigate back to the first department record (Administration), click Previous:

Description of the illustration chap4_db_nagivation_003.gif

You may continue to test and experiment with the application by clicking Next and Previous to navigate to other records in the DEPARTMENTS table, as desired.

Note:

If you navigate past the last record in the DEPARTMENTS table, an error will occur. Error handling is added in Adding Error Recovery in Chapter 5.

Extending the Basic Departments Page

The Departments page is extended to include the following additional information:

The name of the manager of the department
The number of employees assigned to the department
The country name identifying the location of the department

The additional information is obtained by modifying the query to perform a join operation between the DEPARTMENTS, EMPLOYEES, LOCATIONS, and COUNTRIES tables.

To extend the Departments page, perform the following tasks:

Edit the anyco_ui.inc file. Modify the ui_print_department() function by replacing the Manager ID and Location ID references with the Manager Name and Location, respectively, and insert a Number of Employees field after Department Name. Make the necessary changes in the table header and data fields. The function becomes:

function ui_print_department($dept, $posturl)

{

 if (!$dept) {

   echo 'No Department found';

 }

 else {

   echo << 



    
    
    


END;

 echo '';

 echo '';

  echo '';
  echo '';
  echo '';
 echo << 

 
   Department
ID
   Department
Name
Number of
Employees
Manager
Name
Location
 
 
'.htmlentities($dept['DEPARTMENT_ID']).' '.htmlentities($dept['DEPARTMENT_NAME']).' '.htmlentities($dept['NUMBER_OF_EMPLOYEES']).'
'.htmlentities($dept['MANAGER_NAME']).'
'.htmlentities($dept['COUNTRY_NAME']).'

 

 

 

 "     name="nextdept">

 

END;

 }

}

Department ID	Department Name	Number of Employees	Manager Name	Location
'.htmlentities($dept['DEPARTMENT_ID']).'	'.htmlentities($dept['DEPARTMENT_NAME']).'	'.htmlentities($dept['NUMBER_OF_EMPLOYEES']).'	'.htmlentities($dept['MANAGER_NAME']).'	'.htmlentities($dept['COUNTRY_NAME']).'

There is no need to pass a $bindargs parameter to the db_do_query() call because this function 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 query string in construct_departments() with:

$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";

The query string is enclosed in double quotation marks to simplify writing this statement, which contains SQL literal strings in single quotation marks.

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

On Windows:
```
http://localhost/chap4/anyco.php
```
On Linux:
```
http://localhost/~/chap4/anyco.php
```
The Web page result should resemble the following output:

Description of the illustration chap4_enhance_dept_001.gif

Merable Celebrity & Model

Saturday, December 4, 2010

Navigating Through Database Records......

Extending the Basic Departments Page

No comments:

Post a Comment