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

IMPORTANT NOTE: This is a continuation of the HOW TO CONNECT TO A DATABASE (PART 2).

IMPORTANT NOTE: All references with "_ID" has been replaced by "ID." However, the screenshot may not show this because they were taken for a previous version.

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. Before you start, you need to create the necessary template pages:

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. When you have completed the tutorial review these rules again:

Most of the CMS will be created using server behaviors (pre-written code that are used to create the code you need). While using server behaviors are convenient, they have the tendancy of hiding key aspect of the code that is written. The advantage of server behaviors especially for beginners is that it shields them from the complexity of what going on behind the scene.  The disadvantage of server behaviors is that they write more code than usually necessary because they have to account for a variety of situations.

While server behaviors are good for rapid application development (RAD), prototying or proof of concept (POC), you should strive to learn how to write the code manually while at the same time streamlining the code for faster processing and easier understanding.

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." You can skips any NUMBERICAL STEPS and use these steps instead.

Creating An Student Directory Management System Pages

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

The following pages will allow HR to:

  1. ADD student – create a new record for a new student that has enrolled in the school
  2. VIEW students – view existing students records
  3. EDIT student – update a record for an existing student when changes are needed
  4. DELETE student – delete a record of existing student if they drop from school
  5. SEARCH for students – search for records of existing students

To recreate a Content Management System (CMS) you typical create a series of template pages that includes recordset(s), business logic code (behavior) 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 Server Behavior (code) used in template Number of
recordsets on page
Number of record(s) returned SQL Statement SQL Filter
add_student.php RECORD INSERTION FORM WIZARD Not applicalbe unless you want to dynamically populate form elements Depending on number of form elements that will be used INSERT N/A
search_student.php N/A N/A N/A SELECT WHERE
search_student_confirmed.php N/A 1 (rs_Search) 0-Many    
edit_student.php RECORD UPDATE FORM WIZARD 1 (rs_Edit) 1 SELECT AND UPDATE WHERE
student_master.php Master/Detail Page Set 1 Typically Many SELECT  
student_detail.php Master/Detail Page Set 1 1 SELECT WHERE
delete_student.php Delete Record N/A N/A DELETE WHERE

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 Template
  • 1. Add Template
  • 2. Master/Detail Templates
  • 3. Edit Template
  • 4. Delete Template
  • 5. Search Template 1
  • 6. Search Template 2
  • 7. Enhancements

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) that is beyond the scope of this tutorial.

 Create Template To Be Used By All Pages

  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 Student 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.
  1. (CS4+ only) Select the image placeholder in the document's header and then select <a> in Tag Chooser (see screenshot below) and then press the Delete key to delete it and then type "Student Directory."
  2. Select the phrase "Student Directory" and then choose Heading 1 from the Format's drop-down box in the Property panel (typically at the bottom of the screen).

    NOTE: You could replace this text with an image if you want.

  3. Delete all of the content in the main content area except for the word Instructions at the top.
  4. (CS4+ only) Delete all of the text in the sidebar area (but not the links).
  5. Change or add 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 STUDENT - add_student.php
  6. (CS4+ only) Delete the <li> tag for the third and fourth link in the Code view in the sidebar area.
  7. Add a copyright notice in the document's footer (i.e., Copyright 2013 by Cornelius Chopin. All rights reserved.)
  8. Select the content area  by selecting the <div.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.
  1. Select File > Save and then give the template a name of student_directory_template.
  • Notice that Dreamweaver created a directory called Templates and save the file into that directory automatically.  You will use this template to create ALL of the page in this CMS.
  1. Close the template so that you don't accidentally select it as a file.

    NOTE: Disregard the SEARCH FOR STUDENT(S) link in the sidebar. A form element will be added to the page.

 Create Template Page (index.php)

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

 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 thay you can easily link to them from within the wizards that will be used and you don't have to concern yourself about a page not found error. It is best to create all of the STATIC content first and then add the DYNAMIC content afterward by adding recordsets and dynamic content using server-side wizards.

  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_student.php ADD STUDENT PAGE ADD STUDENT
    detail_page.php STUDENT DETAIL PAGE STUDENT DETAIL
    edit_student.php EDIT STUDENT PAGE EDIT STUDENT
    delete_student.php DELETE STUDENT PAGE * DELETE STUDENT *
    search_student.php SEARCH FOR STUDENT PAGE SEARCH FOR STUDENT

    * 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.

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

EDITING THE ADD STUDENT 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 Student  template page will be designed to add students to the database by using the Record Insertion Form behavior to automatically generate the form, form elements, table and the required code to insert a new record into the student_directory table.  This page will be redirected automatically to the index.php page because of code created within Record Insertion Form behavior.

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.

PICK ONE: There are THREE METHODS to create the form, form elements and the code to insert records into a database:

  1. METHOD 1: Use Insert Record Form Wizard server behavior
  2. METHOD 2: Use the Insert Record server behavior
  3. METHOD 3: Hand-code everything or mostly everything

Insert RecordSet (Applies to first two methods)

A recordset is, as the name implies, "a set of records" from a database. However, what is more important to remember is that it is held in memory until it is displayed on the page using dynamic elements.  It is sometimes referred to as a dataset in other server-side technologies like ASP. 

The Add template page is the easiest page template to create because it DOES NOT TYPICALLY require you to create a recordset.  Normally, you INSERT information INTO the database and have no need to retrieve records from it.  However, since we created a table of current departments in the database, we can use a recordset to DYNAMICALLY populate a menu (combo box) with the records from the database so we WILL create a recordset for the Add template page.  You will see later that when you edit the other template pages (i.e., Master Page (index.php), Detail Page, Update Student, Delete Student, or Search Student), you MUST create a recordset.

  1. Open the Server Behaviors Panel  or the Bindings panel and click on the plus sign (+) and choose RecordSet or RecordSet(Query).

    ALTERNATIVELY: You can also click on the Recordset icon on the Data tab in the Insert Bar or select Insert > Data Objects > Recordset.

  2. In the Recordset dialog box that appears, ensure that you are in the Simple mode (Advanced button is displayed when you are in the Simple mode).
  • The Simple mode is used for "simple" query, as the name suggests and can only filter one column in a database.  In the Advanced mode, you can create more "advanced" query and filter multiple columns and perform other actions (e.g., Sort, etc.).
  1. Rename the default recordset name (i.e., Recordset1) to rs_getAllClasses in the Name field in the Recordset dialog box.
  • While you could keep the default name, it is best practice to rename the recordset with a more descriptive name. A common convention is the use the prefix "rs" on the recordset name so that it can be easily recognized in code as a recordset or to make the recordset name very descriptive (e.g., rs_getAllClasses)
  1. Select the table (i.e., Classes) from the Table drop-down menu.
  • If this is the only recordset on the page, most of the options will be automatically populated with the correct information. (i.e., Student_Directory). Dreamweaver will remember the last connection that is used so it will populate the the Connection field with Student_Directory.
  1. Click on the Test button and you should see the data you entered in the previous lesson in the Test SQL Statement dialog box.
  2. Click OK to close the Test  SQL Statement dialog box and then click OK again to close the Recordset dialog box and to create the recordset.

