Page 407 - Beginning PHP 5.3

Chapter 13: Retrieving Data from MySQL with PHP
lastAccess TIMESTAMP NOT NULL,
PRIMARY KEY (memberId, pageUrl)
);

INSERT INTO accessLog( memberId, pageUrl, numVisits ) VALUES( 1, ‘diary.php’,
2 );
INSERT INTO accessLog( memberId, pageUrl, numVisits ) VALUES( 3, ‘books.php’,
2 );
INSERT INTO accessLog( memberId, pageUrl, numVisits ) VALUES( 3, ‘contact
.php’, 1 );
INSERT INTO accessLog( memberId, pageUrl, numVisits ) VALUES( 6, ‘books.php’,

4 );
Why is the password field exactly 41 characters long? Further down in the code, you can see that you
insert the members ’ passwords in encrypted form by calling MySQL ’ s password() function. The
encrypted password strings returned by password() are always 41 characters long, so it makes sense to
use CHAR(41) for the password field.
A few new concepts in these SQL statements are worth exploring here: the BINARY attribute, the UNIQUE
constraint, the ENUM data type, and the TIMESTAMP data type.

The BINARY Attribute and Collations

All character data types have a collation that is used to determine how characters in the field are
compared. By default, a character field ’ s collation is case insensitive. This means that, when you sort the
column alphabetically (which you learn to do shortly), “ a ” comes before both “ b ” and “ B ”. It also means
that queries looking for the text “ banana ” will match the field values “ banana ” and “ Banana ” .

However, by adding the BINARY attribute after the data type definition, you switch the field to a binary
collation, which is case sensitive; when sorting, “ a ” comes before “ b ”, but “ B ” comes before “ a ” (because,
generally speaking, uppercase letters come before lowercase letters in a character set). Furthermore, this
means that matches are case sensitive too; “ banana ” will only match “ banana ”, not “ Banana ”.

In this case, you created the username field of the members table with the BINARY attribute, making it
case sensitive:

username VARCHAR(30) BINARY NOT NULL UNIQUE,
This ensures that there ’ s no ambiguity over the case of the letters in each user ’ s username; for example,
“ john ” is a different username than “ John ”. This is important because many people choose usernames
where the case of the username ’ s characters is significant to them. If they created their account with a
username of “ john ”, and later found out they could also login using “ John ”, they might wonder if they
were working with one account or two!

369

9/21/09 9:11:56 AM
c13.indd 369 9/21/09 9:11:56 AM
c13.indd 369