HOW TO CREATE A PHP CONTENT MANAGEMENT SYSTEM (CMS) (PART 3) - HANDCODE

IMPORTANT NOTE: This is a continuation of the HOW TO CONNECT TO A DATABASE (PART 2). It is important also to note that we will be hand-coding the PHP code and not the HTML code-- we will let Dreamweaver do that for us.

A Content Management System (CMS) is used to allow you to view, edit, update or delete records from a database.  To make pages more secure a user authentication system can also be implemented.

Overriding Principles:

While these overriding (taking precedence over all other considerations) principles may not be obvious now, as you progress through this tutorial they will become more apparent. While you are going through this tutorial refer back to them and when you have completed the tutorial review them again.

To address this concern, there are sections of this tutorial entitled "For Geeks Only or Those Inspiring to be Geeks" to show more of a code-centric view of what going on "behind the scene."

Creating An Employee Directory Management System Pages

The Employee Directory table will be created to store information about each employee. If necessary, additional columns can be created later.

The following pages will allow HR to:

  1. ADD employee – create a new record for a new employee that has been hired
  2. VIEW employees – view existing employees records
  3. EDIT employee – update a record for an existing employee when changes are needed
  4. DELETE employee – delete a record of existing employee if he or she leaves
  5. SEARCH for employees – search for records of existing employees

The following authenication pages will also be created:

  1. Login/LogOut – allow users to login to web site / web app
  2. Registration – allow users to register themselves

To recreate a Content Management System (CMS) you typical create a series of template pages that includes recordset(s), business logic code with SQL statements, etc. Click  this tab below is you want to see a summary of what is typically needed when creating these templates.

Template Information (Click to open tab and click again to close tab)
Template Number of  recordsets on page Number of record(s) returned SQL Statement SQL Filter
add_employee.php N/A* N/A* INSERT N/A*
index.php 1 Typically Many SELECT  
detail_page.php 1 1 SELECT WHERE
edit_employee.php 1 1 SELECT AND UPDATE WHERE
delete_employee.php N/A N/A DELETE WHERE
search_employee.php N/A N/A SELECT WHERE
* Not applicable unless you want to dynamically populate form elements

INSTRUCTION: Click on each of the tabs below for instructions on how to create the corresponding templates.  Templates should be created in the order of the tabs listed below:

  • Master
  • 1. Add
  • 2. Master/Detail
  • 3. Edit
  • 4. Delete
  • 5. Search
  • 6. Login/LogOut
  • 7. Registration
  • 8. Enhancements

CREATING TEMPLATES TO BE USED FOR ALL PAGES

While optional, it is best to use a template to create the "shell" for each page that will be created. You could also use server-side includes (SSI) but they are beyond the scope of this tutorial. Before starting, we will create the necessary template pages:

 Create Template To Be Used By All Pages

 

 

 

 

 

Starting from the top to the bottom and then to the side, make the following changes:

  1. Select File > New... from the menu and then select:
    1. Blank Template from the category
    2. PHP template from the Page Type
    3. two column fixed, left sidebar, header and footer... from the Layout
  2. Click the Create button at the button of the New Document dialog box.
  3. Give the page a title of Employee Directory:   in the Title's text field at the top of the page making sure to add a space after the colon (:).
    • This will come in handy later when you create pages from this template.
  4. Select the image placeholder in the document's header and then select <a> in Tag Chooser and then press the Delete key to delete it and then type "Employee Directory."
  5. Select the phrase "Employee Directory" and then choose Heading 1 from the Format's drop-down box in the Properties panel (typically at the bottom of the screen) or press CTRL+1.
    • You could replace this text with an image if you want.
  6. Delete all of the content in the main content area except for the word Instructions at the top.
    • TIP: Click on the elements in Design view and press the Delete key.
  7. Delete the content from the footer and replace it with a copyright notice in the document's footer (i.e., Copyright 2014 by Cornelius Chopin. All rights reserved.)
  8. Delete all of the text in the sidebar area except the links.
  9. Change the following links in the sidebar and add the following urls in the Properties panel:
    1. HOME - index.php (This will be used for the Master Page)
    2. ADD EMPLOYEE - add_employee.php
    3. SEARCH FOR EMPLOYEE - search_employee.php
  10. In the Code View, delete the <li> tag for the fourth link in the sidebar area as it is not needed for this tutorial.
  11. Select the content area  by selecting the <div.content> or <article.content> in the Tag Chooser and then choose Insert > Template Objects > Editable Region (CTLR+ALT+V) and give it a name of mainBody in the New Editable Region dialog box.
    • This will add a small tab above the content area that is not shown in the browser. When you create one or more editable regions, only these regions are editable when you create a page from this template.
  12. (OPTIONAL) Click on the div.sidebar 1 in the Tag Chooser and change the float to right so that the menu appear on the right side of the page.
  13. Close the template so that you don't accidentally select it as a file.

 

The template should look similar to this:

 Create Template Page  for index.php

  1. Select File > New... and then choose Page from Template (not New Page) and choose the employee_directory_template and then click the Create button.
  2. Edit the title of page (i.e., Employee Directory: Master Page) in the Title text field.
    • Notice that the phrase Employee Directory: was automatically add from the template. All you need to do is type Master Page.
  3. Select the Heading in the mainContent area (Instructions) and type MASTER PAGE in its place and then press the ENTER key.
  4. Save (File > Save) the page as the index.php.
    • Since this page is the home page, it is named index.php instead of master_page.php.

 Create Template For All Other Pages

While you could create each page as they are needed,  it is best to create all of the pages UPFRONT so FINDWORD you can easily link to them. It is best to create all of the STATIC content first and then add the DYNAMIC content afterward by adding recordsets and dynamic variables, repeat regions, etc.

  1. Repeat steps 1-4 in the Create Template Page section above but change the file name, page title and main body heading to the following:

    File Name Page Title Main Body Heading
    add_employee.php ADD EMPLOYEE PAGE ADD EMPLOYEE
    detail_page.php EMPLOYEE DETAIL PAGE EMPLOYEE DETAIL
    delete_employee.php DELETE EMPLOYEE PAGE * DELETE EMPLOYEE *
    search_employee.php SEARCH FOR EMPLOYEE PAGE SEARCH FOR EMPLOYEE
    admin_login.php ADMINISTRATIVE LOGIN PAGE ADMINISTRATIVE LOGIN
    registration.php EMPLOYEE REGISTRATION PAGE EMPLOYEE REGISTRATION

    * Since the delete page will never be seen by the user, you don't really need to give it a page title or main body for this tutorial.

    Notice that there is not an edit_employee.php page. That's because it is so similar to the add_employee.php page that it will be created from it instead later with the following values.
    edit_employee.php EDIT EMPLOYEE PAGE EDIT EMPLOYEE

  2. Close all files except the add_employee.php that will be used in the next tab.

EDITING THE ADD EMPLOYEE TEMPLATE

Before we can update, search or delete records from a database, you need to add records to it or have a database that already contain records. The Add Employee  template page will be designed to add employees. This page will be redirected automatically to the index.php page once the ADD EMPLOYEE button is clicked.

Although you can use phpMyAdmin (or any other graphical interface app) to insert records into a database table, it is best practice to create a CUSTOM form to insert records which gives you more control over who can access the database and where they can access the database to view, insert, update, or delete records.  Also, anybody with the right privilege can access the database from the web.

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

  1. Create Form, Table and Form elements
  2. Create a recordset for the menu/list form element to populate it with data from database
  3. Add Form variables will be used to add a row in the database when a user fill out the form and submit it
  4. Redirect a user back to the another page (e.g., home page (index.php)) to see updated information

Create Form, Table and Form elements

Create your own table, form and form elements or use the one from Method 1 or 2 and modify it.

  1. Create a form and give it a name (e.g., formAddEmployee).
    • Notice that a form is represented by a red dashed box.
  2. While the insertion point is still in the form between the red dashed box, create a table inside the form with the following settings from the Insert panel Common option and then selecting table:
    • 8 columns and 2 rows
    • width of 300 pixels
    • left header
    • border thickness, cell padding and cell spacing of 1 pixel
  3. Click anywhere inside the table and select the <table> tab from the Tag Chooser and then in the Properties panel select Center from the Align's drop down menu to align the table in the center of the form.
  4. From the Insert panel with the Form option selected, click inside each of the cell in the FIRST column to add 6 text fields, 1 select menu and 1 button:


  5. Replace the generic text with the proper STATIC labels for the text fields, select menu and button. For the button, change its label in the Properties panel Value field. Then, drag and drop each of the text fields, select menu and button to the second column. Select all of the cell in the first column except the last one and then choose Right from the Horz drop down menu in the Properties panel.


  6. Click on each of the form element and give them a name the MATCHES the name of the columns in the database (except for the button) in the Properties panel:
    • Fullname
    • City
    • State
    • StartDate
    • Notes
    • Department
    • image
    • buttonAddEmployee

      NOTE: Notice there is no spaces in Fullname and StartDate. This is because it is best practice not to include spaces in column names in a database.

  7. In Code view, ensure that the button type is set to submit otherwise form will not execute when button is clicked. (i.e., <input type="submit" name="buttonAddEmployee" id="buttonAddEmployee" value="ADD EMPLOYEE">).
  8. Save file.

Create recordset

 

 

 

 

 

 

 

 

 

