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

[mongoose00318]

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;
}
?>

 

Edited April 7, 2020 by mongoose00318

[requinix]

If you want each message then you need to query for each message. Not each message per recipient.

The simplest solution is a query in a loop. "A query in a loop" is almost always wrong, and is always the least efficient solution, but it is quick so you can optimize later.

Query for the messages. Set up a prepared statement for the recipients. For each message, run the recipient query and deal with it as you see fit.

[mongoose00318]

I changed my SQL statement to this:

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,
					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 = $order_id
				GROUP BY
					msg.chat_message_id
				ORDER BY
					msg.timestamp
			 ";

	//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 ) {
		$to_user_names = '';

		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_full_name( $row[ 'from_user_id' ], $pdo ) . '</b>';
		}

		$to_user_names .= '<b class="text-success">';
		foreach ( explode( ',', $row[ 'to_user_ids' ] ) as $t ) {
			//message directed to specifc users
			if ( $t != '0' ) {
				$to_user_names .= get_user_full_name( $t, $pdo ) . ' & ';
			//message directed to everyone
			} else {
				$to_user_names .= 'Everyone & ';
			}
		}

		//remove trailing whitespace and ampersand
		$to_user_names = rtrim( $to_user_names, " & " );

		$to_user_names .= '</b>';

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

	}

	$output .= '</ul>';

	//output HTML to browser
	return $output;
}

Using Group By and Group_Concat helped me accomplish it without having to change my code much. I thought I was using prepared statements...I recently learned I'm not. Even though I'm using PDO prepare; I was told I'm doing it wrong.

[Barand]

The purpose of a prepare() is to separate the query from the input data..

You therefore  need

WHERE
		msg.order_id = ?

then provide the data in an array of parameters when you execute

$statement->execute( [$orderid] );

 

Edited April 8, 2020 by Barand