Page 406 - Beginning PHP 5.3

Part III: Using PHP in Practice
If you don ’ t fancy typing all these lines directly into the MySQL command - line tool, you can create a text
file — say, book_club.sql — and enter the lines in there. Save the file in the same folder as you run the

MySQL command - line tool from. Run the tool, then type:

source book_club.sql;
This command reads the lines of the text file and executes them, just as if you ’ d manually entered the
SQL statements into the tool line - by - line.

Without further ado, here are the SQL statements to create and populate the two tables:
USE mydatabase;

CREATE TABLE members (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(30) BINARY NOT NULL UNIQUE,
password CHAR(41) NOT NULL,
firstName VARCHAR(30) NOT NULL,
lastName VARCHAR(30) NOT NULL,
joinDate DATE NOT NULL,
gender ENUM( ‘m’, ‘f’ ) NOT NULL,
favoriteGenre ENUM( ‘crime’, ‘horror’, ‘thriller’, ‘romance’, ‘sciFi’,
‘adventure’, ‘nonFiction’ ) NOT NULL,
emailAddress VARCHAR(50) NOT NULL UNIQUE,
otherInterests TEXT NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO members VALUES( 1, ‘sparky’, password(‘mypass’), ‘John’,
‘Sparks’, ‘2007-11-13’, ‘m’, ‘crime’, ‘jsparks@example.com’, ‘Football,
fishing and gardening’ );
INSERT INTO members VALUES( 2, ‘mary’, password(‘mypass’), ‘Mary’, ‘Newton’,
‘2007-02-06’, ‘f’, ‘thriller’, ‘mary@example.com’, ‘Writing, hunting and
travel’ );
INSERT INTO members VALUES( 3, ‘jojo’, password(‘mypass’), ‘Jo’, ‘Scrivener’,
‘2006-09-03’, ‘f’, ‘romance’, ‘jscrivener@example.com’, ‘Genealogy, writing,
painting’ );
INSERT INTO members VALUES( 4, ‘marty’, password(‘mypass’), ‘Marty’,
‘Pareene’, ‘2007-01-07’, ‘m’, ‘horror’, ‘marty@example.com’, ‘Guitar playing,
rock music, clubbing’ );
INSERT INTO members VALUES( 5, ‘nickb’, password(‘mypass’), ‘Nick’,
‘Blakeley’, ‘2007-08-19’, ‘m’, ‘sciFi’, ‘nick@example.com’, ‘Watching movies,
cooking, socializing’ );
INSERT INTO members VALUES( 6, ‘bigbill’, password(‘mypass’), ‘Bill’, ‘Swan’,
‘2007-06-11’, ‘m’, ‘nonFiction’, ‘billswan@example.com’, ‘Tennis, judo,
music’ );
INSERT INTO members VALUES( 7, ‘janefield’, password(‘mypass’), ‘Jane’,
‘Field’, ‘2006-03-03’, ‘f’, ‘crime’, ‘janefield@example.com’, ‘Thai cookery,
gardening, traveling’ );

CREATE TABLE accessLog (
memberId SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
pageUrl VARCHAR(255) NOT NULL,
numVisits MEDIUMINT NOT NULL,

368

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