Past question related to this Conclusion and subsequent sorting of records

Task:
Maximally combine sql into one or two queries. I tried it differently, but I can’t figure out how to do it better.

Working code:

//Вывод группу диалога $sql_fandom = mysql_query("SELECT `id`,`f_title` FROM `fandom` WHERE `id` = '".intval($im)."' LIMIT 1"); $fandom = mysql_fetch_assoc($sql_fandom); //Проверка на существования группы if(isset($fandom['id'])){ //Вывод пользователя по сессии и определение id диалога в группе $sql_dialog_users = mysql_query("SELECT `dialog` FROM `dialog_users` WHERE `users` = '$_SESSION[id]' AND `fandom` = '$fandom[id]' LIMIT 1"); $dialog_users = mysql_fetch_assoc($sql_dialog_users); //Вывод всех пользователей в этом диалоге в массив $sql_users = mysql_query("SELECT `users` FROM `dialog_users` WHERE `fandom` = '$fandom[id]' AND `dialog` = '$dialog_users[dialog]'"); while($users = mysql_fetch_assoc($sql_users)){ $users_array[] = $users['users']; } //Вывод всех данных пользователей $sql_users_profil = mysql_query("SELECT `id`,`name`,`avatar` FROM `users` WHERE `id` IN (".implode(',',$users_array).")"); while($users_profil = mysql_fetch_assoc($sql_users_profil)){ $users_profil_array[$users_profil['id']] = $users_profil; } //Вывод сообщений по id диалога $sql_chat = mysql_query("SELECT * FROM (SELECT * FROM `chat` WHERE `dialog` = '$dialog_users[dialog]' ORDER BY `date` DESC LIMIT 50) A ORDER BY `date`"); print<<<HERE <div class="chat-vivod" id="messages"> HERE; if(mysql_num_rows($sql_chat) == 0){ $echo = '<center>Диалог отсутствует</center><br>'; } while($chat = mysql_fetch_array($sql_chat)){ $chat_users = array( 'name' => $users_profil_array[$chat['users']]['name'], 'avatar' => avatar ($users_profil_array[$chat['users']]['avatar']), 'text' => to_link ($chat['text']), 'date' => date_smart($chat['date']) ); print<<<HERE <label class='chat-box' id='chat-box-$chat[id]'> <input type='checkbox' class='checkbox chat-box-checkbox' id='chat-box-checkbox-$chat[id]' onChange='checked_chat($chat[id]);' name='chat_text' /> <div class='chat-box-avatar image-min' style='background-image: url($chat_users[avatar]);'></div> <div class='chat-box-content'> <div>$chat_users[name]<small>$chat_users[date]</small></div> <div>$chat_users[text]</div> </div> </label> </div> HERE; }
  • Here in this query, SELECT dialog `FROM dialog_users WHERE users = '$ _SESSION [id]' AND fandom = '$ fandom [id]' LIMIT 1` which 1 record is selected, no sorting is set here - hence the database returns the first matching record. In the database, there is always one for the given selection conditions, or there may be several of them and you really want to get the first one? - Mike
  • @Mike This request is for receiving the dialogue id, since there can be several dialogs in the group and the one to which the participant is subscribed needs to be displayed. That is, the output must be one id according to the specified criteria. - Albert Ushakov
  • and in the database, according to the specified criteria, there can be more than one record (for what is limit 1 here)? - Mike

1 answer 1

I still did not understand why the list of users was obtained from dialog_users, I considered that the necessary users can be easily found immediately in users by id. I suggest that you leave the first request as it is, so that you can quickly make sure that the group exists and get f_title (which you really do not use after). And everything else should be something like this:

 SELECT * FROM ( SELECT c.*, u.name as user_name, u.avatar FROM `chat` c, `users` u WHERE u.id=c.users AND c.dialog IN(SELECT `dialog` FROM dialog_users WHERE `users` = '$_SESSION[id]' AND `fandom` = intval($im) LIMIT 1 ) ORDER BY `date` DESC LIMIT 50 ) A ORDER BY `date`
  • dialog_users is a subscription of users. I will describe the structure and system. The user on the page sees the groups, presses the subscription and the script itself defines it to the dialogue in which there are fewer people. When you go to the chat page, the id remains a group but based on the user's subscription, a dialog is displayed. The output of the group is basically not needed here, I added it to just display f_title, so that the user can see which group to be in. - Albert Ushakov
  • @AlbertUshakov You look at the request, I am 90% sure that it gives out now the same data as your old script. Table dialog_users in this query may be needed only if the chat table with this dialog may contain messages from users for some reason not in the table dialog_users with the same dialog and you don’t want to give such messages - Mike
  • And yes, you are right. Only in the IN place probably =, since one digit on the output. Works. Thank. - Albert Ushakov
  • @AlbertUshakov I'm afraid to write = because if the subquery does not return a single row, in many databases this will lead to a runtime error, unlike IN. As in MySQL, I don’t know, maybe it still doesn’t matter - Mike
  • with IN does not work. Well, in general, if there is a void on the output, I put the handler. But it is almost impossible in this system, since all the dialogues are checked in the admin panel. - Albert Ushakov