Administrator Recipe: New Employee | Macromedia Dreamweaver 8 Recipes

The goal of the final page in the Employee Lookup application is to add an employee's information to the data source. But isn't that just a basic insert record operation? Although our implementation does use Dreamweaver's standard Insert Record server behavior, we've also added a bit of intelligence that ties this page into the rest of the application. As we saw in the New Job page, you can't hire an employee unless there is at least one job opening. If you try to add an employee to the data source and no job opening is available, this page redirects you to the New Job page and displays an error message.

Step 1: Implement Design

Building a static page with all the necessary form elements is the first step.

Create a basic dynamic page, either by hand or derived from a template.

In the EmployeeLookup folder, locate the folder for your server model and open the new_employee page found there.

Add a table to the content region of your page to contain the interface elements for the application.

From the Snippets panel, drag the Recipes > EmployeeLookup > Wireframes > New Employee - Wireframe snippet into the Content editable region.

Within the table, insert the form and the necessary form elements for the new employee. If you follow our example for this page, you'll need five text fields: first name, last name, email, direct line, cell phone, and cubicle number. You'll also need a list element for the job listings as well as a submit button.

Place your cursor in the row below the words NEW EMPLOYEE and insert the Recipes > EmployeeLookup > Forms > New Employee - Form snippet [r2-16].

r2-16.

Step 2: Add Database Components

A single recordset is needed for this page to populate the JobTitle list element. To develop the list of jobs, we'll use an Access view and a nested SQL statement. The SQL statement reads as follows:

SELECT * FROM JobsWithDepartments WHERE JobID NOT IN (SELECT EmployeeJob AS JobID FROM Employees)

Essentially, with this SQL statement, the recordset displays all the JobID records (that is, all the jobs) that are not assigned to an employee.

Note

The JobsWithDepartment view was previously used on the Update Employee page and is discussed in that section.

Before you begin this step, you'll need to place your cursor in any text in Design view and then copy the SQL code from the appropriate snippet by right-clicking the Recipes > EmployeeLookup > SQL > New Employee Jobs RS (New Employee Jobs RS - PHP SQL Statement for PHP) snippet for your server model and then, from the context menu, choosing Copy Snippet.

From the Bindings panel, choose Add (+) and select Recordset.

In the advanced Recordset view, enter a meaningful name for the query.

Enter Jobs in the Name field.

Select your data source connection.

Choose Recipes from the Connection (or Data Source) list.

ColdFusion users: Enter the username and password for the data source, if necessary.

In the SQL area, enter the following code:

Paste the copied snippet into the SQL field by pressing Ctrl-V (Command-V):

[View full width]
SELECT * FROM JobsWithDepartments WHERE JobID NOT IN (SELECT EmployeeJob AS JobID  FROM Employees)

[View full width]
SELECT jobswithdepartments.* FROM  jobswithdepartments LEFT JOIN employees ON  jobswithdepartments.JobID= employees.EmployeeJob WHERE employees.EmployeeJob IS NULL

Verify your code and close the dialog to insert the recordset.

Save your page.

For PHP Users

Because views are not supported in PHP, an additional step is required for that application server. In this step, you'll create a temporary table that joins data from the jobs table with data from the departments table. This table is placed in front of the Jobs recordset to simulate the view. The temporary table is then removed after the code for the Jobs recordset. Because this new code appears before and after a single code block (the Jobs recordset), it can be contained in a wrap type of snippet.

In the Server Behaviors panel, select the Jobs Recordset entry.

Switch to Code view to find the selected code.

Insert the following code:

From the Snippets panel, insert the Recipes > EmployeeLookup > CustomCode_PHP > Temporary Query - JobsWithDepartments snippet.

Before:

[View full width]
<?php  mysql_select_db($database_Recipes, $Recipes);  $query_JobsWithDepartments = "INSERT INTO  jobswithdepartments SELECT CONCAT(jobs.JobTitle,'  (',departments.DepartmentName,')') AS  JobAndDepartment, jobs.JobID FROM jobs INNER JOIN  departments ON jobs.JobDepartment = departments .DepartmentID;";  mysql_query($query_JobsWithDepartments,$Recipes);  ?>

After:

[View full width]
<?php  $query_JobsWithDepartments = "DELETE FROM  jobswithdepartments";  mysql_query($query_JobsWithDepartments,$Recipes);  ?>

Save your page.

Step 3: Data Binding Process

On this page, only one form elementthe listneeds to be populated dynamically.

