Jump to content

[SOLVED] League - On The Fly Position


JamesTalbot

Recommended Posts

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! :D 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

Link to comment
https://forums.phpfreaks.com/topic/75830-solved-league-on-the-fly-position/
Share on other sites

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.

 

 

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.

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

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'

 

 

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.

 

 

 

??? ???

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

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

 

 

 

 

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.