<?php
require(CONNECT);
include('includes/banking_db/banking_queries.php');
?>
	<h2>Banking Database Table Data</h2>
	<?php
		if ($result = mysqli_query($link, 'SELECT * FROM accounts;')) {
			results_to_table($result, 'Accounts');
		    /* free result set */
		    mysqli_free_result($result);
		}
	    if ($result = mysqli_query($link, 'SELECT * FROM customers;')) {
			results_to_table($result, 'Customers');
		    /* free result set */
		    mysqli_free_result($result);
		}
	    if ($result = mysqli_query($link, 'SELECT * FROM transactions;')) {
			results_to_table($result, 'Transactions');
		    /* free result set */
		    mysqli_free_result($result);
		}
	?>
	<h2>Advanced Query Examples</h2>
	<ol>
		<li>
			<ul class="examples"><h3>Aggregate Functions</h3>
				<li>
					<p>Find the smallest and largest account balances.</p>
					<pre><?php echo $q2;?></pre>
					<?php
					if ($result = mysqli_query($link, $q2)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Find the number of accounts.</p>
					<pre><?php echo $q3;?></pre>
					<?php
					if ($result = mysqli_query($link, $q3)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Find the average of all account balances.</p>
					<pre><?php echo $q1;?></pre>
					<?php
					if ($result = mysqli_query($link, $q1)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Count the number of customers</p>
					<pre><?php echo $q5;?></pre>
					<?php
					if ($result = mysqli_query($link, $q5)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
			</ul>
		</li>
		<li>
			<ul class="examples"><h3>GROUP BY</h3>
				<li>
					<p>Find the average of accounts by customer</p>
					<pre><?php echo $q4;?></pre>
					<?php
					if ($result = mysqli_query($link, $q4)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Find the total balance of all accounts and the number of accounts each customer has ordered by customer ID.</p>
					<pre><?php echo $q6;?></pre>
					<?php
					if ($result = mysqli_query($link, $q6)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Find the total balance of all accounts and the number of accounts each customer has ordered by customer NAME.</p>
					<pre><?php echo $q7;?></pre>
					<?php
					if ($result = mysqli_query($link, $q7)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Concatenate each customer’s balance into a single string</p>
					<pre><?php echo $q8;?></pre>
					<?php
					if ($result = mysqli_query($link, $q8)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
			</ul>
		</li>
		<li>
			<ul class="examples"><h3>EXPLAIN</h3>
				<li>
					<p>Find out how MySQL will run a query.</p>
					<pre><?php echo $q9;?></pre>
					<?php
					if ($result = mysqli_query($link, $q9)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
			</ul>
		</li>
		<li>
			<ul class="examples"><h3>TRANSACTIONS</h3>
				<li>
					<p>Every refresh of page transfers $1 from account 2 to account 1.</p>
					<pre>START TRANSACTION;
UPDATE accounts
SET balance = (balance-1)
WHERE account_id=2;
UPDATE accounts
SET balance = (balance+1)
WHERE account_id=1;
COMMIT;
<?php echo $q10;?></pre>
					<?php
					mysqli_query($link,'START TRANSACTION;');
					mysqli_query($link,'UPDATE accounts SET balance = (balance-1) WHERE account_id=2;');
					mysqli_query($link,'UPDATE accounts SET balance = (balance+1) WHERE account_id=1;');
					mysqli_query($link,'Commit;');
					if ($result = mysqli_query($link, $q10)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
				<li>
					<p>Transaction causes Account 2 to be $100 less than committed value.</p>
					<pre>START TRANSACTION;
UPDATE accounts
SET balance = (balance-100)
WHERE account_id=2;
SAVEPOINT save2;
UPDATE accounts
SET balance = (balance+100)
WHERE account_id=1;
ROLLBACK TO SAVEPOINT save2;
<?php echo $q10;?></pre>
					<?php
					mysqli_query($link,'START TRANSACTION;');
					mysqli_query($link,'UPDATE accounts SET balance = (balance-100) WHERE account_id=2;');
					mysqli_query($link,'SAVEPOINT save2;');
					mysqli_query($link,'UPDATE accounts SET balance = (balance+100) WHERE account_id=1;');
					mysqli_query($link,'ROLLBACK TO SAVEPOINT save2;');
					if ($result = mysqli_query($link, $q10)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
			</ul>
		</li>
		<li>
			<ul class="examples"><h3>Encryption</h3>
				<li>
					<p>Get Customer 1 pin in the decrypted form.</p>
					<pre><?php echo $q11;?></pre>
					<?php
					if ($result = mysqli_query($link, $q11)) {
						results_to_table($result);
					    /* free result set */
					    mysqli_free_result($result);
					}?>
				</li>
			</ul>
		</li>
	</ol>
<?php 
	require(DISCONNECT);
?>