Comp 353 MySQL/PHP assignment


Due: April 26, 2011 (M.O.L.)

I have given you employees.php (also lib353.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 a Ssn in one field, and click 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 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. The preliminary version we did 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).
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 positive. 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 any protections against html injection (XSS attacks).

Good software design should be used throughout. Bulk HTML should, where possible, be created in heredoc format.


Navigation between pages is most easily implemented with php buttons in a different <form>; 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.


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

If some entries are missing, figure out how to highlight them, and make it clear to the user that the form is incomplete. (You can embed this logic entirely in the form; you do not have to read from the database which fields have a NOT NULL constraint.)

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


You can work on this on your own machine, having installed LAMP or WAMP (instructions here). Note that you will also have to install the MDB2 PEAR library, at pear.php.net.

PEAR woes

I think you need only four files from PEAR: PEAR.php, PEAR5.php, MDB2.php and mysqli.php. If you are having trouble getting PEAR to install, just download the copies listed in the previous sentence (right-click and do save page as), put them in your project directory, and then include them (not require them!) with

    include 'MDB2.php';

I'm still working on where to put the mysqli.php driver. Let me know if you have other problems (eg version-related, or if there were unforeseen dependencies somewhere). I modified MDB2.php so that it would include PEAR.php rather than require it (include looks first in the local directory; require seems to look first in the library directory). If you have your own copies of PEAR.php, PEAR5.php and MDB2.php, and you're having trouble using mine due to, say, OS or version differences, make that same change to MDB2.php and try your versions in your project directory.

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  (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()
?>

lib353.php

We've already discussed table_format().

I recommend using connect() in my lib353.php file. Note that if connect() fails, you should change 'mysqli' to 'mysql' in connect; see the comment. The latest version tries 'mysqli' first, and, if that fails, tries 'mysql'; it may still lead to annoying warning messages.

Note also that lib353.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
If you wish to turn on E_STRICT, I recommend doing that after MDB2.php is loaded.

I also added makePageButtons(); this draws a bar of four buttons for the four pages. If you add a page, add a button here. 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).

If you get the message
    Fatal error: Call to undefined method MDB2_Error::execute() in ....
the likeliest cause is that you called $stmt->execute($args) when $stmt was earlier the result of an error.

M.O.L = More or Less