database functions: is there one for...

[klaroen]

Oke this is the request:
Is there a query function or code to do this:

I have a database, and there are kind of diffrent number in it: 500 , 360 , 578, 9978, 7589, 125, 154, etc...

Now when I select one of those, can I ask with a code with place he has from the top?
So: I select the number 578 for example, can it tell me what 'rank' it has? so it should be 3th...

Is there a code for that?

[AndyB]

Yes. But we'll need a great deal more information to tell you how. What are those numbers? What is 'rank'. What's the database table structure?

[klaroen]

Imagine: I have a list of products and they all have their price.

[code]
<?php
   $player=$_SESSION['player'];
    $userstats="SELECT * from koc_products where username='$player'";
    $userstats2=mysql_query($userstats) or die("Error while connecting to database! Please report this to an admin!");
    $stat=mysql_fetch_array($userstats2);

   $ranking=[b]thecodeineed[/b]($stat[price]);

print(" The product is our [b]?[/b] most expansive product!");

[/code]

So as you see the code I need should tell me how muchst he is in the list of most expensive products...


Is that understandable?

(ps: rank is the number of how high he is ( how muschest)(is that spelled like that?? )

[.josh]

if you are simply looking for the highest number in the list, you would do like this (assuming "price" is the column name) :
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']select[/span] price [color=green]from[/color] [color=orange]tablename[/color] [color=green]order by[/color] price [color=green]desc[/color] limit 1
[!--sql2--][/div][!--sql3--]
so if you had a column price and it had the following info in it:

price
===
45
453
6432
583
5432
945
34984

that query would return 34984

[klaroen]

yeah, that I know :p... I'll give another example:

In a online game every player has his troops and has his attack power and defence power. Now, if I select the defence from the player that is logged in with query can I get the rank of his defence with a function?
So his defence is like 5009 and there is only one player better with 6582 and a lot players are lower... Now, can I ask my database to count out for me that he's defence is the second highest?
Or for other players how high their defence is (counted in places)...


(get it?)

[.josh]

i don't know if mysql has a built in function for that or not, but i suppose you could write a script in php for that. and even still, there has GOT to be a better way than this, but this is my ghetto code:
[code]
$username = 'klaroen';

$sql = "select * from table order by defense desc";
$result = mysql_query($sql);
while ($info = mysql_fetch_array($result)) {
   $playerinfo[] = $info;
}
$numplayers = count($playerinfo);
$pos = 1;
while ($playerinfo[$pos]['playername'] != $username) {
   $pos++;
}
echo "Name: " . $username . "<br>";
echo "Defense: " . $playerinfo[$pos]['defense'] . "<br>";
echo "Defense rank: " . $pos;
[/code]