Ranking help

[Slasher]

Hi guys..

in need of alittle help im trying to get a rank system running for something im working on but having some trouble iv tried alsorts so far and no luck.

if anyone knows im after making a ranking system like shown below.

Game rank: #3 so out of say 800 users im ranked 3rd how would i go about this.

cheers guys all help great fully received

[rockwood]

which engine you are using ?

[Slasher]

one im building buddy

- - - Updated - - -

and its not an engine its just a simple php script i just need to no alittle info iv currently got the following but nothing is outputted

 

$number = "1";

$gather = mysql_query("SELECT crimes FROM user_info ORDER BY crimes DESC LIMIT 0, 99999999999");

$count=mysql_num_rows($gather);

if ($count=="0"){

echo "Not ranked";

}

$e=0;

while($object=mysql_fetch_object($gather)){

echo"$number.";

$number=$number+1;

}

[k1ngscorp1o]

Would it be based of their experience, or stats or all of them combined? You have to figure out what you want your ranking based off then its just writing a calculation.

[Slasher]

i just want to base it off each action on each page so say for example a player clicks crimes it would say somewhere on that page Crime rank postion: #532 of how ever many players there are

[Alan]

If we assume you are using an integer based experience then the solution is pretty easy.

To demonstrate, create some test data with "points" being the field we are interested in determining rank from:

CREATE TABLE test
(
   id     INT UNSIGNED NOT NULL,
   name   VARCHAR(25)  NOT NULL,
   points INT UNSIGNED NOT NULL,

   PRIMARY KEY (id),
   UNIQUE  KEY (name)
)
ENGINE=InnoDB;

INSERT INTO test (id, name, points)
   VALUES
       (1, 'Tom',   100),
       (2, 'Alison', 58),
       (3, 'Dick',   88),
       (4, 'Susan', 102),
       (5, 'Harry',  99),
       (6, 'Alan',   88);

Now to get the every rank...

  SELECT id, name, points, FIND_IN_SET(points, (SELECT GROUP_CONCAT(points ORDER BY points DESC) FROM test)) AS rank
   FROM test
ORDER BY rank;

which would result in

+----+--------+--------+------+
| id | name   | points | rank |
+----+--------+--------+------+
|  4 | Susan  |    102 |    1 |
|  1 | Tom    |    100 |    2 |
|  5 | Harry  |     99 |    3 |
|  3 | Dick   |     88 |    4 |
|  6 | Alan   |     88 |    4 |
|  2 | Alison |     58 |    6 |
+----+--------+--------+------+

or to get just the rank for an individual...

SELECT id, name, points, FIND_IN_SET(points, (SELECT GROUP_CONCAT(points ORDER BY points DESC) FROM test)) AS rank
 FROM test
WHERE name = 'Alan'

which of course yields

+----+--------+--------+------+
| id | name   | points | rank |
+----+--------+--------+------+
|  6 | Alan   |     88 |    4 |
+----+--------+--------+------+

[Slasher]

If we assume you are using an integer based experience then the solution is pretty easy.

To demonstrate, create some test data with "points" being the field we are interested in determining rank from:

CREATE TABLE test
(
   id     INT UNSIGNED NOT NULL,
   name   VARCHAR(25)  NOT NULL,
   points INT UNSIGNED NOT NULL,

   PRIMARY KEY (id),
   UNIQUE  KEY (name)
)
ENGINE=InnoDB;

INSERT INTO test (id, name, points)
   VALUES
       (1, 'Tom',   100),
       (2, 'Alison', 58),
       (3, 'Dick',   88),
       (4, 'Susan', 102),
       (5, 'Harry',  99),
       (6, 'Alan',   88);

Now to get the every rank...

  SELECT id, name, points, FIND_IN_SET(points, (SELECT GROUP_CONCAT(points ORDER BY points DESC) FROM test)) AS rank
   FROM test
ORDER BY rank;

which would result in

+----+--------+--------+------+
| id | name   | points | rank |
+----+--------+--------+------+
|  4 | Susan  |    102 |    1 |
|  1 | Tom    |    100 |    2 |
|  5 | Harry  |     99 |    3 |
|  3 | Dick   |     88 |    4 | <<<<<<<<<<<
|  6 | Alan   |     88 |    4 | <<<<<<<<<<<
|  2 | Alison |     58 |    6 |
+----+--------+--------+------+

or to get just the rank for an individual...

SELECT id, name, points, FIND_IN_SET(points, (SELECT GROUP_CONCAT(points ORDER BY points DESC) FROM test)) AS rank
 FROM test
