More Tables - PHP

The Messages table is at the heart of the application.  It is responsible for storing all the mail messages as they are downloaded from the server. This is where it all begins. The userid identifies the user that is downloading the messages and is stored in the userid foreign key.

CREATE TABLE `messages` (
  `msg_id` int(11) NOT NULL auto_increment,
  `msg_num` int(11) NOT NULL default '0',
  `from` varchar(100) NOT NULL default '',
  `subject` varchar(200) NOT NULL default '',
  `msg_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `msg_body` text NOT NULL,
  `checked` int(1) NOT NULL default '0',
  `userid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`msg_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

INSERT INTO `messages` VALUES (1, 2, 'admin@lo.com', 'helloooo',
'2005-12-20 00:00:00', '<h1>Hello Jack</h1><br>rn<br>rn<p>This is a <em>cool</em>
day</p>rnrn<font color="#FF000">The End</font>', 1, 1);

INSERT INTO `messages` VALUES (2, 1, 'rach@look.co.uk',
'hellllllllooooo2', '0000-00-00 00:00:00',
'dhdghfghdrnfghdfghdfgh', 1, 1);

The sent and trash tables will store all the sent items and messages classed as trash. Both contain the foreign key that will identify the user of the system.

CREATE TABLE `sent` (
  `sent_id` int(11) NOT NULL auto_increment,
  `to` int(200) NOT NULL default '0',
  `from` varchar(100) NOT NULL default '',
  `subject` varchar(200) NOT NULL default '',
  `msg_body` text NOT NULL,
  `userid` int(11) NOT NULL default '0',
  `date_sent` date NOT NULL default '0000-00-00',
  `cc` varchar(100) NOT NULL default '',
  `bcc` varchar(100) NOT NULL default '',
  `attachment` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`sent_id`)
)
CREATE TABLE `trash` (
  ` trash_id ` int(11) NOT NULL auto_increment,
  `to` int(200) NOT NULL default '0',
  `from` varchar(100) NOT NULL default '',
  `subject` varchar(200) NOT NULL default '',
  `msg_body` text NOT NULL,
  `userid` int(11) NOT NULL default '0',
attachment` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`trash_id`)
)

The "users" table is also another important table. It will help to store user details and will also be used by almost all scripts to retrieve user related information from the database.  The user ID is set at login and is used throughout the application.

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `uname` varchar(100) NOT NULL default '',
  `upass` varchar(50) NOT NULL default '',
  `email` varchar(90) NOT NULL default '',
  `remuser` varchar(200) NOT NULL default '',
  `rempass` varchar(200) NOT NULL default '',
  `pop3` varchar(200) NOT NULL default '',
  `smtp` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`user_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

INSERT INTO `user` VALUES (1, 'yourname', 'pass', 'me@asite.com',' me@asite.com', 'yourpass', 'mail.asite.com', 'mail.asite.com');

Copy and paste the above SQL into your database client and run the SQL. All the tables should now be created and ready for use.

Conclusion

This was just an introduction to how the application is going to function. In the next article we will deal with the login system.