Comp 353 MySQL/PHP assignment

Due: Friday, April 26, 2013 (M.O.L.)

I have given you employee.php (also lib353pdo.php and password.php), which creates a page that displays the employees table from the database and then allows entry of additional employees. (You must use a password.php file!) We'll call employee.php 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 (project.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 are also to use drop-down menus rather than text boxes for all selections where there are only a small number (no more than ten) of options: departments, project numbers, sex, etc.

You should use prepared statements exclusively for MySQL queries. See the file employee.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 employee.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.
function main($hostname, $username, $dbname, $password) {
	print 	"<html><title>Adding Employees</title><body>\n";
	$db = connect_pdo($hostname, $username, $password, $dbname);

	//$db->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); 

	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($db);	// 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 employee.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 employee.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 in which the menu items are the text form of the data; this may entail more complex queries or else figuring out how to modify makeSelectMenuString() so that the values aren't the same as the strings displayed. For example, the existing department-selection menu has
<option> 1
<option> 4
<option> 5
you might change this so that the menu html reads
<option value="1"> Headquarters
<option value="4"> Administration
<option value="5"> Research
This way, the user sees the text version but the html returns the numeric version. (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).

Finally, you should develop a mechanism so that if an entry fails its validation when the user clicks submit, then the form returns with the user's data still filled in (and, ideally, the bad entry highlighted). You are only required to do this for textbox entries. We'll discuss ways to do this in class, but the basic idea is to add an array option to the form-drawing procedure, that takes an array of values to be used for the default entries. When the submit button is pressed, you save this list and pass it to the form-drawing procedure if there was an error.


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 (as of this writing, not yet fully set up). Your login credentials are the same as for knuth.cs.luc.edu or other CS servers. Outside of Loyola you will need to ssh into knuth/shannon first, and from there to lamp.cslabs.luc.edu.

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

You test your work by accessing the page, from within Loyola, as
    http://lamp.cslabs.luc.edu/~uvid/employee.php
This assumes you have put your employee.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