Page 1 of 1
web-based mail [SOLVED]
Posted: Fri Mar 10, 2006 2:27 am
by s.dot
so like a private messaging system.. kind of like the system phpbb has
what's the best way to do that?
currently I have separate database tables for Sent, Saved, & Inbox... is this the best route to go? It does waste a lot of space.. I guess.
I've have tried playing around with using a status field for messages all in one table.. IE: 1= message sent, 2 = message read, etc...
but it got a bit confusing when dealing with saving/deleting
any ideas suggestions?
Re: web-based mail
Posted: Fri Mar 10, 2006 2:45 am
by Chris Corbyn
scrotaye wrote:so like a private messaging system.. kind of like the system phpbb has
what's the best way to do that?
currently I have separate database tables for Sent, Saved, & Inbox... is this the best route to go? It does waste a lot of space.. I guess.
I've have tried playing around with using a status field for messages all in one table.. IE: 1= message sent, 2 = message read, etc...
but it got a bit confusing when dealing with saving/deleting
any ideas suggestions?
Off the top of my head
A table for folder names
A table for userfolders (userid, folderid)
A table for the actual message (sender, title, folderid, userid)
I would have a table for each folder... it's not so scalable when you can store those in one table with some ids

Posted: Fri Mar 10, 2006 4:33 am
by s.dot
Ah, I had never thought of it that way.
Initially that idea sounds nice. But when coding it, i'd run into the same problem as before. User 1 sends user 2 a message. User 1 now has it in their inbox, user 2 has it in their outbox/sent box. Now what if user 1 deletes the message, but user 2 keeps it in the sent box?
Wouldn't I need some type of "status" field to signify where it's at in each users folders.
Posted: Fri Mar 10, 2006 6:17 am
by Chris Corbyn
You have a table called usermessages which links the userid to the messageid. This way the message is always there, whether the user has a copy or not depends upon that table

Posted: Fri Mar 10, 2006 7:13 am
by jayshields
I have made the database table for a private messages system but not implemented it yet.
I use one table for privmsgs. Each row has a privmsg id, a to id, a from id, a read boolean, a subject, a message and a date sent.
The from id is the user which sent the message, the to id is who it was sent to. User's can't delete messages. When the receiver (the user with the to id) opens the message for the first time, the read boolean is set to 1.
For displaying purposes for example user 3, i will select all where from = from id 3 and show it in sent, and then select all where to = to id 3 and show it in inbox, the read boolean will determine if its unread or not.
Posted: Wed Mar 29, 2006 2:27 am
by s.dot
K, so it has now come time for me to program this
this is what i have so far.
I'm pretty much stuck on what to do for the 'saved' and 'trash' messages
I'm doing it based on a status field.
User 1 sends a message, status is 0.. it will show up in their 'Outbox'
User 2 will read the message, and update the status to 1. now this will show up in user 1s sent box, and user 2s inbox.
Code: Select all
// create custom message and query based on folder
switch($folder){
case "inbox":
$msg = 'Any private message sent to you will be shown here. Any time you get a private message a red number will appear in your member navigation bar.';
$sql_text = "SELECT `id`,`from_id`,`message`,`prev_message`,`time` FROM `privatemessages` WHERE `to_id` = '$theperson' AND `status` = '0' OR `status` = '1' ORDER BY `id` DESC";
// we're going to want to update the status to 'read' (1) status
mysql_query("UPDATE `privatemessages` SET `status` = 1 WHERE `to_id` = '$theperson'") or die(mysql_error());
break;
case "outbox":
$msg = 'Your outbox contains messages that you sent to other members that haven\'t been read yet. You may \'unsend\' them at any time.';
$sql_text = "SELECT `id`,`to_id`,`message`,`prev_message`,`time` FROM `privatemessages` WHERE `from_id` = '$theperson' AND `status` = '0' ORDER BY `id` DESC";
break;
case "sent":
$msg = 'This folder contains messages that have been sent to (and read by) other members. Messages in this folder will be deleted after one week.';
$sql_text = "SELECT `id`,`to_id`,`message`,`prev_message`,`time` FROM `privatemessages` WHERE `from_id` = '$theperson' AND `status` = '1' ORDER BY `id` DESC";
break;
// unsure what to do in these cases
// mixed status's maybe? separate tables?
case "saved":
$msg = 'This folder contains messages that you have saved. You can keep them here for reference, or delete them at any time.';
break;
case "trash":
$msg = 'The trash is where all the messages you deleted go. These messages will remain here for one week, unless you delete them yourself.';
break;
}
My table structure looks like this:
Code: Select all
CREATE TABLE `privatemessages` (
`id` int(15) NOT NULL auto_increment,
`to_id` int(10) NOT NULL,
`from_id` int(10) NOT NULL,
`message` text NOT NULL,
`prev_message` text NOT NULL,
`time` int(25) NOT NULL,
`status` int(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
Any help with logic for saved and trash would be much appreciated!
Thanks,
-Scott
Posted: Thu Mar 30, 2006 12:12 am
by s.dot
OK, i guess i'm kind of talking myself through this
When someone sends a message, I insert the message once into a table for the actual message.
Then I insert 2 records into a relational table that relates the user id to the message. 1 entry for the sender, and one entry for the receiver.
So a couple sample records would look like this
data table
Code: Select all
id | to_id | from_id | message_id | folder_id
1 | 107 | 803 | 17 | 1 // inbox for receiver
2 | 107 | 803 | 17 | 2 // outbox for sender
message table
Code: Select all
id | to_id | from_id | message | time
17 | 107 | 803 | 'hey you!' | timestamp
So, to get messages for the inbox this is what my query looks like
Code: Select all
$sql_text = "SELECT `message_id` FROM `privatemessages_data` WHERE `to_id` = '$theperson' AND `folder_id` = 1 ORDER BY `id` DESC";
To get messages for the outbox my query looks like this
Code: Select all
$sql_text = "SELECT `message_id` FROM `privatemessages_data` WHERE `from_id` = '$theperson' AND `folder_id` = 2 ORDER BY `id` DESC";
Everything is working fine except for the trash messages.
I don't know what to query for.
Currently I have this:
Code: Select all
$sql_text = "SELECT `message_id` FROM `privatemessages_data` WHERE `to_id` = '$theperson' AND `folder_id` = 5 ORDER BY `id` DESC";
That works fine if I'm only deleting messages from my inbox (where I am the to_id). But if I delete from my outbox or sentbox, I am the from_id and messages don't show up.
So basically in my query I don't know how to decide whether to select the to_id or the from_id since it could be both! Any help?

Posted: Thu Mar 30, 2006 6:01 am
by phpScott
I think you would need some kind of swith to now if the message is in the sent box, if it is then you would do the correct delete query.
Posted: Thu Mar 30, 2006 8:03 am
by s.dot
I think im just going to permanently delete the message records when they delete from outbox/sentbox. And leave the trash only for messages from the inbox.
Is this how PHPBB does it?
Posted: Thu Mar 30, 2006 8:10 am
by JayBird
Doesn't phpBB store a duplicate of the messages, one for the sender and one for the reciever.
Im pretty sure that is how it used to work!?
Posted: Thu Mar 30, 2006 8:54 am
by s.dot
Yes, and that's what I'm doing as well.
I think I have it all solved. I just feel like I'm using a bunch of ugly hacks.

But if it works, then it works.
Page executed in 0.00664 seconds - with 10 messages in the inbox.. so i'd say it's pretty good.