WHERE name = 'Alan'

which of course yields

+----+--------+--------+------+
| id | name   | points | rank |
+----+--------+--------+------+
|  6 | Alan   |     88 |    4 |
+----+--------+--------+------+

while this is a great example of what i could do its kind of not what im after as u can see in the example there are 2 number 4s i need it to go as follows #1, #2, #3 and so on and so forth thanks anyway alan **

[Alan]

There's no pleasing some people. Standard competitive ranking is the easiest to implement and is the one that will be the most familiar to players given its common usage from primary school upwards. What you want sounds more like dense ranking which is a little more interesting to implement and is database engine specific.

So since you want an answer to a question that you didn't define - you will need to provide more information:

1: Sample data - 4-6 rows with duplicates as I had and of course the rank that you would apply to each row.

2: Database engine (PostgreSQL, MySQL, Oracle, SQL Server, NoSQL* etc)

3: Scripting language (may be helpful)

4: Ideally, the exact ranking mechanism

[Slasher]

There's no pleasing some people. Standard competitive ranking is the easiest to implement and is the one that will be the most familiar to players given its common usage from primary school upwards. What you want sounds more like dense ranking which is a little more interesting to implement and is database engine specific.

So since you want an answer to a question that you didn't define - you will need to provide more information:

1: Sample data - 4-6 rows with duplicates as I had and of course the rank that you would apply to each row.

2: Database engine (PostgreSQL, MySQL, Oracle, SQL Server, NoSQL* etc)

3: Scripting language (may be helpful)

4: Ideally, the exact ranking mechanism

Im very pleased with what you had in-putted to me alan dont worry about that.

the database is mysql and its a simple php mafia game all your normal bells and whistles (just my own work not some random stuff thrown together)

so ill show you what i came up with to get the number but it just dont work out how i had planned it to because even tho theres 2 people registered on my server it says im 2nd out of 2 but so is the other account so i dont no how it works out that 2 of us are both ranked 2nd surely one of us has to have a few say xp then the other and then it should be them #1 me #2 for 1st and 2nd ill show you alittle code i used to get the number.

 

<? 
	  $number = "1";
 $gather = mysql_query("SELECT * FROM users WHERE userlevel > '0' AND userlevel < '4' AND status='Active' ORDER BY rep DESC LIMIT 25");
 $count=mysql_num_rows($gather);
 if ($count=="0"){

 echo "Not Ranked";
 }
$e=0;
 while($object=mysql_fetch_object($gather)){


if ($e == "0"){ $color="#282828"; $e=1; }else{ 
$color="#505050"; $e=0; }

 echo 
 "$number.";
$number=$number+1;}

 ?>

[rockwood]

If we assume you are using an integer based experience then the solution is pretty easy.

To demonstrate, create some test data with "points" being the field we are interested in determining rank from:

CREATE TABLE test
(
   id     INT UNSIGNED NOT NULL,
   name   VARCHAR(25)  NOT NULL,
   points INT UNSIGNED NOT NULL,

   PRIMARY KEY (id),
   UNIQUE  KEY (name)
)
ENGINE=InnoDB;

INSERT INTO test (id, name, points)
   VALUES
       (1, 'Tom',   100),
       (2, 'Alison', 58),
       (3, 'Dick',   88),
       (4, 'Susan', 102),
       (5, 'Harry',  99),
       (6, 'Alan',   88);

Now to get the every rank...

  SELECT id, name, points, FIND_IN_SET(points, (SELECT GROUP_CONCAT(points ORDER BY points DESC) FROM test)) AS rank
   FROM test
ORDER BY rank;

which would result in

+----+--------+--------+------+
| id | name   | points | rank |
+----+--------+--------+------+
|  4 | Susan  |    102 |    1 |
|  1 | Tom    |    100 |    2 |
|  5 | Harry  |     99 |    3 |
|  3 | Dick   |     88 |    4 |
|  6 | Alan   |     88 |    4 |
|  2 | Alison |     58 |    6 |
+----+--------+--------+------+

or to get just the rank for an individual...

SELECT id, name, points, FIND_IN_SET(points, (SELECT GROUP_CONCAT(points ORDER BY points DESC) FROM test)) AS rank
 FROM test
WHERE name = 'Alan'

which of course yields

+----+--------+--------+------+
| id | name   | points | rank |
+----+--------+--------+------+
|  6 | Alan   |     88 |    4 |
+----+--------+--------+------+

You Are Awesome Mate