Instantly share code, notes, and snippets.

Embed
What would you like to do?
All in one enter new & edit form
<?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();
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>View Records</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
</head>
<body>
<h1>View Records</h1>
<p><b>View All</b> | <a href="view-paginated.php">View Paginated</a></p>
<?php
// connect to the database
include('connect-db.php');
// get the records from the database
if ($result = $mysqli->query("SELECT * FROM cartoons ORDER BY toon_no desc"))
{
// display records if there are records to display
if ($result->num_rows > 0)
{
// display records in a table
echo "<table border='1' cellpadding='10'>";
// set table headers
echo "<tr><th>ID</th><th>Artist ID</th><th>Pub Date</th><th>Caption</th><th></th><th></th></tr>";
while ($row = $result->fetch_object())
{
// set up a row for each record
echo "<tr>";
echo "<td>" . $row->toon_no . "</td>";
echo "<td>" . $row->fk_artist_no . "</td>";
echo "<td>" . $row->p_date . "</td>";
echo "<td>" . $row->title . "</td>";
echo "<td><a href='viewmeta.php?toon_no=" . $row->toon_no . "'>View Toon Meta</a></td>";
echo "<td><a href='newForm.php?toon_no=" . $row->toon_no . "'>Edit Toon</a></td>";
echo "</tr>";
}
echo "</table>";
}
// if there are no records in the database, display an alert message
else
{
echo "No results to display!";
}
}
// show an error if there is an issue with the database query
else
{
echo "Error: " . $mysqli->error;
}
// close database connection
$mysqli->close();
?>
<p><a href="newForm.php">Add New Record</a></p>
</body>
</html>
<html>
<head>
<title>View Cartoon Meta</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
</head>
<body>
<?php $id = $_GET['toon_no'];
// connect to the database
include("connect-db.php");
?>
<?php
$recent_cartoon = "SELECT * FROM cartoons WHERE toon_no=" .$id;
$cartoon_result = mysql_query($recent_cartoon) or die('Error getting query');
$row = mysql_fetch_array($cartoon_result) or die('Error returning array');
?>
<h2>Artist ID Key</h2>
<table border="1">
<tr>
<th>Herblock</th><th>Conrad-Denver</th><th>Conrad-LA</th><th>Miller</th>
</tr>
<tr>
<td>1</td><td>2</td><td>3</td><td>4</td>
</tr>
</table>
<h2>Cartoon Metadata:</h2>
<table border="1">
<tr>
<th>Cartoon ID</th><th>Artist ID</th><th>Pub Date</th><th>Caption</th>
</tr>
<tr>
<td><?php echo $row['toon_no'];?></td>
<td><?php echo $row['fk_artist_no'];?></td>
<td><?php echo $row['p_date'];?></td>
<td><?php echo $row['title'];?></td>
</tr>
</table>
<?php
$recent_char = "SELECT cartoon_characters.fk_toon_no, cartoon_characters.fk_actor_no, characters.actor_no, characters.actor "
."FROM cartoon_characters, characters "
."WHERE cartoon_characters.fk_toon_no = " .$id
." HAVING cartoon_characters.fk_actor_no = characters.actor_no";
$recent_event = "SELECT cartoon_events.fk_toon_no, cartoon_events.fk_event_no, events.event_no, events.event "
."FROM cartoon_events, events "
."WHERE cartoon_events.fk_toon_no = " .$id
." HAVING cartoon_events.fk_event_no = events.event_no";
$recent_theme = "SELECT cartoon_themes.fk_toon_no, cartoon_themes.fk_theme_no, themes.theme_no, themes.theme "
."FROM cartoon_themes, themes "
."WHERE cartoon_themes.fk_toon_no = " .$id
." HAVING cartoon_themes.fk_theme_no = themes.theme_no";
$recent_keyword = "SELECT cartoon_keywords.fk_toon_no, cartoon_keywords.fk_keyw_no, keywords.keyw_no, keywords.keyword "
."FROM cartoon_keywords, keywords "
."WHERE cartoon_keywords.fk_toon_no = " .$id
." HAVING cartoon_keywords.fk_keyw_no = keywords.keyw_no";
if ( ($recent_char_result=mysql_query($recent_char)) && ($recent_event_result=mysql_query($recent_event)) && ($recent_theme_result=mysql_query($recent_theme)) && ($recent_keyword_result=mysql_query($recent_keyword)) ) {
?>
<br />
<table border='1'>
<tr>
<th>Cartoon ID</th>
<th>Actor ID</th>
<th>Actor(s)</th>
<th>Event ID</th>
<th>Event</th>
<th>Theme ID</th>
<th>Theme</th>
<th>Keyword ID</th>
<th>Keyword(s)</th>
</tr>
<?php while (($row=mysql_fetch_array($recent_char_result)) || ($row=mysql_fetch_array($recent_event_result)) || ($row=mysql_fetch_array($recent_theme_result)) || ($row=mysql_fetch_array($recent_keyword_result))) { ?>
<tr>
<td><?php echo $row['fk_toon_no'];?></td>
<td><?php echo $row['fk_actor_no'];?></td>
<td><?php echo $row['actor'];?></td>
<td><?php echo $row['fk_event_no'];?></td>
<td><?php echo $row['event'];?></td>
<td><?php echo $row['fk_theme_no'];?></td>
<td><?php echo $row['theme'];?></td>
<td><?php echo $row['fk_keyw_no'];?></td>
<td><?php echo $row['keyword'];?></td>
</tr>
<?php
} // closes while loop
} // closes if statement
?>
</table>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment