Chapter 9 - Accounts Add a new account

Add a new account for an existing customer. A customer can have one checking and one savings account.

Output

Current Accounts

There are currently 12 accounts.
Results
CustomerAccount TypeCurrent Balance
adoUuwvoJdrcpupnQT, EthanSavings500.00
aGtfdaBDhcFpmlpu, DghonsonSavings501.00
kelvin, judeSavings5250.00
Nnamdi, KojoChecking1845.00
O' Taylor, AshlieChecking450.00
of Arc, JoanChecking2399.00
of Arc, JoanSavings1300.00
OSBGCVrGylCpAZ, mattChecking1550.00
Sedaris, DavidChecking906591.24
Sedaris, DavidSavings8095.97
Vowell, SarahChecking5293.24
ZNCWzFyVzIt, RachelSavings500.00

Add Account for an Existing Customer

Source
<?php # Script banking accounts
// add a new account for an existing customer
include (CHAPTER_PATH.'/'.$chapter.'/'.$path.'/includes/header.php');

// connect to db
require(CONNECT);

// check if form has been submitted
if($_SERVER['REQUEST_METHOD'] == 'POST') {

	// create error array 
	$errors = array();

	// check each field for validity, assign error message if fails
	// customer
	if (empty($_POST['customer'])) {
		$errors['c'] = 'Select a customer.';
	} else {	
		$name = explode(',', $_POST['customer']);
		$fn = mysqli_real_escape_string($link,trim($name[1]));
		$ln = mysqli_real_escape_string($link,trim($name[0]));
	}

	// type 
	if (empty($_POST['type'])) {
		$errors['t'] = 'Select account type.';
	} else {
		//prevent multiple accounts with the same type
		$q= "SELECT account_id FROM accounts INNER JOIN customers USING (customer_id) WHERE first_name='$fn' AND last_name='$ln' AND type='$_POST[type]'";
		if($r = mysqli_query($link,$q)) {
			$num = mysqli_num_rows($r);
			if($num>0) {
				$errors['t'] = "The customer already has an account of that type.";
			}
		}
		$t = mysqli_real_escape_string($link,trim($_POST['type']));
	}

	// balance (two decimal float!)
	if ($_POST['balance'] == '' ) {
		$errors['b'] = 'Enter an account balance.';
	} elseif(!is_numeric($_POST['balance'])) {
		$errors['b'] = 'Please enter a number.';
	} elseif ($_POST['balance'] < 500) {
		$errors['b'] = 'A new account requires a minimum deposit of $500.';
	} else {
		$b = mysqli_real_escape_string($link,trim($_POST['balance']));
	}

	if(empty($errors)) { // if no errors
		// get id of selected customer
		$q= "SELECT customer_id FROM customers WHERE first_name='$fn' AND last_name='$ln'";
		if( $r= mysqli_query($link, $q) ){
			if(mysqli_num_rows($r)==1){
				$row = mysqli_fetch_assoc($r);
				$cust_id = $row['customer_id'];
				// build insert query
				$q= "INSERT INTO accounts (customer_id,type,balance) VALUES ($cust_id,'$t',$b)";

				// submit data
				if($r = mysqli_query($link,$q)) {
					$count = mysqli_affected_rows($link);
					if($count == 1) {
						// data successfully inserted
						$message = "<h2>Thank you!</h2><p>The new account has been created!</p>";
					} else {
						// error - data not inserted
						$message = "<h2>System Error</h2><p class='error'>Your new account information could not be added to our database.<br />We apologize for any inconvenience, please <a href='javascript:history.back()'>try again</a>.</p>";
						$message .= '<p><span class="content-caption">Debugging information</span>Error message: <br />'.mysqli_error($link).'<br /><br />Query: <br />'. $q .'</p>';
					}
				} else {
					//query unsuccessful
					$message = '<h2>Error</h2><p class="error-message error">There was an error accessing the database. Please try again later.</p>';	
				}
				
				//disconnect from db
				// require(DISCONNECT);
				
				// provide feedback from submission
				echo '<div id="content" class="message">'.$message.'</div>';

				// include footer
				// include (CHAPTER_PATH.'/'.$chapter.'/'.$path.'/includes/footer.php');
				
				// exit script - do not redisplay form
				// exit(); 

			} else {
				//did not retrieve one cust_id
				$errors['flag'] = "<div class='error-message error'><h2>Error</h2><p>Account creation is not complete[1].<br />Please double check your information and resubmit after correcting the highlighted errors.</p></div>";
			}
		} else {
			//cust_id query unsuccessful
			$errors['flag'] = "<div class='error-message error'><h2>Error</h2><p>Account creation is not complete[2].<br />Please double check your information and resubmit after correcting the highlighted errors.</p></div>";
		}	
	} else {
		$errors['flag'] = "<div class='error-message error'><h2>Error</h2><p>Account creation is not complete[3].<br />Please double check your information and resubmit after correcting the highlighted errors.</p></div>";
	}
}
// begin form output. if submitted with errors - include original submission values with error messages
echo "<div id='content'>";
?>
<h1>Current Accounts</h1>
<?php
$q= "SELECT CONCAT_WS(', ', last_name, first_name) AS Customer, type AS 'Account Type', balance AS 'Current Balance'
FROM customers INNER JOIN accounts USING (customer_id) ORDER BY last_name, type";
if($r = mysqli_query($link, $q)) {
	$num = mysqli_num_rows($r);
	echo "There are currently $num account";
	echo($num>1)?'s.':'.';
	results_to_table($r);
}
?>
<h1>Add Account for an Existing Customer</h1>
<?php echo (isset($errors['flag']))? $errors['flag'] : ''; ?>
<form action="" method="post">
	<p>
		<label for='c'>Select Existing Customer</label>
		<?php 
		//get all customer names to populate dropdown menu
		$q= "SELECT DISTINCT(CONCAT_WS(', ',last_name,first_name)) AS customer FROM customers ORDER BY last_name ASC";
		if($r = mysqli_query($link,$q) ){
			echo "<select id='c' name='customer'>";
			while($row = mysqli_fetch_assoc($r)) {
				echo "<option";
				echo (isset($_POST['customer']) && $_POST['customer'] == $row['customer'])? " selected":"";
				echo " value=\"".$row['customer']."\">".$row['customer']."</option>";
			}	
			echo "</select>";
		} else {
			echo "We are experiencing technical difficulties. Try back later.";
		}
		?>
		<?php echo (isset($errors['c']))?'<span class="error">'.$errors['c'].'</span>' : ''; ?>
	</p>
	<p>
		<label for='t'>Account Type: </label>
		<select id='t' name="type">
			<option <?php if (isset($_POST['type']) && $_POST['type'] == "Checking") echo 'selected '; ?> value="Checking">Checking</option>
			<option <?php if (isset($_POST['type']) && $_POST['type'] == "Savings") echo 'selected '; ?>value="Savings">Savings</option>
		</select>
		<?php echo (isset($errors['t']))?'<span class="error">'.$errors['t'].'</span>' : ''; ?>
	</p>
	<p>
		<label for='b'>Beginning Balance: </label>
		<input type="text" id='b' name="balance" size="10" maxlength="20" value="<?php if (isset($_POST['balance'])) echo $_POST['balance']; ?>"  />
		<?php echo (isset($errors['b']))?'<span class="error">'.$errors['b'].'</span>' : ''; ?>
	</p>
	<p>
		<input type="submit" name="submit" value="Add Account" />
	</p>
</form>
<?php
//disconnect from db
require(DISCONNECT);
echo "</div>";
include (CHAPTER_PATH.'/'.$chapter.'/'.$path.'/includes/footer.php');
?>