/** * 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']));