PHP CMS with jQueryMobile

The Master Page / Detail Page set is a common methodology that is done with many applications. The concept is that the Master Page has only the "vital few" information about a selected record and the Detail Page has "detail" information about the selected record on the same page using a conditional statement or on another page. To simplify matter, we will create it on another page.

After the Master Page is created, it is used to pass an ID to the Detail Page to show more DETAIL information about the selected item from the Master Page.

The Employee Master Page (master_page.php) is used to show a SMALL AMOUNT of information from the database about EACH record that is retrieved from the database. The Employee Detail Page will be used to show a LARGE AMOUNT of information from the database about the SELECTED record from the Employee Master Page. 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 usually returns a set of records which could include only one record in the set.

The Employee Master page will ALSO be used to pass a unique ID to the other pages (e.g., Edit Employee, Delete Employee, and Search) that we will be editing later to perform the corresponding operations (update, delete, and select, respectively).

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

PART A: CREATE STATIC CONTENT
- Create Static Button
- Create Static ListView
PART B: ADD DYNAMIC SCRIPT
- Add SQL SELECT Recordset Script
PART C: REPLACE STATIC CONTENT WITH DYNAMIC CONTENT

Make Full Name and Title Dynamic
Make Query String Dynamic

OPTION 1: Create and Make Images Dynamic
OPTION 2: Create and Make Combo Box Dynamic
OPTION 3: Display Total Records
OPTION 4: Add A Dynamic Time And Date
OPTION 5: Provide An Avatar If No Image Is Available
OPTION 6: Create a Pagination Scheme
OPTION 7: Display Message If No Record Is Found

Master Page

We will create two pages as part of the Master/Detail page set. The first part is the Master Page.

Open the master_page.php that was created earlier.
Delete the HOME button <a> tag in the <div data-role="header"> container.
WHY: Because the EMPLOYEE MASTER PAGE is basically the home page so you don't need a home button on it.
```
<div data-role="header">
<h1>Employee Directory</h1>
<a href="master_page.php" class="ui-btn ui-icon-home  ui-corner-all ui-btn-icon-left">Home</a>
</div>
```

PART A: CREATE STATIC CONTENT

There are two static components (Button and ListView) that will be created for the master_page.php

Create Static Button

The ADD EMPLOYEE button will be created at the top of this page to go to the ADD EMPLOYEE PAGE that will be modified later.

Add an anchor tag (e.g., <a> ) BELOW the <h2> in the <div data-role="content"> container.
WHY: To convert the <a> tag to a button using jQueryMobile.

<h2 class="ui-body ui-body-a ui-corner-all>EMPLOYEE MASTER PAGE</h2>
<a data-role = "button" href = "add_page.php" data-inline = "true" class = "ui-btn ui-corner-all ui-icon-user ui-btn-icon-left">ADD EMPLOYEE</a>

CHECK POINT: Save the file and preview it in a browser. Click the ADD EMPLOYEE button. You should be taken to the ADD EMPLOYEE page. This page is where you will be able add a new employee if you like. Click the HOME button to return to the EMPLOYEE MASTER PAGE.

Create Static ListView

A jQuery ListView will be created from a simple bullet list to show list of employees.

Add the following highlighted unordered bullet list with four jQueryMobile data attributes BELOW the opening </a> tag:
WHY: To create a jQuery ListView that is styled completed different from a regular bullet list.
```
<h2 class="ui-body ui-body-a ui-corner-all>EMPLOYEE MASTER PAGE</h2>
<a data-role = "button" href = "add_page.php" data-inline = "true" class = "ui-btn ui-corner-all ui-icon-user ui-btn-icon-left">ADD EMPLOYEE</a>
 
<ul data-role = 'listview' data-inset = 'true' data-split-icon = 'edit'>
  <li data-role = 'list-divider'>EMPLOYEES</li>
  <li>
   ...
   </li>
  <li data-role = 'list-divider'>&nbsp;</li>
</ul>            
```
CODE EXPLANATION:
- The data-role = 'listview' is a jQueryMobile data attribute that makes the unordered list behave like a ListView.
- The data-inset = 'true' is a jQueryMobile data attribute that indents the ListView on both sides.
- The data-role = 'list-divider' creates a "divider" between list items that is not clickable.
- The data-split-icon = 'edit' will create an edit icon later when another anchor tab is added.
- Instead of a ListView, you could have just as easily used a table with CSS styles:
  <table>
  <tr><td> ... </td></tr>
  </table>
CHECK POINT: Save the file and preview it in a browser. You should see the jQuery ListView that was created from the bullet list.

Add the following highlighted <a> tags within the MIDDLE <li> tag.
WHY: The STATIC TEXT PLACEHOLDERS are used so that it would be easier to "see"' where to place the DYNAMIC CODE later.
NOTE: Notice there is a <p> element nested in the first <a> element. Code has been intentionally indented to see the structure (nested tags) better.
TIP: The highlighted code replaces the three ellipses (...).

