How to join two table with same cells names

[khr2003]

Hello every one
I have a forum written in php language. What I want is to display all the comments made by one user on a certain topic.
The database had two tables "forum" and "comment".

Forum table is structured as follows:
id cat_id title date_time userid name post allow usesig iconid uploadfile reader c_comment sticky close lastuserid timestamp edit_by ip_address rating_total ratings

Comment table is structured as follows:
id modType thread_id userid name title comment news_c_allow cat_id usesig uploadfile timestamp allow

All comments are linked back to the original thread by the "thread_id". And of course the cell "userid" refer to the id of the user in the users table in the Database.

I want to retrieve all the comments by one user, however I want to display the title of the thread and not the title of the comment (Even if it occurs more than one time).

I use this code to retrieve the information:

$result = $apt->query("SELECT DISTINCT forum.*, comment.*
FROM forum JOIN comment
ON forum.userid=comment.userid
WHERE comment.userid = $userid
ORDER BY forum.timestamp DESC
LIMIT $start,$perpagelist");

[sikka_varun]

Hi...
What you are asking is not clear...
Let me try..

you want to display the thread title and not the comment title in the query.... If this is the case.. then join the table threads also and join it on comment.thread_id = threads.thread_id

I hope you are having threads table..

[khr2003]

yes that is excactly what I wanted. Actually forum table is the threads table (I know the wrong name) but thanks I will try it and see

[khr2003]

Thank you very much. it worked.
I have one more question though. Since both tables have the same cell name for the title, how to do I view the two titles? I mean forum.title and comment.title

[sikka_varun]

Hi...
See for this thing, although you can join the two tables with the help of outer join
(search on google for more join types, use outer join as outer join will be a better idea because you have usually one thread/forum but have many comments associated with that)

but i will preferably recommend you only one thing, that use two different queries for this purpose...

for viewing the forum title.. select * from forums where forumid=1; for the comments: select * from omments where threadid=5 and forumid=1;

[khr2003]

Thanks for answering
But would not this add pressure on the server, if I had twenty raw per page then I would have forty queries. isn't there an easier way?

[ashafaaiz]

Hello every one
I have a forum written in php language. What I want is to display all the comments made by one user on a certain topic.
The database had two tables "forum" and "comment".

Forum table is structured as follows:
id cat_id title date_time userid name post allow usesig iconid uploadfile reader c_comment sticky close lastuserid timestamp edit_by ip_address rating_total ratings

Comment table is structured as follows:
id modType thread_id userid name title comment news_c_allow cat_id usesig uploadfile timestamp allow

All comments are linked back to the original thread by the "thread_id". And of course the cell "userid" refer to the id of the user in the users table in the Database.

I want to retrieve all the comments by one user, however I want to display the title of the thread and not the title of the comment (Even if it occurs more than one time).

I use this code to retrieve the information:

$result = $apt->query("SELECT DISTINCT forum.*, comment.*
FROM forum JOIN comment
ON forum.userid=comment.userid
WHERE comment.userid = $userid
ORDER BY forum.timestamp DESC
LIMIT $start,$perpagelist");

Hi,
What exactly you want is, just retrieve all comments from one user from comment table?
OK!

You have to write first;

// made your database connections here
    $q1=mysql_query("select * from forum");

    while($row=mysql_fetch_array($q1))
    {
      $userid=$row['userid'];

       $q2=mysql_query("select  *  from comments where     comments.userid='$userid'");
       while($row1=mysql_fetch_array($q2))
        {
         echo $row1['comment'];
         echo $row1['userid'];//display whatever you want 
        }
   }
mysql_close($con);

[khr2003]

thanks for the reply.

Actually, i am aware of what the code you posted but it not 100% what i want. I want to display the title of the thread that the comment of the user is linked to (and not to display the comment itself), I hope that i am making my self clear

[mschroeder]

SELECT
  forum.title,
  comments.comment
FROM
  forum
LEFT JOIN comments ON ( forum.id = comments.thread_id )
WHERE
  comments.userid = $iUserID AND
  forum.id = $iForumID

this would yield something like this:
+---------------+------------------------+
| title | comment |
+---------------+------------------------+
| Forum Title | Comment text #1 |
| Forum Title | Comment text #2 |
| Forum Title | Comment text #3 |
| Forum Title | Comment text #4 |
+---------------+------------------------+