Practical PHP and MySQL Web Site Databases: A Simplified Approach (2013)

Chapter 9. Adding Multiple Tables and Other Enhancements

Web sites with databases can benefit from the three practical enhancements described in this chapter. For instance, (i) multiple tables can give more specific search results and are essential for the administration of forums and e-commerce sites, (ii) membership fees can be paid by check as well as by using PayPal and credit/debit cards, (iii) check payments can be accompanied by printable application forms. These three enhancements will be used in subsequent chapters. The sections in this chapter are as follows:

· Introduction to multiple tables

· Payment by check

· Printing online forms

Introduction to Multiple Tables

Previous chapters used databases with one table. These databases are called flat file databases. Sometimes databases use several tables that can be related to each other. These databases are called relational databases. Data from each table can be joined (combined) to form virtual tables that can be displayed in a browser. In this tutorial, we will concentrate entirely on the process of joining tables. For simplicity and clarity, the web site for this tutorial will be stripped of several features, such as logging in, registration, and administration. The buttons for these functions will appear on the headers, but they will be dead links.

You may wonder why we need more than one table. Why not put all the data in one table? We could use one huge table, but it would lead to no end of trouble. A great deal of information would be entered many times, causing the database to be very inefficient, and the administration would be time consuming and extremely tedious.

As an example, suppose we had an e-commerce web site selling telephones and intercoms. We could have a set of records as shown in Table 9-1.

Table 9-1. A table with many duplicate entries

This single table containing the latest orders has many problems.

Charlie Smith has ordered three items, and his address is repeated three times. If he changed his address, we would need to amend three rows in the table. Charlie’s name and address should be kept in a separate table; then we need to change his address only once. The stock level will change so that we would have to scroll through the records to determine the lowest stock level for a particular item. The stock level and the product description should be kept in another table so that only that table would need updating. The user and the administrator would then see an accurate figure for the stock level when the product is displayed.

Normalization

The process of eliminating redundancies and other maintenance problems is called normalization. Table 9-1 contains items that illustrate very bad practices. The first name and last name should be in separate columns, The address should be split into separate columns for street, town, and ZIP code or postal code. This process is known by the rather clumsy name of atomicity because, like atoms, the data is separated into minimal components. It is then atomic or indivisible.

Normalization is achieved by applying rigorous atomicity and splitting the data into several tables, instead of using just one table, so that each table has a very specific and singular purpose. Also, the information in each table must have closely related data, such as first name, last name, and e-mail address; if the customer changes his or her e-mail address, you have to amend only one record. Normalization allows a database to be easily extended so that it can hold more types of information. For instance, a table could be added to hold the colors of the phones, or you could have a table for dispatch dates.

Normalization can be difficult to understand at first, but you will be automatically normalizing your tables if you apply atomicity and then group closely related data into separate tables. By breaking data down to the lowest level, you allow for future growth and better data control, and you leave yourself more options in the future to modify and manipulate the data. Being able to have a one-to-many relationship on multiple tables ensures less data redundancy.

We will now create a database with two tables.

Create the Database and Tables

For the first part of this tutorial, we must create a database to contain two small tables. In htdocs, create a new folder and name it birds. In phpMyAdmin, create a database called birdsdb and then set up a user and a password as follows:

Database name: birdsdb

User: faraday

Password: dynamo1831

Host: localhost

Download the files from the book’s page at apress.com, and load them into your birds folder.

View the Connection File

The file mysqli_connect.php has the following code:

<?php
// This creates a connection to the birdsdb database; it also sets the encoding to utf-8
DEFINE ('DB_USER', 'faraday');
DEFINE ('DB_PASSWORD', 'dynamo1831');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'birdsdb');
// Make the connection
$dbcon = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
// Set the encoding
mysqli_set_charset($dbcon, 'utf8');
?>

Use phpMyAdmin to import the birds.sql file, or create a table manually named birds with four columns and the attributes given in Table 9-2.

Table 9-2. The birds table

The birds table contains a column named bird_id. This column is configured as the PRIMARY key.

Using phpMyAdmin, import the file location.sql, or create the table manually with four columns as shown in Table 9-3 and name it location.

Table 9-3. The attributes for the location table

Foreign Keys

The location table contains the location_id, location, and location_type. In addition, it contains a column named bird_id that duplicates the bird_id column that is the PRIMARY key in the birds table. The birds_id column in the location table is called a foreign key because this is a key from a different table (the birds table). This foreign key enables us to join the two tables by means of a SELECT and INNER JOIN statement; the various JOIN methods will be explained later. By this means, we can select certain data from both tables and display the result in a browser as a single table.

IMPORTANT: Foreign keys link to PRIMARY keys in another table, and they must be the same type and length as the PRIMARY key. Also, foreign keys must all be indexed. Indexing these keys ensures fast queries. In our tutorial, all the PRIMARY keys and their linked foreign keys are of the type MEDIUMINT and their length is 4. PRIMARY keys are automatically indexed, but foreign keys must be indexed by the web-site developer. This can be done in phpMyAdmin, as you will see shortly. If you try to set a foreign key that links to a PRIMARY key with a different length or value, you will see an error message stating that the reference to the PRIMARY key could not be created.

Caution Do not enter data in the tables. We will do this later when the tables have been prepared for joining.

Meanwhile, we must begin with some preparation.

Preparing the Tables for Joining

So that we can join the tables, we must establish a link between the bird_id in the first table and the bird_id in the second table. But first, we need to set the storage method.

MySQL uses two methods for storing tables; they are called INNODB, the default for MySQL, and MyISAM. Note that MySQL supports foreign keys only when INNODB storage is used; therefore, we must check that both tables are using the INNODB storage engine. You can quickly check the storage method for all tables by looking on the database Structure tab. In the Type column, it will list the storage type. Alternatively, in phpMyAdmin, select the box next to the birds table and click the Operations tab. Then go to the Table options section If the storage engine is not listed as INNODB, change it to INNODB. Repeat this for the second table. The Operations dialog screen is shown in Figure 9-1.

Figure 9-1. Showing that the storage engine for the location table is INNODB

