Let's say you have authenticated users on your custom website, blog, community whatever, how cool would it be to let the user's chat with each other?
That's what I created this script for, a small and functional user messenger system in PHP & MySQL. Let me walk you through the script:
MySQL tables and how to make them work
These MySQL tables cover most of the program logic, as it should be, let the database do the work! After all everything we do is creating layers.
We create two tables for our chat system, one that saves all the active chats and the the other one to save the messages with a foreign key pointing to the userchat record ID.
A chat record is created before saving the first sent message. In our program logic we will save EVERY chat record and EVERY message record twice, that allows users to delete chats, without affecting the other end.
The tables:
-- Example table for our user accounts:
CREATE TABLE IF NOT EXISTS useraccount (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_name VARCHAR(64),
PRIMARY KEY (id),
UNIQUE (user_name) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- User chat records:
CREATE TABLE IF NOT EXISTS userchat
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
chat_owner INT UNSIGNED NOT NULL,
chat_partner INT UNSIGNED NOT NULL,
last_action INT UNSIGNED NOT NULL,
created_at INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- User chat message records:
CREATE TABLE IF NOT EXISTS userchat_msg
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
chat_id INT UNSIGNED NOT NULL,
msg_owner INT UNSIGNED NOT NULL,
sender INT UNSIGNED NOT NULL,
recipient INT UNSIGNED NOT NULL,
msg_date INT UNSIGNED NOT NULL,
msg_status TINYINT UNSIGNED NOT NULL,
msg_text TEXT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (chat_id) REFERENCES userchat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Example:
User A and user B have never texted before, or both deleted every chat from the past. For us that means there is no userchat record with user A as chat_owner and user B as chat_partner and vice versa.
Now User A (ID: 111) sends a "hello there" to user B (ID: 222), we need to create two records in the userchat table, one for each user as the chat owner, and two records in the userchat_msg table, both containing the original message text.
In PHP we need to run each query separately, and after each query get the userchat record ID, because we need to save it into the message records:
Add new userchat records like this:
-- Chat of user A with user B
$mysqli->query("INSERT INTO userchat (chat_owner, chat_partner, last_action, created_at) VALUES
(111, 222, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());");
$myChatID = intval($mysqli->insert_id);
-- Chat of user B with user A
$mysqli->query("INSERT INTO userchat (chat_owner, chat_partner, last_action, created_at) VALUES
(222, 111, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());");
$partnerChatID = intval($mysqli->insert_id);
And now that we initiated a new user chat for both users, we save the message "Hello there" that User A (ID: 111) sent to User B (ID: 222). Add new userchat_msg records like this:
$mysqli->query("INSERT INTO userchat_msg (chat_id, msg_owner, sender, recipient, msg_date, msg_status, msg_text) VALUES
($myChatID, 111, 111, 222, UNIX_TIMESTAMP(), 1, 'Hello there'),
($partnerChatID, 222, 111, 222, UNIX_TIMESTAMP(), 0, 'Hello there');");
The values for sender, recipient, msg_date and msg_text are of course the same. We saved the message message as 2 copies.
The differences are chat_id, msg_owner and msg_status. Last one because the copy of user A's sent message is set to 1 (opened) by default, and the the copy for user B is set to 0 (not opened), as long as user B hasn't opened the message, yet.
Each user will see only their own copy for all chats and messages, in their mailbox.
Now let's say after that user A sent another message "How are you?", add the new message like this:
$mysqli->query("INSERT INTO userchat_msg (chat_id, msg_owner, sender, recipient, msg_date, msg_status, msg_text) VALUES
($myChatID, 111, 111, 222, UNIX_TIMESTAMP(), 1, 'How are you?'),
($partnerChatID, 222, 111, 222, UNIX_TIMESTAMP(), 0, 'How are you?');");
Since there is already an active chat for and between user A and user B, we used the same chat IDs from before. Now we have to make sure, to update the column userchat.last_action
$mysqli->query("UPDATE userchat SET last_action = UNIX_TIMESTAMP() WHERE id = $myChatID OR id = $partnerChatID LIMIT 2;");
The column last_action holds the timestamp of the last sent message in that chat, thats why we update them for both chats. The user's mailbox will be ordered by last_action, so the newest chat is always on top.
Now we can create chats and send messages into that chat.
How to get the chat ID
Now as soon as users open their mailbox, they will get a list of all their active chats. That happens when our script messenger.php is requested.
If the operating user wants to open a chat or send a message to another user, in both ways the traget user name must be requested as a URL parameter partnerName.
The URL:
URL: myboard.com/messenger.php requests my mailbox
URL: myboard.com/messenger.php?partnerName=Franky requests the chat page with Franky
The chat page will show the message form and if a chat between me and the user I requested already exists, get the chat ID by user name and print the messages below.
Or if you want to rewrite the URL using HTACCESS:
URL: myboard.com/chat requests my mailbox
URL: myboard.com/chat/Franky requests the chat page with Franky
To achieve this you can use this .htaccess code:
RewriteEngine On
RewriteBase /server/httpdocs/myboardcom/
RewriteRule ^chat/?$ messenger.php [QSA]
RewriteRule ^chat/([A-Za-z\d]{3,64})/?$ messenger.php?partnerName=$1 [L]
This requires the user name to be unique and well formed, in our example a valid user name contains letters and numbers, case-insensitive and no spaces.
Minimum length of 3 is arbitrary and max. length should match the user name column in your useraccount table.
Advice: Even if you use htaccess and the regex pattern, you still have to validate the user name! Because the regex pattern in htaccess will be useless, if we request the page by messenger.php?partnerName=Franky.
How to get the chat ID now for real
We validate the requested user name and save it to $partnerName, we will use it to get the chat partner's user ID and user name (original case).
Because I can request chat/fRaNkY and Franky will be found, but I dont need that string to be displayed on the page.
As said before, this whole thing requires some user authentication system to work. We assume now the operating user's user ID is provided in $myUserID.
To get the chat partner by name and get the chat ID (if existant) use this QUERY:
SELECT usr.id, usr.user_name,
(SELECT cht.id FROM userchat cht WHERE cht.chat_owner = $myUserID AND cht.chat_partner = usr.id) AS chat_id
FROM useraccount usr WHERE usr.user_name = '$partnerName' AND usr.id != $myUserID LIMIT 1;
We retrieve the user ID and user name from the chat partner, and use a sub query to get an existant chat record. If I don't have a chat with the requested partner yet,
chat_id from the result above will be NULL.
The whole process:
// Let's just say the operating user is:
$myUserID = 111;
// Indicates if the requested user exists, if not show the mailbox page.
$openChat = FALSE;
if(isset($_GET["partnerName"])) {
// Validate user name
$partnerName = trim($_GET["partnerName"]);
if(preg_match('/^[A-Za-z\d]{3,64}$/' , $partnerName))
{
// User name valid, get info from database:
// Check if username really exists and get the user ID and the name from database
// Also check if a chat between the operating and the requested user exists, if yes get the chat ID
// RESULT: id, user_name, chat_id (will be NULL if no chat exists)
$res = $mysqli->query("SELECT usr.id, usr.user_name,
(SELECT cht.id FROM userchat cht WHERE cht.chat_owner = $myUserID AND cht.chat_partner = usr.id) AS chat_id
FROM useraccount usr WHERE usr.user_name = '$partnerName' AND usr.id != $myUserID LIMIT 1;");
if($res->num_rows === 1) {
$row = $res->fetch_assoc();
// User name of chat partner will be visible in the chat, don't use the $_GET value
$partnerName = $row["user_name"];
$partnerUserID = intval($row["id"]);
// Will be int (0) if no chat exists, yet
$myChatID = intval($row["chat_id"]);
$openChat = TRUE;
// If $openChat is TRUE, you can use these 3 variables: $partnerName, $partnerUserID, $myChatID (can be 0)
}
else {
// Requested user name is valid but not existant!
$errorFeedback = phpFeedback("error", "User name not found!");
}
}
else {
// else: invalid user name requested, you can redirect to 404 page here for example.
$errorFeedback = phpFeedback("error", "User name does not exist!");
}
}
Takeaway:
If $openChat is FALSE, the mailbox will be printed, with all user names as hyperlinks to open the chats.
If $openChat is TRUE, a chat page will be opened, if the requested user name exists.
Even if there is no chat yet, the HTML form for sending new messages will be printed.
Which means on every page request, we get the user name and ID of our chat partner, and my chat ID if I already have a chat with the partner.
Of course the chat partner has his own copy of the chat and his own chat ID, we need to get that ID upon sending a new message.
Now here is the fairly self explanatory script that lets the operating user send a message to the currently requested user.
if($openChat === TRUE AND isset($_POST["newMsgSubmit"]))
{
// Validate and sanitize the text message
// No empty input and no unnecessary empty lines, text messages like that are bad for the layout and user experience.
if(!empty($_POST["newMsgText"]))
{
if(($msgText = trim($_POST["newMsgText"])) != "" )
{
// Sanitize line breaks, replace 3 or more line breaks in a row with 2 line breaks,
// which is one free line in between the paragraphs.
if($msgText = preg_replace('/(\n|\r|\r\n){3,}/', "\r\n\r\n", $msgText))
{
// Message is ready to be saved now, whe have our own chat ID in $myChatID, we need to get the $partnerChatID
// $myChatID can be int(0), meaning the operating user has no open chat with the partner, but he could have deleted it and the the partner still has
// his old chatID with all the message history
// Remember: The whole chat history is saved two times, one copy for each user
$partnerChatID = 0;
$res = $mysqli->query("SELECT id FROM userchat WHERE chat_owner = $partnerUserID AND chat_partner = $myUserID LIMIT 1;");
if($res->num_rows === 1) {
$row = $res->fetch_row();
$partnerChatID = intval($row[0]);
}
// Now create a new chat, if it doesn't exist, for myself and my partner
$updateLastAction = FALSE;
if($myChatID == 0 OR $partnerChatID == 0) $updateLastAction = TRUE;
if($myChatID == 0) {
$mysqli->query("INSERT INTO userchat (chat_owner, chat_partner, last_action, created_at)
VALUES ($myUserID, $partnerUserID, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());");
$myChatID = intval($mysqli->insert_id);
}
if($partnerChatID == 0) {
$mysqli->query("INSERT INTO userchat (chat_owner, chat_partner, last_action, created_at)
VALUES ($partnerUserID, $myUserID, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());");
$partnerChatID = intval($mysqli->insert_id);
}
// Save messages now: One copy for each user
$msgText = $mysqli->real_escape_string($msgText);
$mysqli->query("INSERT INTO userchat_msg (chat_id, msg_owner, sender, recipient, msg_date, msg_status, msg_text)
VALUES ($myChatID, $myUserID, $myUserID, $partnerUserID, UNIX_TIMESTAMP(), 1, '$msgText'),
($partnerChatID, $partnerUserID, $myUserID, $partnerUserID, UNIX_TIMESTAMP(), 0, '$msgText');");
if($mysqli->affected_rows === 2) {
// Both messages saved successfully, if the userchat record was not created on point, you need to update the `last_action` column for the chats
if($updateLastAction) $mysqli->query("UPDATE userchat SET last_action = UNIX_TIMESTAMP() WHERE id = $myChatID OR id = $partnerChatID LIMIT 2;");
}
else {
// Saving error occured
}
}
}
}
}
Other queries:
That was basically it, opening chats and sending messages. Now this table structure we use allows us to do a bit more:
Count all my unread messages:
$query = "SELECT COUNT(id) FROM userchat_msg WHERE msg_owner = $myUserID AND recipient = $myUserID AND msg_status = 0;";
Deleting a chat:
Delete the chat messaages first, because of the foreign key constraint:
$mysqli->query("DELETE FROM userchat_msg WHERE chat_id = $myChatID AND msg_owner = $myUserID;");
$mysqli->query("DELETE FROM userchat WHERE id = $myChatID AND chat_owner = $myUserID LIMIT 1;");
Get my mailbox
We query all of our chats, with the name of the chat partners, the last action time stamp, and count unread messages in that chat with a sub query:
$res = $mysqli->query("SELECT cht.id, cht.last_action, usr.user_name,
(SELECT COUNT(msg.id) FROM userchat_msg msg WHERE msg.chat_id = cht.id
AND msg.recipient = $myUserID AND msg.msg_status = 0) AS numNewMsg
FROM userchat cht LEFT JOIN useraccount usr ON cht.chat_partner = usr.id
WHERE cht.chat_owner = $myUserID ORDER BY cht.last_action DESC LIMIT 100;");
Open a chat
Requesting a chat is simple:
$res = $mysqli->query("SELECT sender, msg_date, msg_text FROM userchat_msg
WHERE chat_id = $myChatID AND msg_owner = $myUserID ORDER BY msg_date DESC LIMIT 100;");
But after requesting it, make sure to mark all messages as seen after printing them to screen.
$mysqli->query("UPDATE userchat_msg SET msg_status = 1 WHERE chat_id = $myChatID AND msg_owner = $myUserID AND msg_status = 0 LIMIT 100;");
And that's it, let me know what you think.
Top comments (0)