Page 417 - Beginning PHP 5.3

Chapter 13: Retrieving Data from MySQL with PHP
mysql > SELECT count( pageUrl ) FROM accessLog;
+------------------+
| count( pageUrl ) |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)

That ’ s no good. All this query has given you is the total number of rows in the table! Instead, you need
to group the pageUrl count by member ID. To do this, you add a GROUP BY clause. For example:
mysql > SELECT memberId, count( pageUrl ) FROM accessLog GROUP BY memberId;
+----------+------------------+
| memberId | count( pageUrl ) |
+----------+------------------+
| 1 | 1 |
| 3 | 2 |
| 6 | 1 |
+----------+------------------+

3 rows in set (0.00 sec)
That ’ s better. By combining an aggregate function, count() , with a column to group by ( memberId ), you
can view statistics on a per - member basis. In this case you can see that members 1 and 6 have each
viewed one distinct page, whereas member 3 has visited two different pages.

You can combine GROUP BY and ORDER BY in the same query. Here ’ s how to sort the previous data so
that the member that has viewed the highest number of distinct pages is at the top of the table:

mysql > SELECT memberId, count( pageUrl ) FROM accessLog GROUP BY memberId
ORDER BY count( pageUrl ) DESC;
+----------+------------------+
| memberId | count( pageUrl ) |
+----------+------------------+
| 3 | 2 |
| 1 | 1 |
| 6 | 1 |
+----------+------------------+
3 rows in set (0.00 sec)

Pulling Data from Multiple Tables

So far, all your queries have worked with one table at a time. However, the real strength of a relational
database is that you can query multiple tables at once, using selected columns to relate the tables to each
other. Such a query is known as a join , and joins enable you to create complex queries to retrieve all sorts
of useful information from your tables.

379

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