Assignments -Third Semester

CodeCooker Home Page

Name Description Language(s) Other Requirements
Refresher in PHP Review Chapter 1-5 in the PHP textbook. Do the exercises if it helps you with the review. It will be assumed that you will be able to handle variables, conditionals, loops, file writing, string manipulation, arrays, and functions when completed. PHP/HTML Be able to answer questions about basic PHP functionality and write programs to demonstrate this ability.
MySQL Databasing We will be building an online store following the examples in our book but with modifications. The book shows the initial creation and management of a database created by typing commands directly into the database server. We will use PHPMyAdmin to accomplish this. You will find PHPMyAdmin installed in your root directory on the server. Log in with your standard log in like you do to FTP.

Getting Started

Read Chapter 7 - This will explain the concepts of a relational database, what tables are, and the function of keys.
PHP/HTML
Setting Up Your Tables Chapter 8, 9, & 10

Read through Chapter 8. When you get to "Creating Database Tables," look at Listing 8.1. Use PHPMyAdmin to create the "customers" table shown in that listing. Leave the other tables for later.

Open the phpMyAdmin tool. (Instruction for this are elsewhere.)

Jump over to Chapter 9 and read through the first few pages covering "Inserting Data into the Database." Don't write the code yet, just see how the commands in MySQL are structured.

Now go to Chapter 10 and read the section on "Setting Up a Connection."

Now build the form page as shown in Figure 10.3 and the program shown in Listing 10.4. Make these changes to the program:
  • use the short version for retrieving the variables from your form: $_POST instead of $HTTP_POST_VARS
  • Do not use mysql_pconnect. Use mysql_connect.
  • instead of 'bookorama' use your username.
  • instead of 'bookorama123' use your student ID number
  • instead of 'books' use your username
Test the form and the program. After running it, go browse your table with PHPMyAdmin to make sure the data arrived properly.
PHP/HTML/PHPMyAdmin For more examples, try this site: www.php-mysql-tutorial.com
Retrieving Table Data to a Web Page Now that we have data inserted into a table we need a way to list it out to the browser.

Read the section in Chapter 9, "Retrieving Data from the Database."

Read the section in Chapter 10, "Retrieving the Query Results."

Write a PHP program that will create a table showing all the data in your customers table.
PHP/HTML For more examples, try this site: www.php-mysql-tutorial.com
Editing and Deleting Table Data In this section you will learn how to edit existing table data and delete customers from the table.

Read the sections in Chapter 9, "Updating Records in the Database" and "Deleting Records in the Database."

Build a web page that lists the customers with hyperlinks to either Edit or Delete data for each customer. For example:

Edit Delete Joe Bob


When click on Delete, that customer will be deleted. When clicking on Edit, you will open a new page that has your original customer entry form preloaded with that customer's data. The user can make changes to everything except the customer ID number. Then they save it and it replaces the original data.

PHP/HTML
Creating a Setup Program Write a program the builds the tables for your bookstore.

While we can use PHPMyAdmin to create tables as we did above, we cannot expect the end user of our software to know how to do that or have the administrative priveledges to do so. To create the tables we need a program to do so. Write this program in PHP, call it setup.php. It will connect to your database and then run multiple queries, each one setting up a table. Here is a sample that creates a table called "tasks" and sets up various fields:



$db = mysql_connect('localhost', 'your_username', 'your_password');

if(!$db)
{
	echo 'Cannot find the database';
	exit;
}

$dbname = 'your_username';
mysql_select_db($dbname) or die('Cannot select the database');	

$query = 'CREATE TABLE tasks( '.
			'task_number INT NOT NULL AUTO_INCREMENT, '.
			'customer_number VARCHAR(20) NOT NULL, '.
			'time_created VARCHAR(20) NOT NULL, '.
			'date_created VARCHAR(20) NOT NULL, '.
			'task_description VARCHAR(999) NOT NULL, '.
			'requested_by VARCHAR(20) NOT NULL, '.
			'notes VARCHAR(999) NOT NULL, '.			
			'task_status VARCHAR(20) NOT NULL, '.			
			'PRIMARY KEY(task_number))';

$result = mysql_query($query);			

if ($result == '1'){echo 'Table created: tasks
'; }

To create the additional tables, build another query and run it.

Create these tables for the bookstore as shown in the book (read Chapter 7 and 8):

customers
orders
books
order_items
book_reviews
PHP/HTML
Adding editing tools to your administrative page In a previous step you wrote PHP programs that allowed you to enter, edit, and delete data in a table without using PHPMyAdmin. We now have additional tables in our databse and need to perform the same functions on them.

Copy your add, edit, and delete programs, saving them under different names, one set for each of your new tables.

Create a main administrator page that shows links to each of these programs. You might want to do something like this:

Customers Orders Books Order_items Book_reviews
Add data Add data Add data Add data Add data
Edit data Edit data Edit data Edit data Edit data
Delete data Delete data Delete data Delete data Delete data
PHP/HTML
Building the Front Page Set up the front page of your online bookstore. This is a standard web page but could be saved with a .php file extension.

Include the following features on the page:
  • the name of your store
  • a random "featured" book from your inventory
  • a search box with a pull-down next to it to search by title or author
  • a link to a "new customer" page where new customers can add themselves to the database
  • log in form(s) for customer and admin that links to a "access.php" program

This page should be named index.htm or index.php

PHP/HTML
Building the Authentication Page This page will check whether the user or admin, logging in from the main page, is authorized to enter the system.

The program will do the following:
  • retrieve the form variables
  • check the sign in data against the username and password list
  • if log in is incorrect, present a page that says so
  • if log in is correct, start a session and foward the user to the appropriate page

To start a session and forward the user to the appropriate page, consider this example:

Let's say that we are authenticating the admin soley by password:


	
// compare passwords
	if ($pass_from_file != $admin_password_from_form) {
	
	// the passwords did not match so we open a page that tells them so
	// this is a separate file save in the same directory as the current program
	// the 'include' inserts it into this program and then this 
	// program terminates with the 'exit' command
	
		include 'error_password_problem.php';
		exit;
	} 
	else {
	
	// the passwords did match so we start a session setting 
	// the session variable to the $admin_password_from_form
	
	session_start();

      $_SESSION['admin_password'] = $admin_password_from_form;


	// this now forwards the operation to the admin.php program
	
	header('Location: admin.php');
    exit;
   }


PHP/HTML
Add session control to your Admin page Page To keep the undesirables out of your admin page, add the following code to the top of the page:


session_start();

// check for freeloaders
if (!isset($_SESSION['admin_password'])){echo 'Access denied'; exit;}


In the above example, we check that the user has been authorized by checking the status of the session variable that was set in the previous program. You should now include the same code in all of your other programs that are under admin control.



PHP/HTML
Building new customer's page Build the page that allows the user to create a new customer account. You can clone your admin program for this.

(Do we need to rework the customer table to include a password?)

PHP/HTML
Building the customer's page Build the page that the customer will see after they have successfully logged in.

Features:
  • place an order
  • review past orders


PHP/HTML




CodeCooker Home Page