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.

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.

Database Clean Up

This page ...

Drops the database (if it exists)
Re-creates the database
Re-creates the three tables
Adds sample records to the tables

The following lines show the SQL statements used to clean up the database.

DROP DATABASE aardvark

CREATE DATABASE aardvark

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

INSERT INTO person
(
  title, last_name, first_name, date_of_birth
)
VALUES
(
  "Mr", "Bloggs", "Fred", "1992-12-27"
)

INSERT INTO person
(
  title, last_name, first_name, date_of_birth
)
VALUES
(
  "Ms", "Jane", "Mary", "1991-11-24"
)

INSERT INTO person
(
  title, last_name, first_name, date_of_birth
)
VALUES
(
  "Mr", "Doe", "John", "1982-05-09"
)

CREATE TABLE book
(
  accession_num MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  isbn          CHAR(13) NOT NULL,
  book_title    VARCHAR(100) NOT NULL,
  author        VARCHAR(50) NOT NULL,
  publisher     VARCHAR(50) NOT NULL
)

INSERT INTO book
(
  isbn, book_title, author, publisher
)
VALUES
(
  "9780140303189", "Jack Holborn", "Leon Garfield", "Penguin Books"
)

INSERT INTO book
(
  isbn, book_title, author, publisher
)
VALUES
(
  "9780450058867", "Chapter House Dune", "Frank Herbert","New English Library"
)

INSERT INTO book
(
  isbn, book_title, author, publisher
)
VALUES
(
  "9780450058867", "Chapter House Dune", "Frank Herbert","New English Library"
)

INSERT INTO book
(
  isbn, book_title, author, publisher
)
VALUES
(
  "9780340186119", "The Hollow Hills", "Mary Stewart", "Coronet Books"
)

CREATE TABLE loan
(
  accession_num MEDIUMINT UNSIGNED NOT NULL,
  person_id     MEDIUMINT UNSIGNED NOT NULL,
  return_date   DATE NOT NULL
)

INSERT INTO loan
(
  accession_num, person_id, return_date
)
VALUES
(
  "1", "2", "2005-01-30"
)

INSERT INTO loan
(
  accession_num, person_id, return_date
)
VALUES
(
  "2", "1", "2005-01-28"
)

INSERT INTO loan
(
  accession_num, person_id, return_date
)
VALUES
(
  "4", "3", "2005-01-05"
)

Code Listing : cleanUp.php

<?php
  require "../MySQL_Login.php";    // Contains host, username, password, and database
	
  $link = mysql_connect($host, $user, $password) or die(mysql_error());

  // DROP THE DATABASE IF IT EXISTS
  if (mysql_select_db ($database))    // If it exists ...
  {
    $sql    = "DROP DATABASE $database";
    $sqls[] = $sql;        // Add the sql to an array to be displayed later
    $result = mysql_query($sql, $link) or die(mysql_error());
  }
  
  // CREATE THE DATABASE
  $sql    = "CREATE DATABASE $database";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());
  
  // SELECT THE DATABASE
  mysql_select_db ($database) or die(mysql_error());
  
  // CREATE THE person TABLE
  $sql = 
"CREATE TABLE person
(
  person_id     MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title         VARCHAR(5),
  last_name     VARCHAR(30) NOT NULL,
  first_name    VARCHAR(30) NOT NULL,
  date_of_birth DATE
)";  
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());  

  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO person
(
  title, last_name, first_name, date_of_birth
)
VALUES
(
  \"Mr\", \"Bloggs\", \"Fred\", \"1992-12-27\"
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());  
  
  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO person
(
  title, last_name, first_name, date_of_birth
)
VALUES
(
  \"Ms\", \"Jane\", \"Mary\", \"1991-11-24\"
)";  
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());
  
  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO person
(
  title, last_name, first_name, date_of_birth
)
VALUES
(
  \"Mr\", \"Doe\", \"John\", \"1982-05-09\"
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());
  
  // CREATE THE book TABLE
  $sql = 
"CREATE TABLE book
(
  accession_num MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  isbn          CHAR(13) NOT NULL,
  book_title    VARCHAR(100) NOT NULL,
  author        VARCHAR(50) NOT NULL,
  publisher     VARCHAR(50) NOT NULL
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());  

  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO book
(
  isbn, book_title, author, publisher
)
VALUES
(
  \"9780140303189\", \"Jack Holborn\", \"Leon Garfield\", \"Penguin Books\"
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());

  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO book
(
  isbn, book_title, author, publisher
)
VALUES
(
  \"9780450058867\", \"Chapter House Dune\", \"Frank Herbert\",\"New English Library\"
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());

  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());

  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO book
(
  isbn, book_title, author, publisher
)
VALUES
(
  \"9780340186119\", \"The Hollow Hills\", \"Mary Stewart\", \"Coronet Books\"
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());

  // CREATE THE loan TABLE
  $sql = 
"CREATE TABLE loan
(
  accession_num MEDIUMINT UNSIGNED NOT NULL,
  person_id     MEDIUMINT UNSIGNED NOT NULL,
  return_date   DATE NOT NULL
)";  
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());  

  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO loan
(
  accession_num, person_id, return_date
)
VALUES
(
  \"1\", \"2\", \"2005-01-30\"
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());

  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO loan
(
  accession_num, person_id, return_date
)
VALUES
(
  \"2\", \"1\", \"2005-01-28\"
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());

  // INSERT SAMPLE RECORD
  $sql = 
"INSERT INTO loan
(
  accession_num, person_id, return_date
)
VALUES
(
  \"4\", \"3\", \"2005-01-05\"
)";
  $sqls[] = $sql;        // Add the sql to an array to be displayed later
  $result = mysql_query($sql, $link) or die(mysql_error());

  mysql_close($link);
?>

<html>
<head>
<title>Database Clean Up</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<?php include "nav.php"; ?>

<h2>Database Clean Up</h2>

<p>This page ...</p>
<ul>
  <li>Drops the database (if it exists)</li>
  <li>Re-creates the database</li>
  <li>Re-creates the three tables</li>
  <li>Adds sample records to the tables</li>
</ul>
<p>
  <strong>
     The following lines show the SQL statements used to clean up the database.
   </strong>
</p>
<hr>
<?php 
  for ($ii = 0;  $ii < count($sqls);  $ii++)
  {
    echo "<pre>" . $sqls[$ii] . "</pre><hr>\n";
  }
  include "dump.php";
  dump_page(basename($_SERVER[SCRIPT_FILENAME]));  // Display the code of this page
?>
</body>
</html>