Page 414 - Beginning PHP 5.3

Part III: Using PHP in Practice
You can reverse the sense of the comparison by using NOT LIKE instead of LIKE . The following example
displays a list of members who don ’ t include travel in their interests:
mysql > SELECT username, firstName, lastName, otherInterests FROM members
WHERE otherInterests NOT LIKE ‘%travel%’;
+----------+-----------+-----------+---------------------------------------+
| username | firstName | lastName | otherInterests |
+----------+-----------+-----------+---------------------------------------+
| sparky | John | Sparks | Football, fishing and gardening |
| jojo | Jo | Scrivener | Genealogy, writing, painting |
| marty | Marty | Pareene | Guitar playing, rock music, clubbing |
| nickb | Nick | Blakeley | Watching movies, cooking, socializing |
| bigbill | Bill | Swan | Tennis, judo, music |
+----------+-----------+-----------+---------------------------------------+

5 rows in set (0.05 sec)
Summarizing Data

Just as PHP contains a large number of built - in functions, MySQL also gives you many functions to assist
you with your queries. In this section you look at some of MySQL ’ s aggregate functions. Rather than
returning the actual data contained in a table, these functions let you summarize a table ’ s data in
different ways:

❑ count() — Returns the number of rows selected by the query
❑ sum() — Returns the total of all the values of a given field selected by the query

❑ min() — Returns the minimum value of all the values of a given field selected by the query

❑ max() — Returns the maximum value of all the values of a given field selected by the query

❑ avg() — Returns the average of all the values of a given field selected by the query
You can use count() in two slightly different ways:

❑ count( fieldname ) — Returns the number of rows selected by the query where fieldname

isn ’ t NULL
❑ count( * ) — Returns the number of rows selected by the query, regardless of whether the

rows contain any NULL values

Here are a couple of count() examples. The first example counts all the rows in the members table:
mysql > SELECT COUNT( * ) FROM members;
+------------+
| COUNT( * ) |
+------------+
| 7 |
+------------+

1 row in set (0.02 sec)

376

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