Counting who has the most

[EchoFool]

I am trying to make a voting system where by a query lists who has the mosts votes from other users with a simple table like this:

 

UserID | VotedFor

 

 

And basically then i trying to query to get the UserID's from that table in DESC order. But i don't know how to collect each userid that has greater than > 0 and then also sort it in order of total votes nor how to count how many they recieved per userid..

 

The end result should show the example of:

 

User 1 : Total Votes: 100

User 2 : Total Votes: 50  etc

 

Whats the best way to do such a thing?

[Maq]

$query = "SELECT VotedFor, Count(UserID) FROM your_table GROUP BY VotedFor"; 

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo "User ". $row['COUNT(UserID)'] ." : Total Votes: ". $row['VotedFor'] ."
";
}

 

 

But i don't know how to collect each userid that has greater than > 0

 

A userid with > 0 votes? :

 

$query = "SELECT VotedFor, Count(UserID) FROM your_table GROUP BY VotedFor"; 

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
   if($row['COUNT(UserID)'] > 0) {
echo "User ". $row['VotedFor'] ." : Total Votes: ". $row['COUNT(UserID)'] ."
";
   }
}

 

[EchoFool]

Thats not working on my screen its loading Count(UserID) as just counting how many users there are in the table regardless of who voted.

 

The VotedFor field is the user ID that the person has voted for not how many votes they have tis why it might explain the confusion. =/

[sqlnoob]

perhaps SUM and HAVING command are what you need, or perhaps you need to add a WHERE clause

 

I'm not terribly sure what you want to achieve with only those 2 columns and what values they can have. Can we assume that VotedFor holds a UserID?

 

Perhaps try GROUP BY UserID instead of VotedFor if that helps

 

Here is a good tutorial for those commands.

http://www.1keydata.com/sql/sql.html

[fenway]

I'm so very confused... how can your group by be getting any people with no votes? There wouldn't be any records in the table???