Edit Employee Page

Now that HR can insert new employee records and view them, we can now turn your attention on how to update (edit) an existing employee record.  A recordset will be created and used to per-populate a form to "see" existing fields and "edit" ones that needs updating so that they all can be reinserted back to the database. The Edit Employee page is a little more involved than the other pages because you have several processes to handle at one time.

It is helpful to think of an EDIT EMPLOYEE page as a "two-way" street. One way is when a recordset is created to "read" a SINGLE RECORD FROM a database via a SQL SELECT query. The other way is to "write" a SINGLE RECORD TO a database via a SQL UPDATE query. A recordset is created and its dynamic text fields will be created for the text fields in the form so that the existing fields can be per-populated with a record from the database based on an ID value passed from the EMPLOYEE MASTER PAGE (index.php).

Since the primary key value is typically not displayed on the page, its value is "captured" via a hidden field that needs to be created so that the correct ID can be used to help per-populated the fields.

An EDIT link on the EMPLOYEE MASTER PAGE (index.php) that was created earlier will be used to pass data to this edit page to build a new recordset and populate the form. A Search Form will also be created on the search page (search_employee.php) that will be used as well to pass data to this page.

The code that will be created will perform the following tasks:

PART A: CREATE STATIC CONTENT

Static form and form elements will be used from the Add Employee page that was created eariler.

Create Edit Employee Page

  1. Open the add_employee.php and save it as edit_employee.php in the site root directory.
    WHY: The EDIT EMPLOYEE page is similar to the ADD EMPLOYEE page, so it will be used to get a head start.
  2. Update the heading to EDIT EMPLOYEE.
  3. Update the button label to EDIT EMPLOYEE
  4. Update the form's action attribute to edit_employee.php (e.g., <form action="edit_employee.php" method="post">).

PART B: ADD DYNAMIC SCRIPTS

Add SQL SELECT Recordset Script

  1. Add a recordset in a PHP code block below the <h2> tag:
    WHY: To be used later to per-populate the form elements.

     
    <h2 class="ui-body ui-body-a ui-corner-all">EDIT EMPLOYEE</h2>
<!-- SQL SELECT QUERY -->
    <?php
    // Store reference of SQL statement in a variable
    $query_selectEmployee="SELECT * FROM employees WHERE EmployeeID=3"; 
    $result_selectEmployee=mysql_query($query_selectEmployee);
    $row_selectEmployee=mysql_fetch_assoc($result_selectEmployee);
    ?>

    - Only the columns needed could have been retrieved from the database. However, to simplify the query the wildcard asterisk ( * ) was used to select all columns. Also, notice that a temporary EmployeeID has been set (e.g., EmployeeID = 3).

  2. Save the file.

Add Dynamic Variables Script

  1. Add the following dynamic variables to each input fields value attribute AND to the inside of the textarea tag:
    WHY: To prepopulate form elements with data from the database.

    <form action="edit_employee.php" method="post">
<div class="ui-body ui-body-a ui-corner-all">
<p><label for="FirstName">First Name:</label>
<input name="FirstName" type="text" autofocus id="FirstName" 
value="<?php print $row_selectEmployee['FirstName']; ?>" data-mini="true"></p>
<p><label for="LastName">Last Name:</label>
<input name="LastName" type="text" id="LastName" 
value="<?php print $row_selectEmployee['LastName']; ?>" data-mini="true"></p>
<p><label for="City">City:</label>
<input name="City" type="text" id="City" 
value="<?php print $row_selectEmployee['City']; ?>" data-mini="true"></p>
<p><label for="State">State:</label>
<input name="State" type="text" id="State" 
value="<?php print $row_selectEmployee['State']; ?>" data-mini="true"></p>
<p><label for="StartDate">Start Date:</label>
<input name="StartDate" type="date" id="StartDate" 
value="<?php print $row_selectEmployee['StartDate']; ?>" data-mini="true"></p>
<p><label for="Notes">Notes:</label>
<textarea cols="40" rows="8" name="Notes" id="Notes" data-mini="true" class="define-textarea">
<?php print $row_selectEmployee['Note']; ?></textarea></p>  
<p><label for="Department">Department:</label> 
<select name="Department" id="Department" data-mini="true" data-inline="true">
      <option>Human Resource</option> 
      <option>IT</option> 
      <option>Engineering</option> 
      <option>Marketing</option> 
      <option>Manufacturing</option>
      <option>Sales</option>
    </select></p>
<p><label for="Image">Image:(e.g., john_doe.jpg)</label>
<input type="text" name="Image" name="Image" size="20" 
value="<?php print $row_selectEmployee['Image']; ?>"></p>
<p><input type="submit" name="edit_button" value="EDIT EMPLOYEE" data-inline="true" 
data-mini="true" data-icon="plus"   data-iconpos="left"/></p>
<input type="hidden" name="submitted" value="true" /><input type="hidden" name="EmployeeID" value="<?php print $row_selectEmployee['EmployeeID']; ?>"/>
</div>
</form>
    If you look at the page in Design View in Dreamweaver, you will see the PHP script literally shown INSIDE of the input form elements:Notice you will NOT see a dynamic variable displayed in the screenshot below for the NOTE textarea because it does not have a value attribute like the text input fields.


    If you look at the page in Live View using Dreamweaver, you will see the the form PRE-POPULATED from data from the database based on a recordID value:


  2. CHECKPOINT: Save the file and preview the page in a browser. You should see the record returned that has a EmployeeID value of 3 from the database. However, we need to make the value dynamic instead of static (e.g., 3). Later, we will test this page  from the Employee Master Page that will pass the value to this page via a URL query string so that the correct record from the database is displayed based on the value passed.
  3. Replace the literal numeric value 3 with a dynamic reference to the value that will be passed to it either from the browser URL or from the Employee Master Page.
    WHY: The $_GET[ ... ] represents a URL variable that will be used later. Conversely, $_POST [ ... ] represents a FORM variable.

    // Store a reference of SQL Statement in a variable
    $query_selectEmployee = "SELECT * FROM employees WHERE employeeID=$_GET[recordID]";

  4. CHECK POINT: Save the file and test it in a browser again by opening the Employee Master Page ( index.php) and clicking on any EDIT icon.  Repeat several times with different links. You should see that the fields get updated for each link tested.

