SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    counting comments

    Hi, I want to count the comments that go with news but well I don't really how to do it.

    Ok, I have 2 tables:

    - news_national (fields: nid,title,message)
    - comments_news_nat (fields: id,nid,comment)

    The news/comments are working but now I want to count those comments

    Could anyone help me on the way plz

    I've tried the following but it doesn't work:

    PHP Code:

    /* count comments */
    $comments_query = @mysql_query ("SELECT nid FROM comments_news_nat WHERE nid = 'news_national.nid'");
      if (!
    $comments_query) {
      echo (
    "Can't REQ data");
      }

    while (
    $comments_array mysql_fetch_array ($comments_query)){
            
    $countnid $comments_array ["nid"];
            }
      
    $comments_count count($countnid); 

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: counting comments

    i'm thinking this would work...
    PHP Code:
    /* count comments */
    $comments_query = @mysql_query ("SELECT nid FROM comments_news_nat WHERE nid = 'news_national.nid'");
      if (!
    $comments_query) {
      echo (
    "Can't REQ data");
      }
    $comments_count mysql_num_rows($comments_query); 

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'll post the entire script cause I can't seem to solve it

    PHP Code:


    <?php

    /* fetch news */

    $news_query = @mysql_query ("SELECT * FROM news_national ORDER BY nid DESC");
      if (!
    $news_query) {
      echo (
    "Can't REQ data");
      exit();
      }
                          

    while (
    $news_array mysql_fetch_array ($news_query)){
            
    $nid $news_array ["nid"];
            
    $author $news_array ["author"];
            
    $date $news_array ["date"];
            
    $title $news_array ["title"];
            
    $message $news_array ["message"];
            
    $image_name $news_array ["image_name"];
            
                
    /* begin news table */
    echo ("<table width=\"100%\" bgcolor=\"999999\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">");
    echo (
    "<tr>");
    echo (
    "<td bgcolor=\"#E5E5E5\"><b><a name=\"$nid\">$title</a></b></td>\n");
    echo (
    "<td bgcolor=\"#E5E5E5\"><div align=\"right\">$author | $date</div></td>\n");
    echo (
    "</tr>\n");
    echo (
    "<tr>");
    echo (
    "<td height=\"1\" colspan=\"2\" bgcolor=\"#999999\"></td>\n");
    echo (
    "</tr>\n");

    if (
    $image_name != '') {
    echo (
    "<tr>");
    echo (
    "<td colspan=\"2\" bgcolor=\"#F2F2F2\"><img src=\"admin/images_upload/$image_name\" align=\"left\">$message</td>\n");
    echo (
    "</tr>\n");
    }
    else {
    echo (
    "<tr>");
    echo (
    "<td colspan=\"2\" bgcolor=\"#F2F2F2\">$message</td>\n");
    echo (
    "</tr>\n");
    }

    echo (
    "<tr>");
    echo (
    "<td colspan=\"2\" bgcolor=\"F2F2F2\"><a href=\"comments_news_nat.php?nid=$nid\" class=\"black\"><div align=\"right\">Comments</div></a></td>\n");
    echo (
    "</tr>\n");
    echo (
    "<tr>");
    echo (
    "<td height=\"1\" bgcolor=\"#999999\"></td>");
    echo (
    "</tr>");
    echo (
    "</table>\n");
    echo (
    "<p></p>");
     
     }          

    ?>
    So how should it look like it?

  4. #4
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Newcastle, UK
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could just use a left join with mysql's count() function to pull the number of comments for the item out with, The query would go something along the lines of

    SELECT news_national.*,COUNT(comments_news_nat.id) AS numcomments
    FROM news_national
    LEFT JOIN comments_news_nat
    ON comments_news_nat.nid = news_national.nid
    GROUP BY news_national.nid
    ORDER BY nid DESC

    should do what you need (that may be a little more complex than needs, but its sunday afternoon so I aint rushing round researching it ) it would have the number of comments for each news item stored in a return field called numcomments (easy huh?)

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do I have to delete my query in the code above than? sorry but I really have no clue what you're talking about

  6. #6
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Newcastle, UK
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah right, well that would be intended as a replacement for that query, all the return data will be the same except you'll get the extra bit called numcomments to call on, so you should just be able to drop it right on in.

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried it, but it doesn't work.

    PHP Code:

    <?php

    /* fetch news */

    $news_query = @mysql_query ("SELECT news_national.*,COUNT(comments_news_nat.id) AS numcomments 
                                 FROM news_national LEFT JOIN comments_news_nat 
                                 ON comments_news_nat.nid = news_national.nid 
                                 GROUP BY news_national.nid 
                                 ORDER BY nid DESC LIMIT "
    .$page.",10");

      if (!
    $news_query) {
      echo (
    "Can't REQ data");
      exit();
      }
                          

    while (
    $news_array mysql_fetch_array ($news_query)){
            
    $nid $news_array ["nid"];
            
    $author $news_array ["author"];
            
    $date $news_array ["date"];
            
    $title $news_array ["title"];
            
    $message $news_array ["message"];
            
    $image_name $news_array ["image_name"];
            
                
    /* begin news table */
    echo ("<table width=\"100%\" bgcolor=\"999999\" border=\"0\" cellspacing=\"0\" cellpadding=\"2\">");
    echo (
    "<tr>");
    echo (
    "<td bgcolor=\"#E5E5E5\"><b><a name=\"$nid\">$title</a></b></td>\n");
    echo (
    "<td bgcolor=\"#E5E5E5\"><div align=\"right\">$author | $date</div></td>\n");
    echo (
    "</tr>\n");
    echo (
    "<tr>");
    echo (
    "<td height=\"1\" colspan=\"2\" bgcolor=\"#999999\"></td>\n");
    echo (
    "</tr>\n");

    if (
    $image_name != '') {
    echo (
    "<tr>");
    echo (
    "<td colspan=\"2\" bgcolor=\"#F2F2F2\"><img src=\"admin/images_upload/$image_name\" align=\"left\">$message</td>\n");
    echo (
    "</tr>\n");
    }
    else {
    echo (
    "<tr>");
    echo (
    "<td colspan=\"2\" bgcolor=\"#F2F2F2\">$message</td>\n");
    echo (
    "</tr>\n");
    }

    echo (
    "<tr>");
    echo (
    "<td colspan=\"2\" bgcolor=\"F2F2F2\"><a href=\"comments_news_nat.php?nid=$nid\" class=\"black\"><div align=\"right\">Comments ($numcomments)</div></a></td>\n");
    echo (
    "</tr>\n");
    echo (
    "<tr>");
    echo (
    "<td height=\"1\" bgcolor=\"#999999\"></td>");
    echo (
    "</tr>");
    echo (
    "</table>\n");
    echo (
    "<p></p>");
     
     }          

    ?>
    That's what you ment right?

    It always shows as Comments ()
    Last edited by InfraRedesign; Mar 10, 2002 at 14:42.

  8. #8
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Newcastle, UK
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats because it looks like you arent taking the value from the sql resultset and putting it into a variable like you do with the other bits, it should be sortable by replacing:

    PHP Code:
            $nid $news_array ["nid"];
            
    $author $news_array ["author"];
            
    $date $news_array ["date"];
            
    $title $news_array ["title"];
            
    $message $news_array ["message"];
            
    $image_name $news_array ["image_name"]; 
    with
    PHP Code:
            $nid $news_array ["nid"];
            
    $author $news_array ["author"];
            
    $date $news_array ["date"];
            
    $title $news_array ["title"];
            
    $message $news_array ["message"];
            
    $image_name $news_array ["image_name"];
            
    $numcomments $news_array["numcomments"]; 

  9. #9
    SitePoint Zealot
    Join Date
    Dec 2001
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  10. #10
    SitePoint Member
    Join Date
    Apr 2001
    Location
    Newcastle, UK
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    np's, I remember what an **** it was working the same sort of thing out when i wrote my sites news system for the first commented version


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •