PHP Browser Game Private Messages?

manpreet Best Answer 2 years ago

First off, I'm asking this question here because gaming and messaging are intimately connected. Why win if you can't gloat? Nevertheless, I won't be offended if this needs to be moved to overflow.

I need a PM system for my browser game, which already has a user and authentication system (To clarify: the game already has a user system). Specifically, I'm wondering if a PHP library already exists that I can use to save some time. Better yet, if anyone knows of a nice Codeigniter library for messaging, that would be perfect.

I already have a good idea on how I would approach this if I have to write my own, but it seems like something that would already exist in a robust form (despite my failed Google searches).

Requirements:

1. Messages can be sent to individuals, multiple users, and guild mates (which is essentially just multiple users with server-side selecting who to target).
2. Users can reply to individual or all recipients
3. Messages appear in sender's outbox and receiver's inbox.
4. Utilizes some 2-table design to handle marking read / deleting messages on a user-by-user basis
5. Messages are semi-threaded. I.e. I won't necessarily have them appear in the GUI as threaded, but previous messages should be included at the bottom of a reply. Maybe better to say, previous messages are identifiable and accessible. (This, I'm not yet 100% sure how I would design if I write my own. I.E. whether to include the text of the previous message into the new message or somehow remember the previous message's database ID to link later)

Technology in Use: PHP, Codeigniter, Javascript, JQuery, Ajax, Sqlite

Note: I have seen that there are some similar questions on overflow, but they were very little help in the answer department. Otherwise, people were looking for either chat boxes or more umm.. like social media site threads, which I find a bit different than something you would find in a game.

manpreet 2 years ago

This sounds like a pretty simple task. First of all, you need a database to store your messages; presumably you already have one for your user accounts, but if you want recommendations, MySQL, PostgreSQL and SQLite are all common choices and should do fine.

Next, you'll need a table to store the messages. A typical message table should probably contain at least the following columns:

• Message ID, the primary key to the table. This can be just an auto-incrementing number.
• A timestamp of when the message was sent; useful for chronological sorting. Create an index on this column. (In fact, if you frequently sort messages by this columns, you may also want to include it as the last column of any other indexes.)
• The sender of the message; a user ID. Also index this column (or mark it as a foreign key into your user table, if your DB supports those).
• A parent message ID, for threading. May be NULL. Should be indexed as a foreign key to this table itself.
• The title / subject of the message; a VARCHAR type should be good.
• The body of the message, whatever variable character type your DB has that lets you store at least several kilobytes of text.

Obviously, that's just a bare-bones sketch of a message table. You might want to add some other fields, like a last edit timestamp if messages can be edited after sending. Also, some of the fields above could be left out if the information is stored in some other way; for example, the sender could instead be stored in the same table as the recipients (see below), and it could be more efficient to store the message body in a separate table as a form of vertical partitioning.

Now we also need to store the recipients. Since a single message might have several of those, we need a separate link table, with columns like this:

• Message ID: a foreign key to the message table.
• Recipient ID: a foreign key to the user table.

Make sure both of these columns are indexed, so that finding both the recipients of a given message and the messages sent to a given recipient will be efficient.

That's basically enough, but you might also want to add a few more columns. For example, this table could conveniently store a flag indicating whether or not the recipient has seen the message. Also, if you added a "role" column, you could use it to indicate different types of recipients (maybe some only got "carbon copies"?), or even use it to store the sender of the message, and any other possibly associated users, in this same table.

Note that you don't really need a separate ID column for this table: the first two columns above (and possibly the role column, if any) already make up a natural primary key together.

Here's what the definitions of these tables might look like in SQL. I've written these for MySQL since it's what I'm most familiar with, but they shouldn't be hard to adapt for other DBs:

-- here's our basic message table
CREATE TABLE message (
  message_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sent TIMESTAMP NOT NULL,
  sender INTEGER NOT NULL, -- foreign key to user table (not shown)
  parent_id INTEGER NULL, -- optional foreign key to this table
  title VARCHAR(255) NOT NULL
);

-- timestamp index for chronological sorting, also appended to other indices
CREATE INDEX i_sent ON message (sent);

-- foreign key indices (because MySQL doesn't really have true foreign keys)
CREATE INDEX i_sender_sent ON message (sender, sent);
CREATE INDEX i_parent_sent ON message (parent, sent);

-- optional, for sorting messages by title if that's a common use case
CREATE INDEX i_title ON message (title);

-- let's make a separate table for the body, just to show how it's done
CREATE TABLE message_body (
  message_id INTEGER NOT NULL PRIMARY KEY, -- also foreign key to msg table
  body TEXT NOT NULL
);

-- and finally, the recipient table
CREATE TABLE recipient (
  message_id INTEGER NOT NULL, -- foreign key to message table
  rcpt INTEGER NOT NULL, -- foreign key to user table (not shown)
  seen TINYINT NOT NULL DEFAULT 0, -- MySQL doesn't have a real boolean type
  PRIMARY KEY (message_id, user_id)
);

-- an optional index for quickly finding unread messages
CREATE INDEX i_rcpt_seen_msg ON recipient (rcpt, seen, message_id);

Using these tables, here are some examples of common queries:

• A user's outbox, newest first:

  SELECT * FROM message
  WHERE sender = :sender
  ORDER BY sent DESC

• A user's inbox, newest first:

  SELECT * FROM message
    JOIN recipient USING (message_id)
  WHERE rcpt = :recipient
  ORDER BY sent DESC

• The number of unread messages to a user:

  SELECT COUNT(*) FROM recipient
  WHERE rcpt = :recipient
    AND seen 
                                                      
  
                                                                                                                  REPLY
                                                          
                                                      
                                                      
  
  
  
                                                          
  
                                                              0
                                                                  views   0
                                                                      likes    
                                                                  0 shares
                                                                                                                                  
  
  
      
  
           Facebook
          
           Twitter
          
          
           Linked In 
          
            WhatsApp