Ad:

PHP Discussion Thread
Unsolved
Views: 1292

Dec 11th, 2008

How to join two table with same cells names

Expand Post »

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:

PHP Syntax (Toggle Plain Text)
    $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");
    $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");

khr2003

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

64 posts
since Dec 2008

Permalink

Dec 12th, 2008

Re: How to join two table with same cells names

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..

sikka_varun

Reputation Points: 11

Solved Threads: 12

Junior Poster in Training

Offline

94 posts
since Dec 2008

Permalink

Dec 12th, 2008

Re: How to join two table with same cells names

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

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

64 posts
since Dec 2008

Permalink

Dec 14th, 2008

Re: How to join two table with same cells names

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

khr2003

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

64 posts
since Dec 2008

Permalink

Dec 14th, 2008

Re: How to join two table with same cells names

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;

Last edited by peter_budo; Dec 15th, 2008 at 4:00 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

sikka_varun

Reputation Points: 11

Solved Threads: 12

Junior Poster in Training

Offline

94 posts
since Dec 2008

Permalink

Dec 24th, 2008

Re: How to join two table with same cells names

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?

khr2003

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

64 posts
since Dec 2008

Permalink

Dec 24th, 2008

Re: How to join two table with same cells names

Click to Expand / Collapse Quote originally posted by 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:
PHP Syntax (Toggle Plain Text)
    $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");
$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;

php Syntax (Toggle Plain Text)
// 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);
// 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);

Last edited by peter_budo; Dec 29th, 2008 at 5:33 am. Reason: Keep It Organized - Use [bbcode] only when it is necessary to the comprehension of your post.

ashafaaiz

Reputation Points: 10

Solved Threads: 1

Newbie Poster

Offline

20 posts
since Dec 2008

Permalink

Dec 24th, 2008

Re: How to join two table with same cells names

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

Last edited by khr2003; Dec 24th, 2008 at 6:16 am.

khr2003

Reputation Points: 10

Solved Threads: 1

Junior Poster in Training

Offline

64 posts
since Dec 2008

Permalink

Dec 24th, 2008

Re: How to join two table with same cells names

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

mschroeder

Sponsor

Reputation Points: 265

Solved Threads: 126

Practically a Master Poster

Offline

624 posts
since Jul 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: PHP and Flash Form help

Next Thread in PHP Forum Timeline: Using PHP/MySql with IIS

DaniWeb Message

Cancel Changes

User Name
Password