PHP and MySQL® 24-Hour Trainer

Lesson 24

Using Multiple Tables

In this lesson, you work with multiple tables. MySQL does not explicitly link tables together. Instead you design the tables so that they have fields that contain the information you need to link to another table. You then use that information in separate SELECT statements for each table or by using multiple tables in a SELECT statement.

The JOIN clause in the SELECT statement specifies what the links are between the tables. Subqueries let you create compound commands by using the result of one statement nested in another statement.

The examples in this lesson are based on three tables: the authors table that contains the author's name, the types table that contains the valid types of books, and the books table that lists the title along with a field whose value matches to the author's primary key plus a field whose value matches to the type's primary key. This is a simplified database where there is only one author per book and each book is in only one type.

You can download the code for this example from the book's web page at www.wrox.com. You can find them in the Lesson24 folder in the download in a file labeled lesson24a.sql.

The first table is the authors table as shown in Figure 24-1.

Figure 24-1

The second table is the types table as shown ...