really really really complicated php / mysql query

[black.horizons]

Hi all,

 

i'm making a fantasy hockey buildin for my clubs website. At the minute I've got:

 

A table with the team details - team name as well as the 11 players in the team - id'd by unique number.

I have another table with a new row for match one player plays in (it has date and number of goals, number of cards).

I also have a table with the players details (name, position)

I also have a table settings with details about points per position for a goal (defense gets 15, midfield gets 10, forward gets 5), as well as points per card (same points for everyone).

 

Now I need to write php to multiply the points per goal by the total number of goals scored, as well as per cards and number of cards for every player in a team, and make a total value of points (so 11 players worth of goal points, and card points). The points each player gets per goal depends on their position.

 

..then when this total is made for every team i'll need to be ordering the total values to make a leaderboard.

 

i've never attempted anything so mind boggling! i once read about temporary tables...and i'm thinking they might be able to help in some fashion? anybody have any ideas?

[dbo]

Please post a table schema.

[phat_hip_prog]

Have you looked at the mysql aggregate functions...

[black.horizons]

fantasy_teams: teamid, teamname, gk, def1, def2, def3, def4, mid1, mid2, mid3, mid4, for1, for2, owner

 

fantasy_players: playerid, firstname, lastname, position

 

fantasy_players_weekly: playerid, clean_sheet, league_goals, cup_goals, green_cards, yellow_cards, red_cards, date

 

fantasy_settings: name, value, comment

 

owner in fantasy_teams links to a table called users (this is irrelevant at this stage). teamid and playerid are PK's (not in _weekly).