Php Mysql count rows function.

add GROUP BY t.ID to the end of your query.

-r-

Thanks for you suggestions,

If make it group by t.id the it will only show the amount of topic made(at the moment 4 topics)..

-T

yes, sorry, you should use:

SELECT count(p.*) as posts, t.ID .... GROUP BY t.ID

-r-

Thanks

now it  gives me the following error:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) as post , t . ID FROM posts p , topics t WHERE p . TopicID

-T

Ok, well in phpmyadmin this query shows the right result:

SELECT count( p . ID ) as posts , t . ID FROM posts p , topics t WHERE p . TopicID = t . ID Group by t . ID LIMIT 0, 30;

it gives the right result:

posts ID
3       1
5       2
3       3
4       4

But when using with php:

$amount = mysql_query("SELECT count( p . ID ) as posts , t . ID FROM posts p , topics t WHERE p . TopicID = t . ID Group by t . ID LIMIT 0, 30");
$num_rows = mysql_num_rows($amount);

And echoing it out <?php echo $num_rows ?> it still displays the amount of topic and that being four??

-T

$amount = mysql_query("SELECT count( p . ID ) as posts , t . ID FROM posts p , topics t WHERE p . TopicID = t . ID Group by t . ID LIMIT 0, 30");
while($row = mysql_fetch_array($amount)) {
    echo $row['posts'].'&nbsp;&nbsp;'.$row['ID'].'<br>';
}

Thanks for your reply, now it displays the right amount of posts, but it also displays all the topics 4 times, here's my code:

$amount = mysql_query("SELECT distinct count(p.ID) as posts, t.ID FROM posts p, topics t WHERE p.TopicID = t.ID Group by t.ID LIMIT 0, 30");
while($row = mysql_fetch_array($amount)) {

$topic_query = mysql_query("SELECT t.id, t.topicname AS topicname, DATE_FORMAT(MAX(p.timestamp), '%d.%m.%Y&nbsp;%H:%i:%s' ) AS insertdate, u.username AS username FROM users u, topics t LEFT JOIN posts p ON ( p.topicid = t.id ) WHERE u.users_id = p.user_ GROUP BY t.id ORDER BY p.timestamp;");
while ($topic = mysql_fetch_array($topic_query)) {
?>
<TR>
<TD>
<span class="member_links_underline_black" style="color: #000000;"># <a href="user.php?id=forum&page=view&topicID=<?php echo $topic['id'] ?>"><?php echo $topic['topicname'] ?></a></span>
</TD>
<TD>
<span class="member_links_underline_black" style="color: #000000;"><?php echo $topic['insertdate'] ?></span>
</TD>
<TD>
<span class="member_links_underline_black" style="color: #000000;"><?php echo $topic['username'] ?></span>

</TD>
<TD>
<span class="member_links_underline_black" style="color: #000000;"><?php echo $row['posts'] ?></span>
<?php } ?>
</TD>
</TR>
<?php
}
?>

Some bugs somewhere in the code I assume:)

-T

Got it working, thanks :)

-T

You're welcome :)