Page 1 of 1

Need some PHP/MySQL help - "number of post list"

Posted: Mon Apr 13, 2009 9:20 pm
by JKM
I've got a table with lots of comments. There are four fields - id, parent, comment and author.

Now, I want to list all authors like this:
Author - Number of comments
-----------------------------
Author1 - 10
Author2 - 3
Author3 - 23
-----------------------------

I tried to do it like this:

Code: Select all

<table>
        <tr>
                <td><strong>Author</strong></td>
                <td><strong>Number of comments</strong></td>
        </tr>
<?php
mysql_connect('xxx','xxx','xxx') or die("mysql error");
mysql_select_db("xx") or die("mysql error");
$all = mysql_query("SELECT author FROM comments");
while($fetch = mysql_fetch_array($cs_all)) {?>
        <tr>
                <td><strong><?php echo $fetch['author']; ?></strong></td>
                <td><?php echo mysql_num_rows(mysql_query("SELECT author FROM comments WHERE author='".$fetch['author']."'")); ?></td>
        </tr>
<?php }?>
</table>
But it didn't work out as planned. It didn't show anything but the rows above the php code, but when I removed the second td in the while loop, it listed up all authors. So I'm wondering how I can solve this.

Thanks.

Re: Need some PHP/MySQL help - "number of post list"

Posted: Mon Apr 13, 2009 11:26 pm
by califdon
JKM wrote:

Code: Select all

<table>
        <tr>
                <td><strong>Author</strong></td>
                <td><strong>Number of comments</strong></td>
        </tr>
<?php
mysql_connect('xxx','xxx','xxx') or die("mysql error");
mysql_select_db("xx") or die("mysql error");
$all = mysql_query("SELECT author FROM comments");
while($fetch = mysql_fetch_array($cs_all)) {?>
        <tr>
                <td><strong><?php echo $fetch['author']; ?></strong></td>
                <td><?php echo mysql_num_rows(mysql_query("SELECT author FROM comments WHERE author='".$fetch['author']."'")); ?></td>
        </tr>
<?php }?>
</table>
SELECT author, COUNT(comments) FROM comments GROUP BY author

Re: Need some PHP/MySQL help - "number of post list"

Posted: Tue Apr 14, 2009 10:11 am
by JKM
Now, there are two authors. One of them got five comments, and the other one got one.

Code: Select all

<table>
    <tr>
        <td><strong>Author</strong></td>
        <td><strong>Number of comments</strong></td>
    </tr>
<?php
mysql_connect('xxx','xxx','xxx') or die("mysql error");
mysql_select_db("xxx") or die("mysql error");
$qry = mysql_query("SELECT author, COUNT(comments) FROM comments GROUP BY author");
while($fetch = mysql_fetch_array($qry)) {?>
        <tr>
                <td><strong><?php echo $fetch['author']; ?></strong></td>
                <td><?php echo mysql_num_rows($qry); ?></td>
        </tr>
<?php } ?>
</table>
Turned out like this:

Code: Select all

<table>
    <tr>
        <td><strong>Author</strong></td>
        <td><strong>Number of comments</strong></td>
 
    </tr>
    <tr>
        <td><strong>Author2</strong></td>
        <td>2</td>
    </tr>
    <tr>
        <td><strong>Author2</strong></td>
 
        <td>2</td>
    </tr>
</table>

Re: Need some PHP/MySQL help - "number of post list"

Posted: Tue Apr 14, 2009 12:46 pm
by califdon

Code: Select all

<table>
    <tr>
        <td><strong>Author</strong></td>
        <td><strong>Number of comments</strong></td>
    </tr>
<?php
mysql_connect('xxx','xxx','xxx') or die("mysql error");
mysql_select_db("xxx") or die("mysql error");
$qry = mysql_query("SELECT author, COUNT(comments) AS numcomments FROM comments GROUP BY author");
while($fetch = mysql_fetch_array($qry)) {
        echo "<tr><td><strong>$fetch['author']</strong></td>
                <td>$fetch['numcomments']</td></tr>"; 
} 
?>
</table>

Re: Need some PHP/MySQL help - "number of post list"

Posted: Tue Apr 14, 2009 1:17 pm
by JKM
Thanks a lot. <3