Homework 35 points - March 23, 2006

Due at the end of next class period

REQUIRED FOR CREDIT: Before you start, at the mysql prompt type the following:

mysql> tee /home/YOURUSERNAME/mySQL_homework7_YOURUSERNAME.txt

This homework assignment will be changing your existing users information into a database.

5 points - create a table to replace the file you used to hold the user name and passwords of the users (login.txt) and the file you used to hold user information (users.txt).

This table must have a field for each field in the users.txt and login.txt files along with an auto incrementing userID field, however do not duplicate any field names (i.e. username fields from the login.txt and users.txt files do not need to be entered twice). All fields must be a reasonably appropriate data type (char, varchar, date, blob, int, etc...).

5 points - populate your database

Insert all the data in your existing files into your new tables. Make sure the passwords are encrypted using the PASSWORD('myPassWord') in your SQL. Consult the mysql online documentation if you have any questions.

5 points - Create a table to hold information about the images

This table must have the following fields:

• ImageID (auto incrementing)
• UserID (to reference the users table)
• ImageName
• ImagePath
• ImageType
• ImageSize

Email the "/home/YOURUSERNAME/mySQL_homework7_YOURUSERNAME.txt" to me

10 points

Update your registration script to interface with your mySQL database rather than a file. This includes doing the following:

• If the registration is valid, insert a new row into your users table.
• Make sure the password is encrypted with the PASSWORD() command.

5 points: Update your login script.

Update the script function / script you use to log a use in. This must do the following:

• When the user enters their password, it must check the database for a vaid username / password combination.
• If the user name / password combination is valid, set the session variable.
• If the user name / password combination is valid, update the "lastLoggedIn" field in your table.

5 points: Update the image upload page.

Update your upload script to work with your mySQL database. This should include the following:

• If all the checks are valid (file size, total images, file type) insert a new entry into your images table.
• Change the display listing to be populated from a mySQL query.

Hints:

It may be advantageous to create a function to handle your database interactions. Perhaps one for selecting data and another for inserting, updating and deleting data.

Questions or problems, feel free to email me at: REDACTED. Make sure you include your code. That will help me better answer and address any problems.

Extra Credit