[SOLVED] League - On The Fly Position

[JamesTalbot]

Hello all,

 

Im currently coding a league and became stuck with a bit of the code.

 

I have 2 tables. Users & Played.

 

User -

 

userid

username

position

wins

losses

points

games

 

Games -

 

gameid

winner

loser

date

 

Here's the code : Report.php

 

// Update Winner 
mysql_query("UPDATE user SET wins = wins + 1, losses = losses, points = points + 2, games = games + 1 WHERE username='".$_POST['winner']."'");

// Update Loser
mysql_query("UPDATE user SET wins = wins, losses = losses + 1, points = points - 1, games = games + 1 WHERE username='".$_POST['loser']."'");

 

For the Standings - Standings.php

 

$position = 0;

$sql = mysql_query("SELECT * FROM user ORDER BY rank ASC");

while ($data = mysql_fetch_array($sql)) {

$position = $position + 1;

// Display the data

 

Now, as you can see the position is just statically predefined.

 

Im not that great with maths which is why im having problems! Im pretty sure this is all to do with maths but if someone could maybe help me out or point me in the right direction?

 

Any help would be greatly appreciated.

 

Thanks,

 

James

[Barand]

The right direction to where?

 

And where is the "rank" column you are sorting by

[GingerRobot]

Surely you need to be sorting by the points a user has?

[JamesTalbot]

Sorry, Rank = Position.

 

mysql_query("SELECT * FROM user ORDER BY position ASC");

 

I basically have pages where it gets the users position i.e

 

SELECT * FROM user WHERE position='1'

 

The standings will be ordered by points but what im trying to achieve is that in report.php that it does some calculation to determine what their rank will be and then update their "position" field in the database. Is that any clearer?

 

Thanks.

 

 

[otuatail]

Sorry to burst a bubble here (i wrote one of these before) but what if it's a DRAW ?

 

Desmond.

[Barand]

As GingerRobot said, order points DESC to get the position. Position is derived data, and should be calculated as required, not stored.

 

To get player whose position is x, you just query for the player/s who has x-1 players with more points.

[JamesTalbot]

At the moment i have been using :

 

$sql = mysql_query("SELECT * FROM user ORDER BY points DESC LIMIT 0,1");

 

How do i get a specific user's info and get their position? A simple WHERE username='$username' wont bring up their position. Im not sure what you mean by "To get player whose position is x, you just query for the player/s who has x-1 players with more points."

 

Thanks.

 

James

[Barand]

League table

SELECT (SELECT COUNT(*)+1 FROM users b WHERE b.points > a.points) as position, a.username, a.points
FROM users a
ORDER BY (SELECT COUNT(*)+1 FROM users b WHERE b.points > a.points)

 

Get user at position 5

SELECT a.username, a.points
FROM users a
WHERE (SELECT COUNT(*)+1 FROM users b WHERE b.points > a.points) = 5

 

Get position of user 'x'

SELECT (SELECT COUNT(*)+1 FROM users b WHERE b.points > a.points) as position, a.points
FROM users a
WHERE username = 'x'

 

 

[JamesTalbot]

Thanks that helps alot however still have a few problems.

 

In my league, a Win is +2 points and a loss is -1 point. This affects the query as when it gets to negative numbers it just displays the users unique userid, eg

 

1 - User1 - 50

2 - User2 - 20

3 - User3 - 15

4 - User4 - 10

215 - User5 - -10

216 - User6 - -20

 

 

Thanks for all your help.

 

 

 

[Cagecrawler]

Are you using an unsigned integer for the points column in your db?

[JamesTalbot]

No, should i be?

[Barand]

No, you need signed INT to hold pos and neg values.

 

I didn't understand your problem with the neg values.

[JamesTalbot]

Well it goes from pos 1 - 19 then if the points is negative its like Position 219 which seems stupid becuase theres only 25 in the standings?

[Barand]

??? ???

My test query

SELECT a.id, a.score,
(SELECT COUNT(*)+1 FROM ratings b WHERE b.score > a.score) as position FROM ratings a
ORDER BY (SELECT COUNT(*)+1 FROM ratings b WHERE b.score > a.score)

 

My test results

[pre]

id  | score  |  pos

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

1  |  75  |  1

2  |  74  |  2

7  |  70  |  3

8  |  66  |  4

3  |  63  |  5

5  |  54  |  6

6  |  49  |  7

4  |  -5  |  8

[JamesTalbot]

Here is what i have :

 

$sql = mysql_query("SELECT (SELECT COUNT(*)+1 FROM user b WHERE b.points > a.points) as position, a.username, a.points, a.country, a.games, a.wins, a.losses, a.streakwins, a.streaklosses, a.userid
FROM user a WHERE games > 0
ORDER BY (SELECT COUNT(*)+1 FROM user b WHERE b.points > a.points)"); 

 

Here is the output :

 

http://www.tlbinteractive.com/testing/test.php

 

Thanks

 

 

 

 

[Barand]

Those results are correct.

 

All those with 0 points are 19th=, all with 18 with more points than them.

 

"eduardo" has 214 people with a higher score than him

[JamesTalbot]

Please refresh the page.

 

Why does it not show position 20, 21 , 22 , 23 etc after position rank 19? Seems odd to jump to that.

 

Thanks.

[Barand]

Because you don't have 1 person ranked 19th, you have 196 of them, all with more points than the next lowest, and 18 more above them.

 

Your ranking reflects how many people are above you.

 

if there are five teams and 4 score 100 points and you score 99, sorry you came last out of 5, not second.

[JamesTalbot]

Thank you for all your help.