Page 409 - Beginning PHP 5.3

Chapter 13: Retrieving Data from MySQL with PHP
ENUM fields serve two purposes. First, by limiting the range of values allowed in the field, you ’ re
effectively validating any data that is inserted into the field. If a value doesn ’ t match one of the values in
the predefined set, MySQL rejects the attempt to insert the value. Second, ENUM fields can save storage

space. Each possible string value — “ crime ”, “ horror ”, and so on — is associated with an integer, and
stored once in a separate part of the table. Each ENUM field can then be stored as an integer, rather than as
a string of characters.

As you can imagine, the ENUM data type is only useful in a situation in which there are a small number of
possible values for the field. Although you can define up to 65,535 allowed values for an ENUM type,
practically speaking, things start to get a bit unwieldy after 20 or so values!

The TIMESTAMP Data Type

You ’ ll remember from the last chapter that MySQL lets you store dates and times using a number of
different data types, such as DATE , DATETIME , TIME , YEAR , and TIMESTAMP . A TIMESTAMP field is a
bit different from the other date/time types in that it can automatically record the time that certain
events occur. For example, when you add a new row to a table containing a TIMESTAMP column, the field
stores the time that the insertion took place. Similarly, whenever a row is updated, the TIMESTAMP field
is automatically updated with the time of the update.

The other point to remember about TIMESTAMP fields is that they store the date and time in the UTC
(Universal Coordinated Time) time zone, which is essentially the same as the GMT time zone. This
probably won ’ t affect you much, because MySQL automatically converts TIMESTAMP values between
UTC and your server ’ s time zone as required. However, bear in mind that if you store a TIMESTAMP
value in a table, and you later change the server ’ s time zone, the value that you get back from the
TIMESTAMP field will be different.

A TIMESTAMP field is great for tracking things such as when a record was last created or updated,
because you don ’ t have to worry about setting or changing its value; it happens automatically. In this
example, you created a TIMESTAMP field in the accessLog table to track when the last access was made:
lastAccess TIMESTAMP NOT NULL,

Retrieving Data with SELECT

In the previous chapter, you took a brief look at SELECT statements, which let you extract data from a
database table. In the following sections you see how to use SELECT to build complex queries. You learn
how to:

❑ Limit the number of rows returned
❑ Sort returned rows in any order
❑ Use pattern matching
❑ Summarize returned data

❑ Eliminate duplicate rows
❑ Group results together

371

9/21/09 9:11:57 AM
c13.indd 371
c13.indd 371 9/21/09 9:11:57 AM