Find biggest poster

[verdrm]

Hi,

 

I need to find the blog poster in my MySQL table with the most # of posts. How would I go about doing that?

 

id  username    post    timestamp

[Maq]

Assuming 'post' is number of total posts.

 

SELECT username from table ORDERY BY `post` DESC LIMIT 1

[killah]

Do you currently count how many post's the user make's? If so:

 

$query = mysql_query('SELECT `username` FROM `table` ORDER BY `post` DESC LIMIT 1', $resource_link);
$user = mysql_fetch_assoc($query);
echo $user['username'];

[bibliovermis]

Use an associative array to tally the records, then reverse sort the array while preserving the keys.

 

$userposts=array();

$result=mysql_query ('select `username` from `table`');

while ($row = mysql_fetch_assoc($result)) $userposts[$row['username']]++;

arsort($userposts);

list($user, $count) = each($userposts);

[Maq]

Use an associative array to tally the records, then reverse sort the array while preserving the keys.

 

$userposts=array();

$result=mysql_query ('select `username` from `table`');

while ($row = mysql_fetch_assoc($result)) $userposts[$row['username']]++;

arsort($userposts);

list($user, $count) = each($userposts);

 

That's the most inefficient way to solve this problem.  Why have MySQL return ALL the records when you only need 1...  What if there's 80,000 users, are you going to put them in an array?

[bibliovermis]

That's the most inefficient way to solve this problem.  Why have MySQL return ALL the records when you only need 1...  What if there's 80,000 users, are you going to put them in an array?

 

Which 1 record contains the tally? Why do you assume that the 'post' field is a count of all posts made, rather than the post itself?

 

Why would verdrm be asking how to find the biggest poster if it was a simple matter of sorting by the 'post' field?

 

I stated to only pull the 'username' field because of efficiency. There is no need to pull the content when you are only interested in the count.

 

This is a PHP problem, not a MySQL problem.

[Maq]

Which 1 record contains the tally?

 

I don't know, that's why I said, "Assuming 'post' is number of total posts.".

 

Why do you assume that the 'post' field is a count of all posts made, rather than the post itself?

 

Same reason you assumed otherwise.  Because the OP didn't specify, it doesn't matter anyway, you can still extrapolate the info with a single query.

 

I stated to only pull the 'username' field because of efficiency. There is no need to pull the content when you are only interested in the count.

 

I'm not.  Do you not understand the code I provided?  You're the one pulling extra information out when you only need MySQL to select 1 username.  Not only that you're putting all of them in an array and performing multiple methods on it.  Very inefficient... 

 

This is a PHP problem, not a MySQL problem.

 

Wrong.

 

 

 

 

[kickstart]

Hi

 

Assuming that it is a table of posts, with `post` being the actual post and not a count:-

 

SELECT username, count(id) AS post_count

FROM `table`

GROUP BY username

ORDER BY post_count DESC

LIMIT 0,1

 

All the best

 

Keith