Web Project Assignment 3

PhP Implementation

In this homework assignment you will write a number of php scripts that allow you to 1) query your mysql database to obtain information that your front-end will display in its forms and 2) update the database with information that the user enters into the forms.

Please observe the following constraints on your php scripts:

  1. We will test your php scripts from the command line by invoking them with the php-cgi command. For example: 
      php-cgi -f login.php email='coyote@utk.edu' password='password'
    The -f option tells php-cgi which script to execute and the name=value pairs will be translated into $_GET parameters that your script can access via the $_GET array. When you hand in the scripts in the final assignment you will change some of your submissions to use POST, but for this assignment you must use GET parameters since we will be testing your scripts using automated scripts.

  2. Your scripts should print any output in JSON format. As demonstrated in class, your php scripts should load all output into a single array and then echo out this array at the end of the script using the json_encode. Do not use multiple json_encode commands as this will cause problems in the final assignment when you send the results back to the front-end.

  3. Use prepared SQL statements to avoid issues associated with malicious attacks and special characters in the input data, like double quotes (") and single quotes (').
  4. In the following description, all $GET parameters to your php scripts are underlined.
  5. Your JSON output will be using key/value pairs for the columns and their values. The keys should have the same names as the columns.
  6. For the questions that insert, update, or delete tuples do NOT perform a SELECT query in order to show that your updates succeeded. The gradescript can query the DB itself and find what was updated.

You need to write the following .php scripts:

  1. login.php: Print the instructorId, sectionId, courseId, major, semester, and year of all sections taught by an instructor with a given email address and password (remember that you will need to convert the password string to a hash string using MySQL's PASSWORD function). Your answer should eliminate duplicate rows but note that the same sectionId could appear twice in the results because that section might be used to assess both EE and CpE majors or both CS and CpE majors. These rows are not duplicates because they will differ in the major column. For example, if the courseId for a section is COSC365, the semester is spring, the year is 2019, and both CS and CpE majors are assessed in COSC365 in spring 2019, then you will have two rows in your result table for this section--one for CS majors and one for CpE majors. The results should be ordered by year in descending order and secondarily by semester in ascending order.
  2. outcomes.php: Print the outcomeIds and outcomeDescriptions of all outcomes assessed by a sectionId for a given major. Order the output by outcomeIds.
  3. results.php: Print all outcome results for a given major and outcome that were assessed by sectionId. Print the performance description (e.g., Meets Expectations) and number of students that achieved that performance description. Order the results by performanceLevel. Note that you are printing the performance description, not the performanceLevel, but you are ordering the results by the performance Level.
  4. assessment.php: Print all assessment plans for a given major and outcome that was assessed by sectionId. Print the assessment description and weight. Order the results by weight in descending order and secondarily by assessment description in ascending order.
  5. narrative.php: Print the narrative summary for a given major and outcome that was assessed by sectionId. Print the strengths, weaknesses, and actions.
  6. checkWeight.php: Print the sectionId, instructor email, outcomeId, major, and sum of the weight fields (name it weightTotal) for any outcome whose assessments' weights for that outcome and major do not exactly equal 100. As one example, the sum of the assessment weights for EE majors for outcome 1 in section 7 is 70, so you would print the requested information for this section. Order the results by instructor email in ascending order, then by major in ascending order, and finally by outcome in ascending order.
  7. updateResults.php: Update the results relation with the given sectionId, outcomeId, major, performanceLevel, and numberOfStudents. If this tuple is not yet in the relation, then insert it rather than updating it (you need to figure out what the primary key is). You can use MySQL's INSERT INTO ... ON DUPLICATE KEY UPDATE statement to perform either the insert or update in one statement.
  8. updateAssessment.php: update the Assessments relation with the given assessmentId, sectionId, assessmentDescription, weight, outcomeId, and major. If this tuple is not yet in the relation, then insert it rather than updating it (you need to figure out what the primary key is). If the primary key is an auto-increment column and it's value is null then the tuple is not yet in the relation. It is fine to give null as the value for an auto-increment key in a INSERT INTO ... ON DUPLICATE KEY UPDATE query--SQL will automatically generate a value for the key in this case.
  9. updateNarrative.php: update the Narratives relation with the given sectionId, major, outcomeId, strengths, weaknesses, and actions. If this tuple is not yet in the relation, then insert it rather than updating it (you need to figure out what the primary key is).
  10. deleteAssessment.php: delete the assessment with the given assessmentId.
  11. updatePassword.php: update the password of the instructor with the given email. Make sure that you use MySQL's PASSWORD function to encrypt the password. You may assume that the tuple is already in the relation.

What To Submit

  1. Submit a zipped file with the .php files specified in this assignment.
  2. In the submission comments tell us the netid, password and database name (e.g., cosc465_bvanderz) you have used in your .php files. If you feel uncomfortable with giving us the password you used, then put another password in your files, but use the same password for all your .php files and make the password something unique because we will be using a script to replace your netid/password with the TA's netid/password. Do not use 'password' as your password as we use a sed script to do search and replace and our scripts will break if you use 'password' as your password.
  3. Your submission must work without us having to edit any files or provide any additional files. We will run a script that changes your netid/password/databaseName to our netid/password/databaseName.
  4. If you are working with someone, please make only one submission and list the name of the other person in the submission comments.