php - 只显示来自我和 friend 的迷你博客

我正在尝试进行一个MySQL查询，该查询将显示来自“我”和“我的朋友”的微型博客。就像Twitter。

这是我的桌子；

--members
m_id INT
m_user VARCHAR
(rest are other member info)

--shouts
s_id INT
s_userid INT
s_content TEXT
s_posted DATETIME

--friends
f_userid INT
f_friendid INT
f_status INT // 0=pending, 1=approved
f_create DATETIME
f_update DATETIME

注意：1友谊有2项

例如：f_userid = ME，f_friendid = MYFRIEND，反之亦然

f_userid = MYFRIEND，f_friendid = ME

有效但不使用friendlist表的代码是：

SELECT * FROM shouts
JOIN members
ON members.m_id = shouts.s_uid
ORDER BY s_posted DESC

无法添加好友表信息。

我相信我只是缺少一行代码或两行代码...

我已经尝试过了，但是失败了。

SELECT * FROM shouts
JOIN members
ON members.m_id = shouts.s_uid
JOIN friends
ON friends.f_friendid = members.m_id
WHERE m_id = 1 //my id
AND f_userid = 1 //my id
AND f_status = 1
ORDER BY s_posted DESC

最佳答案

// GET LIST OF FRIEND ID's FIRST
    $sfflsql = mysql_query("SELECT f_friendid FROM friends
                        WHERE f_userid = ".intval($_SESSION['lalala']['m_id'])."
                        AND f_status = 1
                        ORDER BY f_friendid");
    // CONVERT ARRAY INTO STRING EX: 1,2,3,4
    $sfflrs = "";
    while($s1 = mysql_fetch_assoc($sfflsql))
    {
        $sfflrs .= implode(",",$s1).",";
    }
    $sfflrs = substr($sfflrs,0,-1); // REMOVES LAST COMMA ,

    $feedsql = mysql_query("SELECT * FROM shouts
                           INNER JOIN dbbbl_members
                           ON members.m_id = shouts.s_uid
                WHERE m_id IN (".$_SESSION['lalala']['m_id'].",".$sfflrs.")
                           ORDER BY s_posted DESC
                           LIMIT 15");

它可以工作，但不确定是否正确的方法。

关于php - 只显示来自我和 friend 的迷你博客，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/3690469/