Page 1 of 1

PHP/MySQL messaging service?

Posted: Wed Jun 03, 2009 1:11 am
by cardinal
Hi all,

This may seem like an asinine question, but here it goes anyways -

How does one/would one want to create their own messaging service with PHP?

What I mean is, do most websites implement their own intra-website messaging service? It seems most hosting sites have some default forum creation where the skeleton (posting threads, user registration, messaging, etc.) can be automatically setup. However, I assume a good majority of websites implement their own messaging service (correct me if I'm wrong here).

I'm thinking from an easy lookability standpoint, messaging could be done via having a separate table per user with each row equaling a message. But I'm not sure if this is a good way, or if it's even necessary (built into all hosts?).

Anyways, if anyone has any insight I'd love to hear your view.

cheers,
card

Re: PHP/MySQL messaging service?

Posted: Wed Jun 03, 2009 1:58 pm
by Chalks
If you already have a user database, it's fairly easy to implement a simple messaging system. Here's how I would do it (assuming you have an unique ID for each user):

1. Create new table Messages with fields:
  • msgID int
  • toID int
  • fromID int
  • fromName varchar(100)
  • subject varchar(200)
  • message blob
  • timesent date(or timestamp)
  • useSigFlag int(1)
  • hasBeenReadFlag int(1)
  • useSmiliesFlag int(1)
  • useBBCodeFlag int(1)
  • anyOtherFlags int(1)
To send a message simply have a form that fills in these fields based on user input (for example, your user ID on devnetwork is 50392). When the user clicks "send" it gets saved into the database (after error checking and validation and such). When the other user logs on, a simple query "SELECT count(msgID) FROM Messages WHERE toID=$userID AND hasBeenReadFlag=0" will tell you how many unread messages there are. You can easily fetch messages and so on using the msgID as well.

If you would like a little bit better design, separate this into two tables:
table Messages
  • msgID int
  • toID int
  • fromID int
  • fromName varchar(100)
  • subject varchar(200)
  • timesent date(or timestamp)
  • hasBeenReadFlag int(1)
table MsgData
  • msgID int
  • message blob
  • useSigFlag int(1)
  • useSmiliesFlag int(1)
  • useBBCodeFlag int(1)
  • anyOtherFlags int(1)
then use a join to get everything you need for each user. You can modify the tables so that you could have things like "next message" and "previous message" and so on, but it's not really needed. Any questions, just holler. :)

Re: PHP/MySQL messaging service?

Posted: Wed Jun 03, 2009 5:52 pm
by mikemike
Whyw ould you need 'fromName'? Surely this comes from the users table. Other than that it looks spot on (although smilies and bbcode is the devil! :evil: )

Re: PHP/MySQL messaging service?

Posted: Wed Jun 03, 2009 8:13 pm
by Chalks
mikemike wrote:Whyw ould you need 'fromName'? Surely this comes from the users table. Other than that it looks spot on (although smilies and bbcode is the devil! :evil: )
It just lets you eliminate a second query. You definitely don't need it though. I have no idea if it improves performance or not, just kind of assumed it did.

Re: PHP/MySQL messaging service?

Posted: Wed Jun 03, 2009 8:20 pm
by mikemike
What happens when the user changes his username? You have to remember all the places you duplicate this entry and amend those too.

Re: PHP/MySQL messaging service?

Posted: Thu Jun 04, 2009 7:52 am
by Chalks
mikemike wrote:Massive school boy error. What happens when the user changes his username? You have to remember all the places you duplicate this entry and amend those too.
Thank you for your constructive criticism. It really helps build up the community when users are so willing to teach each other.

Also: how many forums have you found that let you change your username?

Re: PHP/MySQL messaging service?

Posted: Thu Jun 04, 2009 8:44 am
by mikemike
Quite a few. phpBB allows you to do it infact - it's in the administrator options, by default it cannot be changed but there are plenty that do allow it to be changed.

More and more now you are seeing logins by email rather than username, this allows users to have any screenname they want - it may even just use their real name, this adds a much more personal feel to the posting. As such it's essential that users can change their name, as what happens if they marry, re-marry, get divorced or change their name via some Government service (depol, etc). There are a million and one reasons a user may want their screen name changing.

Re: PHP/MySQL messaging service?

Posted: Thu Jun 04, 2009 9:05 am
by Chalks
mikemike wrote:... it's essential that users can change their name ...
I think that's debatable. If you think that is essential however, the original design I posted is obviously a poor choice (as you pointed out). So, cardinal, if you want to allow users to change names, disregard the fromName field. Instead query the user table using the userID to find their name. Otherwise, pretty much everything stays the same.

Re: PHP/MySQL messaging service?

Posted: Thu Jun 04, 2009 12:00 pm
by mikemike
I disagree here.

If you want to change usernames if anything then the fromName field should remain so that the user who received the message knows who the messag ewas originally from. Perhaps you could check if the username was changed and display the current one too.

Re: PHP/MySQL messaging service?

Posted: Thu Jun 04, 2009 1:22 pm
by Chalks
I'm afraid that we've rather threadjacked the original point of this topic. ;)

If you were going to allow users to change names, why would you ever even have the fromNames field? There's no point. Wasn't that what you meant when you said "What happens when the user changes his username? You have to remember all the places you duplicate this entry and amend those too." So instead, just query the user table with the userID to get the name. That way the only place that the name has to be changed is in the user table and that populates everything (as it all queries the user table anyways).

Re: PHP/MySQL messaging service?

Posted: Thu Jun 04, 2009 2:22 pm
by mikemike
I think we're both right. It depends on the applications requirements.

I may want to display old and new usernames, just the old, or just the new. Which I want will cause my schema to change.