C in CRUD 20120924

update-functions.php

<?php

include 'connect.php';

//UNIVERSAL VARIABLES
$comma = ", ";
$break = "<br />";

//CONGRESS

//Create variable for congress entry
$congress = $_POST['congress'];

//Check for blank entry; this entry is required
if (empty($congress)) {
	die ("You must enter a congress in <a href='update.php'>Update Form</a>.");
}

//Create variable containing MySQL Query for finding existing congress; query creates an array and the 0th item is the congress_id
$congress_test = "SELECT congress_id FROM congress WHERE congress_id = '$congress'";
$congress_testq = mysql_query($congress_test);
$congress_row= mysql_fetch_array($congress_testq);
//echo($congress_row[0]) or die('There is no existing congress record.');

//Functions to calculate congress start and end years
function congressStart($congress) {
	$start_calc = $congress * 2 + 1787;
	return $start_calc;
}

function congressEnd($congress) {
	$end_calc = $congress * 2 + 1789;
	return $end_calc;
}
//Creates two-digit start and end years for each Congress
$start = substr(congressStart($congress), -2);
$end = substr(congressEnd($congress), -2);

//Create variable containing MySQL Query for inserting new congress
$congress_query = "INSERT INTO congress (congress_id, congress_start, congress_end ) 
						VALUES ('$congress', '$start', '$end')";
						
//Check if Congress exists (congress_id > 0)
if ($congress_row[0] > 0){
	echo ("<strong>Congress already exists.</strong>".$break);	
	echo ("Query: ".$congress_test.$break);
	$congress_id = $congress_row[0];
	}
// Create new entry if no Congress exists
elseif (mysql_query($congress_query)){
	echo ("<strong>Congress update SUCCESS. Added new record:</strong>".$break);	
	echo ("Query: ".$congress_query.$break);
	echo ("Data: ".$congress.$break);
	}
// If both fail, something is wrong.
else {
	echo ("<strong>Congress update FAILED</strong>".$break.$congress_test.$break.$congress_query.$break);
	}
//Checking that I've captured bio_id of existing record or new bio_id from created record for join_cong table
echo ("Congress: ".$congress.$break);

//BIO

//Create variables for the bio entries
$last_name = $_POST['last_name'];
$first_name = $_POST['first_name'];
$MemIndex = $_POST['MemIndex'];
$home_state = $_POST['home_state'];
$position = $_POST['position'];

//Check for blank entries. All bio entries are required.
if ($last_name == "") {
	die ("You must enter a last name in <a href='update.php'>Update Form</a>.");
}

if ($first_name == "") {
	die ("You must enter a first name in <a href='update.php'>Update Form</a>.");
}

if ($MemIndex == "") {
	die ("You must enter a MemIndex from <a href='http://bioguide.congress.gov'>Bioguide</a> in <a href='update.php'>Update Form</a>.");
}

//Create variable containing MySQL Query for finding existing MemIndex.
//Query creates an array and the 0th item is the bio_id associated with that MemIndex
$bio_test = "SELECT bio_id from bio WHERE MemIndex = '$MemIndex'";
$bio_testq = mysql_query($bio_test);
$bio_row= mysql_fetch_array($bio_testq);
//echo($bio_row[0]) or die('There is no existing bio record.');

//Create variable containing MySQL Query for inserting new bio data
$bio_query = "INSERT INTO bio (MemIndex, last_name, first_name, home_state, position) 
					VALUES ('$MemIndex','$last_name','$first_name','$home_state','$position')";

//Check if MemIndex exists (bio_id > 0)	
if ($bio_row[0] > 0){
	echo ("<strong>Bio already exists.</strong>".$break);	
	echo ("Query: ".$bio_test.$break);
	$bio_id = $bio_row[0];
	}
// Create new entry if no MemIndex exists
elseif (mysql_query($bio_query)){
	echo ("<strong>Bio update SUCCESS. Added new record:</strong>".$break);	
	echo ("Query: ".$bio_query.$break);
	echo ("Data: ".$last_name.$comma.$first_name.$comma.$MemIndex.$comma.$home_state.$comma.$position.$break);
	$bio_id = mysql_insert_id();
	}
// If both fail, something is wrong.
else {
	echo ("<strong>Bio update FAILED</strong>".$break.$bio_test.$break.$bio_query.$break);
	}
//Checking that I've captured bio_id of existing record or new bio_id from created record for join_cong table
echo ("Bio ID: ".$bio_id.$break);


//ADDRESS

//Create variables for the address entries and check for blank entries. 
//Address data is not required: create a string if they are filled and make them NULL if they are blank.
if (!empty($_POST['street'])) {
	$street = "'".mysql_real_escape_string($_POST['street'])."'";
}
else {
	$street = NULL;
}

if (!empty($_POST['city'])) {
	$city = "'".mysql_real_escape_string($_POST['city'])."'";
}
else {
	$city = NULL;
}

if (!empty($_POST['state'])) {
	$state = "'".mysql_real_escape_string($_POST['state'])."'";
}
else {
	$state = NULL;
}

