Page 416 - Beginning PHP 5.3

Part III: Using PHP in Practice
Now, imagine you want to get a list of the IDs of users that have accessed the site since November 7. You
might create a query as follows:
mysql > SELECT memberId FROM accessLog WHERE lastAccess > ‘2008-11-07’;
+----------+
| memberId |
+----------+
| 3 |
| 3 |
| 6 |
+----------+
3 rows in set (0.00 sec)

Now there ’ s a slight problem: the value 3 appears twice in the result set. This is because there are two
rows in the accessLog table with a memberId of 3 and a lastAccess date later than November 7,
representing two different pages viewed by user number 3. If you were displaying this data in a report,
for example, user number 3 would appear twice. You can imagine what would happen if that user had
visited 100 different pages!

To eliminate such duplicates, you can place the keyword DISTINCT after SELECT in the query:

mysql > SELECT DISTINCT memberId FROM accessLog WHERE lastAccess > ‘2008-11-07’;
+----------+
| memberId |
+----------+
| 3 |
| 6 |
+----------+

2 rows in set (0.00 sec)
DISTINCT removes any rows that are exact duplicates of other rows from the result set. For example, the
following query still contains two instances of 3 in the memberId column, because the pageUrl column
is different in each instance:

mysql > SELECT DISTINCT memberId, pageUrl FROM accessLog WHERE lastAccess >
‘2008-11-07’;
+----------+-------------+
| memberId | pageUrl |
+----------+-------------+
| 3 | books.php |
| 3 | contact.php |
| 6 | books.php |
+----------+-------------+
3 rows in set (0.00 sec)

Grouping Results

You ’ ve seen how to use functions such as count() and sum() to retrieve overall aggregate data from a
table, such as how many female members are in the book club. What if you wanted to get more fine -
grained information? For example, say you want to find out the number of different page URLs that each
member has viewed. You might try this query:

378

9/21/09 9:11:59 AM
c13.indd 378
c13.indd 378 9/21/09 9:11:59 AM