Need help count comments!

Discussion in 'PHP' started by Hannaspice, Jul 6, 2009.

0
  1. #1
    Hi ul again,

    I have just managed to do a comment table, which contains some fields below:

    - id
    - news_id
    - name
    - comment
    - date
    - status

    on another table, which name is news table, of course it has some fields, too:

    - id
    - title
    - content


    the id is fr 1, the 'news_id' of comment table above is = id of news table.

    For example 6 comments belong to a news (id=2), the comment table consist of:

    - id: 1,2,3,4,5,6
    - news_id: 2,2,2,2,2,2
    - name...
    - comment...
    - date...
    - status: A

    Now the question is:
    How can I count all of comments which belong to a same 'news_id' and which has 'status'=A?

    	$result = mysql_query("SELECT * FROM comments WHERE news_id='$id' ORDER BY `date` ASC");
	for($i=0; $i<mysql_num_rows($result); $i++) {
	     $name      = mysql_result($result, $i, "name");
		 $comm       = mysql_result($result, $i, "comment");
		 $date_comm  = mysql_result($result, $i, "date");
    PHP:
     
    Hannaspice, Jul 6, 2009 IP
  2. qazu

    qazu Well-Known Member

    Messages:
    1,834
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    165
    #2
    I think
    SELECT COUNT(*) FROM comments WHERE status=A and news_id='$id'
    would work if you want to count comments for news (id=2)
    otherwise
    SELECT news_id, COUNT(*) FROM comments WHERE status=A GROUP BY news_id
    will return comments for each id but I don't think that's what you want
     
    qazu, Jul 6, 2009 IP
  3. samyak

    samyak Active Member

    Messages:
    280
    Likes Received:
    7
    Best Answers:
    4
    Trophy Points:
    90
    #3
    "Select news_id, Count(comment) as myCount FROM comments WHERE status='A' GROUP BY news_id"

    This will return the news id and the corresponding number of comments.

    Amit
     
    samyak, Jul 6, 2009 IP
  4. Hannaspice

    Hannaspice Active Member

    Messages:
    77
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #4
    I mean:

    if there are

    - news_id: 2,2,4,2,2,6,2,2,13...

    How can I count them, there are 6 comments of news_id=2, because I would like to show how many comments belong to a news.

    kindly tks bros.

    Pls help me!!!
     
    Hannaspice, Jul 7, 2009 IP
  5. samyak

    samyak Active Member

    Messages:
    280
    Likes Received:
    7
    Best Answers:
    4
    Trophy Points:
    90
    #5
    Thats what I wrote this query for:
    Did you even try this?
     
    samyak, Jul 7, 2009 IP
  6. Hannaspice

    Hannaspice Active Member

    Messages:
    77
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #6
    Yes, I did, but I dun know how exactly it works.

    I will reply you later after I tried it properly.

    Best regards.
     
    Hannaspice, Jul 8, 2009 IP
  7. Hannaspice

    Hannaspice Active Member

    Messages:
    77
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #7 
    $get_query = mysql_query("SELECT news_id, Count(news_id) as myCount FROM comments GROUP BY news_id ORDER BY myCount desc");
	while($row = mysql_fetch_array($get_query))
	{
	$out .= "". $row['news_id'] . " - Matches: " . $row['myCount'] . "<br />";
	}
    PHP:
    Below display:

    345 - Matches: 5
    344 - Matches: 5
    343 - Matches: 3
    347 - Matches: 2

    Yes it works great!, thank you.
     
    Hannaspice, Jul 14, 2009 IP