[SOLVED] mysql problem

[DEVILofDARKNESS]

Hi,

 

I have a databse where my users are stored in,

now I want to make some ranking list, based on the value in the ammount row,

 

For example:

user_id|user_name|ammount

----------------------------

1        admin        20

2        mod          30

3        tester        5

 

The ranking would be: 

1: Mod

2: admin

3: tester

 

How can I select the place?

if I want to know from tester it would return 3, Mod would return 1, ...

 

I currently have this

"SELECT ??? FROM users WHERE user_id = '$user_id' ORDER BY ammount DESC"

[Mchl]

http://arjen-lentz.livejournal.com/55083.html

[revraz]

SELECT user_name FROM table ORDER BY ammount DESC

[Mchl]

Except it won't give a rank

[corbin]

There was a multipage thread about game rankings that would work perfectly for this situation.  I don't feel like searching though.

[DEVILofDARKNESS]

well, on the site Mchl had give me,

thay said it should go with:

$query = "select count(*) RANKING from users WHERE user_name = 'Darragh' order by ammount desc";

 

It doesn't work, and I have a question,

what does the word RANKING doing there?

 

I also tried to figure it out an my own, with the solution from that site.

$query = "SELECT COUNT(*)+1 AS ranking
  FROM users
WHERE ammount > (SELECT ammount FROM users WHERE user_id= (SELECT COUNT(user_id) FROM users ORDER BY ammount DESC))";
$result = mysql_query($query);
while($topusers = mysql_fetch_array($result)) { # SELECT top 10 users descending with on 1 the user with the most posts.

 

didn't work either... :S

[Mchl]

Go with the solution Giuseppe Maxia (gmaxia) suggests on that page.

[DEVILofDARKNESS]

Which one, ? he has posted a lot of solutions...

 

if I would use this one:

select count(*) as rank from 
    (select * from users where score > 
       (select score from users where user='Arjen') order by score desc)as s;

 

It would be ok, for my member page,

but on the statistics page to show the best 10 it wouldn't go at all!!!

how should I do it that way?

I want to show the top 10 with names, ammount of posts, and the nuber in rank.

[kickstart]

Hi

 

Just had a quick play, and think this gives you what you want:-

 

SELECT users, score, (
SELECT count( * ) 
FROM users b
WHERE a.score > b.score
) AS rank
FROM users a
WHERE 1 
ORDER BY rank

 

Probably hideously inefficient though (count(*) is generally pretty inefficient, and here you are potentially doing it thousand of times on a single piece of SQL). Other issue will be people with exactly the same score.

 

All the best

 

Keith

[DEVILofDARKNESS]

It doesn't although thanks for try to help me

[kickstart]

Hi

 

What is the error you get? I tried similar SQL on a table I have set up and it seemed to work. Could be that I made a typo when changing the field names to the ones you were using.

 

All the best

 

Keith

[DEVILofDARKNESS]

possibly it is just my fault,

the score record is in the users table...

can you make it that way?

the table looks like thhis:

[user_id] [user_name] [score] [tel] [place] [age] [..]

[kickstart]

Hi

 

Will try

 

SELECT user_name, score, (
SELECT count( * ) 
FROM users b
WHERE a.score > b.score
) AS rank
FROM users a
WHERE 1 
ORDER BY rank

 

All the best

 

Keith

[DEVILofDARKNESS]

well if I do it that way

SELECT user_name, ammount, (
SELECT count( * )
FROM users b
WHERE a.ammount > b.ammount
) AS rank
FROM users a
WHERE 1
ORDER BY rank

 

it gives me a name from one of the users???

 

(actually the ranks are based on the column ammount, so I changed all score by ammount,  and am I supposed to do something with rank?

[kickstart]

Hi

 

Think that should work.

 

Quick explanation of what it is doing

 

SELECT count( * )
FROM users b
WHERE a.ammount > b.ammount
) AS rank

 

That bit is doing a subselect to count all the users with a score higher than the user in the outer query (that is why there is an "a" and "b" after the "FROM users").

 

So the outer SELECT gets a list of all the user names, their ammount field and their rank (which is the result of the subselect), and the whole lot is ordered by the rank.

 

All the best

 

Keith

[DEVILofDARKNESS]

I believe what you're telling, me,

but it returns me a username from the database...

 

or should I only use

SELECT count( * )
FROM users b
WHERE a.ammount > b.ammount
) AS rank

[kickstart]

Hi

 

No, that bit gets the rank for a particular user.

 

The SQL should give you the user name, their ammount and their rank.

 

If you run it in phpMyAdmin what do you get?

 

All the best

 

Keith

[DEVILofDARKNESS]

I found the bug

-the code had to be

SELECT user_name, ammount, (
SELECT count( * )
FROM users b
WHERE a.ammount > b.ammount
) AS rank
FROM users a
WHERE user_id = '$user_id'
ORDER BY rank DESC

  (DESC, and the were clause where forgotten or wrong) probably my fault.

 

-I had made it a fetch_row instead of a fetch_array! Stupid Me!

 

Thanks a lot!

 

but still one prob:

it gives me this:

 

user_name ammount rank

Thomas 8         8

Astrid 8                8

Nathalie 5         7

Clara         3         3

BAS         3         3

Darragh 3         3

test         3         3

Alessandra 2         2

Anna         1         0

Lieven Moens 1 0

 

and the ranks have to be upside down, the one with the most ammount should have 1.

[DEVILofDARKNESS]

found it, it should be a.ammount < b.ammount

and COUNT ( * ) + 1