MySQL link field on table to specific field in another table

Posted on 2010-01-05
Last Modified: 2013-12-12
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_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?
Question by:GRDT
    LVL 11

    Accepted Solution

    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.  =)
    LVL 2

    Author Comment

    Thank you!  That sounds like what I am looking for. I didn't know about a trigger.
    LVL 2

    Author Comment

    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.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now