While the Department drop-down menu could be populated MANUALLY by clicking on the List Values... button in the Properties panel and creating name/value pairs for each menu option. It is best practice to DYNICALLY populate a menu if it will be available for multiple pages. This way, if there are any changes (additional or deletions) to the menu, it can be done from one table in the database and all of the pages will reflect the change.

  1. Write the following code to create a recordset to populate the menu/list element. Comments are optional but are written to explain code

    <?php require_once('Connections/myConnections.php'); ?>
    <?php

    // Store reference of SQL statement in a variable
    $query_rs_departmentNames = "SELECT DepartmentID, DeptName FROM departments";
    // Now, store a reference of the query in this variable from the SQL variable using the mysql_query function. This is not a recordset but a resource identifier number which points to the data. The data itself is now yet returned.
    $rs_departmentNames = mysql_query($query_rs_departmentNames);
    // Store the result of the query into an array using the mysql_fetcth_assoc function. The mysql_fetch_assoc retrieves the data and places it in an associative
    $row_rs_departmentNames = mysql_fetch_assoc($rs_departmentNames);
    ?>

    NOTE: PHP code written above <!doctype html> tag gets executed before the page is rendered. While the recordset itself does not need to be displayed on the page, the result of the recordset will be used to populate the menu in the <form> tag of the <body> tag later. The first code line is used to link to the Connection script.

Bind Recordset to Menu/List to Populate Combo Box

 

 

 

 

When binding the result of a recordset to a form element, you are in essence “linking” the form element to a field in the database. In the case of a Menu/List form element that has the syntax of <option value=”data”>Label</option>, you would be replacing the data attribute value and the tag content with a dynamic values. The Label is what a user sees and the data is what is sent to the database.

  1. (Optional) In Code view, insert the following code between the opening and closing <select> tags:

    <?php
    <option value=”data”>Label</option>
    ?>

  2. Now, replace the static text (data and Label) with their dynamic counterpart highlighted in bold.

    <option value="<?php echo $row_rs_departmentNames['DeptName']?>"><?php echo $row_rs_departmentNames['DeptName']?></option>

  3. Save the file and test it by opening it up in a browser or using Live view in Dreamweaver.

    CHECKPOINT: You should see that it returned the FIRST record in the database and populate the Menu/List combo box with it. However, we need to return ALL records in the database. To do so we need to wrap a loop around our current code so that it will return all records.

  4. Wrap the following code highlighted in bold about the current code.
    NOTE: The two blank lines are optional. They are only shown to emphasize the fact that the loop code is "WRAPPED" around current code.

    <?php
    do {
    ?>


    <option value="<?php echo $row_rs_departmentNames['DeptName']?>"><?php echo $row_rs_departmentNames['DeptName']?></option>

    <?php
    } while ($row_rs_departmentNames = mysql_fetch_assoc($rs_departmentNames));
    ?>


    CHECKPOINT: You should see that ALL the records are populated in the combo box.
    NOTE: The PHP do…while loop is used to loop until all records are returned from the database table.

Remove recordset

At the TOP of the file you created a recordset and placed it in memory, in the MIDDLE of the file you displayed the result of the recordset, and at the END of the file you need to close the recordset to release it from memory. Otherwise, the recordset may linger around when it is no longer need. The code will be added at the bottom of the page so that the rest of the page could be processed BEFORE the recordset is cleared; otherwise, the recordset would not have a chance of populating the form element

  1. In Code view, after the closing </html> tag add the following code to remove the recordset from memory:

    </html>
    <?php
    mysql_free_result($rs_departmentNames);
    ?>


  2. Save file.

Add Insert Record Code

Now that we have the form, table, and form elements created and the menu/list (combo box/drop-down box) working, it is time to add the code that will actually ADD the data from the form to a single row in the database.

The following code performs the five actions listed below. Comments are optional but are used to explain code. Blank lines are also optional but are used to emphasize specific code blocks.

  • Check to see if form has been submitted
  • Select database that has already been connected to
  • Set variables for each form elements except submit button or hidden fields.
  • Store reference of SQL Statement in a variable
  • Execute query
  1. Write the following code block below the existing code in the header but before the <!doctype html> tag.
    • This is a conditional statement for the rest of the code that will check to see if the form has been submitted.

      <?php
      //Check to see if form has been submitted
      if(isset($_POST['submitted']))
      {


      }
      ?>


  2. Before the last curly brace, add the following code to set variables for each database bound form elements.
    • The trim () function is used for input fields to trim spaces from users inputs. Hence, the $Department variable doesn't not need a trim() function because it will get its values from the database.

      <?php
      //Check to see if form has been submitted
      if(isset($_POST['submitted']))
      {
      //Set variables for each database bound form elements.

      $Fullname = trim($_POST['Fullname']);
      $City = trim($_POST['City']);
      $State = trim($_POST['State']);
      $StartDate = trim($_POST['StartDate']);
      $Notes = trim($_POST['Notes']);
      $image = trim($_POST['image']);
      $Department = $_POST['Department'];

      }
      ?>

  3. Write the following code before the closing curly brace to store a reference of the SQL statement as a variable.

    <?php
    //Check to see if form has been submitted
    if(isset($_POST['submitted']))
    {
    //Set variables for each database bound form elements.
    $Fullname = trim($_POST['Fullname']);
    $City = trim($_POST['City']);
    $State = trim($_POST['State']);
    $StartDate = trim($_POST['StartDate']);
    $Notes = trim($_POST['Notes']);
    $image = trim($_POST['image']);
    $Department = $_POST['Department'];

    //Store reference of SQL Statement in a variable
    $query_rs_insertEmployee = "INSERT INTO employees(Fullname,City,State,StartDate,Notes,image,Department)
    VALUES('$Fullname','$City','$State','$StartDate','$Notes','$image','$Department')";

    }
    ?>

    NOTE: Notice that the EmployeeID is NOT included in the list of columns. That's because it is a primary key that is set to auto increment when a record is created in the database. You could have however included it in the list and set it value to zero (0) which will automatically get updated in the database with the right value. However, this is not necessary. Notice also that the complete INSERT statement is WRAPPED in double quotes with each value WRAPPED in single quote.

  4. Write the following code before the closing curly brace to execute the SQL query.

    <?php
    //Check to see if form has been submitted
    if(isset($_POST['submitted']))
    {
    //Set variables for each database bound form elements.
    $Fullname = trim($_POST['Fullname']);
    $City = trim($_POST['City']);
    $State = trim($_POST['State']);
    $StartDate = trim($_POST['StartDate']);
    $Notes = trim($_POST['Notes']);
    $image = trim($_POST['image']);
    $Department = $_POST['Department'];

    //Store reference of SQL Statement in a variable
    $query_rs_insertEmployee = "INSERT INTO employees(Fullname,City,State,StartDate,Notes,image,Department)
    VALUES('$Fullname','$City','$State','$StartDate','$Notes','$image','$Department')";

    //Execute query
    mysql_query($query_rs_insertEmployee);
    }
    ?>

    ULTRA GEEK ONLY: Instead of creating individually variables, you could have added dynamic fields directly to the INSERT query. However, it would make the line hard to read and more error proned. Because SQL queries can be complex, it is best to create variables and pass these variables to the mysql_query ( ) function.

  5. In Code view, add the following code after the submit button in the form to add a hidden field that is used by the "if" statement above to check if the form is submitted.

    <input type="hidden" name="submitted" value="true">

  6. Save file and test page by entering some bogus data in the text fields and then clicking on the ADD EMPLOYEE button.
    CHECKPOINT: You can go to the database tab and check the database to ensure a record was added to the database. Later we will create a page so that you can "see" that changes in real time.

Preventing SQL Injection Attack

It is important to note that when you allow users to enter data into a form that is destined for a SQL query via a URL or FORM query, that special care be taken to prevent SQL injection attacks to the database. A SQL Injection Attack is when a malicious attacker attempts to break the SQL query by typing an apostrophe and then attempting to run his or her own query or queries like DROP TABLE that will delete your complete database as in the example below:

INSERT INTO yourTable (column1, column2, column3) VALUES (0, ' ' DROP TABLE yourTable;', value2, value3)

NOTE: The first apostrophe makes the SQL query incorrectly, then another SQL query is added that will run when the first query fails.

To do this, you have to prevent single quotes or apostrophes from appearing in a text string when submitted to a SQL statement. This is done in PHP with the mysql_real_escape_string function that clean up or "escapes" (add backslash) to variables before they get to the SQL query. It also prevents SQL from misinterpreting legitimate apostrophes and thus causing errors.

  1. Wrap the text fields variables with a myssql_real_escape_string function.
    • Notice you will be wrapping a function trim(( ... ) with another function ( mysql_real_escape ( ... )).

      //Set variables for each database bound form elements.
      $Fullname = mysql_real_escape_string( trim($_POST['Fullname']));
      $City = mysql_real_escape_string( trim($_POST['City']));
      $State = mysql_real_escape_string( trim($_POST['State']));
      $StartDate = mysql_real_escape_string( trim($_POST['StartDate']));
      $Notes = mysql_real_escape_string( trim($_POST['Notes']));
      $image = mysql_real_escape_string( trim($_POST['image']));
      $Department = $_POST['Department'];

      CAUTION: Don't forget to add the closing parenthesis.

  2. Save file.

Redirect User to AnotherPage

After page is submitted and data added to the database, a user needs to be REDIRECTED to the home page (index.php). You could create a separate confirmation page if you wanted or needed to.

  1. Write the following highlighted code before the closing PHP block  } (?>) in the previous code you created to redirect user to another page.

    //Execute query
    mysql_query($query_rs_insertEmployee);
    header("Location: index.php");
    }
    ?>
    <!doctype html>

    NOTE: If PHP detects an error, the redirect code will not be executed.

  2. Save file and test by adding bogus content to the form elements and clicking the submit button.

    CHECKPOINT: You should be taken to the home page (e.g., index.php). Using phpMyAdmin, you can check the database to ensure a record was added to the database.

 