Note It is possible to extract and display data from multiple tables even if they do not all use the INNODB storage engine. However, for simplicity, we will not be using MyISAM storage on any of the tables in this tutorial.

Now the indexes need to be set. The index is already prepared in the first table because the column named bird_id is a PRIMARY key, which is an index by default. The foreign key named bird_id in the location table needs to be indexed. In phpMyAdmin, select the location table and then the Structure tab, and select the box next to the bird_id column. Then click the word Index shown circled at the bottom right of Figure 9-2. phpMyAdmin shows a message to confirm that the index has been created, and you can also click the “+ indexes” pull-down item in the Structuretab to see the indexes.

Figure 9-2. Creating an index for the bird_id column

Now we must create a relationship between the two bird_id columns. Click the location table in the left panel and then click the Structure tab. Select the box next to bird_id and then click Relation view shown circled in Figure 9-3.

Figure 9-3. Click Relation view for the location table

A new dialog will appear, as shown in the next screen shot Figure 9-4.

Figure 9-4. Selecting the key in the birds table that matches the foreign key in the location table. In later versions of phpMyAdmin, you will see an additional field between the FOREIGN KEY and the ON DELETE field

Using the phpMyAdmin interface shown in Figure 9-4, we will select the constraints for the birds table by using the following steps:

1. With the details of the location table on the screen, use the pull-down menu in the column headed Foreign key constraint to select the matching PRIMARY key in the birds table. The field next to bird_id in the column under the heading Internal relation should remain empty. If you click the circled question mark next to the heading Internal relation, a tool tip will explain why.

2. In the field named On delete, select RESTRICT. In the field named ON UPDATE, select CASCADE. The terms RESTRICT and NO ACTION are synonymous, and they ensure that when you’re attempting to delete data (say, a user’s record) in one table, the data (matching record) in the linked table is not affected adversely. This is prevented because a warning message is displayed. CASCADE ensures that when a table is updated the linked tables are also updated; this a great help for maintaining database tables.

3. Click Save. The foreign key bird_id in the location table is then related to the PRIMARY key bird_id in the birds table.

When you click Save, the code is displayed above the main pane. You will see the ON DELETE and CASCADE settings.

You can use phpMyAdmin to view the link between the two tables. In the home page of phpMyAdmin, click the database birdsdb and then use the MORE pull-down menu to select Designer. You will see a diagrammatic view of the two tables illustrating the link between the PRIMARY key and the foreign key, as shown in Figure 9-5. Drag the tables around to give a neat diagram clearly displaying the tables and their link. While you are using the Designer view, explore it to see several useful control features.

Figure 9-5. The Designer view showing the link between the two tables

Add Some Data

Import the file birds.sql, which is already populated, but if you wish to create the table manually, use the Insert tab in phpMyAdmin to insert the data shown in Table 9-4 into the birds table.

Table 9-4. The birds table

If you wish to populate the location table manually, use phpMyAdmin and click the location table in the left panel. Then, using the Insert tab in phpMyAdmin, insert the data shown in Table 9-5. Note that because we linked the two bird_id columns, you will see a pull-down menu containing the bird_id numbers for entering the foreign key bird_id in the location table.

Table 9-5. The location table

The following criteria were used to enter data for the foreign key bird_id in the fourth column.

We assumed that some birds can be seen only in certain reserves. For instance, goldeneyes are found in the lake at the Lakeland reserve; therefore, we entered the goldeneye’s id (1) in the Lakeland row. Wrynecks are found only in the Moorfield and Heathville bird reserves; therefore, we entered the wryneck’s id (2) in the rows containing those two locations. The avocet is an estuary bird; therefore, its id (3) was entered in the row for the Westlands reserve, which is an estuary. Moorhens prefer ponds; therefore, the moorhen’s id (4) was entered in the row for the Southpark reserve because that reserve has several ponds.

Now that we have learned about keys (indexes), we can discuss how to use them to join tables.

Joins

We will digress here to discuss the theory of JOINS. Refer to Tables 9-6 and 9-7 as I explain three ways of joining tables by using a SELECT query.

Table 9-6. The users table

Table 9-7. The town table

Note how I placed the tables. I will refer to them as the LEFT table and the RIGHT table. This reflects the order of the tables in the SELECT query that we will use to join the two tables. The first table in a JOIN query we think of as the LEFT table, and the second table in a JOIN query we regard as the RIGHT table.

The IDs (indexes) are the keys to understanding joins. Look through each table, and take particular note of the IDs. Some deliberate (but unlikely) registration errors have been introduced so that the various joins can be demonstrated. The errors are (i) Mr Duff has registered twice because his work means he has addresses in two towns, and a secure database would not normally allow a person to register twice, (ii) the second user_id in the table on the right has been entered as 3 instead of 2, (iii) the details for user_id 5 are missing from the table on the left. We will now examine vatious types of join using Tables 9-6 and 9-7.

INNER Joins

INNER joins are the most frequently used joins for the public display of tables. This is because OUTER joins can display columns containing NULL entries that are helpful for maintaining a database, but they are not good for public display.

If you use a SELECT query to create an INNER JOIN, you will select all the records where the user_id matches in both tables. Where user_ids in the table on the right do not match, the records are not selected. The SELECT query would be as follows:

SELECT user, phone, town FROM users INNER JOIN USING (user_id);

The resulting table display using an INNER join is shown in Table 9-8.

Table 9-8. The result of an INNER join

Mr Rosoft doesn’t appear because the ID in the users table has no matching ID in the town table. The fifth entry in the town table doesn’t show up because there is no equivalent entry in the users table (which is the one we performed the Join query on).

The INNER join query can have the following alternative format:

SELECT user, phone, town FROM users JOIN town ON users.user_id = town.user_id;

Outer Joins

In OUTER joins, the word OUTER is normally not used; instead, they are referred to as LEFT joins and RIGHT joins. However, the word OUTER may be optionally added after the words LEFT or RIGHT. The words LEFT and RIGHT refer to the database tables shown in Tables 9-6 and 9-7, they are on the left and the right of the book’s page, respectively. As previously stated, they are also the first and second tables, respectively, in the SELECT query.

LEFT JOIN

