Get latest comments from multiple tables

Question

I'm having trouble with getting the 3 latest comments from two different tables.

Here is my code, which works perfect with one inner 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
            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());

This works fine it shows the 3 latest comments from the forum table, however I have comments in the decks table too, but when I add another JOIN to the query it just won't work anymore.

$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());

There's a comment table and in the comment table there is a topic_id column, which is equal to the forum table's id column and there's also a deck_id column which is equal to the decks table's id column.

Obviously that GROUP BY f.id isn't good with the two inner joins.

After this query I have while ($top = mysql_fetch_assoc($query)){ ... and then if ($top['deckid']==0) then print the topicid informations else print the deckid informations.

EDIT: Comment table (only what is important to us now): id, topic_id, deck_id

topic_id = forum table's id

deck_id = deck table's id

Obviously there is no comment to every forum topic.

E.g.: Forum ID 5 has 4 comments, then comment table e.g.: ID 1,2,3,4 has topic_id 4,4,4,4 and deck_id 0,0,0,0.

If there's no comment then there's nothing in the comment table. So forum ID 6 has 0 comments, then there is nothing in the comments table.

If deck ID 12 has 2 comments, then comment table e.g.: 5,6 has deck_id 12,12 and topic_id 0,0.

Forum table: id

Deck table: id

EDIT2: Solution (not too nice, but it works):

//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!
                }
            }
        }

Phoenixy · Accepted Answer

//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!
            }
        }
    }

Get latest comments from multiple tables

Answers (2)

Related Questions