Chapter 10 - Accounts
Bank of the People
In Bitcoin We Trust
Current Accounts
Edit | Delete | Last Name | First Name | Account Type | Current Balance |
---|---|---|---|---|---|
Edit | Delete | adoUuwvoJdrcpupnQT | Ethan | Savings | 500.00 |
Edit | Delete | aGtfdaBDhcFpmlpu | Dghonson | Savings | 501.00 |
Edit | Delete | kelvin | jude | Savings | 5250.00 |
Edit | Delete | Nnamdi | Kojo | Checking | 1845.00 |
Edit | Delete | O' Taylor | Ashlie | Checking | 450.00 |
Edit | Delete | of Arc | Joan | Savings | 1300.00 |
Edit | Delete | of Arc | Joan | Checking | 2399.00 |
Edit | Delete | OSBGCVrGylCpAZ | matt | Checking | 1550.00 |
Edit | Delete | Sedaris | David | Savings | 8095.97 |
Edit | Delete | Sedaris | David | Checking | 906598.24 |
Add Account for an Existing Customer
<?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&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&path=pursue&script=accounts&p='.($page-1).'&sort='.$sort.'&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&path=pursue&script=accounts&p='.$i.'&sort='.$sort.'&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&path=pursue&script=accounts&p='.($page+1).'&sort='.$sort.'&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'); ?>