METHOD 1: Use Insert Record Form Wizard Server Behavior

If you want Dreamweaver to do all of the "dirty work" for you, you can use the Insert Record Form Wizard server behavior that creates:

  • the form,
  • form elements,
  • table and
  • write the underlying code.

NOTE: The Insert Record Form Wizard uses the field names (column names) in the database to create the labels and form names.

Insert Server Behavior (Record Insertion Form Wizard)

  1. Press ENTER after the ADD STUDENT title if you have not already and then select Insert > Data Objects > Insert Record > Record Insertion Form Wizard from the menu.

ALTERNATIVELY: You can get to the Record Insertion Form Wizard by clicking on the Data tab in the Insert bar and selecting the Insert Recordicon and then selecting the Record Insertion Form Wizard from the list if this is the first time you are using this menu. Dreamweaver remembers your last selection, so next time you return to this menu you don't have to use the sub-menu, but simply click the icon.

CORRECTION: Tab on screenshot should read add_student.php instead of addEmployee

  1. In the Record Insertion Form dialog box that appears, add or modify the following settings:
    1. Select Data Source (e.g., Student_Directory) from Connection drop down, if necessary.
    2. Select table (e.g., student_directory) from Table: drop-down box.
      NOTE: Typically, an application will have multiple tables listed in alphabetical order. So, it is important to select the correct table from the list.
    3. Type file name to go to (e.g., index.php) in the After inserting, go to: text field or Click on the Browse button and navigate the the index.php page.
      NOTE: This is the page that the browser will go to when the form button is clicked (the form is submitted).  If you leave this field blank, the same page will be displayed again and ready for another record to be inserted.
    4. Select the studentID field and then click the Delete button (-) to delete this field.
      NOTE: The studentID is a primary key and is set to auto increment in the database when a record is created. Typically, you do not want to update it in the database.
    5. (Optional) In the Form fields section, arrange the fields in the order you want them to show in the table from top to bottom using the up/down arrows.
      NOTE: The columns are listed by defaults in the same order they are in the database.
    6. (Optional) Delete any other fields you don't want to include in the table that will be created.
      NOTE: If you inadvertently delete a column that you needed, you can restore it to the list by clicking on the plus (+) sign and reselecting it to add it back to the list.
    7. (Optional) Change combo-word, like GradDate to Graduation Date (with space) for each combo-word field in the Label column by selecting a field and then changing its text in the Label: text field.
      NOTE: Dreamweaver automatically use the same column names in the database as the column labels for the table it will create. This may not always be desirable, so you can change them by adding capital letters and spaces or whatever you want.
    8. Select Classes from the form fields list and change the Display as combo list from Text field to Menu.
      NOTE: Even though a combo box is used on a single item will be sent to the database. So notice that the Classes will still be submitted as Text to the database because that is the default value for Submit as drop-down list.
    9. Click on the Menu Properties button that gets displayed and in the dialog box that is displayed
      1. click on the From database radio button to add dynamic contact to the menu
      2. select getAllClasses from the Recordset drop-down menu.
      3. select className for both Get labels from and Get value from combobox lists
      4. click OK.
    10. Perform the previous two steps for any additional form control that you would like to make dynamic and change the setting accordingly.



    1. Check fields for accuracy and then click OK to close the Record Insertion Form dialog box and to create the table, form, form elements and code needed to insert a record into the database.

      • It is important to check fields because once you click OK you are limited in what you can change if you double-click on the Insert Record behavior in the Server Behaviors panel because the dialog box is totally different. Because additional elements (i.e., form, form elements) has been added to the page, more information about these elements are shown than the previous Insert Record dialog box. It basically shown how the column names in the database are BOUND to the form elements on the page of the same names and how they are submitted (i.e., 'State' Get Value From "FORM.State as Text')



      • If you want to start over, delete the Insert Record behavior in the Server Behaviors panel and the form (which include the table) on the page and insert the Record Insertion Form again.



      • Dreamweaver also add a hidden field (a yellow shield icon at the bottom of the form).

  2. In Design view, select the Insert record button and change the Value to ADD STUDENT in the Properties panel.

Test template page

  1. Save the template page (CTRL+S).
  2. Test the page in a browser by filling out some of the fields with the some bogus information and then click the ADD STUDENT button:



  • The data will be entered into the database when you click the ADD STUDENT button and you will be automatically taken to the index.php page where you can view the newly created record in the table.
  • Notice that on this page you have to know in advance the name of the image you want to insert into the database. You could write an upload image code that will be responsible for not only uploading the name of the image to the database but also saving an image to an images folder somewhere so that it can be reference from the database name. (See Enhancement tab)
  • Open the Databases panel and select the database connected to site (i.e., Student_Directory). Click on the plus sign to open the database and then click on the plus sign again to open Tables.  Right click on the Tables option and select View Data... on the student_directory table.  View data from database to verify record was inserted as the last record into the database and then click OK to close it.
  • While not perfect, this page is a good start. In upcoming lessons, you will learn how to validate the form elements, add security and more to pages you create. (See Enhancement tab)

METHOD 2: Use Insert Record Server Behavior

You could create your own form, form elements and table and then use Insert Record menu item (not the Record Insert Form Wizard) that will RECOGNIZE the form and form elements and create the necessary code to add the fields to the database when the submit button is pressed.

  1. Create your own form, table and then add form elements to table. Give each a descriptive name so that they can be used effectively by the server behavior.
  2. Select Insert > Data Objects > Insert Record > Insert Record from the menu.

ALTERNATIVELY: You can get to the Insert Record server behavior by clicking on the Data tab in the Insert bar and selecting the Insert Record icon from the list if this is the first time you are using this menu. Dreamweaver remembers your last selection, so next time you return to this menu you don't have to use the sub-menu, but simply click the icon.

CORRECTION: Tab on screenshot should read add_student.php instead of addEmployee ans should have Insert Record highlighted.

  1. In the Insert Record dialog box that appears, add or modify the following settings:
    1. Select Data Source (e.g., Student_Directory) from Connection drop down, if necessary.
    2. Select table (e.g., student_directory) from Table: drop-down box.
      NOTE: Typically, an application will have multiple tables listed in alphabetical order. So, it is important to select the correct table from the list.
    3. Type file name to go to (e.g., index.php) in the After inserting, go to: text field or Click on the Browse button and navigate the the index.php page.
      NOTE: This is the page that the browser will go to when the form button is clicked (the form is submitted).  If you leave this field blank, the same page will be displayed again and ready for another record to be inserted.
    1. Check fields for accuracy and then click OK to close the Insert Record dialog box and to create JUST the code needed to insert a record into the database.

      CORRECTION: Fields needs to be updated to reflect new database, etc.

      NOTE: Notice that the Submit Values from combo box is grayed out and not editable because there is only one form on the page. Had there been more than one, you would be able to select from it.

      • If you need make changes, double-click on the Insert Record behavior in the Server Behaviors panel to open this dialog box again.



      • Dreamweaver also add a hidden field (a yellow shield icon at the bottom of the form).

Add Dynamic Menu/List

Unlike the Record Insertion Form wizard that does practically everything for you, the Insert Record server behavior while does create most of the code for you, it does not include code to make the menu/list dynamic. You have to do that with another server behavior.

  1. Click on the menu/list form element to select it so that you can apply a server behavior to it.
  2. In the Insert bar Data tab, click on the Dynamic Data icon and select the Dynamic Select List.
  3. In the Dynamic List/Menu dialog box that appears, select the following options:
    1. Option from recordset: getDeptName
    2. Values: DeptName
    3. Labels: DeptName
      NOTE: Notice the the Menu option was filled in correctly automatically because you first select the menu in the form. Also, note that you could add some static menu option as well.

 

 


Test template page

  1. Save the template page (CTRL+S).
  2. Test the page in a browser by filling out some of the fields with the some bogus information and then click the ADD STUDENT button:



  • The data will be entered into the database when you click the ADD STUDENT button and you will be automatically taken to the index.php page where you can view the newly created record in the table.
  • Notice that on this page you have to know in advance the name of the image you want to insert into the database. You could write an upload image code that will be responsible for not only uploading the name of the image to the database but also saving an image to an images folder somewhere so that it can be reference from the database name. (See Enhancements tab)
  • Open the Databases panel and select the database connected to site (i.e., Student_Directory). Click on the plus sign to open the database and then click on the plus sign again to open Tables.  Right click on the Tables option and select View Data... on the student_directory table.  View data from database to verify record was inserted as the last record into the database and then click OK to close it.
  • While not perfect, this page is a good start. In upcoming lessons, you will learn how to validate the form elements, add security and more to pages you create. (See Enhancements tab)

Add Actual Image to Table (Applys to first two methods)

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. Delete the image dynamic field in the table and leave the cursor in that cell in the table.
  2. Select File > Insert Image and in the Image dialog box select the Data Source dialog box.
  3. In the Data Source dialog box, select images from the list.

    NOTE: This is a dynamic text instead of a static reference to the image from the database.

  4. Add the following text to the front of the dynamic text: images/

    NOTE: This is a static reference to the image folder where the images are.

  5. Click the OK button.
  6. Test again in a browser or with Dreamweaver Live view.

    NOTE: You should see the picture instead of the literal text.

METHOD 3: Hand-code everything or mostly everything

You could write all or most of the code yourself, if you want to get a better understanding of how everything works. It is easier to have Dreamweaver create tables than to hand-code them.

Create Form, Table and Form elements

  1. Open the template for the add employee page created in tab one.
  2. Create your own form, table and form elements (i.e., input field, menu/list and submit button).

Connect to database

  1. Write the following server-side include code to connect the database file created in the How To Connect to a Database (Part 2) tutorial

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

    NOTE: You only have to connect to the database once for all recordsets and queries that will be needed later.
  2. Save file.

Create a recordset to bind to menu/list element

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. Write the following code after the code you created earlier to create a recordset to populate the menu/list element. Comments are optional but are written to explain code

    <?php
    // Store a reference of the database in this variable
    $database = "employeedirectory2";
    // Select the database you need using the mysql_select_db function (i.e., employeeDirectory)
    mysql_select_db($database);
    // 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.

Populate Combo Box

  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

  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);
    ?>


    NOTE:  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 close the recordset to release it from memory. Otherwise, the recordset may linger around when it is no longer need. The code was 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.

  2. Save file.

