There are 4 tables:
fandom - The name of the dialog group.
dialog - List of dialogs in the group.
dialog_users - Users in each group dialog.
chat - Messages in a specific dialog.

Task:
Display dialogs on the profile page in order (New above) + above new dialogs display those dialogs in which new messages have been added (Example as VK, Facebook).
Ready developments: There is a conclusion of dialogs. No sorting by posts.

Code of developments:

$sql_dialog = mysql_query(" SELECT `d`.*, (SELECT `f_title` FROM `fandom` WHERE `id` = `d`.fandom) AS `f_title` FROM `dialog_users` d WHERE `d`.users = '$_SESSION[id]' GROUP BY `d`.fandom ORDER BY `date` DESC"); while($dialog = mysql_fetch_assoc($sql_dialog)){ $array_dialog[] = $dialog['dialog']; } //Вывод сообщений по диалогам $sql_chat = mysql_query(" SELECT `dialog`,`text`,`status` FROM `chat` WHERE `dialog` IN (".implode(',',$array_dialog).") GROUP BY `dialog` ORDER BY `date` DESC"); while($chat = mysql_fetch_assoc($sql_chat)){ $array_chat[] = $chat['dialog']; } foreach(){ //Соединение обеих массивов }


It would be ideal to do all this in one request.
For this code, I don’t understand how to display the dialogues on new messages (the status field (1/0) in the chat table is responsible for this) and adding a condition, if status = 0, sort only by date .
If you have any idea how to finish this code, I would be immensely grateful for your help ...

  • Do you have to give the messages yourself? And how is it determined that the message is new? And in a single request, you can do anything at all ... - Mike
  • @Mike status (0/1). Yes, the text. But also proceeding from status, I think to add a change in the style of the dialogue. Yes, I know that you can do this, tried to combine, but have not yet made such conclusions, because of this I decided in 2 cycles. But in any case, there is room to grow. - Albert Ushakov
  • I understand you need to exit almost the first request from the sample code, but what would it also sort by the presence of messages with status = 1? - Mike
  • @Mike It turns out yes, but also by date, because if there is no new message by status, everything needs to be sorted exactly based on the old messages. That is, you need some condition, if status = 0, then sort only by date. Now I will update the post. - Albert Ushakov

1 answer 1

 SELECT `d`.*, (SELECT `f_title` FROM `fandom` WHERE `id` = `d`.fandom) AS `f_title`, exists(select 1 from `chat` c where c.dialog=d.dialog and status=1) AS `newMsg` FROM `dialog_users` d WHERE `d`.users = '$_SESSION[id]' GROUP BY `d`.fandom ORDER BY newMsg DESC, `date` DESC

Instead of exists (which checks only the presence of such records), you can choose for example the number of messages in the chat or the date of the last new message.

Or for example the text of the last message:

 SELECT `d`.*, (SELECT `f_title` FROM `fandom` WHERE `id` = `d`.fandom) AS `f_title`, (select text from `chat` c where c.dialog=d.dialog and status=1 order by c.date desc limit 1 ) AS `lastMsg` FROM `dialog_users` d WHERE `d`.users = '$_SESSION[id]' GROUP BY `d`.fandom ORDER BY lastMsg DESC, `date` DESC
  • And if the text of the message, I write instead of 1 text, it displays everything exactly the status value. - Albert Ushakov
  • @AlbertUshakov Well, in the same place, exists, it returns only 0 if there are no entries and 1 if there is, that inside the query returns it does not interest. make no exists subquery, just explicitly limit limit 1 so that one record returns - Mike
  • Can you show how? For sure that it was clear ... and close the question. - Albert Ushakov
  • @AlbertUshakov And the text from which message is with status 1 and which by date of the first or last - Mike
  • Well, although I seem to have removed exists, I added a limit with ORDER BY date DESC and displays it normally. Okay, I think right, thanks. - Albert Ushakov