/** * 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; }