Simple Join Question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
llabeoll
Forum Newbie
Posts: 8
Joined: Wed Feb 04, 2009 9:22 pm

Simple Join Question

Post by llabeoll »

I seem to be having a lot of trouble with joining two tables. I know there has to be a way to do this in one query. This is for a very rudimentary blog that I am playing around with for some more practice.

Table1 - blog
- blogid
- blogtitle
- blogpost
- date

Table2 - blogcomments
- commentid
- blogid
- comment
- commentname

I would like to show the all of the content from table blog and within that While loop of pulling that information, display the number of Comments which have a blogid equal to that of the blogid from the first table.

A Read More link which is located within the main blog entry will take the user then to the full article where the comments will be displayed in full under the post as well.

Any help would be greatly appreciated. Thank you.
Last edited by llabeoll on Mon Feb 23, 2009 11:28 am, edited 1 time in total.
mintedjo
Forum Contributor
Posts: 153
Joined: Wed Nov 19, 2008 6:23 am

Re: Simple Join Question

Post by mintedjo »

I would like to show the all of the content from table blog and within that While loop of pulling that information, display the number of Comments which have a blogid equal to that of the blogid from the first table.
I think this is what you mean.

Code: Select all

SELECT blog.blogid, blogtitle, blogpost, blogdate, num_comments
FROM blog JOIN (
        SELECT blogid, COUNT(*) AS num_comments 
        FROM blogcomments 
        GROUP BY blogid
    ) AS tmp ON tmp.blogid = blog.blogid
llabeoll
Forum Newbie
Posts: 8
Joined: Wed Feb 04, 2009 9:22 pm

Re: Simple Join Question

Post by llabeoll »

mintedjo wrote:
I would like to show the all of the content from table blog and within that While loop of pulling that information, display the number of Comments which have a blogid equal to that of the blogid from the first table.
I think this is what you mean.

Code: Select all

SELECT blog.blogid, blogtitle, blogpost, blogdate, num_comments
FROM blog JOIN (
        SELECT blogid, COUNT(*) AS num_comments 
        FROM blogcomments 
        GROUP BY blogid
    ) AS tmp ON tmp.blogid = blog.blogid

Thank you. I used this query and it returns 0 results as I believe it will only return blog posts which have comments. Here is my code. Any advice?

Code: Select all

$result = mysql_query("SELECT blog.blogid, blogtitle, blogpost, date, num_comments FROM blog JOIN (SELECT blogid, COUNT(*) AS num_comments FROM blogcomments GROUP BY blogid) AS tmp ON tmp.blogid = blog.blogid");
 echo "<table width = '99%' align = 'right' cellpadding = '4' cellspacing = '0' border = '0'>";
 
 while($row = mysql_fetch_array($result))
   {
 $example = $row['blogpost'];
       echo "<tr>";
       echo "<td colspan = '2'><p class = 'heading'>" . $row['blogtitle'] . "</p></td></tr>";
       echo "<tr height = '5'>";
       echo "<td colspan = '2' background = 'images/underline.jpg'></td></tr>";
       echo "<tr>";
       echo "<td width = '70%'><p align = 'justify'>";
       echo blogsum($example,75);
       echo "&nbsp;[...]&nbsp;<a href = 'blogdetails.php?id=" . $row['blogid'] . "'>&nbsp;Read More&nbsp;>&nbsp;></a></p></td>";
       echo "<td></td></tr>";
       echo "<tr>";
       echo "<td align = 'right' background = 'images/datebg.jpg'><p class = 'small'>[" . $row['num_comments'] . "] Comments&nbsp;&nbsp;&nbsp;&nbsp;///&nbsp;Posted On:&nbsp;" . $row['date'] . "</p></td>";
       echo "<td>&nbsp;</td>";
       echo "</tr>";
   }
echo "</table>";
llabeoll
Forum Newbie
Posts: 8
Joined: Wed Feb 04, 2009 9:22 pm

Re: Simple Join Question

Post by llabeoll »

Can I get any more input on this?
llabeoll
Forum Newbie
Posts: 8
Joined: Wed Feb 04, 2009 9:22 pm

Re: Simple Join Question

Post by llabeoll »

Anybody want to shed some light on this for me?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Simple Join Question

Post by VladSun »

Use COUNT(), LEFT JOIN and GROUP BY
There are 10 types of people in this world, those who understand binary and those who don't
llabeoll
Forum Newbie
Posts: 8
Joined: Wed Feb 04, 2009 9:22 pm

Re: Simple Join Question

Post by llabeoll »

Thank you, very much. I was about to get it to work using the left join.
Post Reply