PHP CMS with jQueryMobile

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

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

PART A: CREATE STATIC CONTENT
- Create Static Form
- Create Static Message Paragraph
- Create Static jQuery Listview
PART B: ADD DYNAMIC SCRIPTS
- Add Conditional Script
PART C: REPLACE STATIC CONTENT WITH DYNAMIC CONTENT
- Make Total Record Dynamic
- Make Query String and Tooltip Dynamic

PART A: CREATE STATIC ELEMENTS

There are three static components that will be created:

Search Form
Message Paragraph
jQuery Listview

Create Static Form

Open the search_employee.php template page that you created earlier.

Write (or copy) the following highlighted code below the <h2> tag. Comment is optional.
WHY: To create a form and form elements for the Employee Search page.

<h2 class="ui-body ui-body-a ui-corner-all">EMPLOYEE SEARCH</h2>
<!-- Search Form -->
<form action="employee_search.php" method="post" id="formSearch">
<label for="FullNameField">Search By Name:</label>
<input type="search" name="FullNameField" id="FullNameField" data-mini="true">
<input type="submit" name="searchButton" id="submitted" value="SEARCH" 
data-inline="true" data-icon="search" data-iconpos="left" data-mini="true">
<input type="hidden" name="submitted" value="true">
</form>

(OPTIONAL) Wrap a jQueryMobile class around the <form> tags:
WHY: To add a border style around the form.

<!-- Search Form -->
<div class="ui-body ui-body-a ui-corner-all"> 
<form action="employee_search.php" method="post" id="formSearch">
... 
</form>
</div>  <!-- end of form container -->

Create Static Message Paragraph

Write (or copy) the following highlighted code below the closing </div> tag:
WHY: To create a static message for the Employee Search page if no employee record is found. The paragraph will be hidden later.
```
</div>  
<p class = "error">NO EMPLOYEE FOUND!</p>
```
CODE EXPLANATION:
- The <br /> tag is used to create a space between the form and the message.
- The class = "error" is defined in the CSS file to style the text with a red color and in bold. To highlight for the paragraph will only be used for emphasis even though it is not an error message.

Create Static jQuery Listview

Write (or copy) the following highlighted code below the <p> tag. Comment is optional:
WHY: To create a jQuery listview to show employee(s) that have been found from a search criteria.

CAUTION: Do not add spaces in the recordID variables; otherwise, code will not work (e.g., recordID=99999 instead of recordID = 99999).

<p class = "error">NO EMPLOYEE FOUND!</p>
<!-- Listview will show if a record is returned. -->
<div class = "ui-body ui-body-a ui-corner-all">
 <ul data-role = "listview" data-inset="true" data-theme="a" data-split-icon="edit">
  <li data-role = "list-divider">Employee Search Result Total: <span style="color:orange;">12345</span></li>
  <li>
    <a href = "employee_detail.php?recordID=99999">Dynamic FullName Goes Here</a>
    <a href = "edit_employee.php?recordID=99999">EDIT Dynamic FullName Goes Here</a>
  </li>
  <li data-role = "list-divider">&nbsp;</li>
 </ul>
</div> <!-- end of listview container -->

CHECKPOINT: Save the file and preview it in a browser. You should see the the static form, message and listview.

FOR INSPIRING GEEKS ONLY: Instead of creating a separate SEARCH page, you could write similar code with a conditional statement INSIDE the Employee Master Page by creating a search input field at the top of the page and then allowing the user to enter a search which will show the listview if a record is found or show the message (NO EMPLOYEE FOUND!) if no record is found.

PART B: ADD DYNAMIC SCRIPTS

To better understand the PHP script that will be added, it is helpful to see some pseudo code that represents how the code will work:

ADD PSUEDO CODE HERE...

Add Conditional Script

Around the paragraph and the jQuery listview code, within an PHP script, wrap the FIRST part of an "if" statement within it. Within another PHP script, wrap the SECOND part of the "if" statement within it.
WHY: To check to see if the form is submitted. Later, you will include two conditional statements to be able to turn either one on or off depending upon the number of records returned from the database. For example, if no record is returned, display the "NO EXPLOYEE FOUND!" message. If one or more records are returned from the database, display them in a listview.
```
<?php
if(isset($_POST['submitted']))
{ 

?>
<p class="error">NO EMPLOYEE FOUND!</p>
<div class="ui-body ui-body-a ui-corner-all">
  <ul data-role="listview" data-inset="true" data-theme="a" data-split-icon="edit">
   ...
  </ul>
</div> 
<?php } // Show if isset is true ?>
```
CODE EXPLANATION:
- IMPORTANT NOTE: It is important to note how the code is wrapped around TWO separate PHP script blocks with the opening curly brace in the first PHP code block and the second curly brace is in the second PHP code block. This is typically done if you don't have any print statements for HTML tags or other PHP script to include in the code.
Write (or copy) the following highlighted code to below the first curly brace in the "if" statement. Comments are optional.
WHY: To create and execute a query and return its results along with total number of records. Comments are NOT highlighted because they are optional and are used to explain the code and make the code easier to see. However, it is recommended that you copy all lines of code including the comments as a whole.