<h2 class="ui-body ui-body-a ui-corner-all>EMPLOYEE MASTER PAGE</h2>
<a data-role = "button" href = "add_page.php" data-inline = "true" class = "ui-btn ui-corner-all ui-icon-user ui-btn-icon-left">ADD EMPLOYEE</a>
<!-- Listview --> 
<ul data-role = 'listview' data-inset = 'true' data-split-icon = 'edit'>
  <li data-role = 'list-divider'>EMPLOYEES</li>
  <li>
     <a href = 'detail_page.php?recordID=1'>DYNAMIC FULL NAME 1 GOES HERE 
         <p style = 'color:orange; text-transform:uppercase'>DYNAMIC TITLE GOES HERE</p></a>
         <a href = 'edit_page.php?recordID=1'>EDIT DYNAMIC FULL NAME 2  GOES HERE</a>
  </li>
  <li data-role = 'list-divider'>&nbsp;</li>
</ul>

CHECK POINT: Save the file and preview it in a browser. You should see the STATIC TEXT PLACEHOLDERS that will be REPLACED later. Notice also because of the data-split-icon='edit' attribute, you now see a SPLIT list. If you hover over the first part of the list item, the status bar at the bottom of the browser will show you that it will take you to the detail_page.php page. Whereas, if you hover over the second part of the ListView, it will show you that it will take you to the edit_page.php page that has not yet been created.

PART B: ADD DYNAMIC SCRIPT

Add A SQL SELECT Recordset Script

A recordset is needed to retrieve a list of employees from the database.

Add the following PHP connection script ABOVE the <!doctype html> statement at the top of the page.

<?php
require ("db.php");
?>
<!doctype html>
<html>

CODE EXPLANATION:
- The require method is used in this case to link the database connection script to this page.
- It is common practice to have the connection code is a separate file and linked to a page. Otherwise, 
  the complete PHP connection script would have to be written on top of EACH page that required a database connection.

Add the following highlighted code BELOW the require statement in the PHP code block recently created.
WHY: To create a SQL SELECT recordset to populate the LIST.
```
<?php
require ("db.php");
$pdo_statement = $pdo_conn->prepare("SELECT ID, CONCAT(FirstName, ' ' , LastName) AS FullName, Title FROM employees ORDER BY FullName ASC");
$pdo_statement->execute();
$result = $pdo_statement->fetchAll();
?>
```
CODE EXPLANATION:
- While a simpler SQL statement (e.g., "SELECT * FROM employees ORDER BY FirstName") could have been used to select the first and last name from the columns in the database, this would require that the first and last names to be concatenated later. Using the "SELECT ID, CONCAT(FirstName, ' ' , LastName) AS FullName, Title FROM employees ORDER BY FullName ASC" SQL statement with the CONCAT function allows the first and last name to be concatenated together with a space between them and then given an alias column name of FullName.
- The ID is not displayed on the page but will be used later to pass information to another page.
- The ORDER BY [COLUMN] clause causes the result to be ordered by a specific database column (e.g., FullName).
- The DESC option will order records from Z to A. The order by ASC (ascending) is not needed because it is the default option.
- The result of the recordset ($result) will be used later to populate the jQuery ListView later.

PART C: REPLACE STATIC CONTENT WITH DYNAMIC CONTENT

Make Full Name and Title Dynamic

Replace the static phrases with their dynamic PHP counterparts (e.g., <?php echo $result[0]["FullName"]; ?>) within the <ul> tag:
```
<ul data-role = 'listview' data-inset = 'true' data-split-icon = 'edit'>
  <li data-role = 'list-divider'>EMPLOYEES</li>
<li>   
<a href = 'detail_page.php?recordID=1'><?php echo $result[0]["FullName"]; ?>
<p style = 'color:orange; text-transform:uppercase'><?php echo $result[0]["Title"]; ?></p>
</a>
<a href = 'edit_page.php?recordID=1'>EDIT <?php echo $result[0]["FullName"]; ?></a>
</li>
  <li data-role = 'list-divider'>&nbsp;</li>
</ul>  
```
Notice that the the static text are replaced by PHP icons in Dreamweaver's Design View:
CODE EXPLANATION:
- The <?php echo $result[0]["FullName"]; ?> will return only ONE record from the FullName alias column of the database. You will see later how to loop through an array to return ALL of the database records.
- It is important to note that the array elements (e.g., FullName) are case sensitives.
- Since only one record is being returned from the database the array index for all requests is zero which is always the first (and in this case, the last and only) index that is needed.
CHECK POINT: Save the file and preview it in a browser. You should see one record returned from the database as a full name (e.g., Ann Ricoh) that represents the first alphabetically item from the query because of the ORDER BY clause. If the ORDER BY clause was not used, the first record of the database would have been returned (e.g., Carol Green). Only one record was displayed from database—this will be resolved shortly.