With a LEFT outer join, you will SELECT the same matching data as before (Table 9-8), but you will also select items from the table on the left of this page (the first table in the SELECT query) so that every name and phone number is listed. However, you will see NULL in the columns where no matching ID is found.

The OUTER LEFT join would have the following format:

SELECT user, phone, town FROM users LEFT JOIN town ON users.user_id = town.user_id;

The result is as shown in Table 9-9.

Table 9-9. The result of the LEFT outer join

The LEFT JOIN lists every user and phone number because these are in the LEFT table (the first table in the SELECT query). But in the case of Mr Rosoft, you will see NULL in the town column because his ID (number 2) is not present in the RIGHT table (the second table in the SELECT query).

RIGHT JOIN

With a RIGHT OUTER JOIN, you select all the records that match (as in Table 9-8), but you will also select a record for each unmatched record in the table on the RIGHT. This means that all the towns are selected, but NULL will be seen in the user and phone columns where no match is found in the LEFT table. The SELECT query is as follows:

SELECT user, phone, town FROM users RIGHT JOIN town ON
users.user_id = town.user_id;

The data selected is as shown in Table 9-10.

Table 9-10. The result of a RIGHT OUTER JOIN

All three columns have entries for Mrs. Bush and Mr. Druff because their IDs are present in both tables (as shown in Table 9-8). All the towns are listed because we used a RIGHT JOIN and all the towns are present in the RIGHT table (the second table in the SELECT query). However, because the people living in Axminster and Seaton have no corresponding ID in the LEFT table (users), their user and phone columns contain NULL.

Joined tables are virtual tables; they cannot be stored in the data folder within the MySQL folder. However, they can be displayed using a browser. That is the end of the JOINS theory section. We will now return to the birds database and apply the theory. As a reminder, we will view the two birdsdb database tables (Table 9-4 and Table 9-5) once more.

We will use an INNER JOIN to extract and display information from both tables. An INNER join is the most frequently employed method for joining tables, because it never shows NULL fields in a public display.

The INNER JOIN will combine only the records (rows) that have a common item ID in both tables. We want the location to appear in the first column of the table display, so the syntax for INNER joining the two tables birds (tableA) and locations (tableB) is as follows:

$q = "SELECT location.location, birds.bird_name, birds.rarity, birds.best_time
FROM location INNER JOIN birds ON location.bird_id=birds.bird_id";

After the SELECT keyword, add the column names in the order that you want them to be displayed on the screen. Separate the column names using commas. After the FROM key word, JOIN the two tables using the ON keyword (which is the equivalent of WHERE). The statement after the ON key word means “where tableB.FOREIGN key equals the tableA.PRIMARY key”. Note that the dot between the table names and the keys (the IDs) is very important.

The query result is shown in Table 9-11.

Table 9-11. The result of the INNER JOIN query

To demonstrate that the word “INNER” can be omitted in a plain SELECT statement, you could use the following query:

$q = "SELECT location, bird_name, rarity, best_time FROM location JOIN birds
ON location.bird_id=birds.bird_id";

We will briefly examine a LEFT OUTER JOIN for completeness.

What Happens If You Use a LEFT Outer Join

OUTER joins are inclusive. If we add the robin to our list of birds with a bird_id of 5, the robin will be included in the OUTER LEFT joined table, but the robin’s location will contain a NULL entry as shown next in Table 9-12. Tables containing NULL entries are not for public viewing but can be helpful for a web site administrator.

Table 9-12. An outer join using LEFT JOIN

The query statement for the preceding OUTER LEFT JOIN is as follows:

$q = "SELECT birds.bird, birds.rarity, bird,best_time, location.location
FROM birds LEFT JOIN location USING (bird_id) ";

The previous sections demonstrated the creation of virtual joined tables We will now create an environment for displaying those virtual joined tables. This will be achieved by using menu buttons in the home page.

The Home Page for the Multiple Tables Tutorial

To save space and to simplify the tutorial, you do not need to log in or administer the web site because no members’ table is provided in the downloadable files or listings. In addition to these omissions, no provision is made for an Internet Explorer 8 conditional style sheet.

The home page menu buttons are on the left as shown in Figure 9-6. When the buttons are clicked, they will display two separate tables and two joined tables, Later in the chapter we will add a third table so that the fifth button will display three joined tables. The code for the home page is given in Listing 9-6a.

Figure 9-6. The Devon Bird Reserves home page

Listing 9-6a. Creating the home page for demonstrating joined tables (index.php)

<!doctype html>
<html lang=en>
<head>
<title>Home Page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="birds.css">
</head>
<body>
<div id="container">
<?php include("includes/header.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col.php"); ?>
<div id="content"><!--Start of the home page content-->
<div id="midcol">
<h2>Help Save Our Devon Birds From Extinction</h2>
<div id="mid-left-col">
<p class="dark">The Devon Bird Reserves were established in an effort to combat the
massive decline in the bird population due to intensive farming.<br>The reserves are
pesticide-free havens for birds and other wildlife. They provide an ideal habitat where
birds are able to nest and safely search for food. </p>
<p class="dark">The reserves have comfortable bird hides where you may observe the
wildlife without disturbing it. Each hide has an information panel and also a chalkboard
where you can inform others about your sightings.</p>
</p>
</div>
<div id="mid-right-col">
<h3>Become a member and support our cause</h3>
<p class="dark">The annual membership subscription permits free entry or reduced entrance
fees to the reserves. Members receive a free quarterly magazine and news
updates. You will also be able to log in and view the members' special events.</p>
<p class="dark">To join, please click the Register button. </p>
</div><!--End of the home page content--></div>
</div></div><br class="clear">
<div id="footer">
<footer>
<?php include("includes/footer.php"); ?>
</footer></div>
</body>
</html>

The elements on the home page are positioned using the CSS code given in Listing 9-6b.

Listing 9-6b. The main style sheet for the pages (birds.css)

