DaniWeb Forum Index > Web Development > PHP

How to join two table with same cells names

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Dec 2008

Posts: 57

Reputation: khr2003 is an unknown quantity at this point

Solved Threads: 0

khr2003

Offline

Junior Poster in Training

How to join two table with same cells names

Dec 11th, 2008

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:

 Help with Code Tags 
 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");

•

Join Date: Dec 2008

Posts: 94

Reputation: sikka_varun is an unknown quantity at this point

Solved Threads: 11

sikka_varun sikka_varun is offline

Offline

Junior Poster in Training

Re: How to join two table with same cells names

Dec 12th, 2008

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

VâRûN
---Happy to Help---
sikka_varun@yahoo.com

•

Join Date: Dec 2008

Posts: 57

Reputation: khr2003 is an unknown quantity at this point

Solved Threads: 0

khr2003

Offline

Junior Poster in Training

Re: How to join two table with same cells names

Dec 12th, 2008

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

•

Join Date: Dec 2008

Posts: 57

Reputation: khr2003 is an unknown quantity at this point

Solved Threads: 0

khr2003

Offline

Junior Poster in Training

Re: How to join two table with same cells names

Dec 14th, 2008

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

•

Join Date: Dec 2008

Posts: 94

Reputation: sikka_varun is an unknown quantity at this point

Solved Threads: 11

sikka_varun sikka_varun is offline

Offline

Junior Poster in Training

Re: How to join two table with same cells names

Dec 14th, 2008

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.

VâRûN
---Happy to Help---
sikka_varun@yahoo.com

•

Join Date: Dec 2008

Posts: 57

Reputation: khr2003 is an unknown quantity at this point

Solved Threads: 0

khr2003

Offline

Junior Poster in Training

Re: How to join two table with same cells names

Dec 24th, 2008

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?

•

Join Date: Dec 2008

Posts: 19

Reputation: ashafaaiz is an unknown quantity at this point

Solved Threads: 1

ashafaaiz

Offline

Newbie Poster

Re: How to join two table with same cells names

Dec 24th, 2008

•

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:
Help with Code Tags

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;

 Help with Code Tags 
 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.

•

Join Date: Dec 2008

Posts: 57

Reputation: khr2003 is an unknown quantity at this point

Solved Threads: 0

khr2003

Offline

Junior Poster in Training

Re: How to join two table with same cells names

Dec 24th, 2008

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.

•

Join Date: Jul 2008

Posts: 148

Reputation: mschroeder is on a distinguished road

Solved Threads: 25

mschroeder mschroeder is offline

Offline

Junior Poster

Re: How to join two table with same cells names

Dec 24th, 2008

 Help with Code Tags 
 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