|
<?php |
|
/* |
|
Allows the user to both create new records and edit existing records |
|
*/ |
|
|
|
// connect to the database |
|
include("connect-db.php"); |
|
|
|
// creates the new/edit record form |
|
// since this form is used multiple times in this file, I have made it a function that is easily reusable |
|
function renderForm($artist_id = '', $pub_date ='', $caption = '', $id = '', $error = '', &$actor_id = '') |
|
{ ?> |
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> |
|
<html> |
|
<head> |
|
<title><?php if ($id != '') { echo "Edit Cartoon"; } else { echo "New Record"; } ?></title> |
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> |
|
</head> |
|
<body> |
|
|
|
<h1><?php if ($id != '') { echo "Edit Cartoon"; } else { echo "New Record"; } ?></h1> |
|
<?php if ($error != '') { |
|
echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error. "</div>"; |
|
} ?> |
|
|
|
<form action="" method="post"> |
|
<div> |
|
<?php if ($id != '') { ?> |
|
<input type="hidden" name="toon_no" value="<?php echo $id; ?>" /> |
|
<p>Cartoon ID: <?php echo $id; ?></p> |
|
<?php } ?> |
|
|
|
<p>Select Cartoonist: *</p> |
|
<?php $artists = mysql_query("SELECT * FROM cartoonists"); ?> |
|
<select name='fk_artist_no'> |
|
<?php while ($row = mysql_fetch_array($artists)) { ?> |
|
<option value="<?php echo $row['artist_no']; ?>"<?php if ($artist_id == $row['artist_no']) { echo 'selected'; } ?>> |
|
<?php echo $row['f_name'] ." " .$row['l_name'] .": " ,$row['paper']; ?> |
|
</option> |
|
<?php } ?> |
|
</select><br /> |
|
|
|
<p>Enter Cartoon Publication Date: <em>(mm/dd/yyyy)</em> * |
|
<input type="text" name="p_date" size="10" value="<?php echo $pub_date; ?>"/></p> |
|
|
|
<p>Enter Caption for Cartoon: *</p> |
|
<textarea name='title' cols=50 rows=4><?php echo $caption; ?></textarea><br /> |
|
|
|
<p>Add New Character(s): <em>use comma ', ' for multiple entries</em></p> |
|
<input type="text" name="new_actors" size="50"/><br /> |
|
|
|
<p>and/or Choose Character(s): <em>command or control for multiple entries</em></p> |
|
|
|
<?php $characters = mysql_query("SELECT * FROM characters ORDER BY actor asc");?> |
|
<select name="actors[]" multiple="yes" size="10"> |
|
<option value="empty"> --- </option> |
|
<?php while ($row = mysql_fetch_array($characters)) { ?> |
|
<option value="<?php echo $row['actor_no']; ?>" |
|
<?php foreach ($actor_id as $char_id) { if ($char_id == $row['actor_no']) { echo 'selected'; } }?>> |
|
<?php echo $row['actor'];?> |
|
</option> |
|
<?php } ?> |
|
</select></br /> |
|
|
|
<br /><input type="submit" name="submit" value="Submit" /> |
|
|
|
</div> |
|
</form> |
|
</body> |
|
</html> |
|
<?php } // ends function renderForm |
|
|
|
function new_char_table($new_toon_id){ |
|
$char_to_add=mysql_real_escape_string($_POST['new_actors']); |
|
$new_char_arr = explode(', ', $char_to_add); |
|
foreach ($new_char_arr as $new_char_add) { |
|
if (!empty($new_char_add)) { |
|
$new_char = "INSERT INTO characters (actor_no, actor) VALUES ('NULL', '".$new_char_add."')"; |
|
mysql_query($new_char) or die('Error adding new character'); |
|
$new_char_id = mysql_insert_id(); |
|
$new_cartoon_actor = "INSERT INTO cartoon_characters (fk_toon_no, fk_actor_no) VALUES |
|
('".$new_toon_id."', '".$new_char_id."')"; |
|
mysql_query($new_cartoon_actor) or die('Error updating cartoon_characters table with new actor'); |
|
} |
|
} |
|
} // end function new_char_table |
|
|
|
function join_table($table_name, $table_field, $arr_element) { |
|
global $new_toon_id; |
|
$existing_element = "INSERT INTO " .$table_name ." (fk_toon_no, " .$table_field .") VALUES |
|
(".$new_toon_id.", ".$arr_element.")"; |
|
mysql_query($existing_element) or die('Error updating joiner table'); |
|
|
|
} //ends function join_table |
|
|
|
|
|
// EDIT RECORD |
|
// if the 'id' variable is set in the URL, we know that we need to edit a record |
|
if (isset($_GET['toon_no'])) { |
|
|
|
// if the form's submit button is clicked, we need to process the form |
|
if (isset($_POST['submit'])) { |
|
|
|
// make sure the 'id' in the URL is valid |
|
if (is_numeric($_POST['toon_no'])) { |
|
|
|
// get variables from the URL/form |
|
$id = $_POST['toon_no']; |
|
$artist_id = $_POST['fk_artist_no']; |
|
$pub_date =$_POST['p_date']; |
|
$caption = htmlentities($_POST['title'], ENT_QUOTES); |
|
$new_actor_id = htmlentities($_POST['new_actors'], ENT_QUOTES); |
|
$cartoon_actors=$_POST['actors']; |
|
|
|
// check that required fields are not empty |
|
if ($artist_id == '' || $pub_date == '' || $caption == '') { |
|
|
|
// if they are empty, show an error message and display the form |
|
$error = 'ERROR: Please fill in all required fields!'; |
|
renderForm($artist_id, $pub_date, $caption, $error, $id); |
|
|
|
} else { |
|
|
|
// explode date and reformat in MySQL order |
|
$pub_date=explode('/', $pub_date); |
|
$mysqlPDate = $pub_date[2].'-'.$pub_date[0].'-'.$pub_date[1]; |
|
|
|
// if everything is fine, update the record in the database |
|
if ($stmt = $mysqli->prepare("UPDATE cartoons SET fk_artist_no = ?, p_date = ?, title = ? |
|
WHERE toon_no=?")) { |
|
$stmt->bind_param("issi", $artist_id, $mysqlPDate, $caption, $id); |
|
$stmt->execute(); |
|
$stmt->close(); |
|
|
|
} else { |
|
// show an error message if the query has an error |
|
echo "ERROR: could not prepare SQL statement."; |
|
} |
|
|
|
if (!empty($new_actor_id)) { |
|
new_char_table ($id); |
|
} |
|
|
|
foreach($cartoon_actors as $cartoon_actor) { |
|
if ($cartoon_actor >= 1) { |
|
$existing_char = "REPLACE INTO cartoon_characters (fk_toon_no, fk_actor_no) VALUES |
|
(".$id.", ".$cartoon_actor.")"; |
|
mysql_query($existing_char) or die('Error updating joiner table'); |
|
} |
|
} |
|
// $existing_char=mysql_query("REPLACE INTO cartoon_characters (fk_toon_no, fk_actor_no) VALUES (". $id .", " .$cartoon_actor.")"); |
|
// mysql_query("REPLACE INTO cartoon_characters (fk_toon_no, fk_actor_no) VALUES('$id', '$cartoon_actor')"; |
|
|
|
|
|
// code to add existing characters from cartoon_actors array or delete if not in array anymore |
|
|
|
// redirect the user once the form is updated |
|
header("Location: http://localhost:8888/viewcartoons.php"); |
|
} |
|
|
|
} else { |
|
|
|
// if the 'id' variable is not valid, show an error message |
|
echo "Error!"; |
|
} |
|
} |
|
// if the form hasn't been submitted yet, get the info from the database and show the form |
|
else { |
|
|
|
// make sure the 'id' value is valid |
|
if (is_numeric($_GET['toon_no']) && $_GET['toon_no'] > 0) { |
|
|
|
// get 'id' from URL |
|
$id = $_GET['toon_no']; |
|
|
|
// get the record from the database |
|
if($stmt = $mysqli->prepare("SELECT * FROM cartoons WHERE toon_no=?")) { |
|
$stmt->bind_param("i", $id); |
|
$stmt->execute(); |
|
$stmt->bind_result($id, $artist_id, $pub_date, $caption); |
|
$stmt->fetch(); |
|
$pub_date=explode('-', $pub_date); |
|
$p_date = $pub_date[1].'/'.$pub_date[2].'/'.$pub_date[0]; |
|
$stmt->close(); |
|
} |
|
|
|
// this creates an array of all the selected characters to pass to the form in a variable |
|
$actor_query = "SELECT * FROM cartoon_characters WHERE fk_toon_no=".$id; |
|
$actor_array = mysql_query($actor_query); |
|
while ($row = mysql_fetch_assoc($actor_array)) { |
|
$actor_id[] = $row['fk_actor_no']; |
|
} |
|
|
|
// show the form |
|
renderForm($artist_id, $p_date, $caption, $id, NULL, $actor_id); |
|
|
|
|
|
// if the 'id' value is not valid, redirect the user back to the viewcartoons.php page |
|
} else { |
|
header("Location: http://localhost:8888/viewcartoons.php"); |
|
} |
|
} |
|
} |
|
|
|
// NEW RECORD |
|
// if the 'id' variable is not set in the URL, we must be creating a new record |
|
else { |
|
|
|
// if the form's submit button is clicked, we need to process the form |
|
if (isset($_POST['submit'])){ |
|
|
|
// get the form data |
|
$artist_id =mysql_real_escape_string($_POST['fk_artist_no']); |
|
$caption =mysql_real_escape_string($_POST['title']); |
|
$pub_date =$_POST['p_date']; |
|
$cartoon_actors=mysql_real_escape_string($_POST['actors']); |
|
|
|
// check that required fields are not empty |
|
if ($artist_id == '' || $caption == '' || $pub_date == '') { |
|
|
|
// if they are empty, show an error message and display the form |
|
$error = 'ERROR: Please fill in all required fields!'; |
|
renderForm($artist_id, $pub_date, $caption, $error); |
|
|
|
} else { |
|
|
|
// explode date and reformat in MySQL order |
|
$pub_date=explode('/', $pub_date); |
|
$mysqlPDate = $pub_date[2].'-'.$pub_date[0].'-'.$pub_date[1]; |
|
|
|
// insert the new cartoon into the database |
|
$new_toon = "INSERT INTO cartoons (toon_no, fk_artist_no, p_date, title) |
|
VALUES ('NULL', '".$artist_id."', '".$mysqlPDate."', '".$caption."')"; |
|
mysql_query($new_toon) or die('Error adding new cartoon '); |
|
$new_toon_id = mysql_insert_id(); |
|
|
|
new_char_table($new_toon_id); |
|
|
|
// This code creates array of existing characters and function puts those values into joiner table with new cartoon |
|
foreach($cartoon_actors as $cartoon_actor) { |
|
if ($cartoon_actor >= 1) { |
|
join_table('cartoon_characters', 'fk_actor_no', $cartoon_actor); |
|
} |
|
} |
|
|
|
// redirect the user |
|
header("Location: http://localhost:8888/viewcartoons.php"); |
|
} |
|
|
|
// if the form hasn't been submitted yet, show the form |
|
} else { |
|
renderForm(); |
|
} |
|
} |
|
|
|
// close the mysqli connection |
|
$mysqli->close(); |
|
?> |