EDITING THE MASTER/DETAIL TEMPLATES FOR EXISTING EMPLOYEES

Typically, if you are connecting to a database that is already populated with data, you could create the Master/Detail template pages FIRST.  However, since you created a database from scratch, you had to add (employee) records to the database using the add_employee.php template page.

The master page (index.php) is used to show a SMALL AMOUNT of information from the database about EACH record.  The detail page is used to show A LARGE AMOUNT of information from the database about the SELECTED record.  Retrieving information from a database is done with the SQL SELECT statement.  This statement as the name implies will "select" data from a database based on the criteria it is given and return a set of records.

Like the ADD EMPLOYEE page, you will be using a recordset to retrieve data from the database and placing it in memory for later use by displaying the result on a page. The master page will be used to pass a unique ID to the other template pages (i.e., Employee Detail, Update Employee and Delete Employee) that we will be editing later to perform the corresponding operations (view, update, and delete, respectively).

We will be creating two pages as part of the Master/Detail page set.

MASTER PAGE_________________________________________________________________________________

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

  1. Connect to a database from an existing connection file created earlier
  2. Create a recordset that will be used to populate a dynamic table
  3. Create links for each employee to view, edit or delete an employee.
  4. Create a query string to pass the correct employeeID to the correct page so that it can be used there

Create Table

Create your own table that will be used to view, edit or delete an employee.

  1. Open the index.php template you created earlier.
  2. Click below the page table (Master Page) and press the Enter key to create a blank line.
  3. Create a table with
    1. four columns and 2 rows
    2. top column headers 
    3. a width about 500 px
    4. a border thickness, cell padding and cell spacing of 1 px



  4. Click anywhere inside the table and then choose <table> in the Tag Chooser.  Then, select the Center option from the Align drop-down box.



  5. Place regular static text for the label in the first row / first column of Full Name.
  6. (Optional) In Code View, replace the last three <th> tags of the first row with a single <th> tag that expand across three columns. Alternatively, you could select the three cells and the press the merge icon in the Properties panel.

    From this:

    <tr>
    <th scope="col">Full Name</th>
    <th scope="col"></th>
    <th scope="col"></th>
    <th scope="col"></th>

    </tr>

    To this:

    <tr>
    <th scope="col">Full Name</th>
    <th colspan="3" scope="col"></th>
    </tr>

  7. Save file.

Connect to database

The database connection code was written and saved to a file (myConnections.php) in the previous tutorial. (See Connecting To A Database). NOTE: You only have to connect to the database once for all recordsets and queries that will be needed later.

  1. Write the following code on the first line in this file.

    <?php require_once('Connections/myConnections.php'); ?>

  2. Save the file.

Create a Recordset

  1. Write the following code after the code you created earlier to create a recordset to populate the table. Comments are optional but are written to explain code

    <?php
    // Store reference of SQL statement in a variable
    $query_rs_getAllEmployees = "SELECT * FROM employees";
    // Now, store a reference of the query in this variable from the SQL variable using the mysql_query function. This is not a recordset but a resource identifier number which points to the data. The data itself is now yet returned.
    $rs_getAllEmployees = mysql_query($query_rs_getAllEmployees);
    // Store the result of the query into an array using the mysql_fetcth_assoc function. The mysql_fetch_assoc retrieves the data and places it in an associative
    $row_query_rs_getAllEmployees = mysql_fetch_assoc($rs_getAllEmployees);
    ?>

    NOTE: PHP code written above <!doctype html> tag gets executed before the page is rendered. While the recordset itself does not need to be displayed on the page, the result of the recordset will be used to populate the table later.

  2. Save file.

Populate table with dynamic content using a repeat region

  1. In Code view. add the following dynamic variable to the first cell of the second row:

    <table width="500" border="1" cellspacing="1" cellpadding="1">
    <tr>
    <th scope="col">Full Name</th>
    <th colspan="3" scope="col"></th>
    </tr>
    </tr>
    <tr>
    <td><?php echo $row_query_rs_getAllEmployees['Fullname']; ?></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    </tr>
    </table>

    NOTE: Notice in the Design View, the result of the highlighted code above is in curly braces and highlighted with a color background.

  2. Test file by previewing it in a browser (F12) or clicking on the Live view button in Dreamweaver.

    CHECKPOINT: You should see the first name in the database replacing the dynamic text file. Even though the SQL query requested ALL of the records from the database, only one record was displayed because there is only one cell.

  3. In Code View, wrap the second row <tr> tags with a "do...while" loop to create a repeat region of the table to populate the table with ALL of the records from the database.

    <table width="500" border="1" cellspacing="0" cellpadding="3">
    <tr>
    <th scope="col">Full Name</th>
    <th colspan="3" scope="col"></th>
    </tr>
    </tr>

    <?php do { ?>
    <tr>
    <td><?php echo $row_query_rs_getAllEmployees['Fullname']; ?></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    </tr>
    <?php } while ($row_query_rs_getAllEmployees = mysql_fetch_assoc($rs_getAllEmployees)); ?>

    </table>

  4. Test file by previewing it in a browser (F12) or clicking on the Live view button in Dreamweaver.

    CHECKPOINT: You should see that the table expand to include all of the records in the database for the first column.

  5. Add the following ADDITIONAL dynamic variables to the other cells in the second row to create links and dynamic texts for each of the three <td> tags

    <?php do { ?>
    <tr>
    <td><?php echo $row_query_rs_getAllEmployees['Fullname']; ?></td>
    <td><a href="detail_page.php?recordID=<?php echo $row_query_rs_getAllEmployees['EmployeeID']; ?>">VIEW</a></td>
    <td><a href="update_employee.php?recordID=<?php echo $row_query_rs_getAllEmployees['EmployeeID']; ?>">EDIT</a></td>
    <td><a href="delete_employee.php?recordID=<?php echo $row_query_rs_getAllEmployees['EmployeeID']; ?>">DELETE</a></td>
    </tr>
    <?php } while ($row_query_rs_getAllEmployees = mysql_fetch_assoc($rs_getAllEmployees)); ?>

    NOTE: Notice the LINKS are going to the detail_page.php, update_employee.php and delete_employee.php pages, respectively. Also note that a query string (?recordID=<?php echo $row_query_rs_getAllEmployees['EmployeeID']; ?>) is used to pass the value of the employeeID to the corresponding pages so that it can be used there.

  6. Test page by previewing page in a browser (not Live View) and then hovering over any of the View, Edit, or Delete links. DO NOT CLICK on links because we have not flush out the detail_page.php yet.

    CHECKPOINT: You should notice that as you HOVER over any of the links in the status bar at the bottom of the browser you will see not only the page that is is going to (i.e., detail_page.php, update_employee.php and delete_employee.php pages) but also the query string (i.e., ?recordID=2) that is responsible for "passing" the unique employeeID value to the destination page.

Remove recordset

At the TOP of the file you created a recordset and placed it in memory, in the MIDDLE of the file you displayed the result of the recordset, and at the END of the file you need to close the recordset to release it from memory. Otherwise, the recordset may linger around when it is no longer need. The code will be added at the bottom of the page so that the rest of the page could be processed BEFORE the recordset is cleared; otherwise, the recordset would not have a chance of populating the form element

  1. In Code view, after the closing </html> tag add the following code to remove the recordset from memory:

    </html>
    <?php
    mysql_free_result($rs_getAllEmployees);
    ?>


  2. Save file.

OPTIONAL: Create Alternating Row Color

