ITEC 325 2013spring ibarland

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

hw07
DB connectivity

Due: 2013.May.03 (Fri) 23:59

You will add to your hw06 by storing .W.o.W. skills in a database, and retreiving a summary list of all skills. For extra-credit, you can add additional basic functionality.

1. As in previous homeworks, your page at https://php.radford.edu/~yourUserId/itec325/hw07/index.php should have a link to your skill-entry form near the top, and include a list all your files (with links), and should also display their source (between May.04 through May.30). See hw04-soln/ for an example.

2. (30%) Modify the ol' skill entry form so once the information is validated, the skill gets added to the database.
   

   • (You may over-write any previously existing information for that skill, or see extra-credit below.)

   • So that I can grade, include a line at the bottom of the page which is the SQL INSERT command. Please render this line inside a <tt> tag.
     (Of course, I'll also grade by checking your all-skills page, but in case you don't complete that, I need something to grade.)

   • Note: You don't need to add the classes-available-to, to get full credit. Instead, adding this feature is 25% extra-credit. In that case, the classes-available-to should also be included when giving the detailed skill-information.

   • Be sure all your pages/programs always close any database connection they open!

   Here is a hw07-setup.sql file which you can use (either in MyPHP or Oracle SQL), to create the tables you'll need. (It's fine to adjust (or even ignore) this file, if you want something different.)

   As discussed in lecture, be sure to guard against SQL injection by calling mysqli_real_escape_string on any user-provided text which becomes part of a SQL query (even if other validation requirements make such escaping moot).

   Note that I'll test the hw by entering skill-names which include spaces and apostrophes, and skill-descriptions which contain apostrophes and semicolons, to check that HTML/javascript and SQL injection attacks are being guarded against.

3. (40%) Make a page which shows a summary list of all skills previously entered: just the skill name, the associated ability (“Wisdom” etc.), and the minimum-level. This page should also include a link to the skill-entry-form page.

   Be sure to have a prominent link to this page near the start of your php.radford.edu/~yourUserId/hw07/index.php. (This can replace, or simply be next to, the link to the skill-entry-form.)

   Be sure all your pages/programs always close any database connection they open!

4. (30%) Each skill-name on the summary list should be a link which, when clicked, brings you to a detailed-information page that includes the previous information plus the full description and who submitted the skill.

   (The layout of this information does not need to be anything elegant, but it should not be prone to HTML/script injection.)

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

   As always, to guard against HTML/script injection, be sure to call htmlspecialchars on any text which was originally proivded by a user (even if retrieved from the database). (This goes for all pages, even if other validation requirements make such escaping moot).

   I'll say it again, just because it's fun: Be sure all your pages/programs always close any database connection they open!

5. Extra credit (10%): For both: the list of (six) allowed traits, and the list of (four) allowed character classes: keep just one copy of these, each in its own database table. Set up other tables to involve foreign-key constraints into these tables. (Be sure to see the specific note about foreign keys and the MySql engine, in lect10-ch12-mysql—MySQL: chapter 12.) Then, your wow-constants.php file won't manually make an array with these traits; it will instead read the database table. Blah blah blah close connection blah blah.
6. Extra credit (50%): Add sessions (based on cookies), and logging in/out.
   (You don't need to maintain a table of users/passwords; instead let the hard-coded user “i325” with password “web2” log in.)
7. Extra credit (5%): Have most pages accessible without logging in; have logging-in apply only to entering skill information.
8. Extra credit (5%): Allow for deleting a skill (if logged in).
9. Extra credit (5%): Add a 'log out' button/link, as in ../../Lectures/lect07c-ch09-logout.php.
10. Extra credit (25%): Allow for editing an existing skill. But do so in a way that is unified with entering a new skill: 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. (Just like a sticky form.)
    (If you do this for only for the skill name/min-level/description, but not the available-to checkboxes, that's half of the extra-credit points.)

11. Extra credit (15%; more involved): Handle the situation where a second person wants to edit an existing skill'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 (but you don't have to).

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 D2L 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/hw05/. Use index.php as the landing page (or, have it auto-forward to the landing page).

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

©2012, Ian Barland, Radford University Last modified 2013.May.02 (Thu)

Please mail any suggestions (incl. typos, broken links) to ibarlandradford.edu