CAUTION: Notice the SINGLE QUOTES and the use of the percent symbol (%) around the POST variables that is used to create a wildcard search.
```
<?php
if(isset($_POST['submitted']))
{ 
// Store reference of SQL Statement in a variable. Filter query based on form variables.
$query_SearchEmployee = "SELECT EmployeeID, FirstName, LastName 
FROM employees WHERE FirstName LIKE '%$_POST[FullNameField]%' OR LastName LIKE '%$_POST[FullNameField]%'";
// Execute query
$result_SearchEmployee = mysql_query($query_SearchEmployee);
// Get array of data
$row_SearchEmployee = mysql_fetch_assoc($result_SearchEmployee);
// Get total number of records
$totalRows_SearchEmployee = 0;
?>
```
CODE EXPLANATION:
- The SQL SELECT is used to query the database for FirstName and LastName (as FullName) as well as the City and the EmployeeID columns.
- Notice that the WHERE clause has an AND statement that is used to create a multiple search criteria.
- Also, note the word LIKE that is used as a wildcard for the search criteria (e.g., Co will return Cornelius in a search).
Within a set of PHP opening and closing scripts, wrap the paragraph with a conditional statement. Comment is optional.
WHY: To display a message if no employee record is found. Notice the opening and closing of the curly braces at both end of EACH PHP code block like the code created earlier.
```
<?php if ($totalRows_SearchEmployee == 0) {?>
	<p class="error">NO EMPLOYEE FOUND!</p>
<?php } // Show if recordset is empty ?>			
```
CHECKPOINT: Save the file and preview it in a browser. If you click the SEARCH button, you should see the message, "NO EMPLOYEE FOUND!" because the $totalRows_SearchEmployee is hardwired to zero in the SQL code section (e.g., $totalRows_SearchEmployee = 0;).

Wrap the container AND jQuery listview with a set of opening and closing PHP script code blocks with a conditional statement SPLIT across the two blocks.
WHY: To show a jQuery listview with one or more employees listed if employee record(s) are returned from the database.

<!-- Listview will show if a record is returned. -->
<?php if ($totalRows_SearchEmployee > 0) {?>
<div class="ui-body ui-body-a ui-corner-all">
  <ul data-role="listview" data-inset="true" data-theme="a" data-split-icon="edit">
   ...
  </ul>
</div> <!-- end of listview container -->
<?php } // Show if recordset is NOT empty ?>

CHECKPOINT: Save the file and preview it in a browser. You should see that the container and listview are hidden because $totalRows_SearchEmployee is currently hardwired to zero in the SQL code section (e.g., $totalRows_SearchEmployee = 0;).

PART C: REPLACE STATIC CONTENT WITH DYNAMIC CONTENT

Make Total Record Dynamic

Replace the static number (0) within the $totalRows_SearchEmployee variable with its dynamic counterpart (mysql_num_rows($result_SearchEmployee);).
WHY: To return the actual number of records returned from the database based on the mysql_num_rows() method.
```
$totalRows_SearchEmployee = mysql_num_rows($result_SearchEmployee);
```
CODE EXPLANATION:
The mysql_num_rows() method as the name implies will return the total number of records used by the SELECT query.
Replace the static number (12345) in the first <li> tag that is within a <span> tag with its dynamic variable counterpart.
WHY: To display the actual number of records from the database in a phrase in the first <li> tag at the top of the listview.
```
<li data-role="list-divider">Employee Search Result Total: <span style="color:orange;">
<?php print $totalRows_SearchEmployee;?></span></li>
```
CHECKPOINT: Save the file again and preview it in a browser. You should see a number that represents the total numbers of records from the database in the top row of the listview. In this case, 6. It may be different for you depending on the numbers of records you have added or deleted. You can open phpMyAdmin and open the employees table to confirm the number.

Make Query Strings and Tooltip Dynamic

Similar to what what done on the Employee Master Page (index.php) to pass the recordID value to the employee_detail.php and edit_employee.php pages, this is similar code:

Replace:

the two instances of the static number (99999) in the two <a> tags with their dynamic variable counterparts for both recordID values.

the two instances of the static phrase (Dynamic FullName Goes Here) in the second <a> tag's content itself with its dynamic variable counterpart.
NOTE: In the second anchor tag, the word EDIT has a space after it in the code which is not obvious below.

<li>
    <a href = "employee_detail.php?recordID=<?php print $row_SearchEmployee['EmployeeID']; ?>">
    <?php print $row_SearchEmployee["FirstName"]; ?> <?php print $row_SearchEmployee["LastName"]; ?></a>
    <a href = "edit_employee.php?recordID=<?php print $row_SearchEmployee['EmployeeID']; ?>">EDIT 
    <?php print $row_SearchEmployee["FirstName"]; ?> <?php print $row_SearchEmployee["LastName"]; ?></a>
</li>

CHECKPOINT: Save the file and test it in a browser. If you click on the SEARCH button, you should see only the FIRST record from the database even though there are more than one record in the database. This will be resolved in the next step.

Wrap the MIDDLE <li> tag with a do...while loop within a set of PHP script block:
WHY: To return one or more records from the database based on the search criteria.

<?php do { ?>
<li>
<a href="employee_detail.php?recordID=<?php 
print $row_SearchEmployee['EmployeeID']; ?>">
<?php print $row_SearchEmployee["FullName"]; ?></a><a href="edit_employee.php?recordID=<?php 
print $row_SearchEmployee['EmployeeID']; ?>">EDIT 
<?php print $row_SearchEmployee["FullName"]; ?></a>
</li>
<?php } while ($row_SearchEmployee = mysql_fetch_assoc($result_SearchEmployee)); ?>

CHECKPOINT: Save the file and test it in a browser by entering a search criteria (e.g., Name) and then click on the SEARCH button. You should see that a listview is shown with the correct criteria search. If you don't enter anything into the field and press the SEARCH button again, you will see ALL employees returned from the database.

Search Employee Page