In this assignment you will extend our simple resume database to support Create, Read, Update, and Delete operations (CRUD) into a Position table that has a many-to-one relationship to our Profile table.
This assignment will use JQuery to dynamically add and delete positions in the add and edit user interface.
You can explore a sample solution for this problem at
http://www.wa4e.com/solutions/res-position/
There are several resources you might find useful:
This assignment will add one more table to the database from the previous assignment. We will create a Position table and connect it to the Profile table with a many-to-one relationship.
CREATE TABLE Position (
position_id INTEGER NOT NULL AUTO_INCREMENT,
profile_id INTEGER,
rank INTEGER,
year INTEGER,
description TEXT,
PRIMARY KEY(position_id),
CONSTRAINT position_ibfk_1
FOREIGN KEY (profile_id)
REFERENCES Profile (profile_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
There is no logical key for this table.
The rank column should be used to record the order in which the positions are to be displayed. Do not use the year as the sort key when viewing the data.
You should include the JQuery JavaScript along with the bootstrap CSS in your code similar to the following:
<head>
...
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"
integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7"
crossorigin="anonymous">
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap-theme.min.css"
integrity="sha384-fLW2N01lMqjakBkx3l/M9EahuwpSfeNvV63J5ezn3uZzapT0u7EYsXMjQV+0En5r"
crossorigin="anonymous">
<script
src="https://code.jquery.com/jquery-3.2.1.js"
integrity="sha256-DZAnKJ/6XZ9si04Hgrsxu/8s717jcIzLy3oi35EouyE="
crossorigin="anonymous"></script>
...
</head>
We will be extending the user interface of the previous assignment to implement this assignment. All of the requirements from the previous assignment still hold. In this section we will talk about the additional UI requirements.
If the user goes to an add, edit, or delete script without being logged in, die with a message of "ACCESS DENIED".
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 addition to all of the validation requirements from the previous assignment, you must make sure that for all the positions both the year and description are non-blank and that the year is numeric.
All fields are requiredor
Year must be numeric
If you look at the sample implementation, it only allows a maximum of nine positions in the form. This is checked and enforced in the JavaScript for both the add.php and edit.php code.
The logic is somewhat simple and gets confusing when there is a combination of adds and deletes. It will never add more than nine new or total positions, but if you delete some of the positions, you do not get a postion "back" to re-add unless you press "Save". So if you add eight positions and then delete five positions without pressing "Save", you can only add one more entry rather than four more entries.
This makes the JavaScript more simple and you are welcome to take the same approach.
The result is that if you add two positions and delete one position, you will end up with a form that looks like the following in the generated document object model:
<div id="position1">
<p>Year: <input type="text" name="year1" value="">
<input type="button" value="-" onclick="$('#position1').remove();return false;"></p>
<textarea name="desc1" rows="8" cols="80"></textarea>
</div>
<div id="position3">
<p>Year: <input type="text" name="year3" value="">
<input type="button" value="-" onclick="$('#position3').remove();return false;"></p>
<textarea name="desc3" rows="8" cols="80"></textarea>
</div>
In a sense we are simulating an array with the naming convention of the fields
with the number at the end of the field. A way to handle multiple inputs with
a naming convention like this is to use code like the following:
function validatePos() {
for($i=1; $i<=9; $i++) {
if ( ! isset($_POST['year'.$i]) ) continue;
if ( ! isset($_POST['desc'.$i]) ) continue;
$year = $_POST['year'.$i];
$desc = $_POST['desc'.$i];
if ( strlen($year) == 0 || strlen($desc) == 0 ) {
return "All fields are required";
}
if ( ! is_numeric($year) ) {
return "Position year must be numeric";
}
}
return true;
}
Note that we handle gaps by simply checking the data that is present and skipping
any data that is missing.
When you are building the add.php code to add a new profile and some number of positions, you need to insert the profile_id as a foreign key for each of the position rows. But since you have not yet added the profile you do now know the profile_id which will be selected by the database.
Fortunately there is a way to ask PDO for the most recently inserted primary key after the insert has been done using the lastInsertId() method provided by PDO. Here is some sample code:
// Data is valid - time to insert
$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'])
);
$profile_id = $pdo->lastInsertId();
...
$stmt = $pdo->prepare('INSERT INTO Position
(profile_id, rank, year, description)
VALUES ( :pid, :rank, :year, :desc)');
$stmt->execute(array(
':pid' => $profile_id,
':rank' => $rank,
':year' => $year,
':desc' => $desc)
);
$rank++;
The variable $profile_id contains the primary key of the newly created profile
so you can include it in the INSERT into the Position table.
When you implement edit.php the user can do any combination of adds, removals, or edits of the position data. So when you are processing the incoming POST data, you need to somehow get the data in the database to match the incoming POST data.
One (difficult) approach is to retrieve the "old" positions from the database, and loop through all old positions and figure out which need to be deleted, updated, or inserted. If you want to try to do that for this assignment - feel free - but consider it an "extra challenge".
For your first implementation of handling the POST data in edit.php just delete all the old Position entries and re-insert them:
// Clear out the old position entries
$stmt = $pdo->prepare('DELETE FROM Position
WHERE profile_id=:pid');
$stmt->execute(array( ':pid' => $_REQUEST['profile_id']));
// Insert the position entries
$rank = 1;
for($i=1; $i<=9; $i++) {
if ( ! isset($_POST['year'.$i]) ) continue;
if ( ! isset($_POST['desc'.$i]) ) continue;
$year = $_POST['year'.$i];
$desc = $_POST['desc'.$i];
$stmt = $pdo->prepare('INSERT INTO Position
(profile_id, rank, year, description)
VALUES ( :pid, :rank, :year, :desc)');
$stmt->execute(array(
':pid' => $_REQUEST['profile_id'],
':rank' => $rank,
':year' => $year,
':desc' => $desc)
);
$rank++;
}
This approach has the nice advantage that you are reusing code between
edit.php and add.php. The only difference is that in edit.php you just remove
the existing entries first.
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:
Here are some general specifications for this assignment:
Provided by:
www.wa4e.com