mysql : Insert ranking to a table based on conditions ... urggh!

ninefoureight · 26 Nov 2009 at 11:29

table = results
fields = name date performance position meeting date

I have the above table and fields within it. I want to update the table and insert a score (rank) to an appended field in the table depending on conditions.

For each unique meeting on the same date i want to insert a score in the appended field depending on performance then position if the same performance for each name. The best. Incrementing the score given by 1 as you descend down the rank.

I think this would be made into a stored procedure once my host supports such, in the short term i would run this manually periodically.

I've looked at various rank examples and think i have my head round these but cant get my head round adding in this next step of complexity. Would anyone be able to give me a helping hand and and do the heavy lifting.. i need to see the light at the end of the tunnell.! ;]

cheers in advance.

Dj_Jestar · 26 Nov 2009 at 11:30

Appending != insert. It's an update.

ninefoureight · 26 Nov 2009 at 12:34

Dj_Jestar said:
Appending != insert. It's an update.

Probably shouldnt use the literal command when explaining my problems.. ;] Point noted. The field will exist when i run the sql im asking after so it will be an Update.

_TubbZ_ · 26 Nov 2009 at 18:14

are you planning some kind of app to do this? if you are why not have a table to store each meeting data and then just calculate the totals and what not when you display it. You would also be keeping more match information and making life easier for yourself.

ninefoureight · 26 Nov 2009 at 21:50

Not an app but plan to query the database heavily via a website. A table for every meeting will be a bit unweildy i think .. as many meetings have only 1 record

_TubbZ_ · 26 Nov 2009 at 22:24

ooh i worded that badly.

Not have a table for every meeting, but a record. You would have to have a table to store the meeting data then you would have a table to store all events data.

as an example say a poker league. the league has several venues and each venue hosts several events throughout the year, the venue data would be stored to provide details of where it took place which doesn't change each week. But each match would have a table to store the results which would relate to the event through a Foreign key. Then on your website you would query the database to get the data and total it up to calculate the score, get last match played date etc. If its aimed at some club of some kind people would also be interested in previous events results not just the last one, you would then be able to allow them to query the database for previous meetings data.

if you are worried about performance on your database you could also have a script on the admin side of the site to calculate the current league and put it into some hard coded form. Then whenever you update your league data you would run the script.

With what you are proposing of just storing the match previous meetings data you could quite happily get away with just reading it into txt or a simple XML files as i can't see how it would be an advantage to use a database.