Specifications

LISTING 28.1 Continued
sent datetime,
modified timestamp
);
#stores the images that go with a particular mail
create table images
(
mailid int not null,
path char(100) not null,
mimetype char(100) not null
);
grant select, insert, update, delete
on mlm.*
to mlm@localhost identified by ‘password’;
insert into subscribers values
(‘admin@localhost’, ‘Administrative User’, ‘H’, password(‘admin’), 1);
Remember that you can execute this SQL by typing
mysql -u root -p < create_database.sql
You will need to supply your root password. (You could, of course, execute this script via any
MySQL user with the appropriate privileges; we have just used root here for simplicity.)
You should change the password for the mlm user and the administrator in your script before
running it.
Some of the fields in this database require a little further explanation, so let’s briefly run
through them.
The lists table contains a listid and listname. It also contains a blurb, which is a
description of what the list is about.
The subscribers table contains email addresses (email) and names (realname)of the sub-
scribers. It also stores their password and a flag (admin) to indicate whether or not this user is
an administrator. We will also store the type of mail they prefer to receive in mimetype. This
can be either H for HTML or T for text.
The sublists table contains email addresses (email) from the subscribers table and listids
from the lists table.
The mail table contains information about each email message that is sent through the system.
It stores a unique id (mailid), the address the mail is sent from (email), the subject line of the
Building Practical PHP and MySQL Projects
P
ART V
662
34 7842 CH28 3/6/01 3:46 PM Page 662