/**
* Attempts to insert a new row into the Actor or Director table
* @param $data - array of data to be inserted
* @return FALSE on error, otherwise the id of either the newly inserted row or the id of an already existing row
*/
function save_person_in_db($data)
{
// SQL query defines
$actor_find_sql = 'SELECT id
FROM Actor
WHERE first = :first and last = :last
LIMIT 1
';
$director_find_sql = 'SELECT id
FROM Director
WHERE first = :first and last = :last
LIMIT 1
';
$actor_insert_sql = 'INSERT INTO Actor(id, first, last, dob, dod, sex) VALUES (:id, :first, :last, :dob, :dod, :sex)';
$director_insert_sql = 'INSERT INTO Director(id, first, last, dob, dod) VALUES (:id, :first, :last, :dob, :dod)';
// Grabs the next row id when operating normally
$next_id_sql = 'SELECT MAX(id)+1 FROM MaxPersonID';
// Grabs the next id if the MaxPersonID table is not initialized
$next_id_sql_failsafe = 'SELECT MAX(id)
FROM (
SELECT MAX(id)+1 as id
FROM Actor
UNION
SELECT MAX(id)+1 as id
FROM Director
) as tmp
';
// Store an id if there isn't one in the table already
$next_id_failsafe_insert = 'INSERT INTO MaxPersonID(id) VALUES(:id)';
// Update the max id when everything is running smoothly
$update_next_id_sql = 'UPDATE MaxPersonID SET id = :id';
$find_sql = $director_find_sql;
$alt_find_sql = $actor_find_sql;
$person_insert_sql = $director_insert_sql;
$sql_args = array(':first' => ucfirst(strtolower((string) $data['first'])), ':last' => ucfirst(strtolower((string) $data['last'])));
// Init the db and check for existing rows
$dbh = dbHandler();
$sth = $dbh->prepare($find_sql);
if (!$sth->execute($sql_args)) {
return false;
}
$id = $sth->fetch(PDO::FETCH_COLUMN, 0);
// Row exists, bail
if ($id) {
return $id;
}
// Row doesn't exist, check the alt table if person registered there
// e.g. credited actor has now become a director
$sth = $dbh->prepare($alt_find_sql);
if (!$sth->execute($sql_args)) {
return false;
}
$alt_id = $sth->fetch(PDO::FETCH_COLUMN, 0);
$max_id_needs_update = true;
// We can do this the EasyWay™ or the HardWay™
if ($alt_id) {
$new_id = $alt_id;
$max_id_needs_update = false;
} else {
$sth = $dbh->prepare($next_id_sql);
if (!$sth->execute()) {
return false;
}
$new_id = $sth->fetch(PDO::FETCH_COLUMN, 0);
}
// Looks like we're going to have to do it the HardWay™
if (!$new_id) {
$sth = $dbh->prepare($next_id_sql_failsafe);
if (!$sth->execute()) {
return false;
}
$new_id = $sth->fetch(PDO::FETCH_COLUMN, 0);
$sth = $dbh->prepare($next_id_failsafe_insert);
if (!$sth->execute(array(':id' => $new_id - 1))) {
return false;
}
}
$data_dob = (array) $data['dob'];
$data_dod = (array) $data['dod'];
$dob = mktime(0, 0, 0, $data_dob['month'], $data_dob['day'], $data_dob['year']);
$dod = mktime(0, 0, 0, $data_dod['month'], $data_dod['day'], $data_dod['year']);
if (-1 == $dob || empty($data_dob['day']) || empty($data_dob['month']) || empty($data_dob['year'])) {
$dob = NULL;
}
if (-1 == $dod || empty($data_dod['day']) || empty($data_dod['month']) || empty($data_dod['year'])) {
$dod = NULL;
}
$insert_args = array(':id' => $new_id, ':first' => $data['first'], ':last' => $data['last'], ':sex' => $data['sex'], ':dob' => $data_dob['year'] . '-' . $data_dob['month'] . '-' . $data_dob['day'], ':dod' => $data_dod['year'] . '-' . $data_dod['month'] . '-' . $data_dod['day']);
if ($person_insert_sql != $actor_insert_sql) {
unset($insert_args[':sex']);
}
$dbh->beginTransaction();
$sth = $dbh->prepare($person_insert_sql);
$person_status = $sth->execute($insert_args);
$max_id_status = true;
if ($max_id_needs_update && isset($new_id)) {
$sth = $dbh->prepare($update_next_id_sql);
$max_id_status = $sth->execute(array(':id' => $new_id));
}
if ($person_status && $max_id_status) {
$dbh->commit();
return $new_id;
}
$dbh->rollback();
return false;
}
<link rel="stylesheet" type="text/css" href="<?php
echo 'css.php';
?>
">
<?php
require_once 'funCollection.php';
$saved = false;
$error = false;
if (isset($_POST['submit'])) {
// First, validate...
if (empty($_POST['title']) || empty($_POST['company'])) {
$error = 'You must specify all fields!';
} else {
// Looks like we're good to go!
$dbh = dbHandler();
$mmid_sql = 'SELECT id FROM MaxMovieID';
$stmt = $dbh->prepare($mmid_sql);
$stmt->execute();
$mmid = $stmt->fetchColumn();
// If the MaxMovieID table has not been initialized, do so now
if (!$mmid) {
$stmt = $dbh->prepare('SELECT MAX(id) from Movie');
$stmt->execute();
$mmid = $stmt->fetchColumn();
$stmt = $dbh->prepare('INSERT INTO MaxMovieID(id) VALUES(:id)');
$stmt->execute(array(':id' => $mmid + 1));
}
$movie_insert_sql = 'INSERT INTO Movie (id, title, year, rating, company) VALUES(:id, :title, :year, :rating, :company)';
$stmt = $dbh->prepare($movie_insert_sql);
$stmt->execute(array(':id' => $mmid + 1, ':title' => (string) $_POST['title'], ':year' => (int) $_POST['year'], ':rating' => (string) $_POST['rating'], ':company' => (string) $_POST['company']));
