SQL Stuff....

[ari_aaron]

Ok, this is rather complicated, and I'm probably not going to explain it very well....

I have a SQL Table. The 2 relevant columns are unam and points. Points can be a 1 or -1 and unam is a username. I want to get the top 10 amounts of points. The problem is that there is no list of usernames. I somehow need to get a PHP script to get the names of the people with the top 10 amount of points.

Can someone give me some help as to how I would do this?

[Wintergreen]

If your only choices for points are 1 or -1, couldn't there potentially be hundreds of people with the 'most' points? 

[ari_aaron]

Ok, I told you I wouldn't explain it well.

A user can be entered many times. For example:

[code]
|------------------|
|  user      | points|
|ari_aaron  | 1      |
|ari_aaron  | 1      |
|Takagi      | 1      |
|ari_aaron  | -1      |
[/code]
In this case, I have 2 points.

[hostfreak]

I think something along the lines of:
[code]
$query = "SELECT points, user, COUNT(*) AS total_points FROM table GROUP BY points WHERE points != '-1' ORDER BY total_points DESC LIMIT 10";
[/code]
Will work, probably with slight modification.

[Wintergreen]

But if you're grouping by points where != '-1' doesn't that mean it ignores the negatives? 

My suggestion would be to remake your system a little and have it modify the points directly instead of always adding another row.  This way each user has their own row with their total score already calculated

[redarrow]

[code]
<?php

//database connection

$query="SELECT * FROM table";
$result=mysql_query($query);

while($record=mysql_fetch_assoc($result)){
if($record['points']=="10"){

$names=$record['user'];

echo "<br>$names<br>";

}
}
?>
[/code]

[ari_aaron]

hostfreak, that will exclude the negitives, as Wintergreen said. Is there a way to include them?

redarrow, that don't look like it does what i want. Can you explain it?

[hostfreak]

Ah, yeah I see now. Sorry, I misunderstood your post. I see now what your doing, if there is a negative you want it to drop one of the positive points? I am not really sure how to do that, let me look into it a bit more and hopefully I will be able to come up with a solution for you (if someone else doesn't in the meantime).

[roopurt18]

I'm not sure if this will have the desired effect or if SUM is a valid MySQL function, but:

[code]
$sql = "SELECT DISTINCT uname, SUM(points) AS total GROUP BY uname ORDER BY total DESC LIMIT 10"
[/code]

[btherl]

I'm with roopurt18 here :)  But I don't think you should include "DISTINCT".  The group by will make usernames distinct already, and I don't think you want point scores to be distinct..

[code]$sql = "SELECT uname, SUM(points) AS total GROUP BY uname ORDER BY total DESC, uname ASC LIMIT 10"[/code]

The "uname ASC" ordering is in case of ties.. you need to decide who gets in the top 10 if positions 9-11 have the same score, for example.

[ari_aaron]

I used your code, btherl, and got
[quote]Can't select from database : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY uname ORDER BY total DESC, uname ASC LIMIT 10' at line 1[/quote]

[btherl]

Oops.. I just copied and pasted.  You need to say which table to select from too :)

[code]$sql = "SELECT uname, SUM(points) AS total FROM table GROUP BY uname ORDER BY total DESC, uname ASC LIMIT 10"[/code]

where 'table' is the name of your table containing the unames and points.

Algorithmically, that will:

1. Fetch all rows from table
2. Find the sum of points for all matching unames, merging all matching unames into single rows.
3. Order the uname, total (which is sum of points) by total descending, then uname ascending as a tie-breaker.
4. Slice off the first 10 results
5. Return those results to you

[ari_aaron]

thank you very much, it works perfectly now!