Display rank value

[lukep11a]

Hi, I am running a fantasy football league and on a users account page I want to display what rank that user currently is overall, does anybody know how I can modify the code below to display the rank of the current user?

 

<?php
        $query = "SELECT SUM(teams.points) FROM selections, teams WHERE selections.userid = '{$_SESSION['userid']}' AND (selections.groups = teams.groups)";
        $result = mysql_query($query) or die(mysql_error());
        
        $row = mysql_fetch_assoc($result);
        ?>
        <p class="scoring"><?php echo $row['SUM(teams.points)']; ?></p>

[AyKay47]

without knowing your table setup its hard to say, you could probably grab all the rows and order them by the sum of the points that they have, then print what number row that they are

[lukep11a]

This is the code that grabs all rows and orders by sum of the points, do you know how I can print what number row the current user is??

 

<?php

	$query = "SELECT SUM(teams.points) FROM login, selections, teams WHERE login.userid = selections.userid AND selections.groups = teams.groups GROUP by login.userid ORDER by SUM(teams.points) DESC";

	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_assoc($result))
        {

        }
        ?>

[Maq]

Unless you have it in your DB, just use PHP.  Have a variable $i before the while and increment it by 1 every loop.

[lukep11a]

could you show me how to write this please?

[Maq]

could you show me how to write this please?

Sorry I misunderstood your question.  You would have to use MySQL variables like this:

http://stackoverflow.com/questions/1416854/how-to-find-out-the-rank-of-a-row-based-on-a-mysql-sum

[lukep11a]

I have read that and it was helpful, thanks, but it is slightly different to what I am trying to do. I have now included a variable in my code to count the rows, but do you know how I would display the number of the row for the current user (i.e where the session userid = login.userid)? It is currently displaying the number '1'

 

<?php

	$query = "SELECT SUM(teams.points) FROM login, selections, teams WHERE login.userid = selections.userid AND selections.groups = teams.groups GROUP by login.userid ORDER by SUM(teams.points) DESC";

	$result = mysql_query($query) or die(mysql_error());
	$counter = 1; // set a counter value
	$row = mysql_fetch_assoc($result);
        ?>
        <p class="scoring"><?php echo $counter; //output the counter ?></p>