Neverside

comment count in news post

Aside from having a comments field in my news table with the numbers of comments, how would i go about putting the number of comments in my newsposts, which are output by while loop.

PHP:

<?
$result = mysql_query("select * from news ORDER BY id DESC");

while($r=mysql_fetch_array($result)) {    

$title=$r["title"];
$message=$r["message"];
$who=$r["who"];
$date=$r["date"];
$time=$r["time"];
$id=$r["id"];

echo $message, $who, $title, $date,$time?>
<a href="comment.php?id=<? echo $id;?>">Comment ["numbers of comments here"]</a>
<? }
?>

Tried a few things, including counting the size of the array from a return query. Any ideas?

Add the folowing under '$result'

PHP:

<?php

$num = mysql_num_rows($result);

?>

Lets say the query returns that there ar 10 comments, $num will print 10.

___________________

[ insert catchy signature phrase here ]

I have mine set up with a table named news and one named new_comments. in news comments there's a row called 'news_id'

Code:

$query = "SELECT COUNT(comment) AS news_count FROM news_comments WHERE news_id = $id";
$count = mysql_query($query, $db) or die('Query failed: ' . mysql_error() . "<hr>$query<hr>");
$c = mysql_fetch_array($count);

that counts the number of rows that has the news_id that matches the ID of the news you're viewing. Then, to call it. just do

Code:

<a href="comments.php?id=<?php echo $id; ?>"><? echo "Comments (" . $c["news_count"] . ")"; ?></a>

I'm not sure how much that helps, but hopefully it at least gives you an idea.

___________________

dnyy on irc

Last edited by rescribe, April 5th, 2005 11:03 PM (Edited 1 times)

The most efficient method, and one I have used to make my pages faster and keep my query count down to minimum, is to create a field in your news table called "numcom" (or something equivilant), and every time a user posts a new comment you increment said field like so:

Code:

UPDATE news SET numcom = numcom+1 WHERE newsid = <some number>

___________________

I don't suffer from insanity; I enjoy every minute of it.
Unintended Theory | Cacrew v4

chriss: That would give me the count of the number of news items, though, with elements from rescribe's ideas, i was able to do it quite simply lol

PHP:

<?php
$comments = mysql_query("select id from comments WHERE newsid='$id'");
$comments = mysql_num_rows($comments);
?>

right under declaring my variables in the while loop. finished code for anyone this may help is below:

PHP:

<?
$result = mysql_query("select * from news ORDER BY id DESC");

while($r=mysql_fetch_array($result)) {    

$title=$r["title"];
$message=$r["message"];
$who=$r["who"];
$date=$r["date"];
$time=$r["time"];
$id=$r["id"];
$comments = mysql_query("select id from comments WHERE newsid='$id'");
$comments = mysql_num_rows($comments);

echo $message, $who, $title, $date,$time?>
<a href="comment.php?id=<? echo $id;?>">Comment [#<? echo $comments;?>]</a>
<? }
?>

Glad I could help.

___________________

dnyy on irc

Glad you figured it out. Sorry I didn't understand the question in the first place :/

___________________

[ insert catchy signature phrase here ]

If you dont want to bother summaries, you could of modified the code I posted 3 days ago. I'd say I miss the search feature, but no one uses it anyways!

http://forums.neverside.com/view/post734268/#post734268

___________________

http://www.philbrodeur.com - Expert PHP Development and Tutorials

ahh right, was unaware you had posted that, not really too active round here yet. Reason i posted it without looking was because of the lack of a search feature. Actually baffled by something else now, may aswell post it here. Got the entire comments system up and running in my code now, and in my admin panel, i'm looking to output 20 comments per page ordered by news post date (newest first), then comment post id (which would be oldest first) with a single blurb from the newspost for the comments in that post. confusing, is to me a little bit... e.g.

news from 31.03.05 - "another day" - posted by shufty
comment 1
comment 2
news from 30.03.05 - "something happened" - posted by shufty
comment 1
comment 2
comment 3

it's really baffling me, this is what i have come up with so far, but line 11 is giving an error "Parse error: parse error, unexpected T_VARIABLE" ***code removed due to stupidity*** tried a few thing, but i can't get it to work, any ideas?

---------

oops, helps if i add all ;'s lol got that bit working, but it will only display 1 news post details, because of the while loop which was intentional to stop it showing multiple headers for 2 comments in one newspost. Am I coming at this from the wrong angle?

PHP:

<?
    include("connection.php");
    include("../global.php");
    
    $result = mysql_query("select * from news order by id DESC");

    $r=mysql_fetch_array($result);
    $id = $r["id"];
    $i = 1;
    while($i <= 1) {
        $i++;
        echo "News from ";
        echo $r["date"];
        echo " - ";
        echo $r["title"];
        echo " - posted by ";
        echo $r["who"];
        $comments = mysql_query("SELECT * FROM comments WHERE newsid='$id' ORDER BY id DESC");
        while($s=mysql_fetch_array($comments)) {
            echo $s["comment"];
        }
    }
?>

Last edited by shufty, April 6th, 2005 05:02 AM (Edited 2 times)

Your last post confused me a little, but I think your saying its only giving you comments for 1 news posting am I correct?
If so I would assume thats because you are only having it go through the while loop once, and even if you weren't the $id variable is only being assigned once, so really the code is only set up to get one news posting. Change the while loop to look more like the inner while loop that grabs the comments and assign the $id variable inside of it not outside.

Sorry if I misinterpretated your question.

___________________

"Dip your feet in your own pool of knowldege before asking someone else to bathe in theirs"