Comp 353 MySQL/PHP assignment

Due: April 27, 2012 (M.O.L.)

I have given you employees.php (also lib353pdo.php), which creates a page that displays the employees table from the database and then allows entry of additional employees. We'll call this the Employee_Add page.

You are to create additional pages for the following:
  1. Employee_Update (employee_update.php): You enter an ssn in one field, and click a button labeled retrieve. This fills in the other fields with the retrieved values for that employee; the user can then edit some or all of them. You then click a button labeled update to update the entire record with the new values. For simplicity, you may assume that the Ssn field does not change. A nice touch is to display the entire employee table, as in Employee_Add, so you can copy/paste ssns (though it would make sense to omit all columns except ssn, name, and maybe department). A preliminary version discussed in class is here: empupdate.php.
  2. Project_Add (projects.php) Like Employee_Add, except for adding new projects. This is the easiest of these three.
  3. Works_on_Update (works_on.php): Allows entry of an employee Ssn, a project pno, and a value for hours. Relevant columns of Project and Employee are shown (for Employee, there is no need here for bdate, address, sex, salary). Ideally this page will allow both new entries (assigning employees to projects) and updates (adjusting the value for hours). More details may be forthcoming.
All pages (including Empoyee_Add) should validate all input data; that is, Ssn should be 9 numeric characters, Sex should be 'M' or 'F', and salary should be > 0. All pages should also perform all foreign-key checks; MySQL after all does not do these automatically. That is, you should check in Employee_Add that super_ssn is a valid employee ssn, and that dno is a valid department number.

You should use prepared statements exclusively for MySQL queries. See the file employees.php for examples. However, you do not have to implement protections against html injection (XSS attacks).

Good software design should be used throughout. Bulk HTML should, where possible, be created with the php heredoc feature. The "main program" should be kept to a minimum; I limit my "main" sections to connection and an if-then-else dispatch to handling any buttons and displaying the correct form.

Here's the main section of employees.php, after the connection part, showing the button dispatch. To test for the "submit" button I used the proper array_key_exists() method; for the "update" and "deralph" buttons I used the time-honored array-failure-to-boolean-FALSE hack.
print 	"<html><title>Adding Employees</title><body>\n";
if (array_key_exists('submit', $_POST)) {  // submit button pressed		
	submit_employee($db);
	get_employees($db);
} else if ($_POST['update']) {		   // update button pressed
	get_employees($db);
} else if ($_POST['deralph']) {		   // de_ralph button pressed
	de_ralph($db);
	get_employees($db);
} else {				   // no button pressed; initial arrival
	get_employees($db);
}
printform();	                           // done in all cases
print	"</body></html>";
The displayed page/form is more-or-less identical in all cases; if you click "submit" then there's a message printed about the success of the record insertion but otherwise in each of the four cases above the existing employee table is displayed followed by the html form for adding a new employee.

Note that sometimes I do have one .php file display more than one html form. I did this in the empupdate.php example, where on the first form you choose an employee and on the second you enter the changes. Some people don't like this approach, and would say that each form should have its own .php file. Such people are probably right, but the .php files for two closely-related forms would likely overlap significantly and sometimes it's simpler to keep it all in one place.


Navigation between pages is most easily implemented with php buttons in a different <form> section; for example, on employee.php we would create our primary form with the following:
    	<form method="post" action="">
	<input type="text" name="pname" value="demolition"><p>
	...
        <input type="submit" name="submit" value="submit data"><p>
	<input type="submit" name="update" value="update">
	</form>
Note that in the first line the form's action is defined to be the empty string, meaning that employees.php will be run again (but now with different data in $_POST[], which we can test for). The buttons that trigger this action are the two input objects of type="submit" at the bottom; we distinguish which was clicked by checking $_POST with index "submit" or "update". Clicking these buttons will always run the employees.php file, though.

Now we add a second form, as follows:
	<form method="post" action="projects.php">
	<input type="submit" value="projects">
	</form>
This second form immediately follows the close of the first. Because it is a new form, it can have a new action, in this case projects.php; clicking on the button will launch the form action, projects.php. The value is what appears printed in the button; I did not assign a name attribute because we do not look in $_POST for any information as to this button.


Graduate Students

Those enrolled for graduate credit should also implement the following:

Use <select> menus for things like Dept_name and Proj_name; the numeric equivalents should not be used. (You can also use this for the Sex attribute, but this is a special case because 'M' and 'F' do not come from some other table.)

Create an additional page Dependents for adding dependents. Like the Employee_Update page, you will first enter an employee Ssn, and then be allowed to enter one or more dependents of that employee (with multiple clicks).

Additional requirements may be added; I'm still working on a reasonable way to handle having previous data remain entered if the processing detects an error (missing required fields or failed foreign-key constraint).


You can work on this on your own machine, having installed LAMP or WAMP (instructions here).

LAMP

Alternatively, you can work on the CS department LAMP server lamp.cslabs.luc.edu. Your login credentials are the same as for random.cs.luc.edu or other CS servers. Outside of Loyola you will need to ssh into random/infinity first, and from there to lamp.cslabs.luc.edu.

MySQL is running on cslabs.luc.edu. Your database name is your uvid and database username is your uvid too. The database password is pxxxxx  (The letter "p" followed by the last FIVE digits of your student ID, eg 00001234567).

You test your work by accessing the page, within Loyola, as
    http://lamp.cslabs.luc.edu/~uvid/employees.php
This assumes you have put your employees.php file into your subdirectory public_html.

Outside of Loyola you can use http://webpages.cs.luc.edu/~uvid, but php will not actually run.

phpinfo()

The simplest php program is probably
<?php
phpinfo()
?>

lib353pdo.php

We've already discussed table_format_pdo().

I recommend using connect_pdo() in my lib353pdo.php file.

Note also that lib353pdo.php now includes the line
error_reporting (E_ALL & ~E_NOTICE);
This line controls the kind of error messages you get. Other choices:
E_ALL                          // adds E_NOTICE
E_ALL | E_STRICT    // adds E_STRICT
E_ALL & ~E_NOTICE & ~E_DEPRECATED   // takes away E_DEPRECATED, not avail on my machine

I also added makePageButtons(); this draws a bar of four buttons for the four pages. If you add a page, add a button here. Note that these buttons assume particular page names; if you rename a page, update this appropriately.

debugging
Note that you can set the error messages with, eg
    error_reporting (E_ALL & ~E_NOTICE);
You are likely to get significant messages if you either enable E_STRICT, or fail to disable E_NOTICE (as is done above).

M.O.L = More or Less