COMP 353-453
Homework #5
Using PHP to access a MySQL Database

In Labs 4 and 5, we have covered basic programming using PHP, as well as how to access a MySQL Database using PHP.
Specifically, we covered an example of a create table,  an insert, an update and a delete to a database using PHP.  We did this using a variety of techniques in html and php.

Purpose and Motivation:
The purpose of this assginment is to help you to solidify the skills that we covered in the labs, and to clarify some of the confusion that can occur when applying the concepts listed above.

There are several areas that may confuse a novice programmer in PHP:

The functionality for adding a department is very problematic.  If you review the DDL that was used to create the Company database, the social security number that you enter for the manager of the department has to match a social security number in the Employee table.  The way the application is currently written, we have no way to enforce that constraint.  The user may enter any ssn, and if it violated the foreign key constraint, it will generate an exception. 

-->This could be avoided (and the form made much easier to use), if we were to incorporate a drop down list for the ssn, and populate that list with the ssn's from the Employee table.

Below is an example of the html for a drop-down list.  In this example, the user can choose a type of credit card.  The word "select" in this context has nothing to do with SQL.  It is the html way of saying "this is a drop-down list, so select one of the items in the list."
This is an example of statically populating the drop-down list.  So you don't need PHP at all:

<select name="card_type">
     <option value="visa">VISA</option>
     <option value ="mastercard">MasterCard</option>
     <option value = "discover">Discover</option>

The option that you select is sent by POST.  You can access it after you submit the variables with your form:

      $card_type = $POST['card_type'];

To clarify:   As with any form, when you are adding a department, it is inside a form, and the form uses "method=POST".  So any variable inside that form that has a "name" is a variable that will be sent via post to the page specified by "action".  When you use a "select" (a drop-down list inside of a form), that select is associated with a "name"-ed variable.  In the above example, <select name="card_type"> should be inside of a form with method=POST.  In this case, since that is a named variable inside of a POSTed form, what is sent via POST is the variable name "card_type", and the value of whatever was selected.  So, when you get to whatever page is loaded by "action", there is a $_POST variable set called "card_type" and it has a value of "mastercard" or "visa" or whatever the use selected.

Here's an example of dynamically populating a drop-down list, so you need PHP to calculate the values for each option.  This example does not show how to populate the drop-down list from a database; but it does show how to use PHP to generate the html needed to specify the drop-down list:

<label>Interest Rate: </label>
<select name="rate">
<?php for ($v=5; $v<=12; $v++):?>                (Notice the alternative PHP syntax, using the ":" instead of braces for the "for loop")
     <option value="<?php echo $v; ?>" >
            <?php echo $v; ?>
<?php enfor;  ?>
</select> <br />

-->Your job is to take the application that we worked on in class, and change form.html.php so that the use can select the ssn from a drop-down list, which is populated with the results of the Employee ssn's.   The rest of the application should remain the same; however, you should incorporate any changes that you must make in order to get the drop-down list to work.

Some things to consider: 

Reviewing a relevant example:
If you want to review how to mix the php tags with the regular html, below is an example from our lab.
The last example that we covered in lab included a delete button that was part of form.  The form was in one cell of a table. Each row of the table had a form:

  1.     <p><a href="?addDepartment">Add a department</a></p>
  2.     <p>Here are all the departments in the database:</p>
  4.     <table >
  5.     <?php foreach ($result as $department): ?>
  6.       <tr>
  7.       <td> <?php echo $department['dnumber']; ?> </td>
  8.        <td style= "width:150px"> <?php echo $department['dname']; ?> </td>
  9.         <td> <?php echo $department['mgr_ssn']; ?> </td>
  10.          <td> <?php echo $department['mgr_start']; ?> </td>
  11.          <td> 
  12.          <form action="?deleteDept" method="post">
  13.           <input type="hidden" name="id" value="<?php echo $department['dnumber']; ?>">
  14.          <input type="submit" value="delete">
  15.         </form>
  16.      </td>
  17.       </tr>
  18.     <?php endforeach; ?>
  19.     </table>
  21.   </body>

Walking through this example:
What all of this demonstrates is how you can use both PHP (using the results of a query)  and html to build the final html document that you want the server to send to the user.