php - 从多个表中获取最新评论

Question

我无法从两个不同的表中获取 3 条最新评论。

这是我的代码，它与一个内部连接完美结合:

$query = mysql_query("SELECT COUNT(c.topic_id) AS ctid, COUNT(c.deck_id) AS dtid, f.id AS forumid, f.class AS forumclass, f.name AS forumname, f.url AS forumurl,
            c.id AS commentid, c.user_id AS commentuser, c.user_name AS commentusername, c.date AS commentdate,
            c.topic_id AS topicid, c.deck_id AS deckid
            FROM ".$prefix."comment AS c
            INNER JOIN ".$prefix."forum AS f
            ON c.topic_id = f.id GROUP BY f.id
            ORDER BY commentdate DESC LIMIT 3") or die(mysql_error());

这很好用，它显示了论坛表中的 3 个最新评论，但是我在 decks 表中也有评论，但是当我向查询添加另一个 JOIN 时，它就不再起作用了。

$query = mysql_query("SELECT COUNT(c.topic_id) AS ctid, COUNT(c.deck_id) AS dtid, f.id AS forumid, f.class AS forumclass, f.name AS forumname, f.url AS forumurl,
            c.id AS commentid, c.user_id AS commentuser, c.user_name AS commentusername, c.date AS commentdate,
            c.topic_id AS topicid, c.deck_id AS deckid, , d.id, d.url AS deckurl, d.name AS deckname
            FROM ".$prefix."comment AS c
            INNER JOIN ".$prefix."forum AS f
            ON c.topic_id = f.id
            INNER JOIN ".$prefix."decks AS d
            ON c.deck_id = d.id
             GROUP BY f.id ORDER BY commentdate DESC LIMIT 3") or die(mysql_error());

有一个 comment 表，在 comment 表中有一个 topic_id 列，它等于 forum 表的 id 列，还有一个 deck_id 列，它等于 decks 表的 id 列。

显然 GROUP BY f.id 不适用于两个内部连接。

在这个查询之后我有 while ($top = mysql_fetch_assoc($query)){ ... 然后 if ($top['deckid']==0) then print the topicid informations else print the deckid informations.

编辑:评论表(只有现在对我们重要的):id, topic_id, deck_id

topic_id = 论坛表的 id

deck_id = 牌 table 的 id

显然每个论坛主题都没有评论。

例如:论坛 ID 5 有 4 条评论，然后评论表例如:ID 1,2,3,4 有 topic_id 4,4,4,4 和 deck_id 0,0,0,0。

如果没有评论，那么评论表中就没有任何内容。所以论坛ID 6有0条评论，那么评论表里什么都没有。

如果牌组 ID 12 有 2 个评论，则评论表例如:5,6 有 deck_id 12,12 和 topic_id 0,0。

论坛表:编号

甲板表:编号

EDIT2:解决方案(不太好，但有效):

//count how many comments the latest 3 deck topic has
        $new_comment_query = mysql_query("SELECT COUNT(c.deck_id) AS dtid, c.id, c.deck_id, c.date, d.id
        FROM ".$prefix."comment AS c LEFT JOIN ".$prefix."decks AS d ON d.id = c.deck_id GROUP BY d.id ORDER BY date DESC LIMIT 3");
        $new_one = mysql_fetch_array($new_comment_query);

        //count how many comments the latest 3 forum topic has
        $new_forum_query = mysql_query("SELECT COUNT(c.topic_id) AS ctid, c.id, c.topic_id, c.date, f.id
        FROM ".$prefix."comment AS c LEFT JOIN ".$prefix."forum AS f ON f.id = c.topic_id GROUP BY f.id ORDER BY date DESC LIMIT 3");
        $newer_one = mysql_fetch_array($new_forum_query);

        //get all the comments
        $comment_query = mysql_query("SELECT id, topic_id, deck_id, date FROM ".$prefix."comment ORDER BY date DESC LIMIT 3");
        while ($comment = mysql_fetch_assoc($comment_query))
        {
            if($comment['topic_id']==0)
            {
                $deck_query = mysql_query("SELECT * FROM ".$prefix."decks WHERE id=".$comment['deck_id']);
                while ($deck_comments = mysql_fetch_assoc($deck_query))
                {
                    //print all the things!
                }
            }
            elseif($comment['deck_id']==0)
            {
                $forum_query = mysql_query("SELECT * FROM ".$prefix."forum WHERE id=".$comment['topic_id']);
                while ($forum_comments = mysql_fetch_assoc($forum_query))
                {
                    //print all the things!
                }
            }
        }

Answer 1

尝试运行这个查询:

SELECT * FROM
(
SELECT COUNT(c.topic_id) AS ctid, COUNT(c.deck_id) AS dtid,
    f.id AS forumid, f.class AS forumclass, f.name AS forumname,
    f.url AS forumurl,
    c.id AS commentid, c.user_id AS commentuser, c.user_name AS commentusername,
    c.date AS commentdate, c.topic_id AS topicid, c.deck_id AS deckid,
    d.id, d.url AS deckurl, d.name AS deckname
FROM ".$prefix."comment AS c
INNER JOIN ".$prefix."forum AS f
ON c.topic_id = f.id
INNER JOIN ".$prefix."decks AS d
        ON c.deck_id = d.id
ORDER BY commentdate DESC
) t1
GROUP BY t1.forumid
LIMIT 3

我首先执行您的复杂JOIN 没有 GROUP BY 导致了问题。然后，我SELECT 该临时表中的所有内容，按forumid 分组。

老实说，我很惊讶您的服务器没有拒绝您的第一个查询，更不用说第二个查询了，但希望我的回答能帮到您。