Jump to content
mongoose00318

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

Recommended Posts

Posted (edited)

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 by mongoose00318

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Barand

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.