Page 419 - Beginning PHP 5.3

Chapter 13: Retrieving Data from MySQL with PHP
You can pull data from as many tables as you like in this way. However, on its own, this FROM clause
would simply return all rows from the members table. This is why you need the WHERE clause that
creates the actual join:

FROM accessLog, members WHERE accessLog.memberId = members.id
By adding the WHERE clause, you tell MySQL to bring back a row from the members table only if its id
column matches one of the values in the list of memberId values returned by:

SELECT DISTINCT accessLog.memberId
In other words, if a members row ’ s id column isn ’ t 1 , 3 , or 6 , ignore the row.

You probably noticed that this query specifies not just field names, but also the table that each field
belongs to:

accessLog.memberId, members.firstName, members.lastName
This is important when working with multiple tables at once, because it prevents ambiguity over field
names. For example, if your members table ’ s id column was actually called memberId , the following
query would be ambiguous:

SELECT DISTINCT memberId, firstName, lastName FROM accessLog, members WHERE

memberId = memberId;
Which table does the memberId column refer to in each case? There ’ s no way of knowing. By including
the table name before the column name (separated by a dot), you tell MySQL exactly which column
you ’ re talking about.
If you don ’ t prefix a column name by a table name, MySQL is smart enough to work out which table
you ’ re talking about, provided the same column doesn ’ t exist in more than one table. However, it ’ s
generally good practice to include the table name to avoid ambiguity when reading the query. You see
how to use aliases to make your queries shorter and more readable in a moment.

This query is just a simple example of a join, but you ’ ll use joins of this type many times if your database
contains several tables.

Using Aliases

As you start to work with many tables, things can start to get unwieldy. For example, in the preceding
section you used this query to retrieve a list of names of members who have accessed the Web site:

mysql > SELECT DISTINCT accessLog.memberId, members.firstName, members.

lastName FROM accessLog, members WHERE accessLog.memberId = members.id;

381

9/21/09 9:12:00 AM
c13.indd 381
c13.indd 381 9/21/09 9:12:00 AM