Due October 6th, 10:00pm
For this assignment, you'll be extending the dynamic website you built for the second phase to use MySQL for data management. All the functionality from that assignment should be retained; most of the changes will be in the backend.
|Database Design||6pt Total|
You need to create a script SQL for building the database's tables, akin to the SQL and Join Walkthroughs we've looked at in class. On the off chance that you mess up your database during development, running this script will return the database to a clean initial state.
This script should create the tables for the database. Think carefully about the name and type of each column, as well as the unique set of columns that will serve as the table's primary key. The keys of the associative arrays used for data storage in the previous phase can guide your design. I suggest a table for each conceptual entity- profiles, albums, and pictures. Depending on your design, you may have other tables representing relationships between the three main entities.
The SQL script also needs to initialize the database by inserting default data for each table. This data should consist of at least
|MySQL Backend||9pt Total|
Each of the three form handlers from the previous phase should now insert submitted data into the database using the mysqli php library. As in Phase 2, handlers should only insert data after first validating it. All three handlers should use prepared queries to prevent SQL injection attacks. As a reminder, these three handlers are:
Dynamic pages need to also use the mysqli library to fetch data by selecting rows based on parameters provided $_GET.
Album pages should now put a 'Delete' button beneath each picture that deletes the photo from the database. When clicked, the button's handler will delete the corresponding row from the picture table. The handler should also use the unlink function to remove the actual image from the photo upload folder.
|Bonus Points||2pt Total|
Implement a profile update page. Include links to this page on each profile. When clicked, the user should be presented with a form with input elements for each bit of profile data, excluding the username but including a box for the old password. This form might look like the one on the signup page. These input elements should have the user's current information as default values.
The form handler should check that the old password matches the one in the database and validate the data before updating the row in the database for that user ID.
As with all homeworks, you will submit this project in a two-step process:
- First, place all source code (stylesheets, html, and php files) in a single p3 directory. You will submit this homework under the unique id p3 using the command-line turnin per the instructions on the course homepage.
- You will also put your assignment up on your personal webspace on z. Per the FAQ for z, you'll need to place these files in /u/z/users/cs105/YOURCSID/p3.