/**
* 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';
// Validate we are working with the proper type
if ($data['type'] !== 'actor' && $data['type'] !== 'director') {
return false;
}
$find_sql = $data['type'] === 'actor' ? $actor_find_sql : $director_find_sql;
$alt_find_sql = $data['type'] === 'director' ? $actor_find_sql : $director_find_sql;
$person_insert_sql = $data['type'] === 'actor' ? $actor_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 = get_db_handle();
$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;
}
<?php
require_once 'common.php';
$id = $_GET['id'] ? $_GET['id'] : '0';
$movie_sql = 'SELECT title, year, rating, company
FROM Movie
WHERE id = :id
LIMIT 1
';
$dbh = get_db_handle();
$sth = $dbh->prepare($movie_sql);
$sth->execute(array(':id' => $id));
$movie = $sth->fetch(PDO::FETCH_ASSOC);
$cast_sql = 'SELECT Actor.id, CONCAT(Actor.first, " ", Actor.last) as Name, MovieActor.role as Role
FROM Actor
JOIN MovieActor ON MovieActor.aid = Actor.id
WHERE MovieActor.mid = :id
';
$director_sql = 'SELECT Director.id, CONCAT(Director.first, " ", Director.last) as Name
FROM Director
JOIN MovieDirector ON MovieDirector.did = Director.id
WHERE MovieDirector.mid = :id
';
$genre_sql = 'SELECT DISTINCT(genre)
FROM MovieGenre
WHERE mid = :id
';
$avg_rating_sql = 'SELECT AVG(rating)
FROM Review
WHERE mid = :id
';
function store_data_in_db($params)
{
$db = get_db_handle();
$sql = "select * from parent where first_name='{$params['0']}' and secondary_phone='{$params['36']}' and email='{$params['15']}';";
$result = mysqli_query($db, $sql);
if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_array($result);
$parent_id = $row[0];
$sql = "select * from child where parent_id='{$parent_id}' and first_name='{$params['16']}' and birthdate='{$params['22']}' ;";
//echo "parent_id".$parent_id;
$result = mysqli_query($db, $sql);
if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_array($result);
$child_id = $row[0];
$enrollment = 0;
$program = camps($params);
for ($i = 0; $i < count($program); $i++) {
$sql = "select * from enrollment where program_id='{$program[$i]}' and child_id='{$child_id}';";
$result = mysqli_query($db, $sql);
if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_array($result);
$enrollment = 1;
}
}
if ($enrollment == 1) {
write_error_page('The child is already registered for the program selected.Click<a href="Enrollment.html"> here </a> to go back to the previous screen');
exit;
} else {
insert_enrollment($child_id, $params, $db);
}
} else {
insert_child($parent_id, $params, $db);
}
} else {
### NOT A DUP
// $sql = "Insert into parent(first_name,middle_name,last_name,address1,address2,city,state,zip,primary_phone,secondary_phone,email) values ('sdsasdf','sdasdfssa','dsd','asasdfda','dsad','fsdfs','CA',91110,2222222222,1111122222,'*****@*****.**')";
// echo "'".$params[0]."'<br>";
// echo "'".$params[1]."'<br>";
// echo "'".$params[2]."'<br>";
// echo "'".$params[3]."'<br>";
// echo "'".$params[4]."'<br>";
// echo "'".$params[5]."'<br>";
// echo "'".$params[6]."'<br>";
// echo "'".$params[7]."'<br>";
// echo "'".$params[35]."'<br>";
// echo "'".$params[36]."'<br>";
// echo "'".$params[15]."'<br>";
$sql = "insert into parent(first_name,middle_name,last_name,address1,address2,city,state,zip,primary_phone,secondary_phone,email) " . "values('{$params['0']}','{$params['1']}','{$params['2']}','{$params['4']}','{$params['5']}','{$params['6']}','{$params['7']}','{$params['8']}','{$params['35']}','{$params['36']}','{$params['15']}');";
// if(mysqli_query($db,$sql)===True) echo "success";
// else echo "No Donut";
mysqli_query($db, $sql);
//$how_many = mysqli_affected_rows($db);
$parent_id = mysqli_insert_id($db);
//echo "parent_id in else".$parent_id;
if (!empty($parent_id)) {
insert_child($parent_id, $params, $db);
} else {
echo "A critical error occurred.";
}
}
mysqli_close($db);
}
<h3 id="caption">-- An experience to cherish</h3>
</div>
<?php
//Checking Database Connection
function get_db_handle()
{
$server = 'opatija.sdsu.edu:3306';
$user = '******';
$password = '******';
$database = 'jadrn002';
if (!($db = mysqli_connect($server, $user, $password, $database))) {
write_error_page("Cannot Connect!");
}
return $db;
}
$db = get_db_handle();
$UPLOAD_DIR = 'imag__pics';
$COMPUTER_DIR = '/home/jadrnXXX/public_html/proj3/imag__pics/';
function get_current_age($date)
{
return intval(date('Y', time() - strtotime($date))) - 1970;
}
print <<<ENDBLOCK
<h1>Basketball Camp Enrollment</h1>
<table id="table">
<tr>
<th>Child's First Name</th>
<th>Child's Last Name </th>
<th>Child's Preferred Name</th>
<th>Child's Image</th>
<th>Child's Gender</th>
function is_dup_record($params)
{
$db = get_db_handle();
$parent_id = 0;
$parent_phone = $params['home_area_phone'] . $params['home_prefix_phone'] . $params['home_phone'];
$sql = "SELECT id from parent where primary_phone='{$parent_phone}';";
$result = mysqli_query($db, $sql);
if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_array($result);
$parent_id = $row[0];
}
$child_id = 0;
$cname = $params['cfname'];
$sql = "SELECT id from child where parent_id={$parent_id} and first_name='{$cname}';";
$result = mysqli_query($db, $sql);
if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_array($result);
$child_id = $row[0];
}
$enrollment = 0;
$program = $_POST['program'];
for ($i = 0; $i < count($program); $i++) {
$sql = "SELECT * from enrollment where program_id=" . ($i + 1) . " and child_id={$child_id};";
$result = mysqli_query($db, $sql);
if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_array($result);
$enrollment = 1;
}
}
if ($parent_id && $child_id && $enrollment) {
return true;
}
return false;
}
