league table - multiple members

[maztrin]

hi i am trying to create a league table that shows who has the most votes in a forum.

 

the system works like this:

 

every topic that is created by a member is given a topic id AND a member id of who started the topic. 

every time a topic is voted for the counter will update for that topic. 

 

so in the database every topic created has a unique id, but a member id will show up various times depending on how many topics that member started.

 

so then that means there is more than one counter for each member (see the table example below)

 

table:

mysql table

topic id

member id

counter

 

1

23

2

2

34

3

3

22

7

4

23

3

5

34

2

 

 

so what i was trying to figure out was -  is there a way in  mysql or php to select all the members.

then if there member id occurs more that once just add the counter result together then display this in the league table?

 

like this:

member id

username

votes

23

john

5

22

sally

7

34

jack

5

 

 

 

so i want somehthing like this:

 

query - select all the member id;

query - where the member id occurs more than once add the counters together for that member

query - show only the 5 highest members with the most votes

 

i am thinking that maybe i would need to create some sort of variable in php to hold the counts for each member but this seems impractical.

 

i hope i exlpained what i was looking for properly

 

 

any help is appreciated

thanks

 

[sasa]

SELECT a.member_id, a.username, SUM(b.counter) AS votes FROM members_table AS a
INNER JOIN votes_table AS b ON a.member_id=b.member_id
GROUP BY a.member_id

[maztrin]

SELECT a.member_id, a.username, SUM(b.counter) AS votes FROM members_table AS a
INNER JOIN votes_table AS b ON a.member_id=b.member_id
GROUP BY a.member_id

 

would you be able to explain this a little please

[maztrin]

i should also point out that the username field will have to be taken from another table using a join.