PHP ranking function based off results (need help)

[debow]

Hello all, I'll try to explain what I'm wanting to do the best way I can. I have an event_results table that contains multiple athlete id's and for each athlete they are tied to an event in the same table. The screen shots should help explain. I also created a ranktable that contains a score for each place there would be. IE: 1st place is 100pts 2nd place is 97pts and so on. In this event_results table there is a eventrank column and eventscore column. I want to create a button/function that will update those two columns with the correct rank/score based off the results. I'm thinking that a massive mysql select/join/insert statement is needed but I'm not really sure if that's the best approach for this or not. I'm also not that good with sql so am having a little problem coming up with a correct ranking/join statement. I've tried a few but they've not worked. I'm thinking there will have to be 10 updates as well since there are 10 different events that need to be ranked. The file attached is just examples I through into excel. I can give the
db schema if you'd like.

Thanks for any help.

[debow]

Ok, I've found the follow sql that in a step in the right direction I think but still need help with moving forward. The below sql will give me my ranking for the events based off results but I'm not sure how to join it with the ranktable and insert the score. Does anyone have any ideals.

SET @rownum = 0, @rank = 0, @prev_val = NULL;
SELECT @rownum := @rownum + 1 AS row,
@rank := IF(@prev_val!=result,@rownum,@rank) AS rank,
id,
athlete_id,
event,
@prev_val := result AS result
FROM event_results where event='Push Ups' ORDER BY result DESC;

This gives me out put like this.
ROW RANK ID ATHLETE_ID EVENT RESULT
1 1 35 198 PUSH UPS 114
2 2 25 197 PUSH UPS 53
3 3 45 199 PUSH UPS 20

I need to tie the scores from the ranktable to this and somehow insert the rank/score into the above table in a event_rank/event_score column.

[debow]

Hopefully someone will be able to help with this. I've found a solution that works for most of my 10 events but not all. Here is the problem. I have 10 events that will be in the format of either "10" or "59:12.3" for the result. So a whole number and a time format. On my event_results table I had the results column as a "int". That worked fine with the below update however I can't enter the "59:12.3" result into a int column. So I switched results from "int" to "varchar" now I can insert my results how I like but the rank update below doesn't work. Can someone please help me with a work around. I'm thinking I can do some sort of varchar to number conversion but not sure. Below is the update statement that I have working as long as I run it on a int column.

$sql = "UPDATE   event_results
		JOIN     (SELECT    er.athlete_id,
                   IF(@lastPoint <> er.result, 
                   @curRank := @curRank + 1, 
                   @curRank)  AS rank,
                   @lastPoint := er.result
		FROM      event_results er
		JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
		ORDER BY  er.result DESC) ranks ON (ranks.athlete_id = event_results.athlete_id)
		SET eventrank = ranks.rank where event='Push Ups'";

[debow]

I was able to get the insert for whole numbers working with the result column as varchar using the below change but can't figure out what to do with values that are like "01:00.5". I've tried using FLOAT,SIGNED,DECIMAL and TIME. None seem to work.

$sql = "UPDATE   event_results
			JOIN     (SELECT    er.athlete_id,
                  IF(@lastPoint <> CAST(er.result AS SIGNED), 
                  @curRank := @curRank + 1, 
                  @curRank)  AS rank,
                  @lastPoint := CAST(er.result AS SIGNED)
		FROM      event_results er
		JOIN      (SELECT @curRank := 0, @lastPoint := 0) r
		ORDER BY CAST(er.result AS SIGNED) DESC
		) ranks ON (ranks.athlete_id = event_results.athlete_id)
		SET eventrank = ranks.rank where event='Push Ups'";

[debow]

Ok, so I didn't get any help so I decided to re-designed my database and that has worked. Results that are whole numbers are now of type (INT) Results that are in a 00:00:00 format are of type (TIME) and my rank update works. Now I need help with inserting the score.

I have a ranktable that is in this format.
Column's

ID    RANK   SCORE
1      1     150
2      2     145
3      3     140 etc...etc

I also have the results table that has many columns but the most important are
Column's

ATHLETEID    EVENT   EVENTRANK   EVENTSCORE
 12          PushUp      1            
 23          PushUp      2            
 31          PushUp      3

I need a insert/update statement that will query the ranktable rank/score and depending on the eventrank value on the results table insert the correct score for that athlete and event.

So after the update results table should look like this.
Column's

ATHLETEID    EVENT   EVENTRANK   EVENTSCORE
12          PushUp      1         150   
23          PushUp      2         145  
31          PushUp      3         140

Thanks for the help in advance.

[debow]

Think I got it, just have to test it out more.

UPDATE er1 e
LEFT JOIN ranktable rt ON e.eventrank = rt.rank
SET e.eventscore = rt.score
WHERE e.event='Push Ups';

Will let you all know.