**solved** ranking

[Maverickb7]

I have a table full of lots of rows. I want to tell some code to look at one column (which is a number) and then rank it X out of all the rows. So lets say I have 200 rows and I ask it to look at a row that includes the 5th highest number out of all the rows. I want it to output 5. Anyone know how to do this?

[Barand]

Example

I have a table of scores with column called score. The 5th highest value in that table is 37581.

[code]$scoreval = 37581;

$sql = mysql_query("SELECT COUNT(*)+1 FROM scores WHERE score > '$scoreval'");

echo "$scoreval is ranked ". mysql_result ($sql, 0);[/code]

[Maverickb7]

[!--quoteo(post=367638:date=Apr 23 2006, 04:13 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Apr 23 2006, 04:13 AM) [snapback]367638[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Example

I have a table of scores with column called score. The 5th highest value in that table is 37581.

[code]$scoreval = 37581;

$sql = mysql_query("SELECT COUNT(*)+1 FROM scores WHERE score > '$scoreval'");

echo "$scoreval is ranked ". mysql_result ($sql, 0);[/code]
[/quote]

Alright... that code seems to do what I want but I have a problem. I don't want to manually add the $scoreval as that number is going to increase very often and very fast. I have a row that includes the number of comments a user has posted and when a user is logged in I want it to rank the user x out of all members. I want it to rank the user by the comment count so I do not want to specify the $scoreval as the highest number is going to change constantly.

[Orio]

This will be your statement:
$query=[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]`table`[/color] [color=green]ORDER BY[/color] [color=red]'score'[/color] [!--sql2--][/div][!--sql3--]
so- $result=mysql_query($query);
And then :
[code]<?php
$num=$_GET['row_number'];
$i=0;
$rows=mysql_num_rows($result);
while($i<$rows)
{
$id=mysql_result($result,$i,"ID");
if($id==$num){
$i=$row; //break while loop
echo ($i);
}
else{$i++};
}
};
[/code]
Hope it helps,
Orio.

[Maverickb7]

I have no clue what your code is doing... could you explain it a bit? (Sorry.. still learning and want to know what i'm looking at before putting it to use)

[Orio]

Well first it gets from the table everyone ordered by their score.

Then this line:
$num=$_GET['row_number'];
Is defining $num as the row number you want (I assumed you do it with a form).

And then this part:
[code]$i=0;
$rows=mysql_num_rows($result);
while($i<$rows)
{
$id=mysql_result($result,$i,"ID");
if($id==$num){
$i=$row; //break while loop
echo ($i);
}
else{$i++};
}
};
[/code]Starts a loop- $rows is the number of results you got from the query and i is just 0. It basicly checks every row and row from its ID column value (mysql_result($result,$i,"ID")) and checks if what you asked for is the samw as the ID we got. If it is, it brakes the loop and echos the ID number. Go to www.php.net and search the mysql_result function if you are still not understanding.

Orio.

[Maverickb7]

[!--quoteo(post=367718:date=Apr 23 2006, 12:54 PM:name=Orio)--][div class=\'quotetop\']QUOTE(Orio @ Apr 23 2006, 12:54 PM) [snapback]367718[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Well first it gets from the table everyone ordered by their score.

Then this line:
$num=$_GET['row_number'];
Is defining $num as the row number you want (I assumed you do it with a form).

And then this part:
[code]$i=0;
$rows=mysql_num_rows($result);
while($i<$rows)
{
$id=mysql_result($result,$i,"ID");
if($id==$num){
$i=$row; //break while loop
echo ($i);
}
else{$i++};
}
};
[/code]Starts a loop- $rows is the number of results you got from the query and i is just 0. It basicly checks every row and row from its ID column value (mysql_result($result,$i,"ID")) and checks if what you asked for is the samw as the ID we got. If it is, it brakes the loop and echos the ID number. Go to www.php.net and search the mysql_result function if you are still not understanding.

Orio.
[/quote]

so how does this do what I asked? I want this to check a certain column in my table tow and tell me what rank it is out of all the rows. Let's say I have 8 members with the following details....

username | posts
------------------------------------
user1 | 10
user2 | 123
user3 | 3423
user4 | 4
user5 | 583
user6 | 69
user7 | 94
user8 | 383

now lets say user8 is logged in... I want it to check the posts column and compare it to all the other users. Then it should output, "You are ranked #6 out of #8". Get what I'm saying?

[Barand]

Assuming that table is called "post_totals"

[code]$res = mysql_query("SELECT COUNT(*) FROM post_totals");
$total_users = mysql_result($res, 0);

$username = 'user8';

$res = mysql_query("SELECT COUNT(*)+1
             FROM post_totals
             WHERE posts >
                      (SELECT posts FROM post_totals
                       WHERE username = '$username') ");

$rank =  mysql_result($res, 0);

echo "You are ranked #$rank out of $total_users";[/code]

[Maverickb7]

Thanks Barand I'll give it a try.

[Maverickb7]

Works perfect.. thanks!