if (!empty($_POST['hotel'])) {
	$hotel = "'".mysql_real_escape_string($_POST['hotel'])."'";
}
else {
	$hotel = NULL;
}

if (!empty($_POST['modern_address'])) {
	$modern_address = "'".mysql_real_escape_string($_POST['modern_address'])."'";
}
else {
	$modern_address = NULL;
}

//Create variable containing MySQL Query for finding existing street. 
//Query creates an array and the 0th item is the address_id associated with that street
$address_test = "SELECT address_id FROM address WHERE street = $street OR hotel = $hotel";
$address_testq = mysql_query($address_test);
$address_row= mysql_fetch_array($address_testq);
//echo($address_row[address_id]) or die('There is no existing address record.');

//Create variable containing MySQL Query for inserting new address data
$address_query = "INSERT INTO address (street, city, state, hotel, modern_address) 
					VALUES ($street, $city, $state, $hotel, $modern_address)";

//Check if name exists (address_id > 0)	
if ($address_row[0] > 0) {
	echo ("<strong>Address already exists.</strong>".$break);	
	echo ("Query: ".$address_test.$break);
	$address_id = $address_row[0];
	}
// Check for NULL Values
elseif ($address_row[0] == NULL){
	echo ("<strong>This guy must have lived in a cardboard box.</strong>".$break);
	$address_id = NULL;
}
// Create new entry if address does not yet exist
elseif (mysql_query($address_query)){
	echo ("<strong>Address update SUCCESS. Added new record:</strong>".$break);	
	echo ("Query: ".$address_test.$break);
	echo ("Data: ".$street.$comma.$city.$comma.$state.$comma.$hotel.$comma.$modern_address.$break);
	$address_id = mysql_insert_id();
	}
// If both fail. Something is wrong
else {
	echo ("<strong>Address update FAILED.</strong>".$break.$address_test.$break.$address_query.$break);
	}
//Checking that I've captured address_id of existing record or new address_id from created record for join_cong
echo ("Address ID: ".$address_id.$break);

//PARTY

//Create variables for selecting an existing party and creating a new party
$ex_party = mysql_real_escape_string($_POST['party']);
$new_party = mysql_real_escape_string($_POST['insert_party']);

/*
THIS IS NOT WORKING
//Check for empty values or values in BOTH text field and dropdown
if (empty($ex_party) AND empty($new_party)) {
	die ("You must select an existing or enter a new party in <a href='update.php'>Update Form</a>.");
} elseif (isset($ex_party) AND isset($new_party){
	die ("You must select EITHER an existing party OR enter a new party in <a href='update.php'>Update Form</a>.");
} 
*/

//Create MySQL queries for pulling party_id for an existing party
$party_test = "SELECT party_id from party WHERE party = '$ex_party'";
$party_testq = mysql_query($party_test);
$party_row= mysql_fetch_array($party_testq);
//echo($party_row[0]) or die('There is no existing party record.');

//Create variable containing MySQL Query for inserting a new party
$party_query = "INSERT INTO party (party) VALUES ('$new_party')";

//Check if party exists (party_id > 0)	
if ($party_row[0] > 0){
	echo ("<strong>Party already exists.</strong>".$break);	
	echo ("Query: ".$party_test.$break);
	$party_id = $party_row[0];
	}
// Create new entry if party does not exist 
elseif (mysql_query($party_query)){
	echo ("<strong>Party update SUCCESS. Added new record:</strong>".$break);	
	echo ("Query: ".$party_query.$break);
	echo ("Data: ".$new_party.$break);
	$party_id = mysql_insert_id();
	}
// If both fail. Something is wrong.
else {
	echo ("<strong>Party update FAILED</strong>".$break.$party_test.$break.$party_query.$break);
	}
//Checking that I've captured party_id of existing record or new party_id from created record for join_cong
echo ("Party ID: ".$party_id.$break);

//SOURCE

//Create variables for selecting an existing source and creating a new source
$ex_source = mysql_real_escape_string($_POST['source']);
$new_source = mysql_real_escape_string($_POST['insert_source']);

/*Check for empty values or values in BOTH text field and dropdown - NOT WORKING
if (empty($ex_source) AND empty($new_source)) {
	die ("You must select an existing or enter a new source in <a href='update.php'>Update Form</a>.");
} elseif (isset($ex_source) AND isset($new_source){
	die ("You must select EITHER an existing source OR enter a new source in <a href='update.php'>Update Form</a>.");
} 
*/

//Create MySQL queries for pulling source_id for an existing source
$source_test = "SELECT source_id from source WHERE party = '$ex_source'";
$source_testq = mysql_query($source_test);
$source_row= mysql_fetch_array($source_testq);
//echo($source_row[0]) or die('There is no existing source record.');

//Create variable containing MySQL Query for inserting a new source
$source_query = "INSERT INTO source (source) VALUES ('$new_source')";

//Check if source exists (source_id > 0)	
if ($source_row[0] > 0){
	echo ("<strong>SOURCE already exists.</strong>".$break);	
	echo ("Query: ".$source_test.$break);
	$source_id = $source_row[0];
	}
