Get row number???

[Vivid Lust]

Hi, I have this page:

 

http://*domain_removed*/projects/phishfriends/leader.php

 

What it does is gets the user info from the database and orders it in order of phishes and then displays.

 

It also has the rank which goes up everytime.

 

Is there any way in which I can have some script which gets the rank of a certain name????

 

Code:

 

<?php
require("includes/db.php");
$sql = "select * from users order by `s_phish` desc";
$result = mysql_query($sql, $link)
or die(mysql_error());
?>
<center>
<table cellpadding="0" width="50%" cellspacing="0">
<tr>
<td>
Rank
</td>
<td>
Name
</td>
<td>
Phishes
</td>
</tr>
<?php
$rank = 0;
while ($row = mysql_fetch_array($result) AND $rank < 20) {
     $rank++;   // adds 1
     echo "<tr><td>";
     echo $rank;
     echo "</td><td>";
     echo "  <a href='profile.php?id=";
     echo $row['id'];
     echo "'>";
     echo $row['name'];
     echo "</a> ";
     echo "</td><td>";
     echo $row['s_phish']; // put HTML break to end line
     echo "</td></tr>";
     echo "<br />"; 
}
?>
</table>
</center>

 

Thanks for any help in advanced.

 

Edit (Daniel0): Removed domain by request of user.

[redarrow]

if in the database u got the id off the person that got ranked, then add id='$id' to the select query......................................................

 

this will only work if users got to be logged in to rank other users.........................

 

 

 

[rarebit]

if you selected where the phishes are greater than 'name's phishes (with ordering), then technically they'd be the last one...

 

 

if you only want the position then do a count, otherwise maybe an intermediary table might help here, but me not know them tings!

[Vivid Lust]

Ok, so If I had something like this on the script:

 

While name !== name

count++

 

Then It would keep on counting until it found the name of the person the rank was for?

 

Would that work???

 

Thanks.

[Vivid Lust]

I wrote this, just displays 0, any help appreciated

 

<?php
require("includes/db.php");
$sql = "select * from users order by `s_phish` desc";
$result = mysql_query($sql, $link)
or die(mysql_error());
?>
<?php
while ($row = mysql_fetch_array($result)){
$rid = $row['id']; //id of member
}
$rank = 0;
while ($row = mysql_fetch_array($result) AND $rid !== 5 ) {
     $rank++;  

}
echo $rank; 
?>

[Vivid Lust]

I'm asumming this is too hard for everyone :S

[wildteen88]

You're not explaining yourself clearly. I'm not understanding you, however the example code you posted is not recommended for use. Perhaps you mean

<?php
require("includes/db.php");
$sql = "select * from users WHERE id != 5 order by `s_phish` desc";
$result = mysql_query($sql, $link)
or die(mysql_error());
?>
<?php

echo  mysql_num_rows($results) . ' results returned:<br />';

while ($row = mysql_fetch_array($result))
{
    echo '<pre>'.print_r($row, true).'</pre>';
}

?>

[Vivid Lust]

I want to try and get the rank of a member.

 

Say I had this database:

 

id  score

1    10

2    34

3    3

3    0

 

My mysql query would get that data and order so its like this:

 

id  score

2    34

1      10

3      3

4        0

 

Now, I want to specify a certain ID and find its rank.

 

Say i wanted to find the rank of id 4, the program would return the value 4, if i wanted the rank of id 2, the code would return the value 1

 

This is what im trying to do, and I cant figure it out.

 

I hope this is clearer

And some kind person is willing to help!

[Barand]

SELECT COUNT(*) + 1 as rank

FROM users

WHERE score > (SELECT score FROM users WHERE id = '$userid')

[Zane]

Well you've already ordered by phish .. descending...which is what you want

 

all you need to do is put these results into an array and you'll be able to pull them out by number....or rank if you will.

 

 

Just a modification of wildteens script

while ($row[] = mysql_fetch_assoc($result)) { //If you want to do something during the loop, do it}

Now the person who is ranked second will be here

$row[1]

and his score will be $row[1]['s_phish']

[Vivid Lust]

zanus, I want to get the rank of a specific id, Wouldnt that find the id of a specific rank???

 

And Barand, before trying that, what does "as rank" mean/do because there isnt anything set as "rank"

 

 

Thanks both.

[Barand]

"rank" is an alias for the expression "COUNT(*) + 1" so it appears in your results as a column called "rank"

[Vivid Lust]

Ok, im not that tech savvy, sorry, didn't really understand what you said, anyway, how would I echo the answer of the query??? (Still not that good at PHP/MySQL)

[Barand]

$userid = 3;

$sql = "SELECT COUNT(*) + 1 as rank
           FROM users
           WHERE score > (SELECT score FROM users WHERE id = '$userid')";
$res = mysql_query($sql);
if (mysql_num_rows($res) > 0)
{
    echo "User $userid is position " . mysql_result($res, 0, 'rank');
}

[Vivid Lust]

Thankyou loads!!

 

At the moments its echoing the rank, 1 below what it should be, how would I add 1 onto the result?

 

Thanks.

[Barand]

the "COUNT(*)+1" should be adding the 1 for you. If it counts 3 with a higher score then the rank is 4

[Vivid Lust]

Hi, doesn't seem to be working quite right,

 

Have a look here: http://onestamp.co.uk/projects/phishfriends/profile.php?id=2

Then click on the name to see what the actual score is

 

I think the problem is because the value is 0, and it ranks them all the same???

 

How can I correct this

[Vivid Lust]

Please help someone!

[sasa]

Hi, doesn't seem to be working quite right,

 

Have a look here: http://onestamp.co.uk/projects/phishfriends/profile.php?id=2

Then click on the name to see what the actual score is

 

I think the problem is because the value is 0, and it ranks them all the same???

 

How can I correct this

if 2 users have same score do they have same rank or not

if not who has beter rank

[Vivid Lust]

I didnt understand what you are trying to say sasa, sorry.

 

Anyone willing to modify the code for me so it does what I said above??

 

$userid = 3;

$sql = "SELECT COUNT(*) + 1 as rank
           FROM users
           WHERE score > (SELECT score FROM users WHERE id = '$userid')";
$res = mysql_query($sql);
if (mysql_num_rows($res) > 0)
{
    echo "User $userid is position " . mysql_result($res, 0, 'rank');
}

[Zane]

try (COUNT(*)+1)+1

[Vivid Lust]

All this is doing, is adding one onto it, which isnt what I want, sorry.

 

Have a look here: http://onestamp.co.uk/projects/phishfriends/profile.php?id=2

Then click on the name to see what the actual score is. All of the people with the same score, have the same rank!

 

I think the problem is because the value is 0, and it ranks them all the same??? I dont want this, how can I fix this???

 

Thanks

 

[Barand]

Those with same score should have same ranking. e.g.

 

id  score   rank
--  -----   ----
5    300     1
3    250     2
1    200     3=
4    200     3=
6    200     3=
2    180     6

[Vivid Lust]

ok then

[Vivid Lust]

Although now I have the problem that the rank on the leaderboard, isn't the same as the one displayed on the profile... any help please?