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.
You can explore a sample solution for this problem at
http://www.wa4e.com/solutions/res-profile/
There are several resources you might find useful:
Here are some general specifications for this 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.
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.
session_start();
unset($_SESSION['name']);
unset($_SESSION['user_id']);
header('Location: index.php');
$stmt = $pdo->prepare('INSERT INTO Profile
(user_id, first_name, last_name, email, headline, summary)
VALUES ( :uid, :fn, :ln, :em, :he, :su)');
$stmt->execute(array(
':uid' => $_SESSION['user_id'],
':fn' => $_POST['first_name'],
':ln' => $_POST['last_name'],
':em' => $_POST['email'],
':he' => $_POST['headline'],
':su' => $_POST['summary'])
);
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.
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:
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.
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 select the row that you are going to delete or edit.
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.
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.
When you are reading profile data in add.php or edit.php, do the following data validation:
All fields are requiredand redirect back to the same page.
Email address must contain @and redirect back to the same page.
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.
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:
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:
Provided by:
www.wa4e.com