1.	Select the JobTitle list element.
2.	From the Property inspector, select Dynamic to open the Dynamic List/Menu dialog.
3.	In the Options From Recordset list, choose Jobs.
4.	In the Values field, select JobID.
5.	In the Labels field, choose JobAndDepartment.
6.	Leave the Select Value Equal To field blank and click OK to close the dialog.

By previewing the page in a browser, you can check to see what job titles are currently available [r2-17]. You won't be able to use Live Data Preview, because list elements are not populated within Dreamweaver in Preview mode.

r2-17.

Step 4: Insert RecordNew Employee

Now it's time to apply the Insert Record server behavior.

Note

As mentioned before, the Insert Record dialog is different for ASP, PHP, and ColdFusion server models and is easiest understood when it is presented separately.

For ASP

From the Server Behaviors panel, choose Add (+) and select Insert Record.

In the Insert Record dialog, select your connection from the list.

Choose Recipes from the Connection list.

From the list, select the table in the data source to modify.

Choose Employees from the Insert Into Table list.

In the After Inserting, Go To field, enter the path to the file you want the user to visit after the record has been updated.

Choose Browse and locate the employee_results.asp file.

Choose the form to use.

Select NewEmployee from the Get Values From list.

With the current form selected in the Get Values From list, set the form elements to their corresponding data source fields.

	Set the FirstName form element to the EmployeeFirst data column and submit as Text.
	Set the LastName form element to the EmployeeLast data column and submit as Text.
	Set the Email form element to the EmployeeEmail data column and submit as Text.
	Set the Phone form element to the EmployeePhone data column and submit as Text.
	Set the Phone2 form element to the EmployeeMobile data column and submit as Text.
	Set the JobTitle form element to the EmployeeJob data column and submit as Numeric.
	Set the CubeNumber form element to the EmployeeCube data column and submit as Numeric.

Verify your choices and click OK to close the dialog.

For ColdFusion and PHP

From the Server Behaviors panel, choose Add (+) and select Insert Record.

In the Insert Record dialog, choose the current form.

Select NewEmployee from the Submit Values From list.

Select your data source from the list.

Choose Recipes from the Data Source list.

Enter your username and password, if needed.

Select the table in the data source to insert into from the list.

Choose Employees (employees for PHP) from the Insert Into Table list.

Set the data source fields to their corresponding form elements.

	Make sure the EmployeeID data column is set to an Unused Primary Key.
	Set EmployeeJob to the FORM.JobTitle form element and submit as Numeric for ColdFusion and Integer for PHP.
	Set EmployeeFirst to the FORM.FirstName form element and submit as Text.
	Set EmployeeLast to the FORM.LastName form element and submit as Text.
	Set EmployeeEmail to the FORM.Email form element and submit as Text.
	Set EmployeePhone to the FORM.Phone form element and submit as Text.
	Set EmployeeMobile to the FORM.Phone2 form element and submit as Text.
	Set EmployeeCube to the FORM.CubeNumber form element and submit as Numeric for ColdFusion and Integer for PHP.

In the After Inserting, Go To field, enter the path to the file you want the user to visit after the record has been inserted.

Choose Browse and locate the employee_results file for your server model.

Verify your choices and click OK to close the dialog.

Step 5: Hand CodeRedirect When No Job Openings

Our final step on this page and the entire application is to insert some custom code to properly react when no jobs are available. Because this redirect relies on the recordset on the page, which returns a list of available jobs, you can place the redirect on the page itself, where you can be sure the recordset code has already been defined. As noted earlier, should no jobs be available, the code redirects the visitor to the New Job page and triggers the error message previously embedded.

In Code view, place your cursor just above the opening <!doctype> tag. Although you could place this code elsewhere for ASP and ColdFusion, PHP requires that the code appear directly after the recordset definition.

Insert the following code:

From the Snippets panel, open the Recipes > EmployeeLookup > CustomCode folder for your server model and insert the No Job Openings - Display Text snippet.

<% if (Jobs.EOF) then  Response.Redirect("new_job.asp?empty=true") end if %>

<% if (Jobs.EOF)  Response.Redirect("new_job.asp?empty=true"); %>

<cfif Jobs.RecordCount EQ 0>  <cflocation url="new_job.cfm?empty=true"> </cfif>

<?php if ($totalRows_Jobs<=0) {  $url = "new_job.php?empty=true";  Header("Location: $url"); } ?>

Save your page.

The only way to test the redirection functionality is to preview the page in a browser when all the available jobs are filled. The recipe data source has a full slate of company employees for you to test your application with.