Page 420 - Beginning PHP 5.3

Part III: Using PHP in Practice
There ’ s a lot of repetition of the table names accessLog and members in this query. Fortunately, SQL lets
you create short table aliases by specifying an alias after each table name in the FROM clause. You can
then use these aliases to refer to the tables, rather than using the full table names each time:

mysql > SELECT DISTINCT al.memberId, m.firstName, m.lastName FROM accessLog
al, members m WHERE al.memberId = m.id;
+----------+-----------+-----------+
| memberId | firstName | lastName |
+----------+-----------+-----------+
| 1 | John | Sparks |
| 3 | Jo | Scrivener |
| 6 | Bill | Swan |
+----------+-----------+-----------+
3 rows in set (0.00 sec)

You can also use the AS keyword to create aliases for the columns returned by your query. Consider this
query that you looked at earlier:

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)
Notice that the second column in the result set is called count( pageUrl ) . Not only is this not very
descriptive, but you ’ ll find it ’ s awkward to refer to in your PHP script. Therefore, it ’ s a good idea to
rename this column to something more meaningful:

mysql > SELECT memberId, count( pageUrl ) AS urlsViewed FROM accessLog GROUP
BY memberId;
+----------+------------+
| memberId | urlsViewed |
+----------+------------+
| 1 | 1 |
| 3 | 2 |
| 6 | 1 |
+----------+------------+

3 rows in set (0.00 sec)
Other Useful MySQL Operators and Functions

MySQL contains a wealth of operators and functions that you can use to build more complex queries.
You ’ ve already used a few of these in this chapter. Here you explore some other common operators and
functions. Bear in mind that this is nowhere near a complete list (you can find such a list in the MySQL
manual at http://dev.mysql.com/doc/ ).

382

9/21/09 9:12:01 AM
c13.indd 382
c13.indd 382 9/21/09 9:12:01 AM