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

Recommended Answers

All 4 Replies

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.

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";
Member Avatar for 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.

thank you all , solved

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.