Computer Science 390 – PHP Chapter 9 Lab 1

Objective: Access a MySQL database using a PHP program.

· Read through the first part of Chapter 9 up to the section on Ensuring Secure SQL until you understand all of the PHP in the Code Scripts.

· We will use the banking database that you created for last week’s assignment.

· In order to start off with a baseline for your data, delete the existing banking database from your MySQL installation and re-create the database using the SQL commands at this link to create the banking database and insert data into its tables.

· The schema and relationships diagram for the banking database are at this link.

· Set up PHP forms to allow the user to select one of the tables in the database (via a drop-down list) and to display the entire contents of the table onto a well-formatted web page (into an html table).

· Set up PHP forms to accept data from the user via text boxes, drop down lists or radio buttons as appropriate to perform the following actions on the database:

1. Add two new customers to the customers table. Use a SQL command to verify that they were added to the table.

2. Add a checking and a savings account for each of the two new customers to the accounts table. Use a SQL command to verify that they were added to the table.

3. Add 4 new transactions involving the accounts for any of your new customers to the transactions table. Use a SQL command to verify that they were added to the table.

· Execute SQL commands from a PHP form to show (in a nicely formatted html table):

1. Show the first and last names of each customer and the account_id, type and balance for each account sorted by last name.

2. For each customer show customer_id, last_name, first_name, account_id, type, balance, transaction_id, to_account_id, from_account_id, amount, and date_entered for each account that had money transferred into it.

3. For each customer show customer_id, last_name, first_name, account_id, type, balance, transaction_id, to_account_id, from_account_id, amount, and date_entered for each account that had money transferred from it.

· Submit your PHP programs to the Moodle link for this assignment.