Page 418 - Beginning PHP 5.3

Part III: Using PHP in Practice
In the previous examples that retrieved statistics from the accessLog table, your result sets contained a
list of integer member IDs in a memberId column. For instance, let ’ s say you want a list of all members
that have accessed the Web site:

mysql > SELECT DISTINCT memberId FROM accessLog;
+----------+
| memberId |
+----------+
| 1 |
| 3 |
| 6 |
+----------+

3 rows in set (0.00 sec)
Now, of course, the member ID on its own isn ’ t very helpful. If you want to know the names of the
members involved, you have to run another query to look at the data in the members table:
mysql > SELECT id, firstName, lastName FROM members;
+----+-----------+-----------+
| id | firstName | lastName |
+----+-----------+-----------+
| 1 | John | Sparks |
| 2 | Mary | Newton |
| 3 | Jo | Scrivener |
| 4 | Marty | Pareene |
| 5 | Nick | Blakeley |
| 6 | Bill | Swan |
| 7 | Jane | Field |
+----+-----------+-----------+
7 rows in set (0.00 sec)

Now you can see that member number 1 is in fact John Sparks, member number 3 is Jo Scrivener, and
member number 6 is Bill Swan.

However, by using a join, you can combine the data in both tables to retrieve not only the list of member
IDs that have accessed the site, but their names as well, all in the one query:
mysql > SELECT DISTINCT accessLog.memberId, members.firstName, members.lastName
FROM accessLog, members WHERE accessLog.memberId = members.id;
+----------+-----------+-----------+
| memberId | firstName | lastName |
+----------+-----------+-----------+
| 1 | John | Sparks |
| 3 | Jo | Scrivener |
| 6 | Bill | Swan |
+----------+-----------+-----------+
3 rows in set (0.03 sec)

Now that ’ s useful information! Take a look at how this query is built up. First of all, notice that the FROM
clause now contains two tables, separated by a comma:

FROM accessLog, members

380

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