I decided to write my own forums for a website and they turned out fine, but now I'm trying to create a top ten topics/threads and the way I created my database messed it up. (PHP/MySql)

The table that holds the topics has it's normal 'id' column but no posts column.

The table that holds the posts has an 'id' column and a 'tid' column which holds the topic's id.

To find the amount of posts for a topic, for example, I'd use this code:

<?php

$tid = "1"; // Any topic number
$sql = mysql_query("SELECT * FROM f_posts WHERE tid='$tid'");
$posts = mysql_num_rows($sql);

?>

Now what I want is to find the ten topics that have the most posts. I've tried a lot of things but none have worked out like I expected them too. I'm pretty sure that there's something simple I've overlooked, but I can't think of it.

Thanks!

Use GROUP BY in your sql query.

http://dev.mysql.com/doc/mysql/en/counting-rows.html

I F U C K I N G L O V E Y O U !

Hold on, that doesn't work how I need it to. I'm trying to sort the topics by the number of posts it has.

Ok got it


<?php

("SELECT COUNT(*) as count, tid FROM f_posts GROUP BY tid ORDER BY count DESC LIMIT 10");

?>


Works like a charm

Originally posted by djcubez
I F U C K I N G L O V E Y O U !

You're welcome. :)

Why don't you make a 'num_posts' column for each topic and increment/decrement it when a new post is added/deleted?
It's way faster to control it that way than doing it dynamically.