Insert Record Code

  1. Write the following code to perform the five actions listed below.
    NOTE: 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 Statemetn in a variable
    • Execute query

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


      //Select Database that has already been connected to
      mysql_select_db('employeedirectory2');

      //Set variables for each database bound form elements. Also, 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.

      $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 Statemetn 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);

      }
      ?>


      NOTE: Notice that a small "php" icon shown in the Design view that represents this code.
      ULTRA GEEK ONLY: Instead of creating individually variable, you could have added the dynamic field directly to the INSERT query. However, it would make the line hard to read and more error proned.

  2. Add the following code to 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">

  3. Save file and test page by entering some bogus data in the text fields and then clicking on the Add Employee button.
    CHECKPOINT: If you check the database, you should see that a record was added to the database based on your inputs.





 

 

EDITING MASTER/DETAIL TEMPLATES FOR EXISTING STUDENTS

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 (student) records to the database using the add_student.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. Creating a Master/Detail page set can be accomplished either by creating the pages yourself and using a series of server behaviors or using the Master Detail Page Set server behavior that will do most of the work for you.  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 STUDENT 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., Student Detail, Update Student and Delete Student) that we will be editing later to perform the corresponding operations (view, update, and delete, respectively).

Edit Master/Detail Template Pages

You willl need to use two pages that you created earlier to implement the Master/Detail pages.