Notice also that the phrase DYNAMIC TITLE GOES HERE was REPLACED with its dynamic counterpart. What is not obvious is that if you hover over the second half of a ListView item, you see the phrase "EDIT <FullName>" where <FullName> is the actually named returned from the database. If you click any Edit icon, you will get an "Error loading page" because the Edit Employee page has not yet been created.
Wrap an "if" statement AND a foreach loop around the MIDDLE <li> opening and closing tag in a PHP script. Comments are optional.
WHY: To loop through the array () that is in the <a> tag.
```

<?php
	if(!empty($result)) { 
       foreach($result as $row) {
?>   
<li>   
<a href = 'detail_page.php?recordID=1'><?php echo $result[0]["FullName"]; ?>
<p style = 'color:orange; text-transform:uppercase'><?php echo $result[0]["Title"]; ?></p>
</a>
<a href = 'edit_page.php?recordID=1'>EDIT <?php echo $result[0]["FullName"]; ?></a>
</li>
<?php
		}
	}
?> 
  
```
CODE EXPLANATION:
- The "if" statement (!empty($result)) is used to check that the array is NOT empty. Notice the NOT (!) operator in front of the empty() method.
- The foreach loop is used to iterate through the result's array and print each value of the FullName alias in a series of dynamically
  created <li> tags and their content (e.g., <a> and <p> tags).
- The $result as $row is the array that the foreach loop is iterating through.
CHECKPOINT: Save the file and preview it in a browser. You should see only the FIRST record (Ann Ricoh) repeated for all rows instead of ALL of the rows from the database. That's because the $result[0] is used for all of the dynamic variables. This will be resolved in the next step.

Change $result[0] to $row for each dynamic variables.

<!-- Loop through middle list item ------------------------------------------- -->
<?php
	if(!empty($result)) { 
       foreach($result as $row) {
?> 
<li>   
<a href = 'detail_page.php?recordID=1'><?php echo $row["FullName"]; ?>
<p style = 'color:orange; text-transform:uppercase'><?php echo $row["Title"]; ?></p>
</a>
<a href = 'edit_page.php?recordID=1'>EDIT <?php echo $row["FullName"]; ?></a>
</li>
<?php
		}
	}
?> 
<!-- End of middle list item ---------------------------------------------------- -->

CODE EXPLANATION:
- Whereas $result[0] represents a SINGLE row based on a SINGLE index value, $row represents a SPECIFIC row for EACH record in the database.
 Hence, it will return ALL rows from the database.

CHECKPOINT: Save the file and preview it in a browser. You should see this time the jQuery ListView displays ALL of the records from the database in alphabetically order by first name.

Make Query Strings Dynamic

Replace the static number (1) in recordID = 1 with their dynamic PHP counterparts in BOTH <a> tags:
WHY: To set the recordID values dynamically from the recordset.
```

<?php
	if(!empty($result)) { 
       foreach($result as $row) {
?>   
<li>   
<a href = 'detail_page.php?recordID=<?php echo $row["ID"]; ?>'><?php echo $row["FullName"]; ?>
<p style = 'color:orange; text-transform:uppercase'><?php echo $row["Title"]; ?></p>
</a>
<a href = 'edit_page.php?recordID=<?php echo $row["ID"]; ?>'>EDIT <?php echo $row["FullName"]; ?></a>
</li>
<?php
		}
	}
?> 

```
CODE EXPLANATION:
- This statement (detail_page.php?recordID=<?php echo $row["ID"]; ?>) is called a query string. The first half of the string (detail_page.php?recordID=) is STATIC text. The second half of the string using a question mark (?) followed by a CUSTOM PHP variable name (e.g., recordID) with its value set using an equal sign (=) to a DYNAMIC URL variable (e.g.,<?php echo $row["ID"]; ?>) represents the value of the ID that was retrieved with the query earlier and that will be passed to another page.
- The two dynamic variables will pass the ID to the detail_page.php page and the edit_page.php page as URL variables, respectively.
CHECK POINT: Save the file and preview it in a browser. Hover over any ListView's link and the edit icon on the right. You should see the URL variable in the status bar at the bottom of the page (e.g., localhost/EmployeeDirectory/detail_page.php?recordID=1 or localhost/EmployeeDirectory/edit_page.php?recordID=1).

Enhancements

You can come back later after you have finished the "MUST HAVE" features of this app with these "NICE TO HAVE" features for this page. See Enhancement tab for details.

OPTION 1: Create and Make Images Dynamic

To make the app more professional, if would be nice to include small thumbnails images as part of the ListView.

OPTION 2: Create and Make Department Names Dynamic

Making a combo box (drop-down) dynamic is a common process when dealing with database driven apps.

OPTION 3: Display Total Records

It is common practice to return the total number of records in the database to show the total items in a list, table, etc.

OPTION 4: Add A Dynamic Time And Date

Adding a dynamic time and date stamp is a nice feature that you can add to some page.

OPTION 5: Provide An Avatar

If there is an image for each record in the database, you would not need to do this enhancement. However, there may be a case where an employee is added to the database but a picture of that person was not available at the time the information was entered. As a result, a broken image icon would be displayed.

OPTION 6: Create a Search and Pagination Scheme

If there are a lot of records being returned from the database, you could limit the number of records being returned AND provide a navigation bar to see other pages.

OPTION 7: Display 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 to show and hide content on a page. It is a common practice to wrap content in an conditional statement that can show or hide content based on the condition.