[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL link field on table to specific field in another table

Posted on 2010-01-05
3
Medium Priority
?
658 Views
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_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?
0
Comment
Question by:GRDT
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
VanHackman earned 1000 total points
ID: 26182102
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
 
LVL 2

Author Comment

by:GRDT
ID: 26182202
Thank you!  That sounds like what I am looking for. I didn't know about a trigger.
0
 
LVL 2

Author Comment

by:GRDT
ID: 26185067
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month18 days, 16 hours left to enroll

834 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