Add SQL UPDATE Recordset Script

 The first SQL SELECT query was used to per-populate form elements. Now, we need to create a SQL UPDATE query to update any changes to the database.

  1. Replace the INSERT INTO record query so that it will reflect updating instead of adding a record to the database.

    From this:
    // SQL INSERT QUERY ---------------------------------------------------------
// Store a reference of SQL statement in a variable
    $query_insertEmployee="INSERT INTO employees(FirstName, LastName, City, State, 
StartDate, Note, Department, Image, DateEntered)
VALUES('$FirstName','$LastName','$City','$State','$StartDate','$Note','$Department','$Image', NOW())"; 
// Execute query
    mysql_query($query_insertEmployee);
// Close connection to database
mysql_close();
    To this:
    // SQL UPDATE QUERY -----------------------------------------------------
// Store a reference of SQL statement in a variable
    $query_updateEmployee="UPDATE employees SET FirstName='$FirstName', LastName='$LastName', 
City='$City', State='$State', StartDate='$StartDate', Note='$Note', 
Department='$Department', Image='$Image' WHERE EmployeeID={$_POST['EmployeeID']}";
    // $result_updateEmployee=mysql_query($query_updateEmployee);
    // Execute query
    mysql_query($query_updateEmployee);
// Close connection to database
    mysql_close();

  2. Add another hidden form field below the other hidden field at the bottom of the <form> tag:
    WHY: To "hold" the value of the employeeID that will be used to send the correct EmployeeID to the database.

    <input type="hidden" name="submitted" value="true" />
<input type="hidden" name="EmployeeID" value="<?php print $row_selectEmployee['EmployeeID']; ?>"/>
</div>
    </form>

  3. CHECKPOINT: Save the file and then preview the EMPLOYEE MASTER Page (index.php) in a browser:
    1. Click on any of the EDIT icon link to take you to the EDIT EMPLOYEE page (edit_employee.php).
    2. Once on the EDIT EMPLOYEE page, make changes to any of the fields.
    3. Click on the EDIT EMPLOYEE button.

      You should be immediately returned to the Employee Master Page (index.php) and you should see that the data for that employee got updated.

      - If a user modify none, any or all fields, ALL fields will be updated in the database regardless. Even if nothing were changed the same information would be written to the database and as result the record would remain the same.

PART 3: REPLACE STATIC CONTENT WITH DYNAMIC CONTENT

If you were to click on the EDIT icon link on the Master Page for several of the employees, you will see the the menu on the Edit Employee Page (e.g., edit_employee.php) ALWAYS reflects the first record in the department database table. To SYNC the recordset to the correct value from the employees table requires some additional coding.

  1. Write (or copy) the following highlighted PHP script code block above the opening <form> tag. Comments are optional.
    WHY: To create a recordset to retrieve the department names from a table called departments so that it can be used later to populate the combo box with the correct department name dynamically.

    <!-- SQL SELECT QUERY FOR COMBO BOX ---------------------------------------------------->
<?php
    // Store a reference of SQL statement in a variable
    $query_departmentNames="SELECT DepartmentID, Dept FROM departments";
    // Store a reference of the query in this variable from the SQL variable using the mysql_query method.
    $result_departmentNames=mysql_query($query_departmentNames);
    // Store the result of the query into an associative array using the mysql_fetcth_assoc method. 
    $row_departmentNames=mysql_fetch_assoc($result_departmentNames);
    ?>    
<form>

  2. Save file.

Make Department Combo Box Dynamic

  1. Replace ALL of the <option> form tags between the <select> tag with a do...while loop with a built-in conditional statement.

    From this:
        <option>Human Resource</option> 
        <option>IT</option> 
        <option>Engineering</option> 
        <option>Marketing</option> 
        <option>Manufacturing</option>
        <option>Sales</option>
    To this: 
    <p><label for="Department">Department:</label> 
    <select name="Department" id="Department" data-mini="true" data-inline="true"> <?php 
    do {
    if ($row_departmentNames['Dept'] == $row_selectEmployee['Department']) 
    {
    print "<option selected>{$row_departmentNames['Dept']}</option>";
    }
    else
    {
    print "<option>{$row_departmentNames['Dept']}</option>";
    }
       } 
    while ($row_departmentNames = mysql_fetch_assoc($result_departmentNames));
    ?>    
</select></p>

  2. CHECKPOINT: Save the file and retest it again in a browser. If you were to click on the UPDATE link on the Employee Master Page for several of the employees, you should see the the menu on the update_employee.php page reflects the correct record in the employee database table not the directory database table. Also, if you view source for this page, you will see the selected attribute added for the correct record.