Chapter 10 - Accounts View and add new accounts

Output

Current Accounts

Viewing 1-10 of 12 accounts. Sorted by Last Name Ascending.
EditDeleteLast NameFirst NameAccount TypeCurrent Balance
EditDeleteadoUuwvoJdrcpupnQTEthanSavings500.00
EditDeleteaGtfdaBDhcFpmlpuDghonsonSavings501.00
EditDeletekelvinjudeSavings5250.00
EditDeleteNnamdiKojoChecking1845.00
EditDeleteO' TaylorAshlieChecking450.00
EditDeleteof ArcJoanSavings1300.00
EditDeleteof ArcJoanChecking2399.00
EditDeleteOSBGCVrGylCpAZmattChecking1550.00
EditDeleteSedarisDavidSavings8095.97
EditDeleteSedarisDavidChecking906598.24

1 2 Next

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);

// START PAGINATION CONFIG
//allow user to set results per page - post(from form submission) overrides get(in pagination links)
// if(isset($_POST['d']) && is_numeric($_POST['d']) && $_POST['d'] > 0 ) {
// 	$display = $_POST['d'];
// } elseif(isset($_GET['d']) && is_numeric($_GET['d']) && $_GET['d'] > 0 ) {
// 	$display = $_GET['d'];
// }else {
	$display = 10;
// }

//determine which page we are on using url parameters
if (isset($_GET['p']) && is_numeric($_GET['p'])) { 
	$page = $_GET['p'];
} else {
	$page = 1;
}
// Determine how many total pages in result set
$q = "SELECT COUNT(account_id) FROM accounts";
$r = @mysqli_query ($link, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$totalResults = $row[0];
// Calculate the number of pages...
if ($totalResults > $display) { // More than 1 page.
	$pages = ceil ($totalResults/$display);
} else {
	$pages = 1;
}
//reset $page if user is naughty and overwrites $_GET variable
if($page>$pages) $page=$pages;
if($page<1) $page=1;

// set the starting point in the database to return results...
$start = (($page * $display) - $display);
// END PAGINATION Config

// START SORTING functionality
// Default is by last name ASC.
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'ln';

// Determine the sorting order:
switch ($sort) {
	case 'ln':
		$order_by = 'last_name';
		$sort = 'ln';
		$col = 'Last Name';
		break;
	case 'fn':
		$order_by = 'first_name';
		$sort = 'fn';
		$col = 'First Name';
		break;
	case 't':
		$order_by = 'type';
		$sort = 't';
		$col = 'Type';
		break;
	case 'b':
		$order_by = 'balance';
		$sort = 'b';
		$col = 'Balance';
		break;
	default:
		$order_by = 'last_name';
		$sort = 'ln';
		break;
}
$sortCols = array(
	'ln' => 'Last Name',
	'fn' => 'First Name',
	't' => 'Account Type',
	'b' => 'Current Balance'	
);
$dir = (isset($_GET['dir'])) ? $_GET['dir'] : 'ASC';
switch ($dir) { // allow only two values
	case 'DESC':
		$dir='DESC';
		break;
	default:
		$dir = 'ASC';
		break;
}
//format string for display
$column = ucwords(str_replace('_', ' ', $order_by));
$column .= ($dir=='ASC')?' Ascending':' Descending';
//format string for sql orderby clause
$order_by .= ' '.$dir;
// END SORTING 

// 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>';	
				}
				
				// provide feedback from submission
				echo '<div id="content" class="message">'.$message.'</div>';

			} 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 account_id AS id, last_name AS 'Last Name', first_name AS 'First Name', type AS 'Account Type', balance AS 'Current Balance'
FROM customers INNER JOIN accounts USING (customer_id) ORDER BY $order_by LIMIT $start, $display";
if($r = mysqli_query($link, $q)) {
	$num = mysqli_num_rows($r);
	if($num>0){
		// Print how many accounts there are:
		echo "<div class='message'>Viewing ";
		echo $start+1;
		echo "-";
		echo ($page!=$pages)? $start+$display : $totalResults;
		echo "  of <b>$totalResults</b> accounts. Sorted by $column.</div>\n";
		results_to_table_mod($r,'','id','edit account','delete account','chapter=10&amp;path=pursue','accounts',$page,$sort,$sortCols,$dir);
	}
	// Build navigation through paginated results
	if ($pages > 1) {
		echo '<p class="pagination">';
		// If it's not the first page, make a Previous button:
		if ($page != 1) {
			echo '<a href="index.php?chapter=10&amp;path=pursue&amp;script=accounts&amp;p='.($page-1).'&amp;sort='.$sort.'&amp;dir='.$dir.'">Previous</a> ';
		}
		// Make all the numbered pages:
		for ($i = 1; $i <= $pages; $i++) {
			if ($i != $page) {
				echo '<a href="index.php?chapter=10&amp;path=pursue&amp;script=accounts&amp;p='.$i.'&amp;sort='.$sort.'&amp;dir='.$dir.'">' . $i . '</a> ';
			} else {
				echo '<b>'.$i.'</b> ';
			}
		} 
		// If it's not the last page, make a Next button:
		if ($page != $pages) {
			echo '<a href="index.php?chapter=10&amp;path=pursue&amp;script=accounts&amp;p='.($page+1).'&amp;sort='.$sort.'&amp;dir='.$dir.'">Next</a>';
		}
		echo '</p>';	
	} 
}
?>
<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');
?>