body {text-align:center; background-color:#CCFF99; color:green;
font-family:"times new roman"; font-size: 100%; margin: auto; }
h2 { font-size:150%; margin-bottom:0; color:#003300; text-align:center; }
h3 { font-size:110%; color:#003300; text-align:center; }
#container {position:relative; min-width:960px; max-width:1200px; margin:auto;
text-align:left; }
#header, #header-members, #header-admin { position:relative; margin:10px auto 0 auto;
min-width:960px; max-width:1200px; height:183px;
background-image: url('images/header3.jpg'); background-repeat: no-repeat; padding:0;
color:white;}
h1 {position:relative; top:50px; font-size:350%; color:white; margin:auto 0 auto 20px;
width: 700px; }
ul { position:absolute; top:180px; right:-40px; color:navy; width:135px;
text-align:center; margin:0; }
#nav ul { position:absolute; top:190px; left:-40px; color:navy; width:135px;
text-align:center; margin:0; }
#info-col { position:absolute; top:190px; right:10px; color:navy; width:135px;
text-align:center; margin:5px 5px 0 0; }
#reg-navigation ul { position:absolute; top:30px; right:-20px; font-size:medium; width:160px; }
#midcol { margin-left:150px; margin-right:150px; }
#mid-left-col{ float:left; width:46%; text-align:left; }
#mid-right-col{ float:right; width:46%; text-align:left; }
p.dark {font-size:120%; color:#003300; }
#mid-right-col h3 { text-align:left; font-size:130%; color:#003300; margin-bottom:0; }
#footer { text-align:center; }
.clear { clear:both; }
/* set general side button styles */
li { width:115px; list-style-type :none; margin-bottom: 3px; text-align: center; }
/* set general anchor styles */
li a { display: block; width:115px; color: white; font-weight: bold; text-decoration: none; }
/* specify state styles. */
/* mouseout (default) */
li a { background: #559a55; border: 4px outset #559a55; }
/* mouseover */
li a:hover { display:block; background: red; color:white; border: 5px outset red;
width:115px; }
/* onmousedown */
li a:active { background:maroon; border: 5px inset maroon; }

Note As previously mentioned, joined tables are virtual tables. They cannot be seen until they are displayed in a browser. The pages in this tutorial are designed specifically to demonstrate the results of joining tables. The table displays are selected by clicking buttons on the menu. A real-world web site would not have buttons labeled Join 2 Tables and Join 3 Tables. (We will be adding a third table later.) These labels are for your convenience only.

We will now examine the menu for viewing the various tables.

The Main Menu for the Home Page

Buttons on the main menu will be used to select the various tables. The buttons are shown in Figure 9-7.

Figure 9-7. The menu for demonstrating the tables

The menu code is given in Listing 9-7 and the file can be found in the includes folder in the downloaded files.

Listing 9-7. Creating the Menu for Selecting Various Tables (includes/nav.php)

<div id="nav"><!--The side menu column contains the vertical menu-->
<ul>
<li><a href="#" title="Page two">About Us</a></li>
<li><a href="birds.php" title="The Birds">The Birds</a></li>
<li><a href="reserves.php" title="The Reserves">The Reserves</a></li>
<li><a href="join-2.php" title="Two tables joined">Join 2 Tables</a></li>
<li><a href="join-3.php" title="Three tables joined">Join 3 Tables</a></li>
<li><a href="index.php" title="Return to Home Page">Home Page</a></li>
</ul>
</div><!--end of side column and menu -->

The Header for the Home Page

The header for the home page contains two menu buttons as shown in Figure 9-8.

Figure 9-8. The home page header

The code for the header is shown in Listing 9-8

Listing 9-8. Creating the Header Menu Buttons (includes/header.php)

<div id="header">
<h1>The Devon Bird Reserves</h1>
<div id="reg-navigation">
<ul>
<li><a href="#">Login</a></li>
<li><a href="member_reg.php">Register</a></li>
</ul>
</div>
</div>

Caution To save space, a member’s table is not included in the database for this tutorial. Therefore, you will not be able to register members. However, you will be able to click the Register button to view the registration page. Note that the Login link is dead because without a database table for members, it is not possible to log in.

Displaying a Table of Birds

We will now display a single un-joined table using the data from the birds table. The table will display as shown in Figure 9-9.

Figure 9-9. Displaying the table of birds

The code for displaying the table of birds is given in Listing 9-9.

Listing 9-9. Creating the Page for Displaying a Table of Birds (birds.php)

An internal style is used because the table is unique to this page.

<!doctype html>
<html lang=en>
<head>
<title>View birds page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="birds.css">
<style type="text/css"> #1
table { width:500px; background:white; color:black; border:1px black solid;
border-collapse:collapse; margin:auto; }
td { border:1px black solid; padding:1px 0 1px 4px; text-align:left; }
</style>
</head>
<body>
<div id="container">
<?php include("includes/header.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col.php"); ?>
<div id="content"><!--Start of the view birds page content-->
<div id="midcol">
<h2>The Birds that can be seen on our Reserves</h2>
<p>
<?php
// This script retrieves the records from the birds table
require ('mysqli_connect.php'); // Connect to the database
// Make the query
$q = "SELECT bird_name, rarity, best_time FROM birds ORDER BY bird_name ASC";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran OK, display the records
// Table header
echo '<table>
<td align="left"><b>Bird’s Name</b></td>
<td align="left"><b>Rarity</b></td>
<td align="left"><b>Best Time</b></td>
</tr>';
// Fetch and print all the records
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td align="left">' . $row['bird_name'] . '</td>
<td align="left">' . $row['rarity'] . '</td>
<td align="left">' . $row['best_time'] . '</td>
</tr>';
}
echo '</table>'; // Close the table
mysqli_free_result ($result); // Free up the resources
} else { // If it did not run OK
// Message
echo '<p class="error">The current birds could not be retrieved. We apologize
for any inconvenience.</p>';
// Debugging message
echo '<p>' . mysqli_error($dbcon) . '<br><br />Query: ' . $q . '</p>';
} // End of if ($result)
mysqli_close($dbcon); // Close the database connection
?></p>
</div>
</div><!--End of the view birds page content-->
<?php include("includes/footer.php"); ?>
</div>
</body>
</html>

The page can be viewed by clicking the Birds menu button on the home page.

Explanation of the Code

You will have seen all this code in earlier chapters, but the use of an internal style needs explaining as follows:

