web-based mail [SOLVED]

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

web-based mail [SOLVED]

Post 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?
Last edited by s.dot on Thu Mar 30, 2006 8:54 am, edited 1 time in total.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: web-based mail

Post 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 ;)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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 ;)
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

OK, i guess i'm kind of talking myself through this :P

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? :?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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!?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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. :-D
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply