User's Manual

Extending the Basic Departments Form
Beta Draft Querying Data 4-11
The additional information is obtained by modifying the query to performing a join
operation between the DEPARTMENTS, EMPLOYEES, LOCATIONS, and COUNTRIES
tables.
To extend the department form, perform the following tasks:
1. Edit anyco_ui.inc. Modify the ui_print_departments() 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 '<p>No Department found</p>';
}
else {
echo <<<END
<table>
<tr>
<th>Department<br>ID</th>
<th>Department<br>Name</th>
<th>Number of<br>Employees</th>
<th>Manager<br>Name</th>
<th>Location</th>
</tr>
<tr>
END;
echo '<td>'.htmlentities($dept['DEPARTMENT_ID']).'</td>';
echo '<td>'.htmlentities($dept['DEPARTMENT_NAME']).'</td>';
echo '<td>'.htmlentities($dept['NUMBER_OF_EMPLOYEES']).'</td>';
echo '<td>'.htmlentities($dept['MANAGER_NAME']).'</td>';
echo '<td>'.htmlentities($dept['COUNTRY_NAME']).'</td>';
echo <<<END
</tr>
</table>
<form method="post" action="$posturl">
<input type="submit" value="< Previous" name="prevdept">
<input type="submit" value="Next >" name="nextdept">
</form>
END;
}
}
There is no need to pass a $bindargs parameter to the db_do_query() call
because we are not using 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.
2. Edit anyco.php. 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