<style type="text/css"> #1
table { width:500px; background:white; color:black; border:1px black solid;
border-collapse:collapse; margin:auto; }
td { border:1px black solid; padding:1px 0 1px 4px; text-align:left; }
</style>

I always find it convenient to use an internal style for displaying tables. This is partly because most tables are unique to the page and partly because a neat layout is easier to control using an internal style.

We will now examine the locations table, which is the second single unjoined table for this tutorial. It can be viewed by clicking the menu button labeled The Reserves.

Displaying a Table of Locations

The displayed table of locations is shown in Figure 9-10.

Figure 9-10. The table display for the locations

The code for displaying the locations table is given in Listing 9-10.

Listing 9-10. Creating the Page for Displaying the Locations (location.php)

<!doctype html>
<html lang=en>
<head>
<title>View the location page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
table { width:500px; background:white; color:black; border:1px black solid;
border-collapse:collapse; margin:auto; }
td { border:1px black solid; padding:1px 0 1px 4px; text-align:left; }
</style>
</head>
<body>
<div id="container">
<?php include("includes/header.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col.php"); ?>
<div id="content"><!-- Start of the view location page content -->
<div id="midcol">
<h2>The Devon Bird Reserves</h2>
<p>
<?php
// This script retrieves all the records from the location table
require ('mysqli_connect.php'); // Connect to the database
// Make the query:
$q = "SELECT location, location_type FROM location ORDER BY location_id ASC";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran OK, display the records
// Table header
echo '<table>
<td align="left"><b>Location</b></td>
<td align="left"><b>Location type</b></td>
</tr>';
// Fetch and print all the records
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td align="left">' . $row['location'] . '</td>
<td align="left">' . $row['location_type'] . '</td>
</tr>';
}
echo '</table>'; // Close the table
mysqli_free_result ($result); // Free up the resources
} else { // If it did not run OK
// Message
echo '<p class="error">The current location could not be retrieved.
We apologize for any inconvenience.</p>';
// Debugging message
echo '<p>' . mysqli_error($dbcon) . '<br><br />Query: ' . $q . '</p>';
} // End of if ($result)
mysqli_close($dbcon); // Close the database connection
?>
</p>
</div></div><!-- End of the view location page content. -->
<?php include("includes/footer.php"); ?>
</div>
</body>
</html>

We will now show the display for two joined tables.

Displaying Data from the Joined Tables

When the two tables are joined, some data from each will be displayed as shown in Figure 9-11.

Figure 9-11. Displaying two joined tables

The code for displaying selected data from the two joined tables is given in Listing 9-11.

Listing 9-11. Creating the Page for Displaying Selected Data from Two Joined Tables (join-2.php)

<!doctype html>
<html lang=en>
<head>
<title>The display for a pair of joined tables</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="birds.css">
<style type="text/css">
table { width:600px; background:white; color:black; border:1px black solid;
border-collapse:collapse; margin:auto; }
td { border:1px black solid; padding:1px 0 1px 4px; text-align:left; }
</style>
</head>
<body>
<div id="container">
<?php include("includes/header.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col.php"); ?>
<div id="content"><!--Start of the view birds page content using two joined tables-->
<div id="midcol">
<h2>The Display for Two Joined Tables</h2>
<p>
<?php
// This script retrieves all the records from the birds table joined with the location table
require ('mysqli_connect.php'); // Connect to the database
// Make the query
$q = "SELECT location.location, birds.bird_name, birds.rarity, birds.best_time
FROM location INNER JOIN birds ON location.bird_id=birds.bird_id" ;
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran OK, display the records
// Table header
echo '<table>
<td align="left"><b>Location</b></td>
<td align="left"><b>Bird Name</b></td>
<td align="left"><b>Rarity</b></td>
<td align="left"><b>Best Time</b></td>
</tr>';
// Fetch and print all the records
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td align="left">' . $row['location'] . '</td>
<td align="left">' . $row['bird_name'] . '</td>
<td align="left">' . $row['rarity'] . '</td>
<td align="left">' . $row['best_time'] . '</td>
</tr>';
}
echo '</table>'; // Close the table
mysqli_free_result ($result); // Free up the resources
} else { // If it did not run OK
// Message
echo '<p class="error">The current birds could not be retrieved. We apologize for
any inconvenience.</p>';
// Debugging message
echo '<p>' . mysqli_error($dbcon) . '<br><br />Query: ' . $q . '</p>';
} // End of if ($result)
mysqli_close($dbcon); // Close the database connection
?></p>
</div></div><!--End of the view birds page content using two joined tables-->
<?php include("includes/footer.php"); ?>
</div>
</body>
</html>

Note that the order of the items following the ON word can be reversed and you will get the same result. For example,

ON location.bird_id=birds.bird_id)" ;

can be written as follows:

ON birds.bird_id=location.bird_id";

You can view the two joined tables by clicking the menu button labeled Join 2 Tables. You will be able to see that the table contains data from both tables, as shown in Figure 9-11.

We will often have to join more that two tables, and to demonstrate this we will create or import a third table.

Joining More Than Two Tables

Use phpMyAdmin to import the table file rsv_info.sql, or create it manually using the information in Table 9-13. The table has five columns, and it contains useful information about the reserves.

Table 9-13. The structure and attributes for the rsv_info table

Note that we are inserting the index for location_id directly into this table. We could have added the indexes in this way in earlier tables, but it can be advantageous to learn alternative methods.

Using the Insert tab in phpMyAdmin, enter the data shown in Table 9-14.

Table 9-14. The data for the rsv_info table (entr refers to the entrance fee)

After creating the table, click the More tab in phphMyAdmin and select Designer. You will see the first two tables with their connecting link, but the third table will not be linked, as shown in Figure 9-12.

Figure 9-12. Designer view showing that the third table is not yet linked to the second table

Keep the designer view on the screen while you explore this alternative method of linking the tables. In the Designer view in phpMyAdmin, drag the tables around by their title bars so that they are arranged in a convenient pattern. (See Figure 9-14 for an example.) Dragging the tables around is strictly to make it easier for you to view them and does not effect their functionality.

