How to handle a member's site email, like email sent back and forth bwtween members?

[websurfer]

Hello, all: have this general question on how to handle member's email in a site...

what is the right way to setup an email-system structure, say like in dating site, where members can email between each other, and if a member receives a new email it will "show" new email, and once it is read, then it turns off... member would either receive email notices to their email, or coudl also login in the site to read their email... (like Yahoo too per say)

Not sure how the whole system would be setup... would the emails that are sent back and forth between members be entered into a database, which then will show in each member's email list as send/received email? and if so, how would the site know that an "email" has been read or not? and show appropriate "new" email warning? I am thinking, maybe a conditional statement like "if email present in users[email] field, show "new-mail" icon, else show-nothing"??

Or am I completely off base?

Any feedback much appreciated!

[somedude3488]

actually a system like this is quite simple. i have created a few and i am working with one on my newest project.

i can share some of my code once i know how your system is set up so it will easily integrate into your site.

[websurfer]

Hi, Keith:

thanks for your reply..
What I was looking for was just a basic idea/framework on how one would go about setting something like this up.. I am trying to learn this language a bit better so wanted to just get basic direction, guidance on how something like is supposed to work so i can try to replicate it...

[somedude3488]

ok, well start by setting up a database. i used an inbox, outbox, and message table. after this is created, start making a members area where users need to login. this is so you can tell whos messages need to be displayed. upon completion of that make input forms like the "send message" feature. then make the message display pages.

[humbug]

Personally, I would make a few tables like this: (I will refer to email's (eg [e-mail]) as emails and messages between users as Personal Messages (PM's)
table: users

Column:   description
id:       unique, auto increment id of user
username: username
password: hash of password
email:    users email address

table: PMs

Column:   description
id:       unique, auto increment id of message
from:     user id of who sent the message
to:       user id of who the message is to
subject:  message subject
message:  message content
read:     tinyint where 1 means "read" and 0 means unread

I always include an id and use that when referring to rows. On a page that isn't the users "inbox" I would use the query "SELECT `id` FROM `PMs` WHERE to='$user_id' AND read=0" then if a result was returned then you echo a link to their inbox.

In the inbox I'd use "SELECT * FROM `PMs` WHERE to='$user_id'" and the "read" column can be used to, say, display the messages as bold or not bold.

In the outbox I'd use "SELECT * FROM PMs WHERE from='$user_id'" .

[websurfer]

Thanks guys, one more question... I get much of what you are saying, but in the case of Humbug's suggestion. how is the "read" (tinyint) cloumn, suppsoed to indicate, or know to indicate that message has been read or not read? how would it become 0 or 1??

Thanks!

[somedude3488]

Thanks guys, one more question... I get much of what you are saying, but in the case of Humbug's suggestion. how is the "read" (tinyint) cloumn, suppsoed to indicate, or know to indicate that message has been read or not read? how would it become 0 or 1??

Thanks!

once the member goes to the "view" portion of the code, have it mark the message as read if it is unread. pretty simple.

also, when i was creating my system, which was a lot like humbugs', i found a major flaw. once someone deletes the message from the database, it will delete the message from the senders outbox as well. this is why i used an inbox and outbox table. they both refer to an instance the message table, so you can't delete the message itself.

to fix this problem i used something like this:

#DROP TABLE IF EXISTS `inbox`;
CREATE TABLE IF NOT EXISTS `inbox` (
  `in_id` int(11) NOT NULL auto_increment,
  `in_mem_id` int(11) NOT NULL default '0',
  `in_mess_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`in_id`)
);
#DROP TABLE IF EXISTS `messages`;
CREATE TABLE IF NOT EXISTS `messages` (
  `mess_id` int(11) NOT NULL auto_increment,
  `mess_to` int(11) NOT NULL default '0',
  `mess_from` int(11) NOT NULL default '0',
  `mess_subject` varchar(200) NOT NULL default '',
  `mess_text` longtext NOT NULL,
  `unread` int(11) NOT NULL default '0',
  `time` int(11) NOT NULL default '0',
  PRIMARY KEY  (`mess_id`)
);
#DROP TABLE IF EXISTS `outbox`;
CREATE TABLE IF NOT EXISTS `outbox` (
  `out_id` int(11) NOT NULL auto_increment,
  `out_mem_id` int(11) NOT NULL default '0',
  `out_mess_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`out_id`)
);

then to keep your database running smoothly, have cron job that will check if a message is not in a members inbox or outbox and if its not delete it.

[websurfer]

thanks Keith, I think I got the basics of it. Thanks for your feedback.
One more quick question, what is a "cron" job??

[somedude3488]

instead of me trying to explain. check out this link: http://en.wikipedia.org/wiki/Cron

[websurfer]

thanks again... gonna check out link

[humbug]

If you don't want to get into the CRON stuff then I would suggest that instead of having tables for inbos and outbox, you could have two more columns in the "message" table, one called "in_inbox" and one called "in_outbox" then when a user clicks "delete" in their inbox you set "in_inbox" to 0 and then do a check to see if both "in_inbox" and "in_outbox" areboth 0. If so, delete the entry.