How to show how far a number ranks in php/mysql

[t_machine]

hi, i have a database that stores views to a user's profile. Is there a script i could use to sort the views and tell what number the user ranks in page views?

 

example:

user 1 (200 views)

user 2 (400 views)

user 3 (20 views)

user 4 (1000 views)

 

How can I tell where user 2 ranks with php? In this case it would be second..

 

Thanks for any help.

 

[uniflare]

quite simply with a mysql query, you could write a script which does this yourself.

 

mysql_query("SELECT * FROM `profiles` ORDER BY `page_views` DESC;");

 

 

,hope this helps

[t_machine]

Thanks for the reply I know how to order them but how would i user a " WHERE userid='2' " tell where that user's page view rank.

 

Thanks

[uniflare]

oh i see, individually. i dont know of a query that would do this alone, but you could try this:

 

// select all records' usernames
$result = mysql_query("SELECT `username` FROM `profiles` ORDER BY `page_views` DESC;");

// store in an assoc array (named array)
$array = mysql_fetch_array($result, MYSQL_ASSOC);

// set starting rank
$i=1;

// loop through each row and find a username that matches, since its ordered from the query we can just increment the rank by 1 each time.
foreach($array As $row){
      if($row['username'] == $username){ // if username matches return $i ($i = rank)
          return $i;
      }
      $i++; // increment the rank
}

 

 

this is the best i can come up with, hope this helps,

[sasa]

something like this

SELECT COUNT(views) + 1 AS rank FROM table WHERE vews>(SELECT views FROM table WHERE userid=2)

[Kingy]

try this, has worked for me in the past

<?php
$user = USERNAME; //enter the username or however you want to do it
$rank = 0;
$result = mysql_query("SELECT * FROM users ORDER BY page_views DESC");
while($row = mysql_fetch_array($result)) {
  $rank++;
  if ($row['user'] == "$user"){
    echo "$user you are ranked: $rank";
    break;
  }
}
?>

 

don't know if you understand the code or not but i'll explain a little anyway.

 

$user is the user it is looking for this could be anything.. $_SESSION['username'] if you wanted.

then it goes and sets the $rank. 0 first.

does the mysql query listing the rows in page_views order. so it would go in the order.. user4, user2, user1, user3.

it proceedes to go through the mysql rows until it makes a match to whatever was stated in $user adding 1 to the $rank each time it starts again. once it matches the $row['user'] to $user it will output the echo statement

 

$user you are ranked: $rank

 

and then break.

hope this helps

[t_machine]

Thank you all for the prompt replies I went with sasa as it used mysql to do the calculation instead of looping through thousands of results.