Create a Recordset


  1. Return to the index.php page and, open the Server Behaviors Panel  or the Bindings panel and click on the plus sign (+) and choose RecordSet or RecordSet(Query).

    ALTERNATIVELY: You can also click on the Recordset icon on the Data tab in the Insert Bar or select Insert > Data Objects > Recordset. Or, if you forget to create a recordset when you select the Master Detail Page Set behaviou later from the menu, you will be prompted to create a recordset at that time. This makes for four ways to get to the Recordset dialog box.

  2. In the Recordset dialog box that appears, ensure that you are in the Simple mode (Advanced button is displayed when you are in the Simple mode).
  • The Simple mode is used for "simple" query, as the name suggests and can only filter one column in a database.  In the Advanced mode, you can create more "advanced" query and filter multiple columns and perform other actions (i.e., Sort, etc.).
  1. Rename the default recordset name (i.e., Recordset1) to rsMaster in the Name field in the Recordset dialog box.
  • While you could keep the default name, it is best practice to rename the recordset with a more descriptive name. A common convention is the use the prefix "rs" on the recordset name so that it can be easily recognized in code as a recordset.
  1. Select the table (i.e., student_directory) from the Table drop-down menu.
  • Since this is the only recordset on the page, most of the options will be automatically populated with the correct information. (i.e., Student_Directory). Dreamweaver will remember the last connection that is used so it will populate the the Connection field with Student_Directory.
  1. Click on the Test button and you should see the data you entered in the previous lesson in the Test SQL Statement dialog box.
  2. Click OK to close the Test  SQL Statement dialog box and then click OK again to close the Recordset dialog box and to create the recordset.

NOTES:

  • You need to choose all of the columns to create this recordset because it will be used later when you create the Master/Detail Page Set.
  • If you KNOW you will not be needing all of the columns from the database, you could limited the number of columns by selecting the Selected radio button option and then press the CTRL key and the columns you only intended to use later in the Master/Detail Page Set for the list. This will make the page more efficient because you are only retrieving only the columns you need from the database.
  • The Columns section has two radio button options. By default, All is selected so the Columns list below will be grayed out. If you choose the Selected radio button option, you have the option to choose which columns you want to choose. If you want to choose multiple columns, hold down the CTRL/Command key as you click on the column list items.
  • The Filter section allows you to limit the number of records from the database. The default is None which means that ALL records will be returned from the database. Later, you will see how to use this section that typically uses the WHERE SQL clause.
  • The Sort section allows you to sort the columns that get returned from the database in an ascending or descending alphabetical order. It uses the ORDER BY SQL clause.

    NOTE: Screenshot should read student_directory instead of test_prefixstudents


  • Dreamweaver wrote the following SQL code in Code view when you clicked the OK button:  $query_rsMaster = "SELECT * FROM student_directory";
  • You can review or update a Recordset by double-clicking on the recordset name in the Binding or Server Behavior panel.

Insert Master Detail Page Set Server Behavior To Create The Master and Detail Template Pages

CAUTION: Dreamweaver can create a lot of code when you insert server behaviors. Avoid deleting code in the Code View. Instead delete a server behavior from the Server Behavior panel by clicking on the Delete button (-) and then delete any table, form or text that the server behavior created.

  1. Position the cursor on the line below STUDENT MASTER PAGE title and choose Insert > Data Object > Master Detail Page Set from the menu and add the following information highlighted in yellow in the screenshot in the master/detail sections.  Delete all fields in the Master page fields except Full_Name by clicking on the Delete icon (-).
  2. Select Full_Name from the Link to detail from drop-down menu.
  3. In the Pass unique key drop-down box, ensure that the studentID is selected.
  4. In the Show section, ensure that the first radio button is selected. This will allow the Server Behavior to insert a Navigation bar at the bottom of the table so that you can "page" through a set number of records at a time, if you need to (In this case, 10).
  • If you select the All records radio button, you would get all of the record set and no navigations links.
  1. Type detail_page.php in the Detail page name field.
  • This is the page that will receive the studentID information so that it can be populated together.
  1. In the Detail page fields, move the column Full_Name to the top of the List, if necessary.  Also,
  • Dreamweaver will create a table and place the text field in the order they are listed in this dialog box (not the database columns) if you reordered them.
  1. (Optional) Delete the studentID in the Detail page fields by clicking on the delete button (-).
  • In the real world you would not want to show the studentID in a table. However, for this lesson, it is not a problem.

    CORRECTION: Should read studentID instead of student_ID.


  1. Click OK to close the Insert Master-Detail Page Set dialog box.
  • Dreamweaver automatically created the table in Design View and the PHP code in Code View on the master page automatically.



  • Open the Server Behaviors panel and notice that Dreamweaver created over a dozen behaviors for you. How cool is that!!!  Each of the small gray tabs in the screenshot represents a different server behavior (i.e., Repeat, Show If...).



    FOR GEEKS ONLY or THOSE INSPIRING TO BE GEEKS >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    ALTERNATIVELY
    : You could create the Student Master Page table yourself if you want to understand how to add the Repeat Region behavior by following the following steps:
    • Create a one row and one column table with the top row set as a Header.
    • Add the word  FullName in the first row of column 1.
    • Drag-n-drop the Full_Name from the Bindings Panel to the second row.
    • Place the cursor in the second row and select <tr> from the Tag Chooser to select to entire second row.
    • In the Server Behaviors panel, select the Repeat Region server behavior.
    • In the Repeat Region dialog box, leave the default as is and press OK to wrap a loop about the <tr> in the table to repeat the rows based on records in the database.

    Test Page

  1. Perform a File > Save All from the menu and then test page by opening the index.php page in a browser.  Before clicking on link, hover the mouse over one of the link and see the query string in the status bar.
  • You should see the names in the table and when you click on one, it should take you to the DETAIL page. However, the table is at the bottom of the page. To resolve this problem, select the entire table code in Code view by highlighting it and then right-click to Cut it to the clipboard. Location the code (<h1>STUDENT DETAIL PAGE:</h1>) and paste  it below the line. This is a BUG in Dreamweaver..
  1. Save file and test again. You should now see the table on the Detail page when a link is clicked.
  • Because you specified Full_Name as the Link to detail from in the Recordset dialog box and the Pass unique key as studentID, Dreamweaver created a link with a query string attached to the end of it. This is how the studentID value get passed from the index.php template page to the detail_page.php template page.

Insert Picture Instead of Text



