mysql query of two tables mixing together badly

[ryanmetzler3]

I have two comment sections. The data from the first is saved in threaded_comments (a table), the second comment section saves its data in threaded_comments2. I have attached a screen shot of the structure of the tables. The two tables have the same structure and sit in one DB. If you post an original comment, it gets a parent_id of 0. If you reply to a comment its parent_id matches the id of the comment it is in reply to (You can see this in the pic). If a user logs in, I would like to have a section where they can view their recent comments ONLY and see if anyone has replied to them. I ran a query to do this as follows:

//connection
include $_SERVER['DOCUMENT_ROOT'] . '/comm_1/config.php';

// gets original comments only, And only those posted by the user currently logged in.
$sql = "SELECT *, 0 as section FROM threaded_comments WHERE parent_id = 0 AND author = '$username'"
. "UNION SELECT *, 1 as section FROM threaded_comments2 WHERE parent_id = 0 AND author = '$username'";
     $result = mysql_query($sql);
     while ($link = mysql_fetch_assoc($result)) {
           yourComments($link);
     }
//this function gets all the information from the rows in the DB             
	function yourComments($row) {
             echo "<li class='comment'>";
             echo "<div class='aut'>".$row['author']."</div>";
             echo "<div class='timestamp'>".$row['created_at']."</div>";
             echo "<div class='comment-body'>".$row['comment']."</div>";

//this query checks if there is any replies to the comments fetched by the original query and displays them.
                  $q = "SELECT *, 0 as section FROM threaded_comments WHERE parent_id = ".$row['id']." "
                  . "UNION SELECT *, 1 as section FROM threaded_comments2 WHERE parent_id = ".$row['id']."";
                            $r = mysql_query($q);
                            echo "</li>";
                            if(mysql_num_rows($r)>0)
                                    {
                                    echo "<ul>";
                                    while($row = mysql_fetch_assoc($r)) {
                                            yourComments($row);
                                    }
                                    echo "</ul>";
                                    }	
                            }

The problem is a comment from the first table may have an id of "1" and therefore a reply to this comment would have a parent_id of "1". This same exact scenario is possible to happen in table 2. So for example the comment with the id of 1, displays. Then all the comments with a parent_id of "1" display as a reply to it (even though they could be coming from both table). I will attach a screen shot of this issue to help make it more clear. Any idea how to fix this?

[Barand]

Don't run queries in loops, use JOINs.

 

I see you are still spreading the comments over multiple tables

SELECT 1 as type
, t1.id
, t1.author 
, t1.created_at as orig_time
, t1.comments as orig_comment
, t2.author as reply_by 
, t2.created_at as reply_time
, t2.comments as reply_comment
FROM threaded_comment t1
    INNER JOIN threaded_comment t2 ON t1.id = t2.parent_id
WHERE t1.author = 'Barand'
    AND t1.parent_id = 0
    AND t1.created_at > NOW() - INTERVAL 72 HOUR
    
UNION

SELECT 2 as type
, t1.id
, t1.author 
, t1.created_at as orig_time
, t1.comments as orig_comment
, t2.author as reply_by 
, t2.created_at as reply_time
, t2.comments as reply_comment
FROM threaded_comment_2 t1
    INNER JOIN threaded_comment_2 t2 ON t1.id = t2.parent_id
WHERE t1.author = 'Barand'
    AND t1.parent_id = 0
    AND t1.created_at > NOW() - INTERVAL 72 HOUR
    
ORDER BY type, orig_time;

Edited February 6, 2014 by Barand