It is best practice to creating alternating row colors for a large table to make it easier to read each row. It also help user with low vision that uses a screen magnifier like ZoomText to easily scan the rows better. To pull off this feat, we can use a classical technique to determine if a value is odd by using the modulo operation (%).  Any number divided by 2 will always have a remainder of 1 or 0. Knowing that PHP treats 1 as true and o as false, we can use this in an "if" statement to determine if a value is odd. Using a loop that increment a counter by 1 we can than create the alternating rows that we need based on if the "if" statement is true.

  1. Edit the base_template.dwt.php template file and add the following class anywhere within the <style> tag but not inside another style definition.

    .alt_row_color {background-color:#CCC;}

  2. Save template and click on the Update button to update all of the files that this template is based on. Then, click the Close button to close the Update Pages dialog box.  Finally, close the template and select Save All from the File menu or the tab to save all open files.
  3. Return to the index.php page and add the following highlighted code to the second row in the table.

    <table width="500" border="1" cellspacing="0" cellpadding="3">
    <tr>
    <th scope="col">Full Name</th>
    <th colspan="3" scope="col"></th>
    </tr>
    <?php $counter = 0;?>
    <?php do { ?>
    <tr <?php if ($counter++ %2) {echo 'class = "alt_row_color"';}?>>
    <td><?php echo $row_query_rs_getAllEmployees['Fullname']; ?></td>
    <td><a href="detail_page_METHOD3.php?recordID=<?php echo $row_query_rs_getAllEmployees['EmployeeID']; ?>">VIEW</a></td>
    <td><a href="update_employee_METHOD3.php?recordID=<?php echo $row_query_rs_getAllEmployees['EmployeeID']; ?>">EDIT</a></td>
    <td><a href="delete_employee_METHOD3.php?recordID=<?php echo $row_query_rs_getAllEmployees['EmployeeID']; ?>" onclick ="return confirm('Are you sure you want to delete this employee from the database?')">DELETE</a></td>
    </tr>
    <?php } while ($row_query_rs_getAllEmployees = mysql_fetch_assoc($rs_getAllEmployees)); ?>
    </table>

    NOTE: Notice the counter in OUTSIDE of the loop and the if statement is used to increment the counter INSIDE the loop to determine if the row is odd or not. If it is odd, it "writes" the alt_row_color class to the <tr> tag. Separate PHP code blocks are used so that we do not break the current PHP code block.

  4. Save file and preview it in a browser or with Live view

    CHECKPOINT: You should now see that the the odd rows of the table are gray based on the CSS style add to the template and attached to the <tr> tag.

(Optional) Displaying message if no record is found Need ADDITIONAL WORK>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

While it is highly unlikely that you would have no record in the database, you still need to account for when the database is empty. So the next section will only work if you don't have any record in the database table

  1. On the MASTER PAGE, press ENTER to insert a new paragraph line BETWEEN the MASTER PAGE title and the table below it and type NO EMPLOYEE RECORD FOUND. Optionally, add an inline style of bold and red.
  2. Click inside the phrase you just typed and then select the <p> tag in the Tag Chooser to ensure the whole phrase is selected.
  3. In Code View, wrap the following code around the NO EMPLOYEE RECORD FOUND paragraph to Show If Recordset is Empty.

    <?php if ($totalRows_rs_getAllEmployees == 0) { // Show if recordset empty ?>
    <p style="color:red;font-weight:bold">NO EMPLOYEE RECORD FOUND</p>
    <?php } // Show if recordset empty ?>

  4. Click inside of the table and then select the <table> tag in the Tag Chooser to ensure the whole table is selected.
  5. In Code View, wrap the following god around the <table> tag to Show If Recordset is Not Empty.

    <?php if ($totalRows_rs_getAllEmployees > 0) { // Show if recordset not empty ?>
    <table width="500" border="1" align="center" cellpadding="1" cellspacing="1">
    ... TABLE DATA NOT SHOWN TO SAVE SPACE
    </table>
    <?php } // Show if recordset not empty ?>

  6. Add the following variable to the SQL statement to ascertain the number of records returned from database. Comments have been removed to save space.

    <?php
    $query_rs_getAllEmployees = "SELECT * FROM employees";
    $rs_getAllEmployees = mysql_query($query_rs_getAllEmployees);
    $row_query_rs_getAllEmployees = mysql_fetch_assoc($rs_getAllEmployees);
    //Get total number of records
    $totalRows_rs_getAllEmployees = mysql_num_rows($rs_getAllEmployees);

    ?>

  7. To test this function, you have two options:
    1. In Code View, set the variable $totalRows_rs_getAllEmployees TEMPORARILY to zero. (i.e., $totalRows_rs_getAllEmployees = 0) CAUTION: Don't forget to set it back after you check this functionality.
    2. Create a new table (employee_directory_empty) in the same database with an employeeID as the primary key field and one additional column and then re-assign this table for the recordset and then test the page.  CAUTION: Don't forget to set it back after you check this functionality.
  8. Save template page and test in browse.

CHECKPOINT: If there is no record in the database, the table will be hidden and the message will be shown only.

DETAIL PAGE_________________________________________________________________________________

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

  1. Connect to a database from an existing connection file created earlier
  2. Create a recordset that will be used to populate a table fill with dynamic variables
  3. Convert image text to actual image

TIP: It is important to note the color of certain elements to determine if there is a syntax error(s) in your code. In PHP, the default color for string literals are in red, variables (e.g., $_POST) are in light blue and period and square brackets are in dark blue.

NOTE: Both double quotes ( " ) and single quotes ( ' ) are used in code. Content in double quotes are string literals used by PHP and are passed without evaluation. Content in single quotes are used by SQL (not PHP) and represents the values being inserted into a database.

Create Table

Create your own table that will be used to view an employee's data.

ALTERNATIVELY: Instead of doing the steps below, you could copy the table code from the add_employee.php page and paste it into this page and then delete all of the form elements and the last row and then save the file.

  1. Open the detail_page.php template you created earlier.
  2. Click below the page table (Detail Page) and press the Enter key to create a blank line.
  3. Create a table with:
    1. 2 columns and 7 rows
    2. left column headers 
    3. a width about 500 px
    4. a border thickness, cell padding and cell spacing of 1 px
  4. Click anywhere inside the table and then choose <table> in the Tag Chooser.  Then, select the Center option from the Align drop-down box.
  5. Select the complete left column and in the Properties panel select the Right option from the Horz drop-down menu.
  6. Place regular static text for the labels in the first column of table (i.e., Full Name:, City:, State:, Start Date:, Notes:, Department:, image:)
  7. Save file.

Connect to database

The database connection code was written and saved to a file (myConnections.php) in the previous tutorial. (See Connecting To A Database). You only have to connect to the database once for all recordsets and queries that will be needed later.

  1. Write the following code on the first line in the template file.

    <?php require_once('Connections/myConnections.php'); ?>

  2. Save the template.

Create recordset

A recordset is as the names implies, “A set of records” returned from a database. However, what is more important to remember is that it is in the computer memory and is invisible to a user until it is displayed on the page. However, before, we can create a recordset, we have need of selecting a database.

TIP: When creating a recordset for a page, it is help to think of how many records you need to return to the page. For example, for the Add, Update, Delete and Detail pages, you only need one record so you will want to use the WHERE clause to limit the number of records to one typically using the primary key. If you are creating a Master page or a Search page, then you would want to use a WHERE clause to return typically one or more records. The term typically is used because it’s possible that no record could be returned from the database but this is “typically” NOT the case.

  1. Write the following code after the code you created earlier to create a recordset to populate the table. Comments are optional but are written to explain code

    <?php
    // Store reference of SQL statement in a variable
    $query_rs_getSelectedEmployees = "SELECT * FROM employees WHERE EmployeeID = $_GET[recordID]";
    // Now, store a reference of the query in this variable from the SQL variable using the mysql_query function. This is not a recordset but a resource identifier number which points to the data. The data itself is now yet returned.
    $rs_getSelectedEmployees = mysql_query($query_rs_getSelectedEmployees);
    // Store the result of the query into an array using the mysql_fetcth_assoc function. The mysql_fetch_assoc retrieves the data and places it in an associative
    $row_query_rs_getSelectedEmployees = mysql_fetch_assoc($rs_getSelectedEmployees);
    ?>


    NOTE: PHP code written above <!doctype html> tag gets executed before the page is rendered. While the recordset itself does not need to be displayed on the page, the result of the recordset will be used to populate the table later.

  2. Save file.

Populate table with dynamic content using dynamic fields

  1. In Code view, add the following dynamic fields that are highlighted below to the second column in the table:

    <table border="1" align="center">
    <tr>
    <td>Fullname</td>
    <td><?php echo $row_query_rs_getSelectedEmployees['Fullname']; ?></td>
    </tr>
    <tr>
    <td>City</td>
    <td><?php echo $row_query_rs_getSelectedEmployees['City']; ?></td>
    </tr>
    <tr>
    <td>State</td>
    <td><?php echo $row_query_rs_getSelectedEmployees['State']; ?></td>
    </tr>
    <tr>
    <td>StartDate</td>
    <td><?php echo $row_query_rs_getSelectedEmployees['StartDate']; ?></td>
    </tr>
    <tr>
    <td>Notes</td>
    <td><?php echo $row_query_rs_getSelectedEmployees['Notes']; ?> </td>
    </tr>
    <tr>
    <td>Department</td>
    <td><?php echo $row_query_rs_getSelectedEmployees['Department']; ?></td>
    </tr>
    <tr>
    <td>image</td>
    <td><?php echo $row_query_rs_getSelectedEmployees['image']; ?></td>
    </tr>
    </table>

    NOTE: Notice in the Design View, the result of the highlighted code above is in curly braces and highlighted in a background color.

  2. Test file by returning to index.php and previewing it in a browser (F12). Then, click on any VIEW links.

    CHECKPOINT: You should to be taken to the detail_page.php to see table populated with employee data from database.

Remove recordset

At the TOP of the file you created a recordset and placed it in memory, in the MIDDLE of the file you displayed the result of the recordset, and at the END of the file you need to close the recordset to release it from memory. Otherwise, the recordset may linger around when it is no longer need. The code will be added at the bottom of the page so that the rest of the page could be processed BEFORE the recordset is cleared; otherwise, the recordset would not have a chance of populating the form element

  1. In Code view, after the closing </html> tag add the following code to remove the recordset from memory:

    </html>
    <?php
    mysql_free_result($rs_getSelectedEmployees);
    ?>


  2. Save file.

Add Actual Image to Table

Currently, the literal image string (e.g., bob.jpg) is displayed on the page. What we really want is the actually image with a reference name from the database but resides in the images folder. To do this requires a little more work.

  1. Edit the current dynamic image code with a <img> tag reference to that image from the database.

    From this:

    <td>image</td>
    <td><?php echo $row_query_rs_getSelectedEmployees['Department']; ?></td>
    </tr>

    To this:

    <td>image</td>
    <td><img src="images/<?php echo $row_query_rs_getSelectedEmployees['image']; ?>"></td>
    </tr>

    NOTE: Notice there are two part to the src value. First, there is the static part that is common to all images ("images/") and there is a dynamic text to the image from the database.

  2. Save and test again in a browser by previewing to the index.php page and then clicking on any one of the VIEW links.

    NOTE: You should to be taken to the detail_page.php to see table populated with employee data from database BUT this time you should see the picture instead of the literal text.


EDITING THE UPDATE TEMPLATE FOR EXISTING EMPLOYEES

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 populate a form to "see" existing fields and "edit" ones that needs updating. The update 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 update 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 index.php page.

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. To update a record in a database, the form needs to be PRE-POPULATED with existing data from the database, then fields need to be edited and then the updated field reinserted back into the database.

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.

Create/Modify Template Page

  1. Open the add_employee.php and save it as update_employee.php.
    • The UPDATE EMPLOYEE page is basically the same as the ADD EMPLOYEE page, so to save time the Add page is used to get a head start on the Update Page.
  2. Update title, heading and any text on the page to reflect update instead of adding.
  3. Rename button from ADD EMPLOYEE to UPDATE EMPLOYEE by clicking on the button and changing the Value text field in the Properties panels.
  4. In Code View or the Properties panel, change the following form's attributes:
    • Name and ID to formUpdateEmployee
    • Action to update_employee.php
    • Method to POST (if not already)

Add recordset to per-populate text input fields

  1. In Code  View, add a recordset in another PHP code block below the existing code after the ?> and set its filter to read employeeID = 2

    //Execute query
    mysql_query($query_rs_insertEmployee);
    header("Location: index.php");
    }
    ?>
    <?php
    //Store reference of SQL Statement in a variable
    $query_rs_selectEmployee = "SELECT * FROM employees
    WHERE employeeID = 2";
    $rs_selectEmployee = mysql_query($query_rs_selectEmployee);
    $row_rs_selectEmployee = mysql_fetch_assoc($rs_selectEmployee);
    ?>


    NOTE: We could have selected only the columns in the database that we needed; however, we wanted to simplify the query so we used the wild-card asterisk to select all columns.

Add dynamic variables to text fields



  1. Add the following dynamic variables to each input fields value attributes ONLY :

    <table width="300" border="1" align="center" cellpadding="1" cellspacing="1">
    <tr>
    <th align="right" scope="row"><label for="Fullname">Full Name:</label></th>
    <td><input type="text" name="Fullname" id="Fullname" value="<?php echo $row_rs_selectEmployee['Fullname']; ?>"></td>
    </tr>
    <tr>
    <th align="right" scope="row"><label for="City">City:</label></th>
    <td><input type="text" name="City" id="City" value="<?php echo $row_rs_selectEmployee['City']; ?>"></td>
    </tr>
    <tr>
    <th align="right" scope="row"><label for="State">State:</label></th>
    <td><input type="text" name="State" id="State" value="<?php echo $row_rs_selectEmployee['State']; ?>"></td>
    </tr>
    <tr>
    <th align="right" scope="row"><label for="StartDate">Start Date:</label></th>
    <td><input type="text" name="StartDate" id="StartDate" value="<?php echo $row_rs_selectEmployee['StartDate']; ?>"></td>
    </tr>
    <tr>
    <th align="right" scope="row"><label for="Notes">Notes:</label></th>
    <td><input type="text" name="Notes" id="Notes" value="<?php echo $row_rs_selectEmployee['Notes']; ?>"></td>
    </tr>
    <tr>
           CODE FOR DEPARTMENT MENU NOT SHOWN HERE!!!!!!
    </tr>
    <tr>
    <th align="right" scope="row">Image:</th>
    <td><input type="text" name="image" id="image" value="<?php echo $row_rs_selectEmployee['image']; ?>"></td>
    </tr>

    NOTE: Notice Department is NOT part of the list because it is populate with another recordset that was created earlier in the add_employee.php file.

  2. Test code by by previewing this page in a browser.

    CHECKPOINT: You should see the record returned that has a employeeID value of 2 from the database. However, we need to make the value dynamic instead of static (i.e., 2). Later we will test this page  from the 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 (i.e., recordID = employeeID).

  3. Replace the literal numerical value 2 with a dynamic reference to the value that will be passed to it either from the browser URL or from the Master Page.

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

    NOTE:
    The $_GET[ ... ] represents a PHP URL variable that will be used later. Conversely, $_POST [ ... ] represents a PHP FORM variable.

  4. OPTION 1: Test again, but this time from a browser by clicking F12. Initially you will see no fields populated. Now, ADD the following query string to the end of the URL in the browser's Address/Location bar with any employeeID value from the database and then click Enter. 

    http://localhost/EmployeeDirectory2/update_employee.php?recordID=3

    OPTION 2:
    Preview the index.php page and click on any VIEW link. 

    Repeat several times
    with different values for option 1 or different links for option 2

    CHECKPOINT:
    You should see that the fields get updated for each new value or link you test.
    CAUTION:
    If you attempt to use Live view, you will get an error that states that the recordID variable is undefined because it is.

 

Add query to insert record to database

 

 

 


The first SELECT query was used to per-populate th form elements. Now, we need to create a INSERT query to insert any changes to the database.

  1. Edit the Insert record code so that it will reflect Updating instead and adding a record to the database.
    CAUTION: Be careful not to update the SELECT query instead of the INSERT query.

    From this:

    //Store reference of SQL Statement in a variable
    $query_rs_insertEmployee = "INSERT INTO employees(Fullname,City,State,StartDate,Notes,image,Department)
    VALUES('$Fullname','$City','$State','$StartDate','$Notes','$image','$Department')";

    To this:

    //Store reference of SQL Statement in a variable

    $query_rs_updateEmployee = "UPDATE employees SET Fullname = '$Fullname', City = '$City', State = '$State', StartDate = '$StartDate', Notes = '$Notes',image = '$image', Department = '$Department' WHERE EmployeeID = {$_POST['EmployeeID']}";

    $rs_updateEmployee = mysql_query($query_rs_updateEmployee);

    ALTERNATIVELY: Since we don't actually need to store a reference of the query but simply execute it, we could change the statement above to read simply: mysql_query($query_rs_updateEmployee);

    NOTE:
    Remember that SQL statement uses single quote for their values.  Also, be careful not to add and extra comma on the last element in the comma separated list.
    NOTE: Notice the curly braces " { ... }" that surrounds the value of the EmployeeID. Like the other input fields that have their dynamic values represented by curly braces, the hidden field is no exception. the only different is the it is as the name implies "hidden" from the page.

  1. Add another hidden field in the <form> tag to "hold" the value of the employeeID that will be used to send the correct employeeID to the database below the </table> tag. It is important to remember that the query string is used to pass a variable  INTO another page.   However, the hidden field is used as a PLACEHOLDER variable to pass the correct employeeID OUT of the page using a QUERY .

    <input type="hidden" name="EmployeeID" value="<?php echo $row_rs_selectEmployee['EmployeeID']; ?>">

  2. Save file and then preview the index.php page in a browser. Then, click on any of the EDIT links to take you to the update_employee.php page. Once on the UPDATE EMPLOYEE page, make any changes to any of the fields and then click on the UPDATE EMPLOYEE button.

    CHECKPOINT: You should be immediately returned to the index.php page and see that the data for that employee got updated.

    NOTE: If a user modify 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.

Sync menu with EmployeeID

If you were to click on the UPDATE link on the Master Page for several of the employees, you will see the the menu on the update_employee.php page ALWAYS reflects the first record in the department database table. The reason for this that the menu is populated from a DIFFERENT recordset than the other input fields and will always show the first record from the department table. To SYNC the recordset to the correct value from the employees tables requires some additional coding.

If you were to view the source code (i.e., Right click and select View Source) for this page as it is rendered in the browser, you will see that it creates a series of <option> tags.

<select name="Department" id="Department">
    <option value="IT">IT</option>
    <option value="Human Resource">Human Resource</option>
    <option value="Engineering">Engineering</option>
    <option value="Marketing">Marketing</option>
</select>

To make a value the default value, you would have to add attribute the is rightly called selected to the <option> tag you want. The code would look similar to this:

<select name="Department" id="Department">
    <option value="IT">IT</option>
    <option value="Human Resource">Human Resource</option>
    <option value="Engineering" SELECTED>Engineering</option>
    <option value="Marketing">Marketing</option>
</select>

To add this attribute to the current code, an "if" statement is used to check it there is an EmployeeID match and if so, it INJECT the attribute into the code. It is important to note that the "if" statement is added before the closing angle bracket of the <option> tag.

  1. Add the "if" statement to the current code.

    <?php
    do {
    ?>

    <option value="<?php echo $row_rs_departmentNames['DeptName']; ?>"

    <?php
    if ($row_rs_departmentNames['DeptName'] == $row_rs_selectEmployee['Department'])
    {
    echo " SELECTED";
    }
    ?>


    ><?php echo $row_rs_departmentNames['DeptName']?></option>

    <?php
    }
    while ($row_rs_departmentNames = mysql_fetch_assoc($rs_departmentNames));
    ?>

    NOTE: Notice that the last angle bracket ( > ) that is highlighted is used only to emphasize that the code in INSIDE the first <option> tag. It is not part of the code that should be added. Also, notice that there is a space before the word " SELECTED".
    NOTE: Within the loop, the "if" statement check each loop iteration to see if the DeptName of the first recordset MATCHES the Department of the second recordset and if so add the attribute of "SELECT" to that <option> tag.

  2. Save file and retest again.

    CHECKPOINT: If you were to click on the UPDATE link on the 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.

 

 

 


EDITING THE DELETE TEMPLATE TO DELETE AN EXISTING EMPLOYEE

The delete operation is the easiest of the other three operations (i.e., insert, update and select) to implement, yet it is the most dangerous process with the capability of deleting ALL of the records in the database if not done right, so it is created last among the major techniques (Add, Insert, and Update). If you are new to database development, it is best to backup the database if you can because the delete process is irreversible.

Unlike the other pages only code is needed for the Delete Employee page. There is no need to create:

  1. a table
  2. a form or form elements
  3. a title, etc.
  4. a recordset

The reason these elements are not needed is because the page is used only to execute the script only and a user will never see this page.

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

  1. Add the delete script using a query
  2. Redirect user to index.php

Edit Delete Student Template Page

Open the delete_student.php file.

ALTERNATIVELY: Since this page is used for code only, you could sever the page from the template to make is a regular page so that you could delete all of the HTML code in Code View before adding the script.

Connect to database

The database connection code was written and saved to a file (myConnections.php) in the previous tutorial. (See Connecting To A Database). While we could write the server-side include code above the <!DOCTYPE> tag of this document, it is better to include in in the template that we created. That way, it will be available to all of the pages since all of the pages in the CMS requires a database connection. NOTE: You only have to connect to the database once for all recordsets and queries that will be needed later.

  1. Write the following code on the first line in the template file.

    <?php require_once('Connections/myConnections.php'); ?>

  2. Save the template.

Select a Database and Create recordset

A recordset is as the names implies, “A set of records” returned from a database. However, what is more important to remember is that it is in the computer memory and is invisible to a user until it is displayed on the page. However, before, we can create a recordset, we have need of selecting a database.

TIP: When creating a recordset for a page, it is help to think of how many records you need to return to the page. For example, for the Add, Update, Delete and Detail pages, you only need one record so you will want to use the WHERE clause to limit the number of records to one typically using the primary key. If you are creating a Master page or a Search page, then you would want to use a WHERE clause to return typically one or more records. The term typically is used because it’s possible that no record could be returned from the database but this is “typically” NOT the case.

  1. Write the following code after the code you created earlier to create a recordset to delete a record from the database. Comments are optional but are written to explain code

    <?php
    // Store reference of SQL statement in a variable
    $query_rs_deleteEmployee = "DELETE FROM employees WHERE EmployeeID=$_GET[recordID]";
    // Now, store a reference of the query in this variable from the SQL variable using the mysql_query function. This is not a recordset but a resource identifier number which points to the data. The data itself is now yet returned.
    $rs_deleteEmployee = mysql_query($query_rs_deleteEmployee);
    // Store the result of the query into an array using the mysql_fetcth_assoc function. The mysql_fetch_assoc retrieves the data and places it in an associative
    $row_query_rs_deleteEmployee = mysql_fetch_assoc($rs_deleteEmployee);
    ?>

    NOTE: Unlike the SELECT clause, it is important to note that you DO NOT select columns from the database using (DELETE *) because you are deleting a complete row that cut across all of the columns for that record.
    CAUTION: While the WHERE clause is optional, If you inadvertently forget to include it, you will delete ALL of the records from the database.
    NOTE: PHP code written above <!doctype html > tag gets executed before the page is rendered. While the recordset itself does not need to be displayed on the page, the result of the recordset will be used to populate the table later.

  2. Save file.

Redirect to index.php page

  1. Before the closing ?> tag, add the following code to redirect the user back to the home page.

    header("Location: index.php");

  2. Save file and test the script by returning to the index.php page and previewing it in a browser (F12)  and then click on one of the delete button for a given employee.

    CHECKPOINT: You should see the you were immediately returned to the index.php page and the employee that you selected to be deleted is no longer in the list.

Remove recordset

At the TOP of the file you created a recordset and placed it in memory, in the MIDDLE of the file you displayed the result of the recordset, and at the END of the file you need to close the recordset to release it from memory. Otherwise, the recordset may linger around when it is no longer need. The code will be added at the bottom of the page so that the rest of the page could be processed BEFORE the recordset is cleared; otherwise, the recordset would not have a chance of populating the form element.

  1. In Code view, after the closing </html> tag add the following code to remove the recordset from memory:

    </html>
    <?php
    mysql_free_result($rs_deleteEmployee);
    ?>


  2. Save file.

(Optional) Add Confirmation Dialog Box with JavaScript to DELETE links

It is best practice to give the user a notice of confirmation that the record will be deleted and the option to cancel the process if the wrong record is selected or the user changes his or her mind on deleting the record.

  1. Open the the index.php page.
  2. Ensure Split view is selected and the click inside of the DELETE link and then choose the <a> tag in the Tag Chooser to highlight the code in Code View.
  3. Add the following highlighted code to the end of the starting <a> tag of the DELETE button in Code view:

    <a href="delete_employee_METHOD3.php?recordID=<?php echo $row_query_rs_getAllEmployees['EmployeeID']; ?>" onclick ="return confirm('Are you sure you want to delete this employee from the database?')">DELETE</a>

CAUTION: Pay attention to the single quotes.

  • This will cause the Alert dialog to show when the DELETE link is clicked. If a user choose OK, the student's record will be deleted from the database. If a user choose CANCEL, nothing happens.
  1. Return to index.php page again and open it in a browser. Click on a DELETE link for one of the employee in the list. You should see an Alert dialog appear.  First click Cancel. Nothing should happen except the dialog box is closed.  Now, try again and click OK to see a record deleted.

CHECKPOINT: You will automatically be redirected back to the index.php page where you can confirmed that that record was indeed deleted from the database.

EDITING THE SEARCH TEMPLATE FOR EXISTING EMPLOYEE

After employee records has been added to the database, a search page can be created that contains a form that will collect search criteria (full_name, city, state, etc.) and compare (matches) that criteria with record(s) in the database. A result table will also be shown for each employee record that the criteria matches. A link will also be created so that an HR representative can update the employee data.

Modify Template Page

  1. Open the search_employee.php template page that you created earlier.
  2. Press the ENTER key after the title SEARCH FOR EMPLOYEE(S) to create a new blank line.
  3. Insert a form (Insert > Form > Form) and then in Code view or in the Properties Panel, change the following form's attributes:
    • Name and ID to formSearch
    • Action to search_employee.php
    • Method to POST (if not already)
  • NOTE: Notice that the Action is linked to the SAME page. This is called self-posting or post back where the page post on TOP OF ITSELF when the submit button is clicked.
  1. With cursor position inside of form (red dashed-line box), select Insert > Table from menu to create a 4 rows and 2 columns table with a width of 300pxSet border thickness, cell padding and cell spacing to to 1 and the border thickness to 1 and then click OK.
    • NOTE: This table will be used to provide input fields and a submit button for the search function.
  2. Select the <table> from the Tag Chooser and choose Center from Align combo box to center the table within the form.
  3. Position cursor in the first column and first row of the table and select Insert > Form > Text Field from menu.  Give the text field a ID (e.g.,Fullname) and a Label (e.g., Full Name:) and then click OK.
  4. Repeat the previous step two more times in the cells below the text field you just created but give it a different ID name (e.g., City) and Label name (e.g., City:) and ID name (e.g., State) and Label name (e.g., State:)
  5. Drag and Drop the TextField elements to the second column for each TextField.
  6. In the second column and fourth row, insert a Submit button by select Insert > Form > Button. Give the button an ID (e.g., search_Button) but not a Label and then click OK. Ensure the type is set to submit in the Properties panel.
    • ALTERNATIVELY: You could open the Form tab and use the corresponding TextFields and Button icon to insert these form elements.
  7. Click to select the button and in the Properties panel give the Value field a name of SEARCH FOR EMPLOYEE.
  8. Click on the border in the center of the table and resize it to make the table text fit better.
  9. Click and drag to select  the first three cells in the first column and then select Right from the Align option in the Properties panel for the Horizontal alignment.
  10. Position the cursor to the right of the EMPLOYEE SEARCH TABLE and press the ENTER key TWICE to create two new blank lines.
  11. Select Insert > Table to insert a table another table with 2 rows and 4 columns and a width of 500 pixels. Set border, cell padding and cell spacing to 1. Select the Top as a Header type and then click OK.
    • NOTE: This table will be used to display the result of a search if there is any.
  12. Select the <table> from the Tag Chooser and choose Center from Align combo box to center the table within the content area.
  13. On the top row, type the following static text starting with the top-left cell and leaving the last cell empty: Full Name, City, and State.
  14. Add the word EDIT to the last column of the second row.

Insert RecordSet (rs_SearchEmployee)

  1. Write or copy the following code above the <doctype html> tag. Comments are optional but are used to explain code.

    <?php require_once('Connections/myConnections.php'); ?>

    <?php
    // Store a reference of the database in this variable
    $database = "employeedirectory2";
    // Select the database you need using the mysql_select_db function
    mysql_select_db($database);
    //Store reference of SQL Statement in a variable. Filter query based on form variables.
    $query_rs_SearchEmployee = "SELECT * FROM employees WHERE Fullname LIKE '%$_POST[Fullname]%' AND City LIKE '%$_POST[City]%'";
    //Execute query
    $rs_SearchEmployee = mysql_query($query_rs_SearchEmployee);
    //Get array of data
    $row_rs_SearchEmployee = mysql_fetch_assoc($rs_SearchEmployee);
    //Get total number of records
    $totalRows_rs_SearchEmployee = mysql_num_rows($rs_SearchEmployee);
    ?>


    CAUTION: Notice the SINGLE QUOTES and the use of the % at the end of the statements that is used to create a wild-card search.
    NOTE: Notice that the WHERE clause has a series of AND that are used to create a multiple search criteria. Also, note the word LIKE the is used for wild-card searches.

    Create Dynamic Variables to Display result of Search
  1. In Code View, add the following dynamic variables to the second table that was created rows between the do/while loop.

    <table width="500" border="1" align="center" cellpadding="1" cellspacing="1">
    <tr>
    <th scope="col"><strong>Full Name</strong></th>
    <th scope="col"><strong> City</strong></th>
    <th scope="col"><strong>State</strong></th>
    <th scope="col">&nbsp;</th>
    </tr>

    <?php do { ?>
    <tr>
    <td><?php echo $row_rs_SearchEmployee['Fullname']; ?></td>
    <td><?php echo $row_rs_SearchEmployee['City']; ?></td>
    <td><?php echo $row_rs_SearchEmployee['State']; ?></td>
    <td>EDIT</td>
    </tr>
    <?php } while ($row_rs_SearchEmployee = mysql_fetch_assoc($rs_SearchEmployee)); ?>
    </table>

  2. Wrap the second row <tr> tag of the table with a do...while loop so that it can create a repeat region for the table to expand based on the number of records from the database.

    <table width="500" border="1" align="center" cellpadding="1" cellspacing="1">
    <tr>
    <th scope="col"><strong>Full Name</strong></th>
    <th scope="col"><strong> City</strong></th>
    <th scope="col"><strong>State</strong></th>
    <th scope="col">&nbsp;</th>
    </tr>

    <?php do
    { ?>

    <tr>
    <td><?php echo $row_rs_SearchEmployee['Fullname']; ?></td>
    <td><?php echo $row_rs_SearchEmployee['City']; ?></td>
    <td><?php echo $row_rs_SearchEmployee['State']; ?></td>
    <td>EDIT</td>
    </tr>
    <?php
    } while ($row_rs_SearchEmployee = mysql_fetch_assoc($rs_SearchEmployee)); ?>

    </table>

NOTE: Notice Dreamweaver displayed a small Repeat tab on top of the row in Design View.

Create Link To Update Employee Page

  1. Wrap the following code around the word EDIT between the <tr> tags to provide a link to the update_employee.php page while at the same time providing a dynamic query string:

    <td><a href="update_employee.php?recordID=<?php echo $row_rs_SearchEmployee['EmployeeID']; ?>">EDIT</a></td>

  2. Save template page.
  3. Open the search_employee.php page and test search functionality by entering a search criteria (i.e., Full Name, City, and/or State) and then clicking SEARCH FOR EMPLOYEE button.

    CHECKPOINT: You should see that the page is displayed with a lot of errors because some of the variables are not defined. All of the records are displayed because the WHERE clause is not correctly defined because the variables are not yet defined.. To ensure that the variables are defined we will wrap the query and the table with a conditional statement to check to ensure the page is submitted first.

  4. Wrap the following code around the query:

    if(isset($_POST['submitted']))
    {

    $query_rs_SearchEmployee = "SELECT * FROM employees WHERE Fullname LIKE '%$_POST[Fullname]%' AND City LIKE '%$_POST[City]%'";
    //Execute query
    $rs_SearchEmployee = mysql_query($query_rs_SearchEmployee);
    //Get array of data
    $row_rs_SearchEmployee = mysql_fetch_assoc($rs_SearchEmployee);
    //Get total number of records
    $totalRows_rs_SearchEmployee = mysql_num_rows($rs_SearchEmployee);
    }
    ?>

  5. Wrap the following code around the second table:

    <?php //Check to see if form has been submitted
    if(isset($_POST['submitted']))
    {?>


    <table width="500" border="1" align="center" cellpadding="1" cellspacing="1">
    <tr>
    <th scope="col"><strong>Full Name</strong></th>
    <th scope="col"><strong> City</strong></th>
    <th scope="col"><strong>State</strong></th>
    <th scope="col">&nbsp;</th>
    </tr>

    <?php do { ?>
    <tr>
    <td><?php echo $row_rs_SearchEmployee['Fullname']; ?></td>
    <td><strong><?php echo $row_rs_SearchEmployee['City']; ?></strong></td>
    <td><?php echo $row_rs_SearchEmployee['State']; ?></td>
    <td>EDIT</td>
    </tr>
    <?php } while ($row_rs_SearchEmployee = mysql_fetch_assoc($rs_SearchEmployee)); ?>
    </table>

    <?php
    }
    ?>


    NOTE: When the page is first loaded the table will not be shown because the conditional statement is not true.
  1. In Code View, add the following hidden field before the closing </form> tag.

    <input type="hidden" name="submitted" value="true">
    </form>

  2. Open the search_employee.php page and test search functionality by entering a search criteria (i.e., Full Name, City, and/or State) of a valid value and then click the SEARCH FOR EMPLOYEE button.

    CHECKPOINT: You should see that a table is shown with the correct criteria search. If you don't enter anything into the three fields and press the SEARCH FOR EMPLOYEE button again, you will see ALL employees.

Add message if no record is found

  1. Place the cursor to the right of the SECOND table and press the ENTER key TWICE
  2. Type NO EMPLOYEE FOUND!!. Optionally, style it in bold,red and center with an inline style (i.e., <p style="font-weight:bold; color:red; align:center;text-align:center;">NO EMPLOYEE FOUND!!!</p>)
  3. Select the message and then click on the <p> in the Tag Chooser to ensure all of the text is selected.
  4. In Code View, wrap the following code around the line just created..

    CAUTION: Be careful where you placed the code is. Notice that the code is WITHIN the previous PHP code block so that the variable will not be undefined. We choose the second table, because it already as the the isset () method to ensure that the variable does not shown undefined. Otherwise, we would have received an error is we add the code below the first table.
    NOTE: If you include the comments, you will see the "Repeat" and "Show if" tab above the form elements in the Design View in Dreamweaver. Otherwise, you will see the PHP icons.

    </table>
    <?php if ($totalRows_rs_SearchEmployee == 0) { // Show if recordset empty ?>
    <p style="font-weight:bold; color:red; align:center;text-align:center;">NO EMPLOYEE FOUND!!!</p>
    <?php }
    // Show if recordset empty ?>
    <?php
    }
    ?>
    <input type="hidden" name="submitted" value="true">
    </form>

  1. Preview the page and enter "aaaa" into the Full Name field and press the SEARCH EMPLOYEE button.

    CHECKPOINT: You should see the text "NO EMPLOYEE FOUND!!! displayed. However, the table is also displayed but is empty.

  2. Select the last table and then click on the <table> in the Tag Chooser to ensure that the whole table is selected.

  3. In Code View, wrap the following code around the second table <table> tags:

    <?php //Check to see if form has been submitted
    if(isset($_POST['submitted']))
    {?>
    <?php if ($totalRows_rs_SearchEmployee > 0) { // Show if recordset not empty ?>
    <table width="500" border="1" align="center" cellpadding="1" cellspacing="1">

    .... TABLE ROWS NOT SHOWN

    </table>
    <?php } // Show if recordset not empty ?>

    <?php if ($totalRows_rs_SearchEmployee == 0) { // Show if recordset empty ?>
    <p><strong style="color: #F00">NO EMPLOYEE FOUND!!</strong></p>
    <?php } // Show if recordset empty ?>
    <?php
    }
    ?>
    <input type="hidden" name="submitted" value="true">

    </form>

    CAUTION: Be careful where you placed the code. Notice that the code is INSIDE the "if" PHP code block.

  4. Preview the page and enter "aaaa"  again into the Full Name field and press the SEARCH EMPLOYEE button.

    CHECKPOINT: You should see the text "NO EMPLOYEE FOUND!!! displayed and the table is now hidden.

