I've been trying to think of this for the last few days but am making no progress - and this is to do with the final stage of a riddle contest which some of you (Nightfire) have helped me with previously.
The riddles and such work absolutely fine and I am now trying to fathom how to compile a 'statistics' page which shows the Top 10 (consisting of who, out of all members, are in the lead).
I am unsure how best to get the information, but the ideas so far are as follows:
1) Add a 'timestamp' field to phpbb_users table so that I can simply take the username, riddle number and timestamp and sort into order of riddle number and timestamp. This would give the people with the highest number, and who got there first. Problem with this is that the timestamp updates with every movement of the user, not just when they complete a riddle.
Is it possible to update the timestamp only if and when the riddle number field is updated?
2) Use details from a 'riddle_attempts' table which features the fields, username, riddleid, timestamp. The problem with this is that it is updated with every wrong and right attempt to pass a riddle and so it is likely to feature thousands of rows with duplicate usernames and riddleids. Therefore, selecting the top 10 may actually include 2 or more rows occupied by the same user.
murphyz - riddle 5 - 19:05
phonics - riddle 5 - 19:07
chicken - riddle 4 - 19:01
murphyz - riddle 4 - 19:03
other - riddle 2 - 19:07
This would show the leader going by riddle number and time but, if that person also passed a previous riddle before other users, they would be shown twice in the top 10. Is it possible to take only the one occurrance of the name which looks for the riddle number as MAX, followed by the earliest time related to the riddle, and only one occurance of the username.
Any ideas as to the best way to go about creating something like this are greatly appreciated. As always, if you need any more info or further details on what I am trying to do just ask.