Homework #5

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:

There is often a "mix" of html and php in the same document. It's important to undersatnd that anything that is not enclosed in php tags is just plain html. That's usually the static html. Anything that requires logic to determine what you want to generate on the html page should be enclosed in php tags.
The "echo" is simply "printing" to an html page. No PHP will show up on the html document that is sent to the user. The purpose of PHP is to tell the server what html to send to the user.
We can take advantage of html tables, forms, GET and POST, and combine them with PHP functionality and logic to dynamically generate custom html documents.
The "name" in a form means the name of a variable that will be posted to the action page. The "id" is an identifier of an element on an html document. Using "for" in a label associaes a label with a particular element of the form. In a query, using a named parameter (e.g., :deptNum) is simply a placeholder into which you can later bind an actual value. In a query, you also use the actual database variable names (for instance, after the word Select). It's important to know when you have to use a specific name and when you are free to choose any variable name.
It can be confusing to redirect from one page to another, and especially to "postback", which is a somewhat ASP-like term that means to reload the same page that generated the form that you user submitted. We've seen how this can be an efficient way to use the same form under different circumstances (such as, when the user is first presented with a blank form, and when the user is presented with the same form, filled out with data that he or she already completed). If the action is ="", the same page is reloaded. If the action="?", the same page is reloaded, and in addition, the query string (for the GET variable), is cleared.

Assignment:
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>
</select>

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

<?php
      $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; ?>
     </option>
<?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:

form.html.php is called by index.php. After index.php runs a query on the Company DB to retrieve the data from the Department table, it displays the department info, with a link that gives the use the option to add a new department (this is explained below). If the user clicks on the link that says "Add a Department", a querystring is sent back to index.php, via GET. The GET array is tested at the top of theindex.php.
The query that is run in this application is to Select * from department. But you will also need to run a query on Employee, because you need the ssn's from Employee in order to populate the drop-down list. Since this form is "included" at the top of the index.php page (when index.php is loaded, BEFORE running any queries), you would have to run the query on the Employee table in the form.html.php file.
There is more than one way to modify the application. But the easiest way would allow you to make all necessary changes to the form.html.php file.

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

<p><a href="?addDepartment">Add a department</a></p>
<p>Here are all the departments in the database:</p>
<table >
<?php foreach ($result as $department): ?>
<tr>
<td> <?php echo $department['dnumber']; ?> </td>
<td style= "width:150px"> <?php echo $department['dname']; ?> </td>
<td> <?php echo $department['mgr_ssn']; ?> </td>
<td> <?php echo $department['mgr_start']; ?> </td>
<td>
<form action="?deleteDept" method="post">
<input type="hidden" name="id" value="<?php echo $department['dnumber']; ?>">
<input type="submit" value="delete">
</form>
</td>
</tr>
<?php endforeach; ?>
</table>
</body>

------------------------------------------------------------------------------------------------------------------------

Walking through this example:

Line 1: This is an html hyperlink, with a querystring (signified with the ?) with a value of "addDepartment". If this link is clicked, the value "addDepartment" becomes part of the GET array. The index.php file (where this file is "included"), is reloaded, and the GET array can be tested to see if "addDepartment" was added to it.
Lines 4 and 19 are table tags, delimiting the beginning and end of a table. They are not php. They are plain old html. Each row of the table will display one row of the department table. How did it get the data from the department table, which exists in a database on our server, and we don't see any query here? The actual query was executed in the index.php document, which then "included" this file. So this file actually becomes embedded into the index.php file, where the result of the query (Select * from department) was stored in the array variable $result.
Lines 5 and 18 are the beginning and end of a foreach loop. Notice that each of those lines has both the open and the close php tag. However, they form the structure of the loop, and the html that in between these two php statements will be "looped".
Lines 6 and 17 delimit the beginning and the end of each table row. This is plain html, inside of the PHP foreach loop.
Line 7 begins with a plain html table domain (<td>) tag. This is the html way of creating one column/cell in the current row. What is going to be in that cell? If we knew in advance (if it was static), we would just use html and type in the value. But we want the contents of this row's cell to be the value of the database field dnumber that was retrieved in the query, and is now in the first row of the $result array. In Line 5, we have specified that we will refer to each row in that array as $department. We could have named it $row or $item or $whatever. Here, we use PHP to extract the dnumber database field from the first row (or what we called $department). We then close the php tag, because we don't need it in order to specify the close tag for </td>.
Line 8 is similar to Line 7. I just added a width for the column.
Lines 9 and 10 are also similar to Line 7.
Ines 11 and 16 delimit the last cell in each row. The cell contains a form--so there is one distinct form for each row in this table. Note that this form has both a query string, which becomes part of the GET array, and also method=post, which means that variables will be added to the post array. There are two inputs on this form. The first is "hidden". It is the dnumber. We need to post this to the next page, so that the next page knows which record to delete. We made it hidden, because it's already displayed in the first column (but it's not part of the form there, so we couldn't post it). The second field is a button, with a value of "delete".

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