Well, im coding an advertising scheme type web page at the moment. What I need to do is figure out a way to show people how ranked they are in order of the amount of clicks they have got. For example:

Bob has been clicked 5 times
Jim has been clicked 2 times
Lisa has been clicked 2 times
John has been clicked 10 times

The amount of clicks is stored in an integer field name m_clicks.

Now, when Bob logs in, he has the second most clicks so itll say "You are ranked 2 out of 4."

When Jim logs in, he is tied with Lisa so we'll fix that by doing it alphabetically. It will say "You are ranked 3 out of 4."

When Lisa logs in, she will see "You are ranked 4 out of 4."

And when John logs in he will see "You are ranked 1 out of 4."


At the moment I got it working via checking every row and ordering them in an array. But if my site gets bigger, I dont want to do that with 3000 rows, for example. That wouldnt be nice to do every time someone logs in. Im SURE there is an easier way I just cant place my finger on it! Does anyone have any ideas?

Thanks
SoD

PS: I did a search for this but I got some rubbish about cars or something, it didnt help anyway.

Hi,

Proposition :
1) You get the logger id and m_clicks
2) You do a request asking how many people have m_clicks > (your logger m_clicks)

NB : With this system, you can have ex aequo (what seems just fair to me)

Well, that works okay for the main part. But, What happens is:

For Jims rank, he gets #3 and for Lisa's rank, she gets #3 aswell, because they both have 2 people with more clicks than them... And If I do >= instead of > then they both turn out as #4

That is my main problem reallly, getting it to order them even if theres more than one person with the same amount of clicks.


Thanks for the help,
Sod

what should they be ordered by?

why is lisa 4 and jim 3 and not the other way around?

It is absolutely fair that they are both rank 3 if they are 2 people who have more clicks. As they have the same clicks count, you can't order them, and you shouldn't.

Alice 8 clicks
Bob 7 clicks
Jim 5 clicks
Lisa 5 clicks
Tom 4 clicks

should give

Alice rank 1
Bob rank 2
Jim rank 3
Lisa rank 3
Tom rank 5

i was thinking that you could prolly order them by time also ... the one to reach a certain number of licks first will be first in case someone else has the same amount of clicks.

if you onlyu want to order by number ... that they are both the same rank fair and square ...

if you still need to have no more than one person per rank ... think of what else is of interrest to you? you could order the ones with the same rak by time, name, even length of name as an example...

He wants to sort them alphabetically - that's already been stated. Why not combine this with your query, something like:

PHP Code:


$uid = //userid of person you want to rank
$uname = //username of this person
$nclicks = //no of clicks for this person

$result = mysql_query("SELECT COUNT(*) AS num FROM users WHERE numclicks > $nclicks OR (numclicks = $nclicks AND username < '$uname')");

$num = mysql_result($result,0,'num');

$rank = $num +1; 


(That SQL is not tested, I just thought it up so the syntax might not be perfect.)

0beron, because I didnt even know you could do that in sql query But thanks, ill test it and play around with it if it doesnt work.

OmuCuSucu, im not being sexist if thats what you mean, its alphabetical.

Marvin Le Rouge, I see your point, but im going to try this for the moment and if it turns out to be bad then ill follow your idea. Nothing is gained from ranks, you dont get more money or anything, its just a sort of "healthy competition".