hw03
DB connectivity

Due: 2011.Apr.18 (Mon)

You will add to your hw02 by storing course information in a database, retrieving information for one given course, and retreiving a summary list of all courses.

Copy your hw02/ project directory to hw03/.
(10%) Have the user login page authenticate the user/password against a database.

(You'll presumably add authorized users to the database manually — not through web forms.) For grading purposes, mention a user/password that I can use, next to the entry forms.
(30%) Modify the ol' course-information entry form so once the information is validated, the course gets added to the database.
(You may over-write any previously existing information for that course, or see extra-credit below.)

Include all the CREATE statements used for the database, including constraints (as appropriate) for primary key, foreign keys, non-null, and unique keys.
You do not need to include SQL check-constraints enforcing data validation.

As discussed in lecture, don't store the concentration-requirements as part of a course: this information is inherently a list, and 1st Normal Form suggests we should store that information in a different table that holds just concentration-requirements. (If a course is required by three different concentrations, it would appear three times in the table of concentration-requirements, naturally enough.) (To think about, before you create your the table: What is the corresponding foreign-key constraint?)

As discussed in lecture, be sure to guard against SQL injection by calling mysql-real-escape-string on any user-provided text which becomes part of a SQL query (even if other validation requirements make such escaping moot).
(30%) Make a page which shows a summary list of all courses previously entered, including the course number and course title. The landing page should be this and/or your login page.
(30%) Each course-number on the summary list should be a link which, when clicked, brings you to a detailed-information page that includes the full description, and the list of concentrations requiring that class.

Note that this is asking for a link which behaves like a form, since you won't have a separate URL for every single course — instead you'll have one page which (given a particular course-num) pulls the detailed information out of the database. How do you have a link which provides an argument (course-num) to another page? One easy way ¹ is to have the link contain the parameter(s) explicitly, e.g. “<a href="courseDetail.php?coursenum=ITEC423">…”, and the receiving page accesses that argument through $_GET.

As always, to guard against HTML/script injection, be sure to call htmlspecialchars on all user-provided text (even through the database) which you are embedding in a web page. (This goes for all pages, (even if other validation requirements make such escaping moot).
As before, have a file crs-cat-sources.php (in the same directory) which simply show_sources all ² your other hw03 files (preferably, only after the due-date).
Your forms do not need to otherwise contain a show_source.
Extra credit (5%): Have most pages accessible without logging in; have logging-in apply only to entering course information.
Extra credit (5%): Allow for deleting a course (if logged in).
Extra credit (15%): Allow for editing an existing course. But do so in a way that is unified with entering a new course: The user will submit a course-number (new or existing), and be taken to the information-entry form, with all database information already filled in.
(If you do this for only for the course name/number/description, but not the concentration-requirements, that's half of the extra-credit points.)
Extra credit (15%; more involved): Handle the situation where a second person wants to edit an existing course's info even while somebody else is in the process of editing it. You can either use a pessimistic approach (don't let the second person start), or an optimistic approach (when somebody submits, make sure that the database still has the same info as when they started — at least for any fields which they modified).

The former case is easier, but you have to worry about the first user timing out. You can use database locks to help you with this.
Extra credit (15%) Have a single point-of-control for the list of departments, as well as for the list of majors/concentrations — either as a php array (or xml file), or (for full credit) in a mysql table so that you can set up FK constraints.
(By replacing a current collection of (six or eight) concentration-specific names, you'll instead be using loops (and helper functions). Although the result will be slightly more abstract, it might actually be shorter and will definitely be much more flexible — a trademark of a good design re-factoring. (After that change, you could add all majors and concentrations in the university as fast as you can type them.)

Guidelines

Each file should start (after a doctype declaration) with a PHP comment with your name and a summary description of what the php code accomplishes.
Use meaningful variable names, function names, and good comments as needed. Javascript code should be described with inside (“”) comments.
Write functions (both php and javascript) as appropriate, to avoid repeated code.
Be sure to make all links relative within the directory (so I can run the WebCT submitted files in another directory).
All code/html should be well formatted with appropriate white space and indentation so it is easy to read.
Strive to minimize lines that wrap.
Use standard XHTML tags and make sure your javascript does not use browser-specific constructs.
If you have any questions, use the discussion board.
Do not modify your final submission after the due-date.
To submit your assignment,
1. Have the main page accessible via https://php.radford.edu/yourUserID/itec325/hw03/. Use index.php as the landing page (or, have it auto-forward to the landing page).
2. On WebCT, attach all necessary files as hw03-yourUserId.zip.

Transmitting the arguments through $_GET of course is vulnerable to over-the-shoulder snooping; if you wanted to still use $_POST but not require a “submit” button, you can set the link's onclick attribute to be javascript which finds does something like document.getElementByID( 'theFormsId' ).submit().

(A general warning, not pertinent to this homework:)
However, when submit() is being called by the program instead of by clicking on a submit button, the browser (non-intuitively) disregards the form's onSubmit attribute(!). So: your link's onClick code should also run validation (if any) before submitting:

            /* Call 'onSubmit' manually [which runs any code stored in
             * the form's attribute, but doesn't actually submit.]
             * If that code returns true, then *we* trigger the submit.
             */
            if (document.getElementById('theFormsId').onSubmit()) {
              /* We just called onSubmit(); we reach here iff that function returned true. */
              document.getElementById('theFormsId').submit()
              }

(Really, this exactly is what the browser does, when a user hits the submit button.)

↩

² Note that if, for hw02, you took the time to loop over all files in the directory, you're reaping dividends from that investment now. ↩

home—lects—exams—hws
breeze (snow day)

©2011, Ian Barland, Radford University
Last modified 2011.Apr.18 (Mon) Please mail any suggestions
(incl. typos, broken links)
to iba�rlandrad�ford.edu

hw03 DB connectivity

Guidelines

hw03
DB connectivity