AUTHENTICATE USERS

It is important to authenticate users especially as it relates to ensuring only authorized users have access to administrative pages (i.e., add, update or delete employees) as well as having only register users view specific pages. This is done by creating pages that allow users to register, log in or log out through a authentication framework in order to access pages that requires authentication. Other pages are accessable to the public. 

CAUTION: It is best NOT to provide a link on your website to your administrative pages to prevent hackers from attempting to hack your site. Instead, provide a link that the administrative users have to enter manually. This also prevent search engines from indexing those links. However, you should provide links (register, login and logout) to other users (members, client login, guests, etc.)

Login/Logout sometimes is called Sign In/Sign Out.

CREATING LOGIN PAGES

The Login page allows a user to enter a username and password that will be checked against a username and password in the database. If there is a match the user is relocated to the appropriate page.  If there is not a match, the user is presented with a message on the same page or another page that states that the username/password was invalid. Session variables are used to allow user to navigate from page to page without having to login multiple times. Once a user is logged in, a session variable is created. Any page that restrict access will check for a session variable and allow or restrict access to that page depending on if the login is successful or not. If not successful, the user will be redirected to another page.

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

  1. Create Form, Table and Form elements
  2. Create a recordset to check for match of both username and password with username and password from database
  3. Add Session variables to allow a user to navigate from page to page without having to login multiple times
  4. Redirect a user back to the appropiate page page (e.g., home page (index.php))

