I am making a very simple private (just between two people) chat system 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)
The id is auto increment, the sender and recipient are usernames saved in another table with user information, the text is the text sent and the time is the time and date the message was sent.
In the inbox, my idea was to give a mysql_query and select all messages from this table where the recipient was the same as the logged in user, group by sender and sort by date and time desc. It looks like this:
$mensagensqr = mysql_query("SELECT * FROM mensagens WHERE destinatario = '$usuario' GROUP BY remetente ORDER BY horario desc");
Being $ user, the user's username is logged in.
To show on the screen, for example, I make one:
while($mensagensfa = mysql_fetch_assoc($mensagensqr)){echo "certo usuario mandou tantas mensagens";}
So far, everything is fine, but I want to show the messages sent by the logged in user as well. Look, 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 display the messages sent by the user, it will continue to group by the sender.
Does anyone have any suggestions on how I can resolve this?
PS: about mysql I know it's obsolete, the problem is the query .