top 10 users

[Sam1]

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

 

 

Thank you

[JAY6390]

SELECT *, COUNT(userid) as `cnt` FROM table1, table2 WHERE userid = id GROUP BY userid ORDER BY cnt DESC

 

Something along those lines should do it

[ignace]

You should avoid calculation as much as you can. Instead add a post_count to the users table and then use

 

ORDER BY post_count DESC

 

Increment the post_count on each new post the user makes. This is also usefull for other business logic for example if your post count should not increase if you post in a certain forum.

[ToonMariner]

MYSQL is perectly happy performing calculations - and why bother storing such trivial data as post count?

 

$qry = "select DISTINCT(`table2`.`username`) AS `username`, COUNT(`table1`,`userid`) AS `postcount` FROM `table2` LEFT JOIN `table1` ON `table1`.`userid` = `table2`.`userid` GROUP BY `table2`.`userid` ORDER BY `postcount` DESC LIMIT 0,10";

 

Something like that should do the trick...

[Sam1]

WOW...you guys are amazing....thank you so much

 

I'm Beginner so please Be patient with me

 

how to print(echo) query result?

 

$qry = "select DISTINCT(`table2`.`username`) AS `username`, COUNT(`table1`,`userid`) AS `postcount` FROM `table2` LEFT JOIN `table1` ON `table1`.`userid` = `table2`.`userid` GROUP BY `table2`.`userid` ORDER BY `postcount` DESC LIMIT 0,10";

 

 

[ignace]

and why bother storing such trivial data as post count?

 

This is also usefull for other business logic for example if your post count should not increase if you post in a certain forum. Being one reason, second reason would be because most likely it's going to appear as a widget in the left or right nav of the website and when you have many visitors (and thus many posts) your using your database intensively (calculating the total post_count over and over) to provide you with data that is trivial to your website.

[Sam1]

I tried

get_result ("select DISTINCT(`table2`.`username`) AS `username`, COUNT(`table1`,`userid`) AS `postcount` FROM `table2` LEFT JOIN `table1` ON `table1`.`userid` = `table2`.`userid` GROUP BY `table2`.`userid` ORDER BY `postcount` DESC LIMIT 0,10");

echo $query;

 

did not work

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource

[Sam1]

WOW...you guys are amazing....thank you so much

 

I'm Beginner so please Be patient with me

 

how to print(echo) query result?

 

$qry = "select DISTINCT(`table2`.`username`) AS `username`, COUNT(`table1`,`userid`) AS `postcount` FROM `table2` LEFT JOIN `table1` ON `table1`.`userid` = `table2`.`userid` GROUP BY `table2`.`userid` ORDER BY `postcount` DESC LIMIT 0,10";

[Sam1]

JAY6390

 

I tried

 

 

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

echo $result;

 

and it prints "Resource id #68 " !!!

 

what is my mistake?

 

[trq]

Your mistake is that mysql_query returns a result resource, you can't simply echo them. You'll need to turn your result resource into an array with mysql_fetch_assoc or similar, from there you can get to your data via the arrays indexes.

[Sam1]

Your mistake is that mysql_query returns a result resource, you can't simply echo them. You'll need to turn your result resource into an array with mysql_fetch_assoc or similar, from there you can get to your data via the arrays indexes.

 

thank you for your help

 

***

 

like this?

 

$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);

 

and how can i make the result into 2 columns , because now it like this

3333225614848152238156134101321073301129436232119 

 

thank you