Skip to content

Instantly share code, notes, and snippets.

@patrickmj

patrickmj/characters database

Forked from sashaca2/characters database
Created September 16, 2012 14:08
Show Gist options
  • Save patrickmj/3732579 to your computer and use it in GitHub Desktop.
Save patrickmj/3732579 to your computer and use it in GitHub Desktop.
Download ZIP
relational database
Raw
characters database
-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 16, 2012 at 04:16 AM
-- Server version: 5.5.25
-- PHP Version: 5.4.4
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `characters`
--
-- --------------------------------------------------------
--
-- Table structure for table `actors`
--
CREATE TABLE `actors` (
`actor_no` int(11) NOT NULL AUTO_INCREMENT,
`actor` varchar(30) DEFAULT NULL,
PRIMARY KEY (`actor_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;
--
-- Dumping data for table `actors`
--
INSERT INTO `actors` (`actor_no`, `actor`) VALUES
(1, 'Dwight Einsenhower'),
(2, 'Richard Nixon'),
(3, 'Josef Stalin'),
(4, ''),
(5, 'John Q. Public'),
(6, 'Little Red Riding Hood'),
(7, 'Little Miss Muffet'),
(8, 'Little Bo Peep'),
(9, 'Jack and Jill'),
(10, 'wila hoffman'),
(11, 'john hoffman'),
(12, 'sasha hoffman'),
(13, 'wila puppy'),
(14, 'battlefield of love'),
(15, 'hello world'),
(16, ''),
(17, 'working?'),
(18, 'attempt thousand'),
(19, 'loverly'),
(20, 'brain=hurt'),
(21, 'coding hell'),
(22, 'this sucks');
-- --------------------------------------------------------
--
-- Table structure for table `cartoons`
--
CREATE TABLE `cartoons` (
`toon_no` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
PRIMARY KEY (`toon_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
--
-- Dumping data for table `cartoons`
--
INSERT INTO `cartoons` (`toon_no`, `title`) VALUES
(1, 'once and for all I will get this'),
(2, 'I will get this tonight!'),
(3, 'One more time for good measure'),
(4, 'trying this again'),
(5, 'should see a 5 and a 9 if this works'),
(6, 'once upon a time there was an adorable puppy named wila'),
(7, 'see if i can get this to work one more time before moving on'),
(8, 'third time is the charm'),
(9, 'wila is the cutest puppy ever!'),
(10, 'welcome to my insanity!!!!'),
(11, 'will it work with both????'),
(12, 'one of these things will break I know it'),
(13, 'once more to make sure not crazy'),
(14, 'did I do that right???'),
(15, 'will this work?'),
(16, 'once again a test'),
(17, 'trying again'),
(18, 'lucky in love and unlucky in code'),
(19, 'if I only add a new character'),
(20, 'trying to just add a new character again'),
(21, 'third tries the charm...right?'),
(22, 'and now if I only add an existing character will I add a new "blank" space?'),
(23, 'and now if I only add an existing character will I add a new "blank" space?');
-- --------------------------------------------------------
--
-- Table structure for table `cartoon_actors`
--
CREATE TABLE `cartoon_actors` (
`fk_toon_no` int(11) NOT NULL,
`fk_actor_no` int(11) NOT NULL,
KEY `fk_toon_no` (`fk_toon_no`),
KEY `fk_actor_no` (`fk_actor_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `cartoon_actors`
--
INSERT INTO `cartoon_actors` (`fk_toon_no`, `fk_actor_no`) VALUES
(6, 10),
(7, 11),
(8, 12),
(9, 13),
(10, 12),
(11, 14),
(11, 10),
(12, 15),
(12, 12),
(13, 16),
(13, 3),
(18, 19),
(18, 12),
(19, 20),
(20, 21),
(21, 22),
(23, 20);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `cartoon_actors`
--
ALTER TABLE `cartoon_actors`
ADD CONSTRAINT `cartoon_actors_ibfk_1` FOREIGN KEY (`fk_toon_no`) REFERENCES `cartoons` (`toon_no`),
ADD CONSTRAINT `cartoon_actors_ibfk_2` FOREIGN KEY (`fk_actor_no`) REFERENCES `actors` (`actor_no`);
Raw
dropdown2.php
<html>
<head>
<title>Update Cartoon Table of Database</title>
</head>
<body>
<form method="post" action="update4.php">
<?php
mysql_connect ("localhost", "root", "root") or die('Error: . . .');
//echo "Connected to server <br />";
mysql_select_db ("characters");
//echo "Connected to database <br />";
echo "<br />";
?>
<br />Enter Caption for Cartoon:<br />
<textarea name='caption' cols=50 rows=4></textarea>
<br />Add New Character:
<input type="text" name="new_actor" size="30"/><br />
and/or Choose a Character
<select name="actors[]" multiple="yes" size="7">
<option value="empty"> --- </option>
<?php
$actorq = "SELECT actor_no, actor FROM actors ORDER BY actor asc";
$actorr = mysql_query($actorq) or die(mysql_error());
while($row = mysql_fetch_assoc($actorr)) {
echo "<option value='{$row['actor_no']}'>{$row['actor']}</option>";
}
?>
<br /><br /><input type='submit' value='SUBMIT'/>
</form>
</body>
</html>
Raw
update4.php
<html>
<head>
<title>Updated Database</title>
</head>
<body>
<?php
mysql_connect ("localhost", "root", "root") or die('Error: . . .');
//echo "Connected to server <br />";
mysql_select_db ("sasha");
echo "Connected to database <br />";
echo "<br />";
?>
<?php
$toon_to_add=$_POST['caption'];
echo $toon_to_add;
$new_toon = "INSERT INTO cartoons (toon_no, title) VALUES (NULL, '".$toon_to_add."')";
mysql_query($new_toon) or die('Error adding new cartoon ' . mysql_error());
$new_toon_id = mysql_insert_id();
echo "<br />cartoon (" .$new_toon_id .") successfully added to database<br />";
$char_to_add=$_POST['new_actor'];
if (!empty($char_to_add))
{
$new_char = "INSERT INTO actors (actor_no, actor) VALUES ('NULL', '".$char_to_add."')";
mysql_query($new_char) or die('Error adding new character');
$new_char_id = mysql_insert_id();
echo "<br />character (" .$new_char_id .") successfully added to database<br />";
} else
{
echo "No New Character Added<br />";
}
if ($char_to_add >= 1)
{
if ($new_toon_id >= 1)
{
$new_cartoon_actor = "INSERT INTO cartoon_actors (fk_toon_no, fk_actor_no) VALUES
('".$new_toon_id."', '".$new_char_id."')";
mysql_query($new_cartoon_actor) or die('Error updating cartoon actor table with new actor');
echo "<br />new cartoon and new character successfully added to joiner table<br />";
}
} else
{
echo "No new characters to add to joiner table";
}
$cartoon_actors=$_POST['actors'];
if(!$cartoon_actors) {
//uhoh! no actors selected, so stuff in the new character, if it exists
if($new_char_id) {
$cartoon_actors = array($new_char_id);
} else {
$cartoon_actors = array();
}
}
foreach($cartoon_actors as $cartoon_actor) {
if ($cartoon_actor >= 1)
{
$old_cartoon_actor = "INSERT INTO cartoon_actors (fk_toon_no, fk_actor_no) VALUES
('".$new_toon_id."', '".$cartoon_actor."')";
mysql_query($old_cartoon_actor) or die('Error updating cartoon actor table with existing actor');
echo "<br />new cartoon and old character successfully added to joiner table<br />";
} else
{
echo "No Existing Character Added to Joiner table<br />";
}
}
/*
$q = "SELECT * FROM cartoon_actors";
$r = mysql_query($q) or die(mysql_error());
while($row = mysql_fetch_array($r)){
echo $row['fk_toon_no']. " - ". $row['fk_actor_no'];
echo "<br />";
*/
?>
<br /><a href="http://localhost/sasha/dropdown2.php">Return to entry form</a>
</body>
</html>
@patrickmj
Copy link
Author

Some changes from original:

in the HTML of the dropdown, named it actors[] instead of actor. That gets the array of ids, so that multiple select works. so in update2.php, also did some things around line 57 changed things to use the array. that also means, if nothing was selected, need to do some checks to get the data in place, or an empty array if there's nothing to add

Also, around 29 - 32, checked for whether the new character is set a little differently....not sure that the original check for a number was doing the trick

@patrickmj
Copy link
Author

oh...notice how the change to actors[] uses the array syntax for PHP. that name with [] is what makes it an array when it gets to the PHP

@patrickmj
Copy link
Author

Last bit -- I haven't done this, but there's a danger of duplicate entries for the actors: nothing checks whether a new actor being entered actually already exists. Not too bad as long as you're careful when you look through the list, but might be good to check for just in case.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment