top 10 users

[SamiBH]

Hello

I want to learn how to make a script, that reads 2 tabels to find and print top 10 users

Table1:
postname - userid
Tabel2:
username - id

( userid = id )

the script needs to count and print top 10 users with most posts

#1 userA 1000 Posts
#2 userB 900 Posts

***
so far

$qry = "SELECT *, COUNT(userid) as `cnt` FROM table1, table2 WHERE userid = id GROUP BY userid ORDER BY cnt DESC LIMIT 0,10";";
$result = mysql_query($qry) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["cnt"];

}

mysql_free_result($result);

this code don't print username only id, i want username
also how can i make it print into to columns "username" "#posts"
this code print like this

3333225614848152238156134101321073301129436232119

thank you

[amd_k8]

I am always using separate queries, so as my example would be:
1. Get all users id's.
2. Foreach user id, get posts count. Store the id's and posts count into arrays.
3. Sort the array by descendant.

[cwarn23]

Try making this your query and see what error mysql reports back.

$qry = "SELECT `userid`, COUNT(*) as `cnt` FROM table1, table2 WHERE userid = id GROUP BY userid ORDER BY cnt DESC LIMIT 0,10";

[diafol]

this code don't print username only id, i want username
also how can i make it print into to columns "username" "#posts"
this code print like this

The reason it prints userid instead of username is because you specified userid

echo $row["userid"];

To format output:

$output = "<table>"; //include any table header cells here too
while(..){
  $output .="<tr><td>{$row['username']}</td><td>{$row['posts']}</td></tr>";
}
$output .= "</table>";
//echo $output where required in the page

The above does't beautify html nor is it a 'nice' xhtml table as it doesn't include thead/tbody etc.

[SamiBH]

thank you all , solved