PHP and MySQL Library Tutorial

General
Site Root
Database Home
Conect to MySQL
MySQL_Login.php
nav.php
dump.php

Clean Up resets the
database to contain
a few sensible
records.

Download Tutorial

Database
Create
Drop

Person Table
Create
Drop

Insert
Delete
Update
Search
Date-Search

Row Dump
Better View 
Table View 
Form View

Book Table
Create
Drop

Insert
Delete
Update
Search




Table View 
Form View

Loan Table
Create
Drop

Insert
Delete

Search




Table View

Documentation
Data Requirements
User Interface Design
Entity Relationship Diagrams
Data Flow Diagrams
Data Dictionary
Implementation Notes
Table Designs
Goals, Testing and Appraisal

Note - Create, Drop and Clean Up would be hidden on a real life system.
Contact - nbauers at samphire dot demon dot co dot uk
Copyright © - You may use and reproduce this material in a "not for profit" setting.

Project Goals Testing and Appraisal

Print using Landscape layout.

 

Project Goal

This column was created during the early stages of the project. Some of the higher numbered goals were added as afterthoughts

Testing

This column was created in outline form at the same time as the project goals column. Ths was done during systems analysis.

More detail and test data was added during the project design stage.

Test Result

Expected output and actual output

Appraisal

This columns was completed after testing had taken place
1 Store information about books, book loans and the people who have borrowed books. Overall system testing. Separate tests for the individual database functions were carried out. The database does what it should but improvements are needed. These are detailed below under the more specific goals.
2

Create the database

DBcreate.php will contain SQL to create the database

Execute SQL in DBcreate.php

" CREATE DATABASE aardvark;"

Database should be created

This works.

Success.

No improvements needed.
3

Drop the database

DBdrop.php will contain SQL to drop the database

Execute SQL in DBdrop.php

" DROP DATABASE aardvark;"

Database should be dropped

This works.

Success.

No improvements needed.
4

Create the person table

personCreateTable.php will contain SQL code to create the person table

Execute SQL in personCreateTable.php

"CREATE TABLE person ( person_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(5), last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, date_of_birth DATE );"

Person table should be created

This works.

Success.

No improvements needed.
5

Drop the person table

personDropTable.php will contain SQL code to drop the person table

Execute SQL in personDropTable.php

" DROP TABLE person;"

Person table should be dropped

This works.

Success.

No improvements needed.
6

Create the book table

bookCreateTable.php will contain SQL code to create the book table

Execute SQL in bookCreateTable.php

"CREATE TABLE person ( person_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(5), last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, date_of_birth DATE )"

Book table should be created

This works.

Success.

No improvements needed.
7

Drop the book table

bookDropTable.php will contain SQL code to drop the book table

Execute SQL in bookDropTable.php

" DROP TABLE book;"

Book table should be dropped

This works.

Success.

No improvements needed.
8

Create the loan table

loanCreateTable.php will contain SQL code to create the loan table

Execute SQL in loanCreateTable.php

"CREATE TABLE loan ( loan_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, accession_num MEDIUMINT UNSIGNED NOT NULL, person_id MEDIUMINT UNSIGNED NOT NULL, return_date DATE NOT NULL );"

Loan table should be created

This works.

Success.

No improvements needed.
9

Drop the loan table

loanDropTable.php will contain SQL code to drop the loan table

Execute SQL in loanDropTable.php

" DROP TABLE loan;"

Loan table should be dropped

This works.

Success.

No improvements needed.
10

Insert a new person into the person table

personInsertForm.php will contain a data entry form

personInsertFormDo.php will contain SQL code to add the record.

Fill in the data entry form on personInsertForm.php

Submit data to the personInsertFormDo.php SQL processing page.

Test Data:

Typical - Mr Fred Bloggs 2005-11-11
Invalid date - Mr Fred Bloggs 11-11-2005
Invalid missing required field - Mr Fred 2005-11-11
Invalid missing required field - Mr Bloggs 2005-11-11
Invalid date is in the past - Mr Fred Bloggs 1999-11-11

 

If the input is valid, a record shold be added to the Person table.

Success: It is possible to add a valid record.

Failure: It is also possible to enter records with one, several or every field left blank.

Failure: Date checking does not work. Invalid dates are converted to 0000-00-00. It is possible to enter past dates.

Partial Success.

The Problems: The data entry form is not validated in any way. MySQL does not detect blank required fields because PHP submits an empty string. It is possible to enter a blank record.

The Solution: Add validation to personInsertFormDo.php to generate error messages OR implement a form with validation using Macromedia flash.
11

Delete a person

personDelete.php will contain a form to allow the person/s to be selected.

personDelete.php will submit data to itself an if present SQL code to delete the record will run.

Select one or more records to be deleted by checking the check boxes.

Press Submit

This works

It is not convenient to use.

Partial Success.

The Problems: When the database contains up to 20 people, the user interface is fine. With more than 20 people it is necessary to scroll to the correct person. With more than 100 people it takes too long to locate the person. The records are not sorted making it hard to locate a person.

The Solution: Sort the data and implement a search page to locate the correct person more quickly.
11 Re-test Re-test Re-test

The data is now sorted. The other problems remain.

Old SQL code: SELECT * FROM person

New SQL code: SELECT * FROM person ORDER BY last_name, first_name