Assignment: Profile Database

In this assignment you will write a simple resume database that support Create, Read, Update, and Delete operations (CRUD). You will also move user information into its own table and link entries between two tables using foreign keys. You will also add some in-browser JavaScript data validation.

Sample solution

You can explore a sample solution for this problem at

http://www.wa4e.com/solutions/res-profile/

Resources

There are several resources you might find useful:

General Specifications

Here are some general specifications for this assignment:

Databases and Tables Required for the Assignment

You will need to have a users table as follows:

CREATE TABLE users (
   user_id INTEGER NOT NULL AUTO_INCREMENT,
   name VARCHAR(128),
   email VARCHAR(128),
   password VARCHAR(128),
   PRIMARY KEY(user_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE users ADD INDEX(email);
ALTER TABLE users ADD INDEX(password);
You will also need to add a Profile table as follows:
CREATE TABLE Profile (
  profile_id INTEGER NOT NULL AUTO_INCREMENT,
  user_id INTEGER NOT NULL,
  first_name TEXT,
  last_name TEXT,
  email TEXT,
  headline TEXT,
  summary TEXT,

  PRIMARY KEY(profile_id),

  CONSTRAINT profile_ibfk_2
        FOREIGN KEY (user_id)
        REFERENCES users (user_id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
This table has a foreign key to the users table.

The Screens for This Assignment

We are going to have a number of screens (files) for this assignment. Functionality will be moved around from the previous assignment, although much of the code from the previous assignment can be adapted with some refactoring.

If the user goes to an add, edit, or delete script without being logged in, die with a message of "Not logged in".

You might notice that there are several common operations across these files. You might want to build a set of utility functions to avoid copying and pasting the same code over and over across several files.

Storing Users and Hashed Password in the Database

In this assignment, we are going to allow for more than one user to log into our system so we will switch from storing the account and hashed password in PHP strings to storing them in the database. The salt value will remain in the PHP code.

Once you create the users table above, you will need to insert a single user record into the "users" table using this SQL:

INSERT INTO users (name,email,password)
    VALUES ('UMSI','[email protected]','1a52e17fa899cf40fb04cfc42e6352f1');
The above password is the salted MD5 hash of 'php123' using a salt of 'XyZzy12*_'. You will need this user in the database to pass the assignment. You can add other users to the database is you like.

The salt value remains in the PHP code while the stored hash moves into the database. There should be no stored hash in your PHP code. You can compute the salted hash of any password / salt combination using this PHP code:

Salt-O-Matic 2000

Since the email address and salted hash are stored in the database, we must use a different approach than in the previous assignment to check to see if the email and password match using the following approach:

$check = hash('md5', $salt.$_POST['pass']);
$stmt = $pdo->prepare('SELECT user_id, name FROM users
    WHERE email = :em AND password = :pw');
$stmt->execute(array( ':em' => $_POST['email'], ':pw' => $check));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
Since we are checking if the stored hashed password matches the hash computation of the user-provided password, If we get a row, then the password matches, if we don't get a row (i.e. $row is false) then the password did not match. If he password matches, put the user_id value for the user's row into session as well as the user's name:
if ( $row !== false ) {
    $_SESSION['name'] = $row['name'];
    $_SESSION['user_id'] = $row['user_id'];
    // Redirect the browser to index.php
    header("Location: index.php");
    return;
...
Make sure to redirect back to login.php with an error message when there is no row selected.

Keeping Primary Keys Straight

This may be the first assignment that you have to keep track and properly use of two primary keys (user_id and profile_id) rather than one primary key like autos_id. This application is about building a CRUD app for profiles so the profile_id is like autos_id in the autos assignment. The user_id value is just there for login and log out purposes and to "mark" the profile entries with the user_id of the owner.

The user_id is put into the session in the login.php and read elsewhere. The profile_id is what is used in delete.php, and edit.php to sleect the row that you are going to delete or edit.

Login Data Validation in JavaScript

In addition to the PHP data validation in the previous assignment, you need to add JavaScript based data validation on the login.php screen that pops up an alert() dialog if teither field is blank or the email address is missing.

Image of the JavaScript popup

This is done using an onclick event on the form submit button that calls a JavaScript function that checks the data, puts up an alert box if there is a problem and then returns true or false depending on the validity of the data.

...
<input type="password" name="pass" id="id_1723">
<input type="submit" onclick="return doValidate();" value="Log In">
...

This is a partial implementation of the doValidate() function that only checks the password field.

function doValidate() {
    console.log('Validating...');
    try {
        pw = document.getElementById('id_1723').value;
        console.log("Validating pw="+pw);
        if (pw == null || pw == "") {
            alert("Both fields must be filled out");
            return false;
        }
        return true;
    } catch(e) {
        return false;
    }
    return false;
}

Make sure to retain the PHP data validation checks as well given that any in-browser checks can be bypassed by a determinied end-user.

Profile Data validation

When you are reading profile data in add.php or edit.php, do the following data validation:

To redirect back to the same page when the page requires a GET parameter (i.e. like edit), you need to add the GET parameter to the URL that you put in the location header using a technique similar to the following:
header("Location: edit.php?profile_id=" . $_POST["profile_id"]);
You may need to change profile_id to match the GET parameter your code is expecting and the name of the hidden parameter in your form (and $_POST) data.

What To Hand In

As a reminder, your code must meet all the specifications (including the general specifications) above. Just having good screen shots is not enough - we will look at your code to see if you made coding errors. For this assignment you will hand in:

  1. A screen shot (including the URL) of your index.php with the user logged in three resumes in the list.
  2. A screen shot (including the URL) of your edit.php showing the error message for a bad email address
  3. A screen shot (including the URL) of your delete.php showing that you are showing the confirmation screen before the delete is being done.
  4. A screen shot of your Profile database table showing three rows
  5. Source code of index.php
  6. Source code of login.php
  7. Source code of edit.php
  8. Source code of delete.php

Optional Challenges

This section is entirely optional and is here in case you want to explore a bit more deeply and test your code skillz.

Here are some possible improvements:

Database Setup Detail

More ...

Provided by: www.wa4e.com

Copyright Creative Commons Attribution 3.0 - Charles R. Severance