Two variables and a group by? PHP and MYSQL

Asked

Viewed 1,494 times

1

I’m making a very simple private chat system (just between two people) with PHP and Mysql, Jquery and HTML/CSS. In the database, the table has the following structure:

id (int)
remetente (varchar)
destinatario (varchar)
texto (text)
horario (datetime)

id is auto increment, sender and recipient are usernames saved in another table with user information, text is text sent and time is the time and date the message was sent.

In the inbox, my idea was to give a mysql_query and select all the messages of this table where the recipient was equal to the logged-in user, group by sender and sort by date and time desc. It is like this:

$mensagensqr = mysql_query("SELECT * FROM mensagens WHERE destinatario = '$usuario' GROUP BY remetente ORDER BY horario desc");

$usuario being the username of the logged in user.

To show on the screen, for example, I make a:

while($mensagensfa = mysql_fetch_assoc($mensagensqr)){echo "certo usuario mandou tantas mensagens";}

Until then, everything is going well, but I want to show the messages sent by the logged in user as well. If you look at the first query I mentioned, it only shows messages where the recipient = '$user'. However, if I put "OR sender = '$user'" in the query to show the messages sent by the user, it will continue grouping by sender.

Does anyone have any suggestions on how I handle this?

PS: on the mysql I know it’s obsolete, the problem is query.

php mysql group-by chat
  • 1

    It would be good to give an example of what you want on the way out. What are you using GROUP for, first of all? Will only show the last one? And in the case of OR makes it easier to write WHERE $usuario IN ( remetente, destinatario) if you want to find messages from both of you. As for the problem in using the OR, how it came out, and how the result was to have come out?

  • I’m not sure I understand all the questions. This code is not the chat itself, imagine that facebook page or the Whatsapp homepage, where all messages are. There it groups by person and only shows the last message, the time, etc, with a link that opens the rest of the messages. So I used GROUP. As for the problem, as I am grouping by the sender, when the logged-in user is the sender, instead of appearing the recipient’s name, the sender’s name appears (because it is grouped by the sender). I know, you’re confused, I’ll add more details to the question. Thanks in advance.

  • Ai the problem is not the grouping, in PHP you need for a echo $remetente = $usuario ? $destinatario : $ remetente; - nonsense wanting to mess with this in SQL (and anyway, the logic is the same. The sender, no matter how you do, will be the one who sent. To show one or the other, you need this logic that I mentioned. If in SQL, it is SELECT IF( sender = $usuario_php, sender ) AS name

  • I think I got it and solved it in parts. Now the groups of duplicated messages are appearing. I believe it is because the same user is in the sender on one line and the recipient on another line, in the database, and then when the while runs, it ends up showing twice the same user. I have to find a way if the user has already been shown, not to show again. Anyway, I really need to sleep now. Thank you very much, it was a breakthrough! If there’s any way I can thank you, give you a like, favorite, or whatever, show you helped me, just say, please.

  • Rest there, then if you give us an improved question or revise it for your current problem (as there is no answer, you can put your current code in it later) and then I or another user can post an "official" answer later.

  • I did not understand what you said with "group by sender and order by time". Since you’re making an appointment with the "*" joker, I wouldn’t expect to see one group by in it. I would also expect to see an aggregator in some selected column when doing the group by, but I don’t see any. My ideas where your phrase makes sense imply the existence of more than two people in the chat as a whole (even if all communication is done on channels unicast)

Show 1 more comment

1 answer

1

Hello, one way to solve is by using Mysql UNION, where you can perform two queries, as if you were bringing data from two tables.

Try this way:

$mensagensqr = mysql_query("SELECT * FROM mensagens
WHERE destinatario = '$usuario'
GROUP BY remetente ORDER BY horario DESC
UNION
SELECT * FROM mensagens
WHERE remetente = '$usuario'
GROUP BY remetente ORDER BY horario desc");

  • Note: Although Mysql does not complain, GROUP BY must be accompanied by the fields explicitly in SELECT, e.g.: SELECT sender FROM messages ... GROUP BY sender, otherwise Voce may have unexpected results in your select and waste a lot of time cracking your head to solve.

Browser other questions tagged php mysql group-by chat

You are not signed in. Login or sign up in order to post.