Above the tables, you will see a row of icons. Two are shown circled in Figure 9-12. Hover over the right ringed icon and you will see a “Create relation” tool tip. Click that icon and then click the referenced key location_id, which is the first item in the location table on the right. This is the PRIMARY key that we want our new table to link with. A tool tip will appear asking you to click the foreign key. Then click the foreign key in the rsv_info table, which in this case is the last item in the rsv_info table. A dialog box will appear at the bottom right of the window. This is shown in Figure 9-13.

Figure 9-13. The dialog box

The dialog requests more information about the foreign key. At the item named On delete, select RESTRICT. At the item named On update, select CASCADE and click OK. You will see the new relational link appear, as shown in Figure 9-14. To save the Designer diagram, click the floppy disk icon on the toolbar.

Figure 9-14. Showing the Designer diagram with the third table linked to the second table

Next we will explore the code for displaying the three linked tables.

Create a Page to Display the Three Joined Tables

Now that the relationships have been set, we can learn how to select and display data from the three tables. The principle is quite logical: first join two tables to produce a virtual table, and then join that to the third table. The syntax for a query for joining and selecting data from three tables is as follows:

$q= "SELECT some column, some other column, another column
FROM table1
INNER JOIN table2 USING (the key that links table1 and table2)
INNER JOIN table 3 USING (the key that links table2 and table3) ";

Select some data from our three joined tables:

$q = "SELECT bird_name, best_time, location, bird_hides, entr_memb, entr_n_memb
FROM birds
INNER JOIN location USING (bird_id)
INNER JOIN rsv_info USING (location_id) ";

The resulting display is shown in Figure 9-15.

Figure 9-15. Three tables joined and displayed in a browser

The table can be viewed by clicking the menu button labeled Join 3 Tables.

The code for the page showing three joined tables is given in Listing 9-15.

Listing 9-15. Creating the Page for Displaying Three Joined Tables (join-3.php).

Once again an internal style is used.

<!doctype html>
<html lang=en>
<head>
<title>Display three joined tables</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="birds.css">
<style type="text/css">
table { width:700px; background:white; border:1px black solid; border-collapse:collapse;
margin:auto; }
td { border:1px black solid; padding:1px 0 1px 4px; text-align:left; color:black; }
</style>
</head>
<body>
<div id="container">
<?php include("includes/header.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col.php"); ?>
<div id="content"><!-- Start of content of the display three tables page -->
<div id="midcol">
<h2>The Display for Three Joined Tables</h2>
<p>
<?php
// This code fetches data from the three tables
require ('mysqli_connect.php'); // Connect to the database
// Make the query #1
$q = "SELECT bird_name, best_time, location, bird_hides, entr_memb, entr_n_memb
FROM birds
INNER JOIN location USING (bird_id)
INNER JOIN rsv_info USING (location_id)";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran OK, display the records
// Table header #2
echo '<table>
<td align="left"><b>Birds Name</b></td>
<td align="left"><b>Best Time</b></td>
<td align="left"><b>Location</b></td>
<td align="left"><b>Bird Hides</b></td>
<td align="left"><b>Entrance-Member</b></td>
<td align="left"><b>Entrance Non-Member</b></td>
</tr>';
// Fetch and echo all the records
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td align="left">' . $row['bird_name'] . '</td>
<td align="left">' . $row['best_time'] . '</td>
<td align="left">' . $row['location'] . '</td>
<td align="left">' . $row['bird_hides'] . '</td>
<td align="left">' . $row['entr_memb'] . '</td>
<td align="left">' . $row['entr_n_memb'] . '</td>
</tr>';
}
echo '</table>'; // Close the table #3
mysqli_free_result ($result); // Free up the resources
} else { // If it did not run OK
// Error message
echo '<p class="error">The current data could not be retrieved. We apologize
for any inconvenience.</p>';
// Debugging message
echo '<p>' . mysqli_error($dbcon) . '<br><br />Query: ' . $q . '</p>';
} // End of if ($result)
mysqli_close($dbcon); // Close the database connection
?>
</p>
</div></div><!-- End of the view three tables content -->
<?php include("includes/footer.php"); ?>
</div>
</body>
</html>

Explanation of the Code

// Make the query #1
$q = "SELECT bird_name, best_time, location, bird_hides, entr_memb, entr_n_memb
FROM birds
INNER JOIN location USING (bird_id)
INNER JOIN rsv_info USING (location_id)";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran OK, display the records

The code is very similar to the code for two tables except for the lines beginning with line #1.

We selected some data (but not all) from the three joined tables. Six items were selected from three tables using the SELECT query. Then we used that data in the code in lines #2 through #3 to display the table in a browser.

We will now learn how to add an alternative method of paying for goods or membership fees using a check instead of PayPal or debit/credit cards.

Payments by Check

A surprising number of uses prefer to pay by check because either they do not have a PayPal account or they prefer not to disclose their debit/credit card details over the Internet. Check payments usually need to be accompanied by a printed form containing the payer’s name, address, e-mail address, and telephone number.

This tutorial uses a simplified printable form for an organization that requires online registration for the users’ full details, together with a choice of payment methods. This is ideal for web sites that require membership registrations.

Let’s assume that Devon Bird Reserves requires an online registration form, as shown in Figure 9-16.

Figure 9-16. The registration page

You can view the registration page by clicking the Register button on the home page header, but as previously mentioned, you will not be able to register members because a members’ table is not included in the database for this tutorial. The registration page is essentially the same as the page used in Chapter 7. The majority of the code from Chapter 7 is embedded into the birds template and saved as member_reg.php. When users register successfully, the Register button redirects them to a thank you page that contains the alternative methods of paying the membership fee. The redirection instruction differs from the one in Chapter 7, and the code is given in Listing 9-16.

Listing 9-16. The Snippets of Code for Redirecting the User (extracted from member_reg.php)

Once again, an internal style for formattimg the form fields has been used, in addition to the main style sheet; this is because the style is unique to this page.

