[SOLVED] PHP Mysql Count Query

[noj75]

Hi all,

 

I have a database that users enter records into. I want to list the top 5 record submitters but am unsure how to do it.

 

Each time they enter a record their username and id is also entered so thats the basis of the query I believe.

 

I want to display the results like so:

 

Username: 21 Records

Username: 19 Records

 

and so on..

 

The query I need is stumping me a little. I understand that i first need to find out who has the most entries, how many they have and then display the results. Displaying the results is not a problem it is just the query itself as I am unable to use "WHERE id='blah'" etc.

 

Anyone any ideas on this query?

 

Regards

[GingerRobot]

This isn't really a PHP question then is it? It's a MySQL question.

 

You'd want a GROUP BY clause, rather than a WHERE clause:

 

SELECT COUNT(*) as cnt, username FROM yourtable GROUP BY username

 

And by the by, when you said you're storing the "their username and id" I assume you mean the user's personal id? In which case, you shouldn't be storing the username and id really -- it's a bit of a waste. You'd be better off storing just the id and selecting the username with a join.

[noj75]

Thanks for the reply Ben.

 

where do I go from here? I have no clue at all now. How do I get from that query to listing the top 5 entries by the user?

 

Appreciate your valuable time!

[GingerRobot]

Thanks for the reply Ben.

 

where do I go from here? I have no clue at all now. How do I get from that query to listing the top 5 entries by the user?

 

Appreciate your valuable time!

 

Well you'll then need ORDER BY and LIMIT clauses:

 

SELECT COUNT(*) as cnt, username FROM yourtable GROUP BY username ORDER BY cnt DESC LIMIT 5

[noj75]

Think I have sussed it:

 

<?php

$qry = "SELECT COUNT(*) as cnt, username FROM $table GROUP BY username ORDER BY cnt DESC LIMIT 5";
$res = mysql_query($qry);
while ($row = mysql_fetch_array($res)) {

echo $row['cnt'].' '.$row['username'].'<br />';

}
?>

 

Look ok to you Ben? It seems to be working fine.

[GingerRobot]

Look ok to you Ben? It seems to be working fine.

 

Well given that it's the exact same query i posted, i'd say so

 

Don't forget to mark as solved.

[noj75]

Well given that it's the exact same query i posted, i'd say so

 

LOL by "sussed it" I meant the stuff after the query. I got myself stumped on how to display it  :-\

 

Thanks very much for your help with the query mate, very kind.