5 Paging Through Employee Data

This chapter creates the main display page of the AnyCo application as shown in Figure 1-1, "Overview of the Sample Application". It will show five employee records at a time and allow you to page through the list of employees.

This chapter contains the following topics:

Creating the Employee Listing
Running the Employee List

Creating the Employee Listing

Create a new PHP file ac_emp_list.php initially containing:

<?php
 
/**
 * ac_emp_list.php: list of employees
 * @package Employee
 */
 
define('NUMRECORDSPERPAGE', 5);
 
session_start();
require('ac_db.inc.php');
require('ac_equip.inc.php');
 
$sess = new \Equipment\Session;
$sess->getSession();
if (!isset($sess->username) || empty($sess->username)) {
    header('Location: index.php');
    exit;
}
 
$page = new \Equipment\Page;
$page->printHeader("AnyCo Corp. Employees List");
$page->printMenu($sess->username, $sess->isPrivilegedUser());
printcontent($sess, calcstartrow($sess));
$page->printFooter();
 
// Functions
 
?>

The NUMRECORDSPERPAGE constant determines how many employee records to show.

After the $sess->getSession() call retrieves the stored session data there is some basic validation to confirm the user is authorized to view this page. Here the only requirement is that the username is set. If it is not set, the browser is redirected to the login page, index.php. Here is where a production application would do more validation, perhaps checking a timestamp and forcing users to re-login after a certain idle period. The user name could have been encrypted, making it harder for a hacker to view session data or to impersonate a session.

The body of the file prints the HTML page header, menu, content and footer of the page.

Th is file will show PHP's traditional procedural style instead of continuing the object oriented approach previously used. Under the Functions comment add the function to print the page content:

/**
 * Print the main body of the page
 *
 * @param Session $sess
 * @param integer $startrow The first row of the table to be printed
 */
function printcontent($sess, $startrow) {
    echo "<div id='content'>";
 
    $db = new \Oracle\Db("Equipment", $sess->username);
    $sql = "SELECT employee_id, first_name || ' ' || last_name AS name,
            phone_number FROM employees ORDER BY employee_id";
    $res = $db->execFetchPage($sql, "Equipment Query", $startrow,
           NUMRECORDSPERPAGE);
    if ($res) {
        printrecords($sess, ($startrow === 1), $res);
    } else {
        printnorecords();
    }
 
    echo "</div>";  // content
    // Save the session, including the current data row number
    $sess->empstartrow = $startrow;
    $sess->setSession();
}

This runs a query on the EMPLOYEES table. The Db::execFetchPage() method is similar to Db::execFetchAll() and will be shown in a moment. If there are records to display then printrecords() will show them, else printnorecords() will display a message that there was nothing to show. The final stage of printing the content is to update the session with the new starting row number.

The call to printcontent() at the top level uses calcstartrow() to decide which row number to start at. Add this function to ac_emp_list.php:

/**
 * Return the row number of the first record to display.
 *
 * The calculation is based on the current position
 * and whether the Next or Previous buttons were clicked
 *
 * @param Session $sess
 * @return integer The row number that the page should start at
 */
function calcstartrow($sess) {
    if (empty($sess->empstartrow)) {
        $startrow = 1;
    } else {
        $startrow = $sess->empstartrow;
        if (isset($_POST['prevemps'])) {
            $startrow -= NUMRECORDSPERPAGE;
            if ($startrow < 1) {
                $startrow = 1;
            }
        } else if (isset($_POST['nextemps'])) {
            $startrow += NUMRECORDSPERPAGE;
        }
    }
    return($startrow);
}

The rows will be displayed with a form having Next and Previous buttons. The calculation for the starting row depends on which button was clicked and whereabouts in the data set the user has got to.

Add printrecords() to ac_emp_list.php to show any fetched records:

/**
 * Print the Employee records
 *
 * @param Session $sess
 * @param boolean $atfirstrow True if the first array entry is the first table row
 * @param array $res Array of rows to print
 */
