Messaging/Chat System - Directing messages to more than one recipient

General PHP Help
#1

I have built a messaging/chat system and I needed to extend it’s functionality. When a user posts a message, they have the option to direct the message to one or more users. Here is the structure of the two tables I made.

chat_message    CREATE TABLE `chat_message` (
 `chat_message_id` int(11) NOT NULL AUTO_INCREMENT,
 `order_id` int(11) DEFAULT NULL,
 `chat_message` text NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time message was sent',
 PRIMARY KEY (`chat_message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=latin1

chat_to_users   CREATE TABLE `chat_to_users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `message_id` int(11) NOT NULL,
 `to_user_id` int(11) NOT NULL DEFAULT 0,
 `from_user_id` int(11) NOT NULL,
 `read_timestamp` timestamp NULL DEFAULT NULL COMMENT 'time user read message',
 `status` int(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=56 DEFAULT CHARSET=latin1

I’m trying to output the messages now: https://imgur.com/qaAZhey

That is really 3 messages but it’s showing 5 in total because 2 of the 3 were directed to more than one user.

I want it to show something more like “From: from_user - To: to_user1, to_user2, etc.” Just adding the additional usernames if the message was directed to more than one person.

Here is my PHP:

<?php
function fetch_order_messaging_history( $order_id, $pdo ) {

    //construct SQL query
    $query = "
             SELECT 
             msg.chat_message_id,
             msg.order_id,
             msg.chat_message,
             msg.timestamp AS sent_timestamp,
             usr.id AS chat_to_users_id,
             usr.message_id,
             usr.to_user_id,
             usr.from_user_id,
             usr.read_timestamp AS read_timestamp,
             usr.status
             FROM chat_message AS msg 
             LEFT JOIN chat_to_users AS usr 
             ON msg.chat_message_id = usr.message_id 
             WHERE msg.order_id = $order_id 
             ORDER BY msg.timestamp DESC
             ";

    //prep, execute, & fetch SQL results
    $statement = $pdo->prepare ( $query );
    $statement->execute();
    $result = $statement->fetchAll();

    //construct HTML for browser output
    $output = '<ul class="list-unstyled">';

    foreach ( $result as $row ) {
        $user_name = '';

        $track_msg_id = $row['chat_message_id'];

        if ( $row[ 'from_user_id' ] == $_SESSION['user_id' ] ) {
            $from_user_name = '<b class="text-success">You</b>';
        } else {
            $from_user_name = '<b class="text-success">' . get_user_name( $row[ 'from_user_id' ], $pdo ) . '</b>';
        }

        $output .=  '
                    <li style="border-bottom:1px dotted #ccc">
                        <p>' . 'From: ' . $from_user_name . ' To: ' . get_user_name( $row[ 'to_user_id' ], $pdo ) . ' - '. $row[ 'chat_message' ] . '
                            <div align="right">
                            - <small><em>' . $row[ 'sent_timestamp' ] . '</em></small>
                            </div>
                        </p>
                    </li>
                    ';

    }

    $output .= '</ul>';

    //output HTML to browser
    return $output;
}
?>
#2

You really need to use something like sockets.io and Node.js (maybe even React). I have been slowly setting up a NodeJS chat system myself, but even I have ways to go.

No offense, but I think you’re just spinning your wheels with these scripts. They are also very insecure as the don’t use prepared statements.

#3

Isn’t using PDO->prepare() a prepared statement? But, I am interested in using something like node, etc but I need more experience with JS. I can write it; just struggle though. I managed to get it how I need it by modifying my SQL as so:

	//construct SQL query
	$query = "
				SELECT
					msg.chat_message_id,
					msg.order_id,
					msg.chat_message,
					msg.timestamp AS sent_timestamp,
					usr.id AS chat_to_users_id,
					usr.message_id,
					GROUP_CONCAT(usr.to_user_id SEPARATOR ',') AS to_user_ids,
					usr.from_user_id
				FROM
					chat_message AS msg
				LEFT JOIN chat_to_users AS usr
				ON
					msg.chat_message_id = usr.message_id
				WHERE
					msg.order_id = 75334
				GROUP BY
					msg.chat_message_id
				ORDER BY
					msg.timestamp
			 ";

	//prep, execute, & fetch SQL results
	$statement = $pdo->prepare( $query );
	$statement->execute();
	$result = $statement->fetchAll();
#4

It is, but you aren’t using it as a prepared statement, you are using it as a mysql_ query because you are just putting the variable in rather than the placeholder that it should have.

So, there are several ways to do this. The least painful, is going to mean inserting multiple messages into the table. So for example:

Users A, B, and C.

A sends a message to the group. You now need to insert a message in your chat_to_users table twice, once for A to B and another for A to C. Understand?

#5

Sort of. Can you provide an example? I guess you’re saying I’d have run a query in a loop for each person the message is directed to? Also, I don’t know what you mean by using the placeholder.

Thank you for pointing this out. I was under the impression I was using prepared statements…

#6 
$sql = "SELECT id, lastName, dateAdded FROM User WHERE firstName = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_POST_['first_name']);

$resp = $stmt->fetchAll();

Prepared statement example.

That’s exactly it. Because of how the architecture currently is. it will be a major rework to redesign. whether that means code changes or a technology change. So if you loop through to each person in the chat, everyone gets the message. However, you are greatly bloating the tables do to this and duplicating data O(N+) which is bad. But it is a way to solve the immediate problem until you get to a point where the tech debt warrants the overhaul.

#7

Tables

  • topic
  • subscriber
  • message
  • user

Topic holds an id that links to the message.
Subscriber holds the user id, topic id, active status.
Message has the user id, topic id, message, and time stamp.
User holds typical user properties.

Your query to get the messages is based on topics, join on message where the user is in the subscriber table.

Preliminary thoughts on how to handle a data model redesign.

Sponsor our Newsletter | Privacy Policy | Terms of Service