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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

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

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

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

Post 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>
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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>
JKM
Forum Contributor
Posts: 221
Joined: Tue Jun 17, 2008 8:12 pm

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

Post by JKM »

Thanks a lot. <3
Post Reply