[RESOLVED] Equal places in race results

dickiebow

My hobby is sailing and I have two organisations where the task of re-building websites has fallen to me (they call it volunteering). Both involve a lot of yacht racing.

While it is easy to put results into order with a MYSQL query from PHP I am trying to work out how to handle situations where a deal heat occurs in a race. The second instance of the same conflict is where a particular event consists of three races and overall places are determined on the best two placings achieved out of the three. This is where it is most likely that two competitors achieve identical points and a simple select ...order by ... query would be unfair. Just to add to the complexity, one of the organisations has a super table of events leading to the seasonal winners and other placings.

I am happy to amend the schemas but I am the type who wants to know how to get down the tree before climbing up so knowing the end trick to solve this one will inform my processes.

PS I have tried loads of combinations of search words to try to find the solution before asking here.

tomhath

The rows have to come back from the database in some order, you can't have a tie there 😉

So you'll have to work out what to display on the website or report when it happens. In other words, write down the requirements for what to do, then make sure the other people in your organization agree that's what they want. After that the design should be pretty obvious.

dickiebow

The key issue here is how to get a set of results to recognise where competitors have equal placings - eg joint third place which means they are both noted as no 3 in the results but the next competitor would count as 5th.

Normally I would do a while loop to output table results and increment the placings counter with a $place++ but I really need to check whether one field ( the sum of points) is the same in successive rows and then change the placing field for both to read the same, also double incrementing the counter for the next row.

tomhath

Do just what you said. Inside the WHILE loop, save the previous row's number of points and compare to the current row. Display and increment the place accordingly.

dickiebow

You are absolutely right - I was missing the point that I could allocate the next increment in the counter to the first result and wait for equality to trip the routine and simply didn't need to write it back after the comparison. I think I will have to write the output to a table to keep the placings ready for the further comparisons of the seasonal aggregations. That needs a bit of consideration as to normalisation but I am sure economical design can still be achieved.

Somehow just writing this thread and getting a positive reply has clarified what had taken days (and waking in the early hours) of solo thinking - but that is what forums are about, apart from expert advice on specific code snags. It is interesting how often people find their own solutions after such a prompt. As I haven't yet written the code, I can't quote it nor is it really necessary in this case, but it is good when people do post their solutions, even when reached as a result of feedback, for the rest of us to read.