MySQL link field on table to specific field in another table

Hi There,

I have a problem that I am not sure what terminology to use to describe it and fix it, so here goes.

I have a table that gets populated with players scores for a fantasy football contest, there are a couple hundred players and I am already acheving this piece of it.
The table looks like this. Basically we track the score for each player during and after each game played.
Player_Scores
player_id - week - year - points

In this contest, I have several thousand teams that pick from the couple hundred players to have on their teams. Right now, I am pulling the scores from the above table when the team visits their page to see how they are doing, it's done individually to keep the processing power down.
The team table looks like the one above, but has the team ID added to it.
team_id - player_id - week - year - points

Is it possible to have the points field in the team table show whatever is in the point field in the players table without loading the info via PHP script or something?

I'd like to be able to pull leaderboards among other stats based on the results of all the teams data, but currently unless they all visit thier pages, this is not possible. I tried a loop grabbing and inserting the data, but with 10K plus teams, it takes a while and I am on a shared host.

Anything I can do to make this more efficient?
LVL 2
GRDTAsked:
Who is Participating?
 
VanHackmanConnect With a Mentor Commented:
You can use a TRIGGER Event in MySQL to update the team's points field adding points every time that a player on that team gain points.  =)

http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx
0
 
GRDTAuthor Commented:
Thank you!  That sounds like what I am looking for. I didn't know about a trigger.
0
 
GRDTAuthor Commented:
My shared host doesnt support it :(

Thanks for the answer, I'll probably bring up a VM to test it, but this is what I needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.