Group Messaging in php mysql

Question

I have created a table in MySQL with following columns:

id - from_id - to_id - datetime - message

My PHP code is :

$user = $_SESSION['user'];

$sql= "SELECT * FROM messages where to_id=".$user;

$result = $conn->query($sql);

echo "<table><tr><td>FROM</td><td>MESSAGE</td></tr>";

while($row=mysqli_fetch_assoc($result)){
    $from = $row['from_id'];
    $message = $row['message'];

    if(count($from) >1){
        echo "More than 1 message from ".$from;
    }

    echo "<tr><td>".$from."</td><td>".$message."</td></tr>";

}
echo "</table>";

I can get all the messages sent to the user. But Im also getting messages sent FROM the same user in different lines. So for example, if user2 has sent user1 4 messages, all the 4 messages are listed in this list. I want only one row for the user2 and when i click on the row, i will see all the messages from that user. But on this page i want to see only 1 row with the latest message.

How can I achieve this?

**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jul 29 '16 at 05:59

score 1 · Answer 1 · answered Jul 29 '16 at 05:49

1

SELECT * FROM (
  SELECT * FROM messages
  ORDER BY datetime DESC
) as temp_messages
GROUP BY from_id

Inner query orders the records by datetime to make last record first.
Outer query groups records and gives you first record, which is latest by respective user.

This is will give you single row with latest message from respective user. Upon clicking on row, you want to fetch all records from that user. This has to be implemented separately. Not considered within this scope.

answered Jul 29 '16 at 05:49

Bhavik Shah

2,300
1
17
32

How does this solve the actual problem of marking from_id which have more than one message? Also this is invalid sql, trusting on random behaviour of mysql. – Philipp Jul 29 '16 at 06:00
@Philipp: What random behaviour are you referring to? Can you please elaborate? I envision that this solves the issue in question completely. I would surely like to hear your comments. – Bhavik Shah Jul 29 '16 at 06:01
You are not specifying which `message` to fetch if the same from_id is there multiple times, you leave it to mysql to decide. Then you are in no way referring to `echo "More than 1 message from ".$from;` which was the actual problem – Philipp Jul 29 '16 at 06:03
@Philipp: I request you to read the explanation I have added below query. Inner query sorts the table in reverse order by time. This means, latest record will always be the first for each user. So, I believe, there is no random behaviour involved in this. – Bhavik Shah Jul 29 '16 at 06:06
please read the mysql documentation: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html The paragraph starting with "If ONLY_FULL_GROUP_BY is disabled, [...]" – Philipp Jul 29 '16 at 06:09
Btw, if you would use group_concat on the messages (with some separator), things would be totaly fine. The count would be available by splitting the concated messages and then counting them. So you would provide a sql based solution and mine would be php based. – Philipp Jul 29 '16 at 06:17
@Philipp: Thanks a lot for the link and your efforts. I think, you should notice that the description is not relevant with the implementation I have demonstrated. I kindly request you to create one sample/dummy database and replicate the records like the one in this use case and try the query. Check the results. – Bhavik Shah Jul 29 '16 at 06:18
Well, we won't get to the same opinion on this point, so let's end this talk. But please use `SELECT from_id, group_concat(messages SEPARATOR '|') FROM (`[...] so the op gets the data he needs to display them according to his plan. If he has that data he can simply explode the messages string and has everything he needs. – Philipp Jul 29 '16 at 06:28
@BhavikShah .. Thanks you. Your query returns the very first entry even after Orderby Datetime DESC. I will use this if i dont want to show the last message. – Somename Jul 30 '16 at 06:06
@Philipp .. your suggestion worked. I learnt about Group_Concat and the solution just works. THanks a ton guys for helping me. – Somename Jul 30 '16 at 06:07

score -1 · Answer 2 · edited May 23 '17 at 12:32

First: How can I prevent SQL injection in PHP? Your script is dangerous, please use prepared statements for accessing the database with userdata.

In php you could do it like this, might be good if you are planning some other things with the messages.

$messages = array();
while($row = mysqli_fetch_assoc($result)){
    $messages[$row['from_id']][] = $row['message'];
}
foreach ($messages as $key => $messagesByFrom) {
    if(count($messagesByFrom) >1){
        echo "<tr><td>More than 1 message from " . $key. "</td><td></td></tr>";
    } else {
        echo "<tr><td>".$key ."</td><td>".$messagesByFrom[0]."</td></tr>";
    }
}

To test it:

$messages = array(
    '1' => array('hello', 'world'),
    '2' => array('whaddup'),
);
echo '<table>';
foreach ($messages as $key => $messagesByFrom) {
    if(count($messagesByFrom) >1){
        echo "<tr><td>More than 1 message from " . $key . "</td><td></td></tr>";
    } else {
        echo "<tr><td>".$key."</td><td>".$messagesByFrom[0]."</td></tr>";
    }
}
echo '</table>';

Now you should add the $from_id as attribute to the <tr> of dataset with multiple messages, then you can set an eventlistener on the click event of that row, sending the id to your backend and returning the messages.

Group Messaging in php mysql

2 Answers2