13 replies to this topic

[Tyler2499]

Hey, for the last hour or so i've been trying to figure out how i can update the users ranking with my league script. What i am trying to do is query all the points from the race_results table, group them by racer id, and then give them a rank number 1-8 based on their points. It might be just the way the application is configured (form fields are arrays and data is inputted for each form field). The rank would be updated on the season_racers table in prev_pos column.

Everything works but this, I'm really stuck. I'm thinking a function would work but even then i don't get how i could do it. Maybe I'm just dumb...

[dotbart]

Hey Tyler,

Please tell us exactly where you are stuck.
From what I understand, you want to rank users based on their points. Also you want those ranks to be compares to other users.

So taking the maximum and minimum of all the points would be a start. Then create a ranking accordingly
I take it in the table 'race_results' you have multiple results per user.

So in Pseudo code (translate this to PHP/MySQL):

• Get the total points for each racer
  
• Take the maximum of those points
  
• Take the minimum of those points

Then to give a racer a rank:

$rank = round((($user_points - $minimum) / $maximum) * 80);
If I'm correct, that will give each user points from 1 to 80, rounded down.

Once you have that, you just update the records in your table.


Few notes of advice:

• It's a very heavy calculation to get all the points for all the users. So maybe think about saving the total points in the user record. That way, you select just 1 records as opposed to potentially thousands
  
• Update the ranking whenever a user races. Don't do them all at once. Just to spare your poor server a bit :-)

Did that answer your question?


Bart

[Tyler2499]

hmm.. Thank you. What you said was true. The way it's set i have racers table (racers not assigned to season) and season_racers (racers assigned). So i could very well store total points in season_racers because in there season id is included so it all works.

Each race has only 8 racers and our website is not "interactive". I am going to enter the data through the admin panel. Thanks for the advice and help, I am going to start to implement your ideas.

By the way, there won't be thousands of selected records. I am guessing about 12 races a season therefore should be only 96 records a season. Granted it'd still have to search all the records (which I'm not sure how much that would hurt performance).

$rank = round((($user_points - $minimum) / $maximum) * 80);

Gives a negative integer after the first race...
Position 1 = 0
Position 2 = -80 (which is not 2nd)
Position 3 = -64

Edited by Tyler2499, 07 October 2009 - 02:53 PM.

[Tyler2499]

What exactly is that calculation? I've been working out scenarios and it just isn't representing the data correctly for whatever reason....

[dotbart]

Hmm,

well it's something I basically made up on the spot :-)
I did the following:

$user_points - $minimum

to make sure that the lowest user doens't instantly have 7 out of 8 stars.

Then break it down to a percentile by doing:

($user_ponts - $minimum) / $maximum

So you should have points from 0 to 1

Do that times 80 and you have them based on 80.

For instance:

$user1 = 2;
$user2 = 5;
$user3 = 12;

$min = 2;
$max = 12

function stars($points,$min,$max)
{
	return round((($points - $min) / $max) * 80);
}

echo stars($user1,$min,$max);
echo stars($user2,$min,$max);
echo stars($user3,$min,$max);

//Not tested but output should be:
//User 1: 0
//User 2: 20
//User 3: 80

Test it for some data you allready know the answer to and you'll find flaws in your calculation much faster

Bart

[Tyler2499]

Don't I have the option to add up the total points, then divide the current users points by total points, multiplied by 100 and from there I will have the percentages.

[dotbart]

Correct me if I'm wrong (because it's quite late :-))
but wouldn't that give a percentage of how much the user's points are to the total points?

When I get home later, I'll try and remember to put up a test case to check my own code. Who knows I've been talking jibberish for days..


Bart

[dotbart]

Allright,

tested and working.
I made a few errors on my part so here's tested and working code:

<?php
//Array with 5 random user's total points
$user_points[] = 20;
$user_points[] = 54;
$user_points[] = 37;
$user_points[] = 72;
$user_points[] = 51;

//Minimum of those 5: 20
$minimum = 20;
//Maximum of those 5: 72
$maximum = 72 - $minimum;  //Don't forget to substract the minimum from the maximum. Otherwise your precision is off

function getRanking($totalpoints,$minimum,$maximum)
{
		return round((($totalpoints - $minimum)/$maximum)*16)/2; // I divided by 2 so you can have 2.5/8 If you don't want this, just remove the /2
}

for($i=0;$i<sizeof($user_points);$i++)
{
		print "User " . $i . " in array has " . $user_points[$i] . " points and has a ranking of " . getRanking($user_points[$i],$minimum,$maximum) . " out of 8<br />";
}
?>

Output is:

Quote

User 0 in array has 20 points and has a ranking of 0 out of 8
User 1 in array has 54 points and has a ranking of 5 out of 8
User 2 in array has 37 points and has a ranking of 2.5 out of 8
User 3 in array has 72 points and has a ranking of 8 out of 8
User 4 in array has 51 points and has a ranking of 5 out of 8

Good luck,
Bart

[Tyler2499]

I still am not convinced that this is what I am looking for...

I have 8 racer's per season, so I need to rank them on their points (preferably 1-8 for ease of use). Say RACER#1 has 28 points and RACER#2 has 26 after three races. RACER#1 would be in first and RACER#2 in second. After the next race RACER#2 has 36 points and RACER#1 has 34 points. RACER#2 advanced up 1 position (so i'd like to be able to show +1) and RACER#1 lost a position (-1).


Just doesn't work and if it does how do I accurately measure how many positions the racer gained. (You can't)

Thanks for your time and effort dotbart

Edited by Tyler2499, 20 October 2009 - 02:03 PM.

[Hayden]

SELECT * FROM `racers` ORDER BY `racers_score` DESC LIMIT 0, 5

Will select 5 racers and all their fields in the racers table and order it descending (highest score on top).

SELECT * FROM `racers` WHERE `assigned`='1' ORDER BY `racers_score` DESC LIMIT 0, 5

Presuming you only need a simple indicator that says if they're assigned or not, I did that presuming a boolean type of setup. 0=not assigned, 1=assigned

Edited by Hayden, 21 October 2009 - 12:21 AM.

[Tyler2499]

thanks but did you actually even read any of the thread posts? That's not what I am looking for. I know a little bit of mysql and can do that much.

[Hayden]

It's all jumbled up, I couldn't tell what you had answers to and exactly what you were looking for, it appeared you had a way of creating the ranking per racer you were looking for a way retrieve the information.

You stated you needed to get your racers list ordered by the rank points.

Tyler2499, on Oct 20 2009, 02:02 PM, said:

I have 8 racer's per season, so I need to rank them on their points (preferably 1-8 for ease of use).

You also stated that you insert the information manually, so if you create a separate script to input their score into the database, then you can use the SQL to easily pull the top 8 racers ordered by the score.

Your question already seemed to be answered about calculating the racers score, so I didn't bother answering that. Either input the database the raw scoring and use the one of the functions (or some variation) to output the formatted score or format the score before putting it into the database.

[Tyler2499]

All i want to do is calculate how many positions the racer gained/lost in the points standings. So I need a way to rank them so i can compare later.

So if you were 3rd in standings and after the next race you were in 1st in the standings you would show as +2 in the standings tables (representing you gained 2 positions).

Edited by Tyler2499, 21 October 2009 - 10:30 AM.

[Hayden]

Ahhh, okay. Not sure if you already have the tables setup or not but I would set it up so that it was in a log type setup. Each row you insert in contains the race data for a particular racer as well as a time stamp. The SQL is going to depend on how exactly you want to compare results (last X days, difference between current race and last race or ???). Then you can just pull the data for the last few days by the time stamp or assign each race an ID and you can pull the data for 2 race IDs then compare the results. Presuming on there are 6 races...

SELECT * FROM `racers` WHERE `race_id`>'5'

The idea is this should pull 16 rows, 2 per racer, one from the current race and one from the previous race.




*The ??? is because I was thinking of a third but forgot it by the time I went to type it.