A PHP Application: The Wedding Gift Registry - Learning MySQL - page 475

CREATE TABLE `users` (
`username` VARCHAR(30) NOT NULL,
`password` VARCHAR(30) NOT NULL,
PRIMARY KEY (`username`)
);
and gifts , which stores data about gifts:
CREATE TABLE `gifts` (
`gift_id` SMALLINT NOT NULL AUTO_INCREMENT,
`description` VARCHAR(255) NOT NULL,
`shop` VARCHAR(100) NOT NULL,
`quantity` SMALLINT NOT NULL,
`color` VARCHAR(30) DEFAULT NULL,
`price` VARCHAR(30) DEFAULT NULL,
`username` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`gift_id`)
);
The username is unique for each user and is used as a foreign key in the gifts table.
Since this is a relatively simple application, we won't use a separate user ID field; for a
complex application with heavy usage, it would be more efficient to have a small user
ID field in the declaration, as shown here:
CREATE TABLE `users` (
`username` VARCHAR(30) NOT NULL,
`password` VARCHAR(30) NOT NULL,
`user_id` INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`user_id`)
);
and use the user_id field as a foreign key in other tables.
Instead of typing in the CREATE statements, you can conveniently create the database by
running the create_wedding_database.sql file, which you can download from the topic's
home page:
$ mysql --user=root --password= the_mysql_root_password \
< create_wedding_database.sql
You'll find these lines near the beginning of the file:
DROP DATABASE IF EXISTS wedding;
CREATE DATABASE wedding;
USE wedding;
These delete any existing wedding database before creating the new one from scratch.
We should also allow access to the database from a client; in our application, the client
is primarily the PHP engine that executes our code. We can create the MySQL user
fred who has a password shhh . This user is set up using the following SQL GRANT
statement:
GRANT SELECT, INSERT, DELETE, UPDATE
ON wedding.*