Page 413 - Beginning PHP 5.3

Chapter 13: Retrieving Data from MySQL with PHP
Although this approach is good if you know the exact column values you ’ re after, sometimes it ’ s useful
to be a bit less specific in your queries. For example, say you wanted to get a list of book club members
that have travel among their interests. Each otherInterests field in the members table is free - form,
consisting of a plain - English list of topics. How can you find out which otherInterests fields contain
the word “ travel ” ?

The answer is to use the LIKE operator. This operator allows you to specify a string in the form of a
pattern to search for, rather than an exact string:

SELECT ... WHERE fieldName LIKE pattern

;
Within the pattern string, you can include the following wildcard characters in addition to regular
characters:
❑ % matches any number of characters (including no characters at all)
❑ _ (underscore) matches exactly one character

So to retrieve a list of members that list travel as one of their interests, you could use:

mysql > SELECT username, firstName, lastName, otherInterests FROM members
WHERE otherInterests LIKE ‘%travel%’;
+-----------+-----------+----------+------------------------------------+
| username | firstName | lastName | otherInterests |
+-----------+-----------+----------+------------------------------------+
| mary | Mary | Newton | Writing, hunting and travel |
| janefield | Jane | Field | Thai cookery, gardening, traveling |
+-----------+-----------+----------+------------------------------------+
2 rows in set (0.00 sec)

Notice how MySQL has picked up both the word “ travel ” and the word “ traveling ”. Both these strings
match the pattern ‘ %travel% ’ (zero or more characters, followed by the word “ travel ”, followed by
zero or more characters).

By the way, there ’ s no requirement to include the column that you ’ re comparing — otherInterests in

this case — in the list of column names to retrieve. This is only done here so that you can see that both
members ’ interests include travel. The following SQL is equally valid:

mysql > SELECT username FROM members WHERE otherInterests LIKE ‘%travel%’;
You can use the _ (underscore) wildcard character to match a single character — for example:

mysql > SELECT firstName, lastName FROM members WHERE firstName LIKE ‘Mar_y’;
+-----------+----------+
| firstName | lastName |
+-----------+----------+
| Marty | Pareene |
+-----------+----------+

1 row in set (0.03 sec)
Notice that this query doesn ’ t bring back Mary Newton ’ s record because the underscore matches exactly
one character, and there are no characters between the “ r ” and “ y ” of “ Mary ”.

375

9/21/09 9:11:58 AM
c13.indd 375 9/21/09 9:11:58 AM
c13.indd 375