Modify Template Page

  1. Open the admin_login.php template page that you created earlier.
  2. Press the ENTER key after the title Administrative Login to create a new blank line.
  3. Insert a form (Insert > Form > Form) and then in Code View or in the Properties Panel, change the following form's attributes:
    • Name and ID to formLogin
    • Action to admin_login.php
    • Method to POST (if not already)
      • NOTE: Notice that a form is represented by a red dashed box. Notice also that the Action is linked to the SAME page. This is called self-posting or post back where the page post on TOP OF ITSELF when the submit button is clicked.
  4. While the insertion point is still in the form between the red dashed box, create a table inside the form with the following settings from the Insert panel Common option and then selecting table:
    • 3 rows and 2 columns
    • width of 400 pixels
    • left header
    • border thickness, cell padding and cell spacing of 1 pixel
      NOTE: This table will be used to provide input fields for the username/password and a submit button for the login function.
  5. Click anywhere inside the table and select the <table> tab from the Tag Chooser and then in the Properties panel select Center from the Align's drop down menu to align the table in the center of the form.
  6. Position the cursor in the first column and first row of the table and select Insert > Form > Text Field from menu.  Give the text field a ID (e.g.,userName) in the Properties panel and a Label (e.g., Username:) in the table itself.
  7. Repeat the previous step in the cell below the text field you just created but give it a different ID name (e.g., password) in the Properties panel and a Label name (e.g., Password:) in the table itself.
  8. Drag and Drop the TextField elements to the second column for each TextField.
  9. In the second column and third row, insert a Submit button by selecting Insert > Form > Submit Button. Give the button an ID (e.g., login_Button) but not a Label in the Properties panel and ensure the type is set to submit.
    • ALTERNATIVELY: You could open the Form tab and use the corresponding Text Fields and Button icon to insert these form elements.
  10. Click to select the button and in the Properties panel give the Value field a name of LOGIN.
  11. Click on the border in the right of the table and resize it to make the table text fit better by dragging the border.
  12. Click and drag to select  the first two cells in the first column and then select Right from the Align option in the Properties panel for the Horizontal alignment.