<!doctype html>
<html lang=en>
<head>
<title>The members' reg page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="birds.css">
<style type="text/css">
#container { min-width:960px; max-width:1100px; }
#midcol { margin-left:150px; margin-right:150px; color:#003300; }
label {font-weight:bold; width:330px; float:left; text-align:right }
input, select { margin-bottom:5px; }
#submit { margin-left:330px; }
h2 { margin-bottom:0; margin-top:5px; color:#003300;}
h3.content { margin-top:0; color:#003300;}
.cntr { text-align:center; }
</style>
</head>

Between these two blocks of code, the code is the same as the registration page in Chapter 7.

if (empty($errors)) { // If it runs OK
// Register the user in the database using an INSERT query
$q = "INSERT INTO users (user_id, title, fname, lname, email, psword, registration_date,
uname, class, addr1, addr2, city, county, pcode, phone, paid)
VALUES (' ', '$title', '$fn', '$ln', '$e', SHA1('$p'), NOW(), '$uname','$class',
'$ad1', '$ad2', '$cty', '$cnty', '$pcode', '$ph', ' ' )";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran OK
header ("location:choose_pay.php");
exit();

The code shown in bold type is the redirection instruction so that the user can choose from three methods of payment: check, PayPal, or debit/credit card.

A Choice of Payment Method

When the membership registration form has been filled out, clicking the Register button sends the user to a page giving the user a choice of three payment methods, as shown in Figure 9-17.

Figure 9-17. Giving the user a choice of payment method

The display of PayPal logos has been deliberately duplicated on the page to show that you have a choice of either a vertical or horizontal logo. To avoid cluttering the page, you choose one only.The code for creating the page shown in Figure 9-17 is given in Listing 9-17.

Listing 9-17. Creating a Page for Alternative Methods of Payment (choose_pay.php)

The layout of the button elements can be tricky; therefore, in addition to a link to the main style sheet, an internal style is used to enable better control of the layout of the Pay by Check button.

<!doctype html>
<html lang=en>
<head>
<title>Choose pay. Alternative payment methods.</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="birds.css">
<style type="text/css">
#container { min-width:960px; max-width:1100px; }
#midcol { margin-left:150px; margin-right:150px; color:#003300; }
label {font-weight:bold; width:330px; float:left; text-align:right }
input, select { margin-bottom:5px; }
#submit { margin-left:330px; }
h2 { margin-bottom:0; margin-top:5px; color:#003300; }
h3.content { margin-top:0; color:#003300; }
.cntr { text-align:center; }
#mid-left-col { width:46%; float:left; height: 63px; }
#mid-right-col { width:46%; float:right; }
#content-button { position:absolute; left:-10px; top:-20px; margin:10px 30px 0 10px;
width:180px; }
#content-button li { list-style-type:none; }
</style>
</head>
<body>
<div id="container">
<?php include("includes/header-thanks.php"); ?>
<?php include("includes/nav.php"); ?>
<?php include("includes/info-col-cards.php"); ?>
<div id="content"><!-- Start of the thank you page content. -->
<div id="midcol">
<h2>Thank you for registering</h2>
<h3>To complete your registration please pay the membership fee now. <br></h3>
<p class="cntr">When you have completed your registration you will be able to login
to the member's only pages</p>
<p class="cntr">Membership classes: Standard 1 year: £30,
Standard 5years: £125, Under 21: one year: £2
</p>
<div id="mid-left-col">
<div id="content-button">
<ul class="btn">
<li class="btn"><a href="pay-with-check.php">Pay by Check</a></li>
</ul>
</div>
</div>
<!--</div>-->
<div id="mid-right-col">
<!--insert the PayPal code next-->
<form action="https://www.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_s-xclick">
<input type="hidden" name="hosted_button_id" value="XXXXXXXXXXXXX">
<table>
<tr>
<td><input type="hidden" name="on0" value="Membership Class"><b>
PayPal or Debit/credit card<br>Select the Membership Class</b></td>
</tr>
<tr>
<td><select name="os0">
<option value="Standard 1 Year £30">Standard 1 Year £30
</option>
<option value="Standard 5 Year £125">Standard 5 Year £125 £
125.00 GBP
</option>
<option value="Under 21 1 Year £2">Under 21 1 Year £2 £2.00 GBP
</option>
</select> </td>
</tr>
</table>
<input type="hidden" name="currency_code" value="GBP">
<input style="margin:10px 0 0 40px" type="image"
src="https://www.paypalobjects.com/en_US/GB/i/btn/btn_buynowCC_LG.gif"
name="submit" alt="PayPal The safer, easier way to pay online.">
<img alt="" src="https://www.paypalobjects.com/en_GB/i/scr/pixel.gif"
width="1" height="1">
</form><!--end of the PayPal code-->
</div>
</div>
</div>
</div><br><br><br class="clear">
<!--End of the thankyou/payment page content-->
<?php include("includes/footer.php"); ?>
</body>
</html>

When the PayPal Pay Now button is clicked, the user is taken to the usual PayPal page for processing the payment.

The Check Payment

When users click the Pay by Check button, they will be taken to a page containing a printable form, as shown in Figure 9-18.

Figure 9-18. The printable form can be filled out on the screen except for the signature and date (pay-with-check.php)

The code for creating the printable form is given in Listing 9-18.

Listing 9-18. Creating the Printable Form (pay-with-check.php)

The form is designed to be filled out on the screen and then printed.

<!doctype html>
<html lang=en>
<head>
<title>Pay with check</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="birds.css" media="screen"> #1
<link rel="stylesheet" type="text/css" href="print.css" media="print"> #2
<style type="text/css"> #3
label { margin-bottom:5px; }
label { width:570px; float:left; text-align:right; }
#button { text-align:center; }
.cntr { width: 655px; }
.sign { font-weight:bold; margin-left:200px; }
#content { font-weight:bold; color:#003300; }
</style>
</head>
<body>
<div id='container'>
<?php include('includes/header.php'); ?>
<?php include('includes/nav.php'); ?>
<?php include('includes/info-col.php'); ?>
<div id='content'><!--Start of page content-->
<div id="midcol">
<h2>Complete your Registration by Paying with a Check</h2>
<h3>Thank you for registering online. Now please fill out this form. Asterisks
indicate essential fields.<br>When you have filled out the form please print two
copies by clicking the "Print This Form" button. <br>Sign one copy and keep one for
reference. Sign a check payable to "The Devon Bird Reserves". <br>Mail the
signed form and check to: <br>The Treasurer, The Devon Bird Reserves, 99 The Street,
The Village, EX99 99ZZ </h3>
<form>
<div id="fields">
<label class="label" for="title">Title<span class="large-red">*</span>
<input id="title" name="title" size="35"></label>
<br><br><label class="label" for="firstname">First Name<span class="large-red">*</span>
<input id="firstname" name="Firstname" size="35"></label><br>
<br><label class="label" for="lastname">Last Name<span class="large-red">*</span>
<input id="firstname" name="lastname" size="35"></label><br>
<br><label class="label" for="useremail">Your Email Address<span class="large-red">*</span>
<input id="useremail" name="useremail" size="35"></label><br>
</div>
</form><br><br>
<p class="sign">
Signed____________________________ Date_________________</p>
<br>
<div id="button"> #4
<input type="button" value="Click to automatically print the form in black and
white" onclick="window.print()" title="Print this Form"><br>
</div>
<!--End of content.--></div>
</div></div>
<?php include('includes/footer.php'); ?>
</body>
</html>

Explanation of the Code

<link rel="stylesheet" type="text/css" href="birds.css" media="screen"> #1
<link rel="stylesheet" type="text/css" href="print.css" media="print"> #2

It is impossible to print the form using PHP because PHP is a server-side script. However, the form is displayed by a browser and we can print from a screen displayed by a browser. We achieve this by means of a link to a separate conditional style sheet with the media attributemedia="print".

Line #1 links to the main style sheet birds.css that displays the page on the screen using the media attribute media="screen". Line #2 links the page to the print version of the page. This is automatically invoked when the display is sent to a printer. The printed form does not need the header, menu, or footer. Also, using the CSS style sheet, we can cause it to print using only black ink so that the user does not have to use the more expensive color cartridge.

<style type="text/css"> #3
label { margin-bottom:5px; }
label { width:570px; float:left; text-align:right; }
#button { text-align:center; }
.cntr { width: 655px; }
.sign { font-weight:bold; margin-left:200px; }
#content { font-weight:bold; color:#003300; }
</style>

The layout of the page is unique; therefore, an internal style is used to position the fields, the space for a signature, and the print button.

<div id="button">
<input type="button" value="Click to automatically print the form in black #4
and white" onclick="window.print()" title="Print this Form"><br>
</div>

This is the code for the button on the page that sends the browser display to the printer.

Printing Online Forms

The printed page is shown in Figure 9-19.

Figure 9-19. The top half of the printed page in black ink contains all the essential information

To produce the printout shown in Figure 9-19, the form was filled out on the user’s screen and then the print button was clicked. The result used minimal ink and no colored ink; however, it contained all that is necessary to become a member of the Devon Bird Reserves. The user signs the form and mails it together with the check. The user’s address is entered into the database when he fills out the registration page; therefore, this does not need be repeated in the printed form.

The style sheet print.css is the key to producing the printable form. The code for this style sheet is shown in Listing 9-19.

Listing 9-19. Creating the Style Sheet for Printing Forms (print.css)

/*PRINT.CSS: style amendments for printing only. Set the font color to black only*/
body {color:black;}
/*SELECT ITEMS THAT YOU DO NOT WANT TO PRINT, e.g., header, menu, print-this-page
button, and footer*/ #1
#header, #nav, #leftcol, #button, #rightcol, #footer, #info-col, ul { display:none; }
input { border:1px black solid; }
h2 { font-size:16pt; color:black; text-align:center; } #2
h3 { text-align:center; font-size:11pt; color:black;}
/*REVEAL OUTGOING URL links on printed page*/ #3
a[href^="http://":after {content: "(" attr(href)")"; }

Explanation of the Code

/*SELECT ITEMS THAT YOU DO NOT WANT TO PRINT, e.g., header, menu, print-this-page button, and footer*/ #1

The CSS statement { display:none; } tells the printer which items should not be printed. To avoid wasting paper and ink when testing the appearance of the printed page, use the “Print preview” feature on the browser. Load the page into a browser, and click File-->Print Preview to see what the printable page will look like. If the printable page includes a page break, click the right-facing arrow at the bottom of the print preview screen to see subsequent pages.

Press the Esc key to switch out of the Print Preview mode.

h2 { font-size:16pt; text-align:center; } #2
h3 { text-align:center; font-size:11pt;}

To choose the correct font sizes for the printer, use point sizes (such as 16 pt. and 12 pt.), and use trial and error to optimize the sizes. You might have text within <p> </p> tags, so be sure to include a style for the paragraph font size.

/*REVEAL OUTGOING URL links on the printed page*/ #3
a[href^="http://]:after {content: "(" attr(href)")"; }

If the page contains a URL to your web site, you might wish this to appear on the printed page in a format that will be useful for the user (assuming that he prints and retains a copy of the form). Note the use of three types of brackets. In the form, the HTML would look like this:

<p>Click for
<a title="Click to visit the Devon Bird Reserves web site"
href="http://www.the devonbirdreserves.co.uk">The Devon Bird Reserves</a></p>

The URL would be displayed in a browser as follows:

Click to visit the Devon Bird Reserves web site.

Using the code shown on line #3, the printed form would appear as follows:

Click to visit the Devon Bird Reserves web site (http://www.devonbirdreserves.co.uk).

Caution Square check boxes are often used on HTML forms and printable forms. If you use Wingdings or Webdings for the check boxes, they might not display or print as boxes. However, practically every computer has the Unicode Lucida symbols. The check box entity &#9633; from the Unicode Lucida symbols will display correctly in all popular browsers. Use a font for the box that is much larger than the surrounding text.

Summary

In this chapter, I introduced the theory and practice of using multiple tables. You learned that such tables are virtual tables that are present only in the volatile memory of the server and that they can be viewed in a browser. The difference between various join methods was described. I then demonstrated that the virtual tables could be made visible on the screen by using SQL queries and PHP. A tutorial then showed you how to implement membership-fee payments by check. This was augmented by a demonstration of economical form printing, so that an application form and a check could be sent to the organization. In the next chapter, I will introduce you to an online message board.