Complex join

[iBram007]

I have 2 tables, members (id,name) and posts (id,title,text,posterid). I want to list the members by total posts.
1 problem, I don't have a number of total posts in the members table. I suppose I have to work with a join query.

So, I want something like this:

$total_posts = mysql_num_rows(mysql_query("SELECT * FROM posts WHERE posterid='$id'"));
mysql_query("SELECT * FROM members, posts WHERE members.id='$id' ORDER BY $total_posts");

I know this queries are not correct, but It's my way to express what I mean

Hope somebody can help me...

[alexk]

SELECT members.id, COUNT(posts.id) AS posts_num FROM members, posts WHERE posts.posterid=members.id GROUP BY members.id ORDER BY posts_num DESC;

something like that

[alexk]

if you also want to see the ones who didn't post then:

SELECT members.id, COUNT(posts.id) AS posts_num FROM members LEFT JOIN posts ON posts.posterid=members.id GROUP BY members.id ORDER BY posts_num DESC;

tho I'm not sure if that's optimal queries, i've just started with SQL...

[iBram007]

This for() has an unlimited number of counts...

$query = mysql_query("SELECT $table[members].ID, COUNT($table[posts].posterid) AS posts_num FROM $table[members], $table[posts] WHERE $table[posts].posterid=$table[members].ID GROUP BY $table[members].ID ORDER BY posts_num DESC");

$h = mysql_fetch_array($query);

$count = mysql_num_rows($query);

for($i = 0; $count > 0; $i++){
    echo $h[posts_num];
    echo " - ";
    echo $h[username];
    echo "<br>";
} 

[Abstraction]

$query = mysql_query("SELECT $table[members].ID, COUNT($table[posts].posterid) AS posts_num FROM $table[members], $table[posts] WHERE $table[posts].posterid=$table[members].ID GROUP BY $table[members].ID ORDER BY posts_num DESC");

$count = mysql_num_rows($query);
$h = mysql_fetch_array($query);

for($i = 0; $i <= $count; $i++){
    echo $h[posts_num];
    echo " - ";
    echo $h[username];
    echo "<br>";
    $h = mysql_fetch_array($query);
} 

[alexk]

one more fixie...

$query = mysql_query("SELECT $table[members].ID, COUNT($table[posts].posterid) AS posts_num FROM $table[members], $table[posts] WHERE $table[posts].posterid=$table[members].ID GROUP BY $table[members].ID ORDER BY posts_num DESC");

while($fres = mysql_fetch_row($query))
{
    echo $fres[1], " - ", $fres[0], "<br>";
}