Write Login Script

  1. Write or copy the following code above the <doctype html> tag. Comments are optional but are used to explain code.

    <?php require_once('Connections/myConnections.php'); ?>
    <?php
    //Check to see if form has been submitted by checking the userName instead of creating a hidden field (i.e., submitted) as in the other pages.
    if(isset($_POST['userName']))
    {
    //Convert form POST variables to REGULAR variables
    $Fullname = $_POST['userName'];
    $password = $_POST['password'];
    //If either form field is empty, post back to page
    if( (!$Fullname) or (!$password))
    {
    header("Location: admin_login.php");
    }
    //Create query. Store reference of SQL Statement in a variable. Filter query based on form variables.
    $query_rs_Login = "SELECT Fullname, password FROM employees WHERE Fullname = '$Fullname' AND password = '$password'";
    //Execute query
    $rs_Login = mysql_query($query_rs_Login);
    //Determine number of record return from database
    $numRow = mysql_num_rows($rs_Login);
    if ($numRow != 0)
    {
    header("Location: index.php");
    }
    else
    {
    header("Location: admin_login.php?failed=true");
    }
    }
    ?>


  2. Test admin_login.php by entering valid and non-valid usernames and passwords and then pressing the Login button

    CHECKPOINT: You should see that the page go nowhere when there is a invalid username or password and it takes you to the index.php page when there is a valid username and password.

