ITEC325 DB connectivity

DB connectivity

Due: 2019-Apr-16 at the start of class

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

db platform: You can use either the mysql database (running on ~~php~~itec-php01.radford.edu, and all the lecture-examples use it), or the Oracle database (which is more robust, but you'll need to use php functions oci_… rather than the mysqli_… functions used in the notes). An example program connecting to the department's Oracle server is here.

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

your okaymon-handle-test-N files
your reset-database.php (see below)
your Okaymon-entry form,
this homework assignment.

Submit your files on D2L (individually, not as a jar). You are allowed to use posted-on-our-D2L solutions to early homeworks (hw05-soln suffices; hw06-soln pending)

Note that you won’t be able to run your php files directly/locally from the command-line any more (on rucs, via putty or ssh)¹, since they need to run on itec-php01.radford.edu in order to access the MySQL database as localhost.

server: itec-php01 is now also a MySQL server! Your db credentials on itec-php01 are:
username: same as RU username

password: your RU ID

db/schema: 325 followed by your RU username.
For example, mysqli_connect('localhost','ibarland','900876543','325ibarland'). Once connected, you should change your password, which can be done via: mysqli_query($connect,"SET PASSWORD = PASSWORD('your-highly-secure-password')"). ~~For now, access your files via php.radford.edu, rather than itec-php01.~~

(15pts) Make a page reset-database.php 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.
~~Since php.radford.edu’s MySqli doesn’t support foreign keys, you can still include it in your create-table statement if you like, but realize that the DB won’t actually be enforcing it (sigh).~~

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

(20pts) 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, also include a line at the bottom of the page which is the specific SQL INSERT command(s) just used. (That is, print the exact query you just made — it will contain (for example) the name of the okaymon-species just now entered, unless you used prepared statements.) 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.php 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!
To help with grading, please make sure your okaymon-handle-test-1.php and okaymon-handle-test-2.php correspond to valid okaymon (moving/renaming test files if necessary).

This allows a script to visit (in this order):
- reset-database.php,
- index.php (which will show 0 entries),
- okaymon-handle-test-1.php and okaymon-handle-test-2.php,
- and finally index.php again, which will now show exactly two entries.

(15pts) 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 okaymon-handle-test-N files. to include such characters.

(When grading, it will be convenient for me to visit this landing page, then click on some of your okaymon-handle-test-N pages, to insert new entries and verify they get added. Make sure that the first couple of test-N’s are valid data that does get inserted into the database!)

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

(20pts) Each Okaymon-name on the summary list should be a link which, when clicked, brings you to a detailed-information page. It should include the full information, except that you can get full credit without showing the energy-biases. (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="OkaymonDetails.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 (and an example). 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).

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.

¹ If you try running your database-program on rucs, and then try connecting to localhost, it fails since rucs isn’t running a MySQL database server. The error-message will be something like:

Warning: mysqli_connect(): (HY000/2002): Can’t connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

The solution, as mentioned, is to run your program over the web, so that your php program gets run on the machine itec-php01.radford.edu, which is running a MySQL database server. ↩

² This can’t be done through a gui interface, since we want it triggered via the php script. However, you might have previously once set up your table via the mysqladmin GUI interface, and then noted what actual command mysqladmin used. ↩

³ In “real life” you would probably TRUNCATE the tables rather than DROP-then-reCREATE them. However, for this homework I want to see your CREATE statements. ↩

⁴

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 (a) sets the value of a hidden input tag, and then (b) does something like document.getElementByID( 'theFormsId' ).submit().

↩

This page licensed CC-BY 4.0 Ian Barland
Page last generated

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