// Create new entry if source does not exist 
elseif (mysql_query($source_query)){
	echo ("<strong>Source update SUCCESS. Added new record:</strong>".$break);	
	echo ("Query: ".$source_query.$break);
	echo ("Data: ".$new_source.$break);
	$source_id = mysql_insert_id();
	}
// If both fail. Something is wrong.
else {
	echo ("<strong>Source update FAILED</strong>".$break.$source_test.$break.$source_query.$break);
	}
//Checking that I've captured party_id of existing record or new party_id from created record for address_source joiner
echo ("Source ID: ".$source_id.$break);

//UPDATE JOINER TABLES

$join_congq = "INSERT INTO join_cong (congress_num, bio_id_fk, address_id_fk, party_id_fk) 
					VALUES ('$congress', '$bio_id','$address_id', '$party_id')";
$add_sourceq = "INSERT INTO address_source (address_id_fk, source_id_fk)
					VALUES ('$address_id', '$source_id')";
					
//Update join_cong joiner table and check for failure
if (mysql_query($join_congq)){
	echo ("<strong>The join_cong joiner table has been updated</strong>".$break);	
	echo ("Query: ".$join_congq.$break);
	echo ("Data: ".$congress.$comma.$bio_id.$comma.$address_id.$comma.$party_id.$break);
	}
// Tells me something is wrong.
else {
	echo ("<strong>Joiner join_cong update FAILED</strong>".$break.$join_congq.$break);
	}

//Update address_source joiner table and check for failure
if (mysql_query($add_sourceq)){
	echo ("<strong>The add_source joiner table has been updated</strong>".$break);	
	echo ("Query: ".$add_sourceq.$break);
	echo ("Data: ".$address_id.$comma.$source_id.$break);
	}
// Tells me something is wrong.
else {
	echo ("<strong>Joiner address_source update FAILED</strong>".$break.$add_sourceq.$break);
	}

//Closing MySQL connection
mysql_close($connect);
?>

update.php

<html>
<head>
	<title>Update dc_address</title>
	<style type="text/css">
				body {
					width: 800px;
					margin-left: auto;
					margin-right: auto;
				}
	</style>
</head>
<body>
<!--Built using w3schools tutorials-->

<h1>Updating Database dc_address</h1>

<p>* Required Fields</p>

<form action="update_functions.php" method="post">

<h2>Congress</h2>
	congress number:*<br/>
	<input type="text" name="congress" size="3"><br/>

<h2>Bio</h2>
	last_name (ALL CAPS):*</br>
	<input type="text" name="last_name"><br/>
	first_name:*<br />
	<input type="text" name="first_name"><br/>
	<a href="http://bioguide.congress.gov">Bioguide</a> MemIndex:<br/>
	<input type="text" name="MemIndex" size="7"><br/>
	home state:*<br/>
		<?php include 'state_dropdown.php'; ?><br />
	position:*<br/>
		<select name="position" size='4'>
			<option> Representative </option>
			<option> Senator</option>
			<option> Delegate </option>
			<option> Resident Commissioner</option>
		</select><br/>

<h2>Address</h2>
	street:<br />
		<input type="text" name="street"><br />
	<em>Example Street Syntax: (123 1st St. NW)</em><br/>
	city: <br />
		<input type="text" name="city"><br />
	state:<br />
			<select name="state" size="3">
				<option value="empty"> --- </option> 
				<option>DC</option>
				<option>MD</option>
				<option>VA</option>			
			</select><br />
	hotel:<br/> 
		<input type="text" name="hotel"><br/>
	modern address:<br/> 
		<input type="text" name="modern_address"><br/>


<h2>Party</h2>
	party:*<br/>
		<em>Select party:</em><br/>
		<select name="party" size="5">
			<option value="NULL"> --- </option>
					<?php
					// Getting party info from mySQL party table	
					$partyq = "SELECT party FROM party;";
					$partyr = mysql_query($partyq) or die('Not connecting to party table');
					
					//Putting party array created by mysql_query() into the dropdown menu
					while($row = mysql_fetch_assoc($partyr)) {
					  echo "<option value='{$row['party']}'>{$row['party']}</option>)";
					}
					?>
		</select><br />
		<em>If party is not available, type in blank:</em>
		<input type="text" name="insert_party"><br/>

<h2>Source</h2>
	source:*<br/>
		<em>Select source:</em><br/>
		<select name="source" size="8">
			<option value="NULL"> --- </option>
					<?php
					// Getting party info from mySQL party table	
					$sourceq = "SELECT source FROM source;";
					$sourcer = mysql_query($sourceq) or die('Not connecting to party table');
					
					//Putting party array created by mysql_query() into the dropdown menu
					while($row = mysql_fetch_assoc($sourcer)) {
					  echo "<option value='{$row['source']}'>{$row['source']}</option>)";
					}
					?>
		</select><br />
		<em>If source is not available, type in blank:</em>
		<input type="text" name="insert_source"><br/>


<br /><input type="submit" value="Create"/>
</form>

</body>
</html>