Notice that the reference to the image name from the database column images is displayed in the table instead of the table itself. In the next series of steps, dynamic code will be added to reference images from the images folder.
  1. In the detail_page.php, click inside the last row of the table and then then choose the <tr> tag from the Tag Choooser. Then click on the Merge selected cell using spans icon in the Properties panel to merge the cells into one cell.
  2. Then delete the static and dynamic text in that cell.
  3. Select Insert > Image from the menu and add/select the following highlighted info
  • Anytime you want to add a dynamic image instead of a static image, you can use the Data sources radio button option.

    CORRECTION: Recordset name need to be update to rs_Detail or something like this.

  1. Add "images/" to the front of the path.

    NOTE: Notice there are two parts of the URL. The first part is STATIC (images/) and the second part is DYNAMIC (<?php echo $row_DetailRS1['Image'];?>

  2. and then click OK.
  3. Save file and test page from the index.php by clicking on a link.  You should now see a picture instead of a text reference on the DETAIL page.

Create Cells for VIEW, EDIT AND DELETE Operations

  1. In the index.php template page, insert the cursor in the first column of the table and then right clicking inside and select Table > Insert Rows or Columns... 
  1. In the Insert Rows or Columns dialog box that appears, select or enter the following highlighted options and then click OK.
  • Dreamweaver will create 3 new columns after the current column.



    TIP: If it is hard to insert the cursor in a cell, select ALT+F6 or click on the Expanded button in the Layout tab or select View > Table Mode > Expanded Table to expanded to table padding. Repeat this step again when you are finished.
  1. Add the following static text in the second row:



  2. Select the last three cells in the first row and then click on the Merge cells using spans in the Properties panel to merge these cells into one (see previous step).
  3. Select the word VIEW and then in the Properties panel click on the folder icon to open the Select File dialog box.
  4. In the Select File dialog box that appears, select the student_detail.php from the list and then click on the Parameters... button at the bottom of that dialog box.
  5. In the Parameters dialog box that appears, MANUALLY type the word recordID in the field below the Name column. Then, click on the field below the Value column and then click the lightning bolt icon to open up the Dynamic Data dialog box.
  6. In the Dynamic Data dialog box that appears, select studentID Dynamic Text field from the list.
  7. Click the OK button three times to close all of the dialog boxes and to create the query string on the VIEW link.
  8. Create two additional links using steps similar to to steps 5-10 but use the words EDIT and DELETE and link them to edit_student.php and delete_student.php, respectively.
  • NOTE: You will use these two columns additional links later when you create the edit and delete pages later.



  • It is important to realize that the recordID variable is passed to the other page via a query string (i.e., <a href="student_detail.php?recordID=<?php echo $row_rsMaster['studentID']; ?>">VIEW</a>)
  1. Select the Dynamic Text {rsMaster.Full_Name} in the second row of the first column and then select the link field in the Properties panel and then press Delete to delete the link and its query string.
  • Since you added a query to the VIEW word, you no longer need a link on the Full_name Dynamic Text field. Notice only the link is deleted and not the dynamic varialbe.S
  1. Open index_.php template page again in a browser.  Hover over several VIEW words to see the query strings in the status bar at the bottom of the browser.  Then, click on any VIEW link to go the the detail_page.php template page.

    (Optional) Displaying message if no record is found
  • 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 between the MASTER PAGE and the table below it and type NO STUDENT RECORD FOUND.
  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 the Server Behavior panel, click on the plus sign and select Show Region > Show If Recordset is Empty behavior from the menu.
  4. In the dialog box that appears, click OK.
  • Since there is only one recordset on this page already, the correct recordset will automatically be selected.
  1. Click inside of the table and then select the <table> tag in the Tag Chooser to ensure the whole table is selected.
  2. In the Server Behavior panel, click on the plus sign and select Show Region > Show If Recordset is Not Empty behavior from the menu.
  3. In the dialog box that appears, click OK.

    CORRECTION: Screenshot should show VIEW, EDIT, AND DELETE and not VIEW, EDIT AND SAVE


  4. Save template page and test in browse.
  • If there is no record in the database, the table will be hidden and the message will be shown only.
  • To test this function, create a new table (student_directory_empty) in the same database with a studentID as the primary key field and one additional column and then re-assign this table in the Server Behaviors panel (rsMaster) for the recordset and then test the page.  CAUTION: Don't forget to set it back after you check this functionality.


EDITING UPDATE TEMPLATE FOR EXISTING STUDENTS

Now that HR can insert new student records and view them, you can now turn your attention on how to update (edit) an existing student 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 dragged and dropped into the text fields in the form so that the existing fields can be pre-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 pre-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 STUDENT 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_student.php) that will be used as well to pass data to this page.

Using the Record Update Form Wizard server behavior, once the required fields are changed and when the UPDATE STUDENT RECORD button is clicked, a query is used to update the data from the fields to the database and redirect the user back to the STUDENT MASTER PAGE (index.php) so you can see that the record was indeed updated.

Edit Template Page (Update Student)

  • Open the edit_student.php file that was created earlier.

    Insert RecordSet (rsEdit) to pre-populate text fields from Student_Directory table

    Unlike the Add page that required the recordset with no filter (WHERE clause), the EDIT page require a WHERE filter so that that the record that is returned is based on one record from the studentID primary key. However, since it is not displayed on the page, it will be only tested in the RecordSet dialog box.
  1. Open the Server Behaviors panel or the Bindings panel and click on the plus sign (+) and choose RecordSet or RecordSet(Query).
  • Before we can edit a record, a recordset is needed to create and store the result of an SQL query of a single record.
  1. In the Recordset dialog box:
    1. Ensure Advanced... button is shown. Otherwise, click on it to go to Simple mode.
    2. Name the recordset rsEdit and choose the Student_Directory as the Data source and the student_directory as the Table type.
    3. Choose the All radio button in the Columns: area.
    4. Select studentID and, if necessary, the equal sign ( = ) in the Filter combo box on the first row and then select URL Parameter and recordID on the second row
    5. Click the Test button.

      Note: Screenshot shown test_prefixstudents instead of student_directory.

      NOTE: Notice that the studentID is equal the a URL Parameter that is set to recordID.

  2. In the Please Provide Test Value dialog box that appears, enter 1 (or any other studentID value if any record have been deleted from the database table) in the Test Value field.  Click OK to close this dialog box.

  • You should see the record in the database shown in the Test SQL dialog box.
  • Since you created a filter, you will be prompted to enter a test value if you click the Test button. Normally, if there is no filter, no test value is needed and ALL of the records from the database will be displayed.
  • If you were to test page DIRECTLY, you would see that the form elements are empty. This is because the studentID is not passed from another page (i.e., student master or the search page). Even if you fill out the form and click on the UPDATE STUDENT RECORD button, no record would be updated in the database. Later, you will see how to automatically passed a studentID to this page.
  1. Click OK to close the Recordset dialog box and to create the recordset.
  • Notice in the Bindings Panel, Dreamweaver adds the Recordset (rsEdit). Also, note the recordset code added in Code View.
  • The Filter section basically says "Select studentID and set its value equal to the URL parameter (from query string from master page) value.
  1. Save template page.

A second recordset is need to populate the Classes combo box dynamically from the Classes table in the Student_Directory database.

Insert RecordSet to prepopulate combo box from Classes table

  1. Open the Server Behaviors Panel  or the Bindings panel and click on the plus sign (+) and choose RecordSet or RecordSet(Query).

    ALTERNATIVELY: You can also click on the Recordset icon on the Data tab in the Insert Bar or select Insert > Data Objects > Recordset.

  2. In the Recordset dialog box that appears, ensure that you are in the Simple mode (Advanced button is displayed when you are in the Simple mode).
  • The Simple mode is used for "simple" query, as the name suggests and can only filter one column in a database.  In the Advanced mode, you can create more "advanced" query and filter multiple columns and perform other actions (i.e., Sort, etc.).
  1. Rename the default recordset name (i.e., Recordset1) to getAllClasses in the Name field in the Recordset dialog box.
  • While you could keep the default name, it is best practice to rename the recordset with a more descriptive name. A common convention is the use the prefix "rs" on the recordset name so that it can be easily recognized in code as a recordset. or to make the recordset name very descriptive (i.e., getAllClasses)
  1. Select the table (i.e., Classes) from the Table drop-down menu.
  • If this is the only recordset on the page, most of the options will be automatically populated with the correct information. (i.e., Student_Directory). Dreamweaver will remember the last connection that is used so it will populate the the Connection field with Student_Directory.
  1. Click on the Test button and you should see the data you entered in the previous lesson in the Test SQL Statement dialog box.
  2. Click OK to close the Test  SQL Statement dialog box and then click OK again to close the Recordset dialog box and to create the recordset.

ALTERNATIVELY: Since this is the same recordset used on the Add Student page to prepopulate the combo box, you could open that page and right-click on the the getAllClasses recordset in the Bindings panel and then select Copy. Then, return to this page and right-click in the Binding panel and select Paste.

Insert Server Behavior (Record Update Form Wizard)

Like the ADD STUDENT page that uses the Record Insertion Form Wizard to create the table and code to ADD a record to the database.  The EDIT STUDENT page will use a similar behavior called Record Update Form Wizard to UPDATE a record in the database. The different is that  the EDIT STUDENT template page uses a recordset to get data for the form element's fields and then update any fields that has been changed when the UPDATE STUDENT button is clicked.

  1. Position cursor on the line below EDIT STUDENT title and choose Insert > Data Object > Update Record  > Record Update Form Wizard from the menu.
  • Since a recordset was created earlier (rsEdit) on this page, most of the values are ALREADY properly selected (i.e., Student_Directory, student_directory, etc.).  This is a good reason why you should create the recordset BEFORE you use any wizard.
  1. In the Record Update Form dialog box, add or modify the following settings:
    1. Type file name to go to (e.g., index.php) in the After inserting, go to: text field.
    2. Select the studentID from the Form field and then click the Delete button (-) to delete this field.
      NOTE: The studentID is a primary key and is set to auto increment in the database when a record is created. Typically, you do not want to display it.
    3. (Optional) In the Form fields section, arrange the fields in the order you want them to show in the table from top to bottom using the up/down arrows. For example, you can select the Department field and click the up arrow to move it above the Title field.
    4. (Optional) Delete any of the fields you don't want to include in the table that will be created.
    5. (Optional) Change the combo-word, like Zip_Code to Zip Code (with space) for each combo-word field in the Label column by selecting a field and then changing its text in the Label: text field.
      NOTE: Dreamweaver automatically use the same column names in the database as the column labels for the table it will create. This may not always be desirable, so you can change it to add capital letters and spaces.

      CORRECTION: Screenshot database table will be different.


      NOTE: If you select one of the Text column from the Form fields list,  you will see the Default value equal to text field filled in automatically. This uses the rsEdit recordset result to display the record's existing value in the form.

    6. Double-check to ensure you have the correctly options selected.

      NOTE: Once you click OK, you will not be able to view this dialog box again.  Instead, you will be presented with the Update Record dialog box which has limited editing.

    7. Click OK to close the Menu Properties dialog box and then click OK again to close the Record Update Form dialog box to create the table, etc.

      NOTE: Notice that Dreamweaver created Text Fields as well as the Update Record button.  To see the Update Record server behavior, open the Server Behaviors panel (Window > Server Behaviors) and double-click on the Update Record option.  However, you will notice that the information is displayed differently. In additional, notice that the fields are PRE-POPULATED with dynamic text fields.  This is not the case, with the Record Insertion Form behavior.



      NOTE: Dreamweaver will create the following table, form, form elements and two hidden fields. The studentID hidden field is used to pass the primary key to the update server behavior script. If you cannot see the two hidden field icons, ensure there is a check mark next to the Invisible Element when you select View > Visual Aids> Invisible Fields from the menu. Also, ensure the Invisible Element check box is select when you select the Invisible Elements category when you select Edit > Preferences...



    8. Select the Update record button and change the Value to UPDATE STUDENT RECORD in the Properties panel.
    9. (Optional) If you did not change the combo-word names when inserting the Update Record Form behavior, you can still change the static text in the fist column for combo words by adding a space between them (i.e., Zip_Code to Zip Code)
  2. Save page (CTRL+S)

Test Pages and view database

  1. TEST: To manually check this page, type it in the browser with a recordID: http://localhost/StudentDirectory_PHP/edit_student.php?recordID=2 and edit a few fields and then click on the UPDATE STUDENT RECORD button.  You should be redirected to the index.php template page.  CAUTION: Be careful to not add studentID instead of recordID.
  2. To check to see if the record was updated, open the Databases panel and expand the Student_Directory database and then expand the Tables link.  Next, right-click on the student_directory link and select View Data... from the menu to open the View Data dialog box to see if the change you made took place.
  3. Now, go to the index.php page and open it in a browser and select a EDIT from one of the student in the list.  You should be taken to the EDIT STUDENT page. Make some changes to a few of the fields and then click the UPDATE STUDENT RECORD button again.
  • You should be taken back to the index.php page where you can now click on the SAME student and then the VIEW link to see changes on the STUDENT DETAIL page.

ALTERNATIVELY: The Update page is basically the same as the ADD page, so you could save the Add page as a new Update page and change the following settings:

  • Add a recordset (rsEdit) and set its filter to read studentID (URL Parameter) = recordID.
  • Add another recordset (getAllClasses) to return all records from the Classes table.
  • Rename button from ADD STUDENT to UPDATE STUDENT by clicking on the button and changing the Value textfield in the properties panels.
  • Drag and drop the corresponding Dynamic Text Fields into their corresponding text fields on the page from the Bindings panel. Ensure they are INSIDE of the text field.
  • Add a hidden field to hold the value of the studentID (<input name="studentID" type="hidden" id="studentID" value="<?php echo $row_rsUpdate['studentID']; ?>") See screenshot above.
  • Delete Add server behavior in the server behaviors panel  and replace it with an update record behavior from the Update Record menu (i.e., Insert > Data Obect Update Record). DO NOT use the Update Record Wizard option.
  • Update title, heading and any text on the page to reflect update instead of adding.
  • Save and test from index.php page.

EDITING DELETE TEMPLATE TO DELETE AN EXISTING STUDENT

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.

You will be using the Delete Record server behavior to delete a student from the database. The Delete Record server behavior can only delete one record at a time.  The best way to delete a record from a database is to target its primary key field (column) using a variety of options (i.e., form, URL, session variable; cookie or any other data source).  In this case, you will use the studentID field as a URL variable.

Edit Delete Student Template Page


  1. Open the delete_student.php file.

NOTE: Since this page will not be displayed, you don't have to press ENTER after the title like you did for the ADD / UPDATE pages.

Insert RecordSet

Since we are not retrieving any data from the database, there is no need for a recordset.

Insert Server Behavior (Delete Record)

  1. Select Insert > Data Objects > Delete Record from the menu and filled out or select the following highlighted fields and then click the OK button.
  • Dreamweaver created a Delete Record behavior. You can open it up by double-clicking on the Delete Record option in the Server Behaviors panel.

    NOTE: The screenshot read test_prefixstudents instead of student_directory.

  1. Save file and then test delete operation by going to the index.php page and selecting DELETE from one of the student in the list.
  • The record will be delete from the database and you will be redirected back to the index.php page where you can confirmed that the record was indeed delete from the database.


(Optional) Add JavaScript to DELETE link

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. 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.
  2. Add the following highlighted code to the end of the starting <a> tag of the DELETE button in Code view:

    <a href="delete_student.php?recordID=<?php echo $row_rsMaster['studentID']; ?>" onclick ="return confirm('Are you sure you want to delete this student 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 student 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.
  • You will automatically be redirected back to the index.php page where you can confirmed that that record was indeed deleted from the database. You may have to click on the Next link to go to another page if necessary.

EDITING SEARCH TEMPLATE FOR EXISTING STUDENTS (SIMPLE)

After student records has been added to the database, a search page can be created that contains a form that will collect  a SINGLE search criteria (full_name) and compare (matches) that criteria with record(s) in the database. A result table will also be shown for each student record that the criteria matches. A link will also be created so that an HR representative can update the student data.
NOTE: Unlike the other operation (update, delete and insert), there is no Create Search Page wizard so we will have to do most things manually.

 Edit  Template Page

  1. Open the index.php page and add a form about the table and then add text field and button to the form.
  2. Give the search field an ID name of search and a value of SEARCH STUDENT for the button in the Properties panel.
    • This is an important step that will be used later.
  3. Click anywhere inside the form (denoted by a red dash border) and in the Properties panel type search_student.php in the Action text field.
    • This is the page the file will go to when the search button is clicked.
  4. Open the search_student.php file that was created earlier.

 Create  a Recordset (rsSearch)

  1. Open the Server Behaviors panel or the Bindings panel and click on the plus sign (+) and choose RecordSet or RecordSet(Query).
  • Before search result can be displayed, a recordset is needed to create to store the result of an SQL query.
  1. In the Recordset dialog box, set the recordset name (e.g., rsSearch) and choose the data source (e.g., Student_Directory) and then select the student_directory table. Add a filter that will filter by full_name that contains from a Form Variable name search.
    NOTE: Remember we gave the search text field a name of search. Unlike, the other processes (i.e., Edit/Delete) that use a URL parameter that was passed along a query string to the next page, the search process is using a form variable that is being passed from the index.php page. Also, unlike the other filter where we used the equal sign (=), the search criteria here uses the CONTAINS key word instead. So instead of saying the value the EXACTLY MATCH, we are saying the value CONTAINS the characters that were typed into the search field.
  2. On the search_student.php page, add a dynamic table from the Data Tab > Dynamic Table and select the rsSearch recordset.

EDITING SEARCH TEMPLATE FOR EXISTING STUDENTS (ADVANCED)

