ITEC 325 2016fall ibarland

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

db
DB connectivity

Due: 2016.Nov.14 (Mon) 11:00.

You will complete your new-Okaymon page by storing the okaymon in a database, and retreiving a summary list of all Okaymon.

1. Your landing page https://php.radford.edu/~yourUserId/itec325/hw06/index.php should now be a summary-list of all the okaymon in the database (see below), followed by links to:

   • your Okaymon-entry form,
   • your demo-form handlers,
   • your initialize-database page (see below)
   and
   • this homework assignment.
   Submit your files on D2L (individually, not as a jar).

2. (5pts) Make a page which initializes your database: it creates all the necessary table(s) (via a CREATE SQL statement). Before doing that, it should drop the table(s) — so really, this page is re-setting your database.

   hint: The energy-type column should be a Foreign Key, of course.

   To help me grade, include a line at the bottom of the page which is the SQL CREATE command. Please render this line inside <code> and <pre> tags. Also, include a link to your index.php. (When grading, it will be convenient for me to visit this page, then click on some of your handle-sample-N pages, to insert new entries and verify they get added. Make sure that the first couple of sample-N's are valid data that does get inserted into the database!)

   You don't need to add the energy resistances/weaknesses, to get full credit. Instead, adding this feature is extra credit worth 5% of this assignment.

3. (10pts) Modify the ol' Okaymon form handler so that once the information is validated, the Okaymon is added to the database.
   

   • Just over-write any previously existing information in the database, for that Okaymon's name.

   • To help me grade, include a line at the bottom of the page which is the SQL INSERT command. Please render this line inside <code> and <pre> tags.

   • As discussed in lecture, be sure to guard against SQL injection — either by calling mysqli_real_escape_string on any user-provided text which becomes part of a SQL query, or (preferred, but not required) via prepared-statements. (Do this even if other validation requirements make such escaping moot.)

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

4. (5pts) Make your landing page show a summary list of all Okaymon previously entered: just the Okaymon name and its the associated energy-type. This page should also include a link to the Okaymon-entry-form page. (The list does not need to be particularly pretty — just loop over the results of the database query.)

   Make sure you that you can insert/retrieve values that have “SQL-sensitive” characters like apostrophes, spaces, and semicolons (as well as the “html-sensitive” characters like less-than and ampersand that we've already handled). Automate this testing by adding-to/modifying your sample-handle files. to include such characters.

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

5. (15pts) Each Okaymon-name on the summary list should be a link which, when clicked, brings you to a detailed-information page.

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

   Pro tip:

   From hw03 on, you already have a page which prints the summary information (on a successful submit); it used $_POST.

   You can re-factor that page so that instead of reading from $_POST, it reads from some other array, say, “$theOkaymonInfo”. Then:

   • To get the old behavior, $theOkaymonInfo = $_POST; followed by require("print-Okaymon-info.php");.
   • To meet this new requirement for this hw, read the database-info for a Okaymon into $theOkaymonInfo, and then you can just require("print-Okaymon-info.php"); from there!

   Adding a layer of indirection: is there any problem it can't solve?

   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 Okaymon — instead you'll have one page which (given a particular Okaymon-name) pulls the detailed information out of the database. How do you have a link which provides an argument (the Okaymon-name) to another page? One easy way is to have the link contain the parameter(s) explicitly, e.g. “<a href="Okaymon.php?okaymonName=pugglez">…”, and the receiving page accesses that argument through the $_GET array (e.g., $_GET['okaymonName']).

   If using arbitrary text as part of a URL, you have to guard against characters not usually allowed in URLs. See rawurlencode and its inverse rawurldecode. It is also acceptable to add an ID-number to an okaymon, and use that ID-number for some database lookups. (However, it's a good principle to not going around and adding artificial fields into your database design—things that aren't part of the actual Okaymon info.)

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

   You don't need to include the Okaymon's energy-biases on this page. If you do, it's extra-credit worth 15% of this homework, done properly.

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

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.

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

©2016, Ian Barland, Radford University Last modified 2016.Nov.04 (Fri)

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