Jump to content

Archived

This topic is now archived and is closed to further replies.

ari_aaron

SQL Stuff....

Recommended Posts

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?

Share this post


Link to post
Share on other sites
If your only choices for points are 1 or -1, couldn't there potentially be hundreds of people with the 'most' points? 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.