function printrecords($sess, $atfirstrow, $res) {
    echo <<< EOF
        <table border='1'>
        <tr><th>Name</th><th>Phone Number</th><th>Equipment</th></tr>
EOF;
    foreach ($res as $row) {
        $name = htmlspecialchars($row['NAME'], ENT_NOQUOTES, 'UTF-8');
        $pn   = htmlspecialchars($row['PHONE_NUMBER'], ENT_NOQUOTES, 'UTF-8');
        $eid  = (int)$row['EMPLOYEE_ID'];
        echo "<tr><td>$name</td>";
        echo "<td>$pn</td>";
        echo "<td><a href='ac_show_equip.php?empid=$eid'>Show</a> ";
        if ($sess->isPrivilegedUser()) {
            echo "<a href='ac_add_one.php?empid=$eid'>Add One</a>";
            echo "<a href='ac_add_multi.php?empid=$eid'> Add Multiple</a>\n";
        }
        echo "</td></tr>\n";
    }
    echo "</table>";
    printnextprev($atfirstrow, count($res));
}

This function's logic is similar to that shown in test_db.php. Remember the 'EOF;' token must be at the start of the line and not have any trailing white space.

Privileged users see extra links to issue pieces of equipment to each employee. At the end of the HTML table any Next and Previous buttons are shown by calling printnextprev().

Add printnextprev() to ac_emp_list.php:

/**
 * Print Next/Previous buttons as needed to page through the records
 *
 * @param boolean $atfirstrow True if the first array entry is the first table row
 * @param integer $numrows Number of rows the current query retrieved
 */
function printnextprev($atfirstrow, $numrows) {
    if (!$atfirstrow || $numrows == NUMRECORDSPERPAGE) {
        echo "<form method='post' action='ac_emp_list.php'><div>";
        if (!$atfirstrow)
            echo "<input type='submit' value='< Previous' name='prevemps'>";
        if ($numrows == NUMRECORDSPERPAGE)
            echo "<input type='submit' value='Next >' name='nextemps'>";
        echo "</div></form>\n";
    }
}

The printnextprev() logic handles the boundary cases including

not displaying a Previous button on the first page
not showing a Next button when a full page wasn't displayed.

Finally, add printnorecords() to ac_emp_list.php to display a message when there are no records to show:

/**
 * Print a message that there are no records
 *
 * This can be because the table is empty or the final page of results
 * returned no more records
 */
function printnorecords() {
    if (!isset($_POST['nextemps'])) {
        echo "<p>No Records Found</p>";
    } else {
        echo <<<EOF
            <p>No More Records</p>
            <form method='post' action='ac_emp_list.php'>
            <input type='submit' value='< Previous' name='prevemps'></form>
EOF;
    }
}

Note:

The EOF; token must be at the start of a line and not have trailing white space.

There are two cases here, one where the table has no rows, and the other when the user is paging through the table and clicking Next gives no more data to display. This latter case will occur when the number of rows in the table is a multiple of NUMRECORDSPERPAGE.

Beforethe application can run, the Db::execFetchPage() method must be created. In the file ac_db.inc.php add a new method to the Db class:

    /**
     * Run a query and return a subset of records.  Used for paging through
     * a resultset.
     *
     * The query is used as an embedded subquery.  Do not permit user
     * generated content in $sql because of the SQL Injection security issue
     *
     * @param string $sql The query to run
     * @param string $action Action text for End-to-End Application Tracing
     * @param integer $firstrow The first row number of the data set to return
     * @param integer $numrows The number of rows to return
     * @param array $bindvars Binds. An array of (bv_name, php_variable, length)
     * @return array Returns an array of rows
     */
    public function execFetchPage($sql, $action, $firstrow = 1, $numrows = 1,
    $bindvars = array()) {
        //
        $query = 'SELECT *
            FROM (SELECT a.*, ROWNUM AS rnum
                  FROM (' . $sql . ') a
                  WHERE ROWNUM <= :sq_last)
            WHERE :sq_first <= RNUM';
 
        // Set up bind variables.
        array_push($bindvars, array(':sq_first', $firstrow, -1));
        array_push($bindvars, array(':sq_last', $firstrow + $numrows - 1, -1));
        $res = $this->execFetchAll($query, $action, $bindvars);
        return($res);
    }

Oracle Database does not have a LIMIT clause to return a subset of rows so nesting the caller's query is needed. PHP's array_push() function appends the extra bind variables used for the start and end row numbers in the outer query to any bind variables for the caller's query.

Because the SQL text is concatenated watch out for SQL injection issues. Never pass user input into this function.

Running the Employee List

Save all the files and run the application. Login first as Simon. You will see:

Click Logout. Re-login as Administrator. You will see:

Use the Next and Previous buttons to page through the data.

Try changing NUMRECORDSPERPAGE to see the effect on paging.