star Phase 3 star

SQL

Due October 6th, 10:00pm

star Assignment star

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.

Requirements

Requirement Value
(15pt Total)
Database Design 6pt Total

Table Design

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.

4pt

Database Initialization

The SQL script also needs to initialize the database by inserting default data for each table. This data should consist of at least

  • Four Users
  • One Album per User (Four Total)
  • Three pictures per album (Twelve Total)

2pt
MySQL Backend 9pt Total

Form Handling

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:

  • User Signup
  • Photo Uploader
  • Album Creator

5pt

Page Generation

Dynamic pages need to also use the mysqli library to fetch data by selecting rows based on parameters provided $_GET.

3pt

Picture Deletion

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.

1pt
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.

1pt

star Submission Instructions star

As with all homeworks, you will submit this project in a two-step process: