[SOLVED] selecting rows by order

[almightyegg]

I want a sort of, 'Hall of Fame' type script. So I want to know how I can select a certain places rank? eg...

For the peson in first place

$first = mysql_query("SELECT MAX(col) FROM users LIMIT 1");
$first = mysql_fetch_array($first);
// so now:
echo "First Place: $first[username]";

 

I want to know how I could select, say, the 34th ranked person?

[DeepakJ]

LIMIT 34,1

[almightyegg]

I made a start...

 

<?
$start = $_GET['start'];
if(!$start){
$start = 1;
}
$first = mysql_query("SELECT MAX(level) FROM users LIMIT $start,1") or die(mysql_error());
$sec = mysql_fetch_array($first);
echo "$start. $sec[username]<br>";

$number = $start+1;
$first = mysql_query("SELECT MAX(level) FROM users LIMIT $number,1") or die(mysql_error());
$sec = mysql_fetch_array($first);
echo "$number. $sec[username]<br>";

$number = $start+2;
$first = mysql_query("SELECT MAX(level) FROM users LIMIT $number,1") or die(mysql_error());
$sec = mysql_fetch_array($first);
echo "$number. $sec[username]<br>";

$number = $start+3;
$first = mysql_query("SELECT MAX(level) FROM users LIMIT $number,1") or die(mysql_error());
$sec = mysql_fetch_array($first);
echo "$number. $sec[username]<br>";

$number = $start+4;
$first = mysql_query("SELECT MAX(level) FROM users LIMIT $number,1") or die(mysql_error());
$sec = mysql_fetch_array($first);
echo "$number. $sec[username]<br>";
?>

 

 

But all this echoes out is:

1.

2.

3.

4.

5.

[almightyegg]

I ond an easier way to do it through while loop. thanks for the help

[akitchin]

one query can handle this - not sure if this is how you solved it, but it's an option (i realize you've already marked this solved):

 

SELECT username FROM table ORDER BY level DESC LIMIT 10

 

this will process your list from highest 'level' column value to lowest, for the top ten (due to the LIMIT clause).

[almightyegg]

Thanks akitchin, it's sort of how I did it. But I've realised there is one thing I still can't do, that is to select how far in the ranks you are. So the user would see: Your rank: X

[akitchin]

this can be done very easily in PHP.  just start a counter at 1, increment it at the end of each cycle (since you need to use a while() loop to process the query results anyway):

 

$rank = 1;
while ($row = mysql_fetch_something($blah))
{
  echo $rank.': '.$row['username'].'<br />';
  $rank++;
}

 

even easier, you could just let HTML handle your list ordering:

 

echo '<ol>';
while ($row = mysql_fetch_something($blah))
{
  echo '<li>'.$row['username'].'</li>';
}
echo '</ol>';

[almightyegg]

But that would echo out everybodies rank. I just need a way of finding the current users rank.

[akitchin]

then you can simply select the COUNT() of the number of rows above the user's current position:

 

SELECT (COUNT(*) + 1) AS rank FROM table WHERE level > (SELECT level FROM table WHERE username='$username_var')

 

this requires subquery support.  keep in mind this doesn't account for ties at all (although i doubt your current code does either, as it's a bit more complicated where ties are involved).

[almightyegg]

I editted it a bit because some of the variables I had already selected elsewhere...

 

$memberpos = mysql_query("SELECT COUNT(*) AS rank FROM users WHERE level > '{$mem['level']}'") or die(mysql_error());
echo "$memberpos";

 

This echoes out Resource id #13

[Barand]

echo mysql_result ($memberpos, 0);

 

 

@akitchin

 

It should still give the correct results even with ties

[pre]

 

        user  |  score |  rank

        ------+--------+-------

        a    |  20  |    1

        b    |  18  |    2=

        c    |  18  |    2=

        ME  |  15  |    4=

        d    |  15  |    4=

        e    |  15  |    4=

        f    |  12  |    7

[/pre]

ME, d and e should still get a rank of 4

[akitchin]

ah, touché my good man.  thank you for proving me wrong (because it means my method was more robust than i thought).

[almightyegg]

Thanks, that worked For some reason the person in 1st place is level 78 yet 2nd place is 500....why is it mixing up??

 

1. User1 78

2. User2 500

3. User3 499

4. User4 342

5. User5 12

Your rank: 2

 

<?
$start = $_GET['start'];
if(!$start){
$start = 0;
}
$end = $start+5;
$i = $start+1;
$level = mysql_query("SELECT * FROM users ORDER BY level DESC LIMIT $start,$end") or die(mysql_error());
while($lvl = mysql_fetch_array($level)){
echo "" . $i++ . ". <a href='/player/view.php?id=$lvl[id]'>$lvl[username]</a> $lvl[level]<br>";
}
$memberpos = mysql_query("SELECT COUNT(*) FROM users WHERE level > '{$mem['level']}'") or die(mysql_error());
$mempos = mysql_result($memberpos, 0)+1;
$memposi = $mempos-1;
echo "Your rank: <a href='index.php?start=$memposi'>$mempos</a>";
?>

[Barand]

Are you storing them as varchar instead of INT?

[almightyegg]

ah they were stored as Char thanks that's all done now