1
I have a table "conversations" in the database where I have
- transmitter
- receiver
- message
- id
another "users" table that is not very relevant, however, I want to show in an html table the messages that whoever is logged in sent or received, but only the last message of each conversation, for example I have a conversation between the user X and the user Y ,the Y is logged in and he also has a conversation with the user Z, Then on the screen it would show the last message in which X had with Y and the last message that Y had with Z.
I made the following code
<?php
$user = $_SESSION['usr'];
$sql = "SELECT * FROM conversas WHERE transmissor LIKE '$user' OR receptor LIKE '$user'";
$result = mysqli_query($connect, $sql);
if (mysqli_num_rows($result) > 0) {
echo "<table class='table'><tbody>";
while ($row = mysqli_fetch_array($result)) {
array_push($receptores, $row['receptor']);
array_push($transmissores, $row['transmissor']);
}
$transmissores = array_unique($transmissores);
if ($receptores) {
$receptores = array_unique($receptores);
foreach ($receptores as $key => $value) {
if ($key <= count($transmissores) && $transmissores[$key] != $user)
$transmiter = $transmissores[$key];
else
$transmiter = $user;
$sql = "SELECT * FROM conversas WHERE transmissor LIKE '$user' AND receptor LIKE '$receptores[$key]' OR receptor LIKE '$user' AND transmissor LIKE 'transmiter' ORDER BY id DESC LIMIT 1";
$res = mysqli_query($connect, $sql);
if ($l = mysqli_num_rows($res) > 0) {
while ($row = mysqli_fetch_array($res)) {
$msg = $row['mensagem'];
$receptor = $row['receptor'];
$msg = base64_decode($msg);
$transmissor = $row['transmissor'];
$msg = trim($msg);
$value = trim($value);
echo "
<tr class='seleciona'>";
if ($receptor == $user)
echo "<td scope='row' class='cnt'>$transmissor:</td>";
else
echo "<td scope='row' class='cnt'>$value:</td>";
echo "<td class='msg'>
<div class='dropdown'>
<button class='but dropdown-toggle' type='button' id='dropdownMenuButton' data-toggle='dropdown' aria-haspopup='true' aria-expanded='false'>
$msg
</button>
<div class='dropdown-menu' aria-labelledby='dropdownMenuButton'>
<a class='dropdown-item' id='apagar' href='#' value='value'>Apagar conversa</a>
</div>
</div>
</td>
</tr>
";
}
}
}
}
echo " </tbody></table>";
}
But in that way, X sent a "hi" to Y and Y answered a "blz" to X ,what happens is that with this code I did shows both the message that X sent it to Y as to the message that Y sent it to X,as I would to show only the last message of this conversation,in case I would have to show only the "blz" in which Y responded to X ?
as receiver and transmitter can be different between messages becomes impossible to group, can not know when the records represent a conversation, unless create a column with a conversation identifier, ai can group by this identify taking the latest record
– Ricardo Pontual
Based on the following, the user who is logged in will always be either the transmitter,or receiver, so if I have multiple messages between the one who is logged in and the other person ,this would be a conversation, X and Y ,the messages between X and Y regardless of who sent or received,.
– mecatrônica com reciclagem
from X to Y ok (thinking that X is the one who is logged in), da para agrupar o pegar a mais recente, but from Y to X, how will you know if it is an answer from the same conversation, or was it a message that started with Y? This is confusing... if you don’t have an identifier for the conversations, will difficult to make a query that covers these scenarios
– Ricardo Pontual
From there it can be checked if who is logged in in the case X is the receiver or transmitter with an exception ,if X is receiver,to group with Y,it is necessary that Y is the transmitter ,and vice versa
– mecatrônica com reciclagem
For this I did the following, in the first sql, I took all the situations where the logged-in user is transmitter or receiver, in case to receive only the messages that he is participating, until then ok. With the result I store all receivers and all transmitters, and use the array_unique to pick up only 1 of each receiver and each of the transmitters
– mecatrônica com reciclagem
with this modeling I don’t see how to do this with a single query, you can’t group xy and yx, you need something that relates the two, maybe group each thing into a temporary table and apply a rule that understands that xy and yx are the same conversation
– Ricardo Pontual
You would have some example for me to base?
– mecatrônica com reciclagem
Get it ,I’ll put as an answer
– mecatrônica com reciclagem