PHP/MySQL messaging service?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cardinal
Forum Newbie
Posts: 14
Joined: Mon Jun 01, 2009 2:29 pm

PHP/MySQL messaging service?

Post 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
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: PHP/MySQL messaging service?

Post 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. :)
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: PHP/MySQL messaging service?

Post 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: )
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: PHP/MySQL messaging service?

Post 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.
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: PHP/MySQL messaging service?

Post 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.
Last edited by mikemike on Thu Jun 04, 2009 2:52 pm, edited 1 time in total.
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: PHP/MySQL messaging service?

Post 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?
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: PHP/MySQL messaging service?

Post 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.
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: PHP/MySQL messaging service?

Post 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.
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: PHP/MySQL messaging service?

Post 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.
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: PHP/MySQL messaging service?

Post 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).
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: PHP/MySQL messaging service?

Post 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.
Post Reply