Private Messaging System
Part 1
Overview and Structure
The first thing we need to consider when planning a project such as this is what features we want included. We have limitless options at our disposal, and as with all other things PHP related, you are really only limited by your imagination. However, to keep everyone sane, we are simply going to include very basic, yet helpful features in this system. Here is a list of some of the features we will build into our PM system:
Now that we have the basic overview out of the way, let's begin to develop our database to support the features mentioned above. For the sake of time, I will not be creating an entire membership system here, however, I will be "faking" it with some session variable to simulate what you may have in your own membership system. To help simulate this system, we will generate a small users table and populate it with some generic usernames:
MySQL Example:
---------------------------------------------------------------------
CREATE TABLE users (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE
);
INSERT INTO users (username) VALUES ('grant', 'ralph', 'carrie', 'stacy', 'gertrude', 'sam', 'steph');
----------------------------------------------------------------------
Once this simple table has been created and populated, we have a base set of users off of which we can build the application. The next thing we need is our PM table. All of the features we listed above can very easily be included in one fairly simple table. Here is the table I came up with to use. As a side note, the included INSERT statements will simply give us some random entries between our primary user and others so that we have some data to work our magic on.
MySQL Example:
----------------------------------------------------------------------
CREATE TABLE myPMs (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
to_id INT(11) REFERENCES users (id),
from_id INT(11) REFERENCES users (id),
time_sent DATETIME NOT NULL,
subject VARCHAR(50) NOT NULL DEFAULT '',
message TEXT NOT NULL DEFAULT '',
opened CHAR(1) NOT NULL DEFAULT 'n',
time_opened DATETIME DEFAULT NULL
);
INSERT INTO myPMs (to_id, from_id, time_sent, subject, message) VALUES
(1, 2, '2006-02-14 02:34:22', 'Welcome to UKWW Forums!', 'Just wanted to wish you a happy heart day!'),
(1, 3, '2006-04-01 08:59:45', 'Have a great day!', 'And enjoy it to the fullest'),
(2, 1, '2006-02-14 10:14:52', 'Back at ya', 'Thanks for the note... happy valentines yourself ;-)'),
(1, 6, '2005-12-25 22:01:19', 'Merry Christmas!', '...and a Happy New Year, too!'),
(1, 4, '2006-09-18 16:48:02', 'Happy B-Day', 'It is your birthday, right???');
-------------------------------------------------------------------
As you can see by the structure of our PM table, we only have to provide five pieces of information any time we are creating a new PM: 1) user id of the recipient, 2) user id of the sender (our session user ID), 3) the date and time it was sent (will always be NOW() from our script), 4) a subject, and finally 5) the actual body of the message. All the other columns will be handled initially by the database, and we will modify a couple of the other fields as we start marking messages as "read" and so forth.
Part 1
Overview and Structure
The first thing we need to consider when planning a project such as this is what features we want included. We have limitless options at our disposal, and as with all other things PHP related, you are really only limited by your imagination. However, to keep everyone sane, we are simply going to include very basic, yet helpful features in this system. Here is a list of some of the features we will build into our PM system:
- Send new messages to other users
- Distinguish between read and unread messages in my inbox
- Delete selected messages
- View my sent messages
- Record time message was sent as well as time message was opened for logging purposes
Now that we have the basic overview out of the way, let's begin to develop our database to support the features mentioned above. For the sake of time, I will not be creating an entire membership system here, however, I will be "faking" it with some session variable to simulate what you may have in your own membership system. To help simulate this system, we will generate a small users table and populate it with some generic usernames:
MySQL Example:
---------------------------------------------------------------------
CREATE TABLE users (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE
);
INSERT INTO users (username) VALUES ('grant', 'ralph', 'carrie', 'stacy', 'gertrude', 'sam', 'steph');
----------------------------------------------------------------------
Once this simple table has been created and populated, we have a base set of users off of which we can build the application. The next thing we need is our PM table. All of the features we listed above can very easily be included in one fairly simple table. Here is the table I came up with to use. As a side note, the included INSERT statements will simply give us some random entries between our primary user and others so that we have some data to work our magic on.
MySQL Example:
----------------------------------------------------------------------
CREATE TABLE myPMs (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
to_id INT(11) REFERENCES users (id),
from_id INT(11) REFERENCES users (id),
time_sent DATETIME NOT NULL,
subject VARCHAR(50) NOT NULL DEFAULT '',
message TEXT NOT NULL DEFAULT '',
opened CHAR(1) NOT NULL DEFAULT 'n',
time_opened DATETIME DEFAULT NULL
);
INSERT INTO myPMs (to_id, from_id, time_sent, subject, message) VALUES
(1, 2, '2006-02-14 02:34:22', 'Welcome to UKWW Forums!', 'Just wanted to wish you a happy heart day!'),
(1, 3, '2006-04-01 08:59:45', 'Have a great day!', 'And enjoy it to the fullest'),
(2, 1, '2006-02-14 10:14:52', 'Back at ya', 'Thanks for the note... happy valentines yourself ;-)'),
(1, 6, '2005-12-25 22:01:19', 'Merry Christmas!', '...and a Happy New Year, too!'),
(1, 4, '2006-09-18 16:48:02', 'Happy B-Day', 'It is your birthday, right???');
-------------------------------------------------------------------
As you can see by the structure of our PM table, we only have to provide five pieces of information any time we are creating a new PM: 1) user id of the recipient, 2) user id of the sender (our session user ID), 3) the date and time it was sent (will always be NOW() from our script), 4) a subject, and finally 5) the actual body of the message. All the other columns will be handled initially by the database, and we will modify a couple of the other fields as we start marking messages as "read" and so forth.