PHP Email - More Tables
(Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 10 
