mysql select 和 where 多个表(非常棘手) [英] mysql select and where over several tables (very tricky)

问题描述

我有 4 个表(如下所列)并且需要:

I have 4 Tables (listed bellow) and need:

1. 在没有被禁止的用户的情况下获取来自 Room 3 的最后 10 条聊天记录
2. 显示 fromuserid 的昵称
3. 隐藏用户 $userid 不喜欢看到隐藏"表

表 1聊天"

ID(autoinc)   fromuserid   roomid   text
 1               23          3      bla
 2               14          1      bla
 3               11          3      bal

表2用户"/shorted/

Table 2 "user" /shorted/

ID(autoinc)   nickname   banned
1             chris        0
2             paul         1     // 1 = banned

表3隐藏"

ID(autoinc)   orguser    hideuser
1             12          3
2             33          12

现在我用 PHP Routine 解决了这个问题，但我必须检查每个结果并总是创建一个新的查询，这需要太长时间；

Right now i solved it with PHP Routine, but I have to go through EACH result and make always a new query, that needs too long;

  $userid = 1; // actual user

  // List all chats and show userid as nickname
  $sql_com = "SELECT user.id, user.nickname, chats.text, chats.id ".
        " FROM chats, user".
        " WHERE ".
        " chats.fromuserid = user.id ".
        " AND chats.roomid = 3 ".
        " AND user.banned != 1 ".
        " ORDER BY chats.id DESC";  
  $result = mysql_query ($sql_com);       

  $count = 0;
  while ($row = mysql_fetch_array($result, MYSQL_NUM)) 
   {
       $dontshow = false;

       // Filter : dont show users $userid dont like to see (table "hide")
       $sql_com2 = "SELECT id from hide WHERE ( (orguser = ".$userid.") AND (hideuser = ".$row[0].") ) ";

       if ($result2 = mysql_query ($sql_com2)) 
       {
          if (mysql_num_rows($result2) > 0) $dontshow = true;
       }      


       // Output     
       if ($dontshow == false)
       {
            $count++;
            echo "Nickname: ".$row[1]." Text: ".$row[2];
       }

       if ($count > 10) break;
}

顺便说一句.我已经做了一些改进，所以实际问题可能不适合所有答案(感谢您的帮助)

Btw. I made already some improvments, so the actual question may not fit with all answers (thanks for your help till now)

最后它现在即将集成过滤器不显示表中列出的人为我的实际用户隐藏".

Finaly its now just about to integrate the filter "dont show people listed in table "hide" for my actual user".

推荐答案

我认为您需要一些与这些大致相符的东西.我做的与你的问题略有不同.而不是获得前 10 名然后删除记录.它获取不会隐藏的前 10 条记录.

I think you need something along these general lines. I've done it slightly different from your question. Instead of getting the top 10 then removing records. It gets the top 10 records which would not be hidden.

SELECT c.ID, c.fromuserid, c.roomid, c.text, u.nickname
FROM chats c
JOIN user u ON c.fromuserid = u.id
where c.roomid = 3 AND user.banned = 0
AND NOT EXISTS(
              SELECT * FROM hide h 
              WHERE h.hideuser = c.fromuserid
              AND orguser = $userid) 
ORDER BY c.ID DESC
LIMIT 0,10

这篇关于mysql select 和 where 多个表(非常棘手)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！