After student 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 student record that the criteria matches. A link will also be created so that an HR representative can update the student data.
NOTE: Unlike the other operation (update, delete and insert), there is no Create Search Page wizard so we will have to do most things manually.

 Create  Template Page (Search Student)

  1. Select File > New... from the menu and then select Page from Template. Ensure the current site is  (e.g., Student Directory PHP) and the current  Template (e.g., student_directory_template) is selected and then click the Create button.
  2. Add Search Student Page to the Title text field (e.g., Student Directory: Search For Student(s) Page) and then save the file with a descriptive name (e.g., search_student.php).
  3. Select the Heading in the mainContent area (Instructions) and type SEARCH FOR STUDENT(S)  to replace it and then press the ENTER key.
  4. Press ENTER again to create a new line and then select Insert > Form > Form and then immediately give the form a name (e.g., formSearch)  in the Form ID field in the Properties panel. Also, type search_student.php in the Action field.
  • 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 column table with a width of 300pxSet border thickness, cell padding and cell spacing to to 1 and the border thickness to 0 and then click OK.



  2. Select the <table> from the Tag Chooser and choose Center from Align combo box to center the table within the content area.



  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., full_Name) 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 second row, insert a button by select Insert > Form > Button. Give the button an ID (e.g., search_Button) but not a Label and then click OK.

    ALTERNATIVELY: You could open the Form tab and use the corresponding TextField and Button icons 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 STUDENT.
  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.



    Insert RecordSet (rsSearch)

  10. Open the Server Behaviors panel or the Bindings panel and click on the plus sign (+) and choose RecordSet or RecordSet(Query).
  • Before search result can be displayed, a recordset is needed to create to store the result of an SQL query.
  1. In the Recordset dialog box, set the recordset name (e.g., rsSearch) and choose the data source (e.g., Student_Directory) and then select the student_directory table.

    CORRECTION: Screenshot show test_prefiixstudents instead of student_directory


  2. Click the Advanced... button and in the Database Items field click the plus sign (+) next to the Tables and then click the plus sign (+) next to the student_directory to display the table's fields. Select the studentID field and click the SELECT button.
  • Notice Dreamweaver update the SQL from SELECT * FROM student_directory  to SELECT student_directory.studentID
  1. Repeat the previous step to add Full_Name, City and State to the query by selecting each field and then clicking the SELECT button for each and then click OK to close the RecordSet dialog box.
  • Again, notice how Dreamweaver is writing the SQL code for you.

  • Dreamweaver automatically wrote the recordset code at the top of the page even before the <html> and <DOCTYPE> tags (See Code View). This code basically says "Select studentID, Full_Name, City and State from the student_directory table and store it in memory (rsSearch)."
    IMPORTANT NOTE: ANYTIME you need to display information from a database, you have need of creating a recordset.
  1. Copy and paste the following statement BELOW the current SQL in the Recordset dialog box:

    WHERE student_directory.Full_Name LIKE '%$_POST[full_Name]%'
    AND student_directory.City LIKE '%$_POST[city]%'
    AND student_directory.State LIKE '%$_POST[state]%'";


    CAUTION: Notice the SINGLE QUOTES and the use of the % at the end of the statements.

    Display results of search

  2. Position the cursor to the right of the STUDENT SEARCH TABLE and press the ENTER key TWICE to create two new lines.
  3. Select Insert > Table to insert a table with 2 rows and 5 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.
  4. Select the <table> from the Tag Chooser and choose Center from Align combo box to center the table within the content area.
  5. On the top row, type the following static text starting with the top-left cell and leaving the last cell empty: ID, Full Name, City, and State. (See screenshot below)
  6. Click inside of the table and then click on the small arrow about each column and select the Clear Column Width for each column.
  • Clearing the Clomn Width allows that text within the cells to fix better.
  1. Select Window > Bindings to open the Bindings Panel.  Click the plus sign (+) next to the rsSearch recordset to display the fields. Drag the studentID and drop it in the lower-left cell under the ID header cell.
  2. Repeat the previous step for Full Name, City, and State  but under their corresponding heading cell.
  3. In the bottom-right cell, type EDIT.



  4. Click anywhere inside a cell on the second row and the choose <tr> from the Tag Chooser to select the entire row.  Then open the Server Behaviors Panel and click the plus sign (+) and choose Repeat Region from the menu.
  5. In the Repeat Region dialog box, select rsSearch recordset and have the page display all records and then click OK to close the dialog box.


  • Notice Dreamweaver wrap a set of PHP do/while loop around the table row and displayed a small Repeat tab on top of the row  (not shown)



  1. Select the EDIT text and type edit_student.php in the Link field in the Properties panel.
  2. Click on the folder icon next to the Link field and In Select File dialog box that appears, click on the Parameters... button.
  3. In the Parameters dialog box that appears, manually type recordID as the parameter for the Name  field.
  4. Click in the field below the Value column and then click on the lightning bolt icon and choose studentID in the Dynamic Data dialog box.
  5. Click OK three times to close Dynamic Data, Parameter and Select File dialog boxes.
  • Notice the fields that get updated as you clicked OK.
  • ALTERNATIVE: Notice this is the same set of steps of use it the EDIT link on the index.php page. You could have copied the link from that page and used it on this page and not have to do steps 27-31 above.


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

    Add message if no record is found

  3. Place the cursor to the right of the last table and press the ENTER key twice.
  4. Type NO STUDENT WAS FOUND
  5. Select the message and then click on the <p> in the Tag Chooser to ensure all of the text is selected and click the Bold icon in the Properties panel.
  6. In the Server Behaviors panel, click on the plus (+) and select Show If Recordset Is Empty and press the OK button when the dialog appears.
  7. Select the last table and then click on the <table> in the Tag Chooser to ensure that the whole table is selected.
  8. In the Server Behaviors panel, click on the plus (+) and select Show If Recordset Is Not Empty and press the OK button when the dialog appears.
  9. Test page again.

OPTIONAL ENHANCEMENTS THAT CAN BE ADDED

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

  1. If no picture is available, provide an avatar icon
  2. Upload image instead of having images in image folder
  3. Add a dynamic year function to the copyright notice
  4. Validate form elements on client side
  5. Convert table to list and and add jQueryMobile functionality

1. 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 Student form page, there will be a broken link on both the index.php (home page) and 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.

Index.php (Home Page)

  1. Add a default avator and name it photo_not_available_large.png to the images folder that is the same size as the images of the students.
  2. Open the index.php page and click on the image to select it.
  3. (Optional) In code view add space before and after the selected code so that you can see the changes that will be made better.
  4. Delete the selected code below:

    <img src="images/<?php echo $row_rsMaster['PHOTO']; ?>" width="80" height="80">

  5. Replace the deleted code with this code:

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


    NOTE: This code is a simple "if" statement that shows the default 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.

  6. Save file and test code by adding another employee without specifying an image.

    NOTE: You should see the default avatar on the home page when there is not an image in the images folder for that employee.

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_DetailRS1['PHOTO']; ?>" width="307" height="240" style="border: 2px solid orange">

  4. Replace the deleted code with this code:

    <?php
    $source_img = $row_DetailRS1['PHOTO'];
    if($source_img == "")
    {
    echo "<img src='images/photo_not_available_large.png' width='307' height='240' style='border: 3px solid orange'/>";
    }
    else
    {
    echo "<img src='images/$source_img' width='307' height='240'style='border: 3px solid orange'/>";
    }
    ?>


    NOTE: This code is a simple "if" statement that shows the default 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.

    NOTE: You should see the default avatar on the home page AND when you click on an employee to go to the detail page when there is not an image in the images folder for that employee.

2. 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="mutlpart/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 "thefile":

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

    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 hiden 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:

3. Add a dynamic year function to the copyright notice

While this enhancement is not required, it addsa 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 <h4>  or <p> element text with the following code:

    <h4>&copy; 2012 - <?php echo date("Y")?> by RMCS. All rights reserved.</h4> or
    <p>&copy; 2012 - <?php echo date("Y")?> by RMCS. All rights reserved.</p>

    NOTE: The <?php echo date("Y")?> code is a simply PHP 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.