Ranks

[Kingy]

I have some stats for an irc server, which are stored in a mysql database.

 

Table looks like:

User | Lines | Characters

 

When a user types - `mystats, it retrieves his information from the mysql database alll good

 

It will then display - *user* you have written X lines and X characters at an average of X.XX characters per line.

 

That works perfectly fine. What i would also like it to do is say Your rank is: X

 

but i have no idea where to start? hints or tips anyone

[predator]

firstly what are they being ranked on? Then we can assist u

[teng84]

you have to store every person who types in your ? and and order them by highest to lowest..

is this hard? knowing you're able to know the x and y thing 

[Kingy]

ordering by lines written

 

yes it stores and yes it works, when someone types a message in irc, the php bot will pick it up find the user who wrote it and add 1 to there line count.

 

Pretty simple and it works, i have made a top 10 ranking which works perfectly well, but i would just like it to display the one rank for the person

[hitman6003]

Think this might work.  Barand will be of more help as he is scary good at SQL...

 

SELECT COUNT(id) AS rank
FROM (
	SELECT (characters / lines) AS avg_chars
	FROM table_name
)
WHERE avg_chars > (
SELECT (characters / lines)
FROM table_name
WHERE username = 'user'
)

 

May be thrown off by ties.

[predator]

well u got a top 10 rank u are pretty much there just use a loop to find the user in the listing whilst u increamment a var that will give them there position (just remember before you show them their ranks do $i+1 or else it will show them one under than what they actually are)

[Kingy]

yeah ok well for the top 10 ive got

 

$rank = 1;

 

while ... {

$rank $user($lines)

$rank++;

}

 

so that works perfectly well

 

but when trying to do this for individual rank, would i have to make a completely different sql query, because right now i am just going select * from stats where user=$user which will only give me the info for that person not everyone else..

[predator]

yeah u could have another sql statement running allong side that to check for there rank (is probably not the cleanest way to do it but it is the fastest to get working)

[hitman6003]

See this page:

 

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

[Kingy]

gosh! those guys really are quite good at mySQL, ive confused myself senseless now

[Kingy]

ok im trying but really getting nowhere :S

 

$query = "SELECT count(*) as rank from (select * from stats where written > (select written from stats where user='Kingy') order by written desc)";

 

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

 

that code gives the error - Every derived table must have its own alias

 

also how would i display the above??

[teng84]

OK ill try i..

 

$query = 'select * from user oder by rank desc;

$query =mysql_query($query);

while($row = mysql_fetch_array($query )){

    ++$x;

  if ($row[id] == yourid){

    echo 'rank'.$x;   

    break;

  }

}

 

this will echo the rank of specific user

[Kingy]

thanks that works well on a test.php page, but for some reason as soon as i put the exact same code into the bot.php it starts saying Rank <userid> instead of rank :s