Add Error Message

It is best practice to provide an error message if the user enter an invalid username of password.

  1. Add the following URL query string to the code to the previous code (highlighed in bold):

    header("Location: admin_login.php?failed=true");

    NOTE: This query string will be used next to see if the page has been submitted and invalid username or password has been entered.

  2. Add the following code anywhere you want the error message to appear (highlighed in bold). In this case, it was added between the <h1> and the <form> tags:

    <h1>ADMINISTRATIVE LOGIN</h1>
    <?php echo (isset($_GET['failed']))?"<p style='color:red;font-weight:bold;text-align:center'>Enter valid username or password</p>": "";?>
    <form action="admin_login.php" method="post" name="formLogin" id="formLogin">

    NOTE:  Because the code is inline, instead of using the tradition "if" statement, another conditional operator called trinary is used. If the opening statement [ (isset($_GET['failed'])) ] is true than the first clause [ "<p style='color:red;font-weight:bold;text-align:center'>Enter valid username or password</p>" ] after the question mark (?) is executed. Otherwise, the second clause [  "" ] which is an empty string after the colon [ : ] is executed.

Add Server Variables

It is best practice to add server variables when you want to refer to the SAME page.

  1. Add the following two additional SERVER variables below the POST variables. Comments are optional. :

    //Convert form POST arrays to REGULAR variables
    $Fullname = $_POST['userName'];
    $password = $_POST['password'];
    //Add two server variables
    $self = $_SERVER['PHP_SELF'];
    $referer = $_SERVER['HTTP_REFERER'];


  2. Replace the static form Action's variable VALUE with a dynamic variable VALUE:

    From this: <form action="admin_login.php" method="post" name="formLogin" id="formLogin">
    To this: <form action=$self method="post" name="formLogin" id="formLogin">
    Or this: <form action="<?php echo (PHP_SELF) ?>" method="post" name="formLogin" id="formLogin">

    NOTE: $PHP_SELF is a built-in server variable that is used to replace the location of the current page.

  3. Update the the following two header with the following code

    From this: header("Location: admin_login.php");
    To this: header("Location: $referer");

    From this: header("Location: admin_login.php");
    To this: header("Location: $referer?failed=true");

  4. Test page again.

    CHECKPOINT: You should not see any changes but the page is more dynamic.

Create LogOut Code

It is important for a user to log out so that the another user can not use the same computer with previous user still login. The Log User Out behavior will be used to destroy the session variables.


  1. Save page.

Restrict Admin Pages

Add note and code for restrict page here...

Add Session Variable

ddfdf

Registration content goes here....

OPTIONAL ENHANCEMENTS THAT CAN BE ADDED

The following additional enhancements can be added to the CMS to add some important finishing touches to it.

  1. Add a dynamic year function to the copyright notice
  2. If no picture is available, provide an avatar icon
  3. Validate form elements on client side and server side (i.e., date)
  4. Upload image instead of having images in image folder
  5. Use multiple tables to create a relational database instead of a flat database (one table)
  6. Use radio buttons and check boxes in form
  7. Using Cookies and SESSION variables
  8. Sending email
  9. Create a Paging Navigational Scheme
  10. Create Sticky Form

1. Add a dynamic year function to the copyright notice

While this enhancement is not required, it adds a nice touch to the footer of each page and cause the year to update AUTOMATICALLY when a new year occurs.

  1. Open up the template in the Template folder.
  2. Replace the static year with dynamic code in the copyright notice in the footer with the following code:

    From this:
    <p>Copyright 2010 - 2014 by Cornelius Chopin. All rights reserved.</p>
    To this:
    <p>Copyright 2010 - <?php echo date("Y")?> by Cornelius Chopin. All rights reserved.</p>

    NOTE: The <?php echo date("Y")?> code is a simply PHP built-in date function that returns the current year as a four-digit number.

  3. Save and test.

    NOTE: You should see the current year displayed in the footer on each page. However, this time, it is dynamic and will change on the first day of a new year.

 

2. If no picture is uploaded, provide and avator icon

With the current implementation of the CMS, if a picture is not already in the images folder AND the name provided in the Add Employee form page, there will be a broken link on Detail page. It is best practice to provide an avatar that is a default image that get displayed instead when an image is not provided.

Detail_page.php (Employee Directory Detail Page)

  1. Open the detail_page.php page and click on the image to select it.
  2. (Optional) In Code View, add space before and after the selected code so that you can see the changes that will be made better.
  3. Delete the selected code below:

    <img src="images/<?php echo $row_query_rs_getSelectedEmployees['image']; ?>">

  4. Replace the deleted code with this code:

    <?php
    $source_img = $row_query_rs_getSelectedEmployees['image'];
    if($source_img == "")
    { echo "<img src='images/photo_not_available_large.png' width='307' height='240'/>"; }
    else
    { echo "<img src='images/$source_img' width='307' height='240'/>"; }
    ?>


    NOTE: This code is a simple "if" statement that shows a default avator image if an image is not in the the images folder. Otherwise, it will show the image from the images folder based on the dynamic data from the database.

  5. Save file and test code by adding another employee without specifying an image from the add_employee.php page. Then on the Detail Employee page check to see the avator.

x. Creating a Paging Navigational Scheme

If you know that you may have more records from the database that will fit within the viewing area or the page or device (i.e., smartphone), you may want to implement a paging navigational scheme. To limit the number of records returned from the database, you can use the LIMIT keyword in an SQL statement and then build a navigational scheme to "fetch" additional records from the database as needed. The navigational scheme will also be used to move back and forth to "flip through" the "pages" or records.

Selecting a Subset of Records

The LIMIT keyword is used with two numbers separated by a comma. The first is used to set which record to start from. The second is used to set the maximum number of records to retrieve from the database. For example, LIMIT 0, 11 will return the first 10 records from the database-- zero up to 10 but not including 11. Remember, like array, SQL count start with zero. To return the next ten records, the LIMIT statement would need to read LIMIT 11,11

  1. (OPTIONAL) Open the index.php page and ensure that there are about 10 records in the database. If not, add some additional records.
  2. Hard-code the LIMIT keyword and two numbers into the query to see how it works.

    // Store reference of SQL statement in a variable
    $query_rs_getAllEmployees = "SELECT * FROM employees LIMIT 0,8";

    CHECKPOINT: You should see the number of records returned from the database is "LIMITED" to 8. However, to may the navigational scheme, we need to make these numbers dynamic instead of static.

 Creating a Navigational Scheme

Add note here...

x. Upload image instead of having images in image folder

While having images already in an folder name images is convenient, it is not best practice. In this case, you would have to FTP images to the images folder separate and remember the names to enter into the Add Student form page. This is not very practical. To be a true CMS, an image should be uploaded AUTOMATICAILLY to the server when a student is added to the database. To do this requires some specialize handling of binary data in the form.

To make an EXISTING form capable of uploading a file (i.e., image, etc.) requires a minimum of  two changes to form elements and addition of two input elements as well as a PHP upload script.

  1. The form element must edited to have an attribute of enctype="multipart/form-data" and method="post"

    NOTE: This "enctype" attribute lets the browser know that "multiple" data type can be submitted (i.e., text and binary). The "method" is how to file is submitted--in this case post.

  2. Replace the current PHOTO text field (i.e., <input type="text" name="PHOTO" value="" size="32 >) from a text type to a file type and add a name attribute of "the file":

    <input type="file" name="the file" />

    NOTE: This is used to create a text field AND a Browser button so that a user can either type in a file name or browser to a file on their computer.

  3. A special hidden input type needs to be added to the form: <input type="hidden" name="submitted" value="true" />

    NOTE: This hidden input field will be used by the PHP upload script to determine if the form has been submitted.

  4. A special hidden input type needs to be added to the form: <input type="hidden" name="MAX_FILE_SIZE" value="2000" />

    NOTE: This lets the browser know how large (in bytyes) a file can be uploaded.

  5. Add the following script (or any other script you create or can get) below the opening <body> tag: