 |
boardom
b2 message board
|
| View previous topic :: View next topic |
| Author |
Message |
benny_01670
Joined: 11 Apr 2002
Posts: 22
|
 Posted: Thu Jan 09, 2003 5:13 pm Post subject: Help Please |
 |
|
I'm trying to create my own little blog tool (for a course project), but i'm having huge problems in getting the number of comments that have been left about a particular comment to be displayed.
I use a VERY basic select to select all blog id's, titles and content from my blog table. This works well, but when I try to select and count the number of comment from my comments table it does not work at all. If I use two seperate queries it will work, but it also means using two seperate bit's of code to display them. What I would like is to find away where I can get both queries and use one bit of code to display the results.
Hope I have made myself clear.
This is what I use to select and display my blog.
| Code: |
$QUERYBLOG = "SELECT id, title, blog, author, email, post_date FROM $DBTABLEBLOG ORDER BY post_date DESC LIMIT 0, 7";
$RESULTBLOG = mysql_query($QUERYBLOG) or die ("Error in query: $QUERYBLOG. " . mysql_error());
<?php
// Find if blog records exist
if (mysql_num_rows($RESULTBLOG) > 0)
{
// Print blog Titles
while($ROW = mysql_fetch_object($RESULTBLOG))
{
?>
<h2><? echo $ROW->title; ?></h2>
<p><? echo nl2br($ROW->blog); ?></p>
<span style="font-size:10px;padding-left:8px;"> - blogged by <a class="body-links" href="mailto: <?php echo $ROW->email; ?>" title="<?php echo $ROW->email; ?>"><? echo $ROW->author; ?></a> :: <? echo formatDate($ROW->post_date); ?> :: <a class="body-links" href="add_comment.php" title="Add a Comment?"><?php echo $COMMENTNUMBER; ?>Comments (<span style="color:red;">0</span>)</a> :: <img src="/images/search.gif" border="0" align="absmiddle" /> <a class="body-links" href="http://www.google.co.uk/search?q=<?php echo $ROW->title; ?>" title="Search Google for <?php echo $ROW->title; ?>">google it?</a></span><br /><br />
<?php
}
}
// if no records present display message
else
{
?>
<p style="color:red;font:11px/14px verdana, sans-serif;">No blogs found</p>
<?php
}
// close database connection
mysql_close($CONNECTION);
?>
|
Now how would I write another query that would take the blog id and select the number of comments related to that blog?
Just like it's done in b2.
Many many thanks in advance
Ben
_________________
Ben Swinney
fcuk-me.net | do ya wanna?
ben at fcuk-me dot net
http://www.fcuk-me.net / http://www.fcuk-me.com |
|
| Back to top |
|
 |
Cyberian75
Joined: 26 Sep 2002
Posts: 1007
Location: Washington
|
| Posted: Thu Jan 09, 2003 6:17 pm Post subject: |
|
|
You could use the mysql_num_rows function to get the number of rows in a query, and for your SQL statement, I think you could left join posts and comments table. If this is your own project, I would use a "flag" field so you can easily use the where clause to count them or something.
_________________
Michael P. |
|
| Back to top |
|
|
mikelittle
Joined: 11 May 2002
Posts: 374
Location: UK
|
| Posted: Wed Jan 15, 2003 11:42 pm Post subject: |
|
|
Ben,
Here is a b2 specific version, you should be able to transalte it to your project tables.
| Code: |
SELECT b2posts.id, post_title, user_nickname, user_email, post_date, COUNT(comment_id) AS comment_count
FROM b2posts LEFT JOIN b2comments ON b2posts.id = comment_post_id
LEFT JOIN b2users ON post_author = b2users.id
GROUP BY b2posts.id
ORDER BY post_date DESC LIMIT 0, 25;
|
Using this one query gives you all the info in one. You can pick out the bits you want to display.
Hope this helps,
Mike
_________________
Mike Little
http://zed1.com/journalized/
"Share what you know. Learn what you don't." |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB 2 © 2001, 2002 phpBB Group
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
