5 Replies - 2212 Views - Last Post: 17 September 2013 - 01:42 PM Rate Topic: -----

#1 berbatov  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 15-September 13

Using PHP and MySQL to manage a fantasy football league

Posted 16 September 2013 - 07:14 PM

Hello guys!

I have a fantasy football (soccer) league where we use the american version of picking players. The english standard is that anyone can pick any player they want within a budget, but we like the american way.

The way we keep track of our league at the moment is very manual. We draft in person and post the teams to a facebook group where we also post scores, transfers and matchups. Now I would like to create an "application" we can use to keep track of our league on my website, where we can transfer players and keep track of which player is on which team through a MySQL DB.

I spent my spare time in May, June and July learning HTML and CSS through google and learning from other peoples questions, and in August and September thus far I have tried doing the same with PHP, but I find the language quite a bit more challenging, which is why I decided to finally sign up here to be able to post.

I hope to be able to create this fantasy football platform within 9 months (for next years season), and plan on posting here for help along the way.

Here is a quick list of functions I think I'll need, so that you can comment even if you are not familiar with Fantasy Football.

User identification

As it is, I've done it the easy way. I've downloaded the Usercake script, since I'm not familiar enough with prepared statements and preventing SQL injections to be sure my site is safe.

Q1) Do you think I should develop my own script or is Usercake more than sufficient? (Let me know if you want me to paste the code here)


Team selection


I have a DB with all the players you can choose from and their respective information. E.g:

player_id FirstName LastName Position Team
1 Landon Donovan Forward LA Galaxy
2 Thierry Henry Forward NY Red Bulls
3 Clint Dempsey Midfielder Seattle Sounders

Upon creation of a league I need a method to tie each player to a User. And so far, this is what I have been thinking I should do:

Each user gets 15 slots to fill with players of their choosing. I know creating a live draft is going to be very difficult for me at this point, so we'll just draft offline first and fill in the players later. But I haven't figured out the best way to connect a player to a user.

I was initially hoping to create an input field with a live search within the player_table, using AJAX, and when the search is successfully completed (you have found the player you were looking to add to your team), you could submit and the PHP script would post this players player_id to a designated mysql table for the league where all the users are registered.

For example:

league_table

user_id player_id1 player_id2 player_id3 player_id4 (up to 15, the max amount of players per team)
1 2
2 3
3 1
4
etc

User1 would now have player_id(2) on his team, which in this case is Thierry Henry.
User2 would have Clint Dempsey, and User3 would have Landon Donovan.


Q2) Is this something you think I can manage within a reasonable time frame? And where should I look to get started on this? Any specific examples to help me get on the right track are also much appreciated.


Q3) Do you have a suggestion for an alternative way of connecting the players to a team?



Calculating scores

I don't want to write too much, because I would be more than happy getting some feedback on my first three questions, but this one is kind of essential to determine whether I should be using PHP and MySQL at all.

The thing is that I need to be able to calculate and store every players score for each gameweek.

And not only that. I need to be able to take each players score, and attribute them to the user who owns the player, giving the user a total score based on the score of all of his 15 players in a gameweek.

I need something that lets me calculate a players score (I can probably just do this in excel if its too difficult), and then assign it to his player_id. When a User has the player_id in his row of the league_table, the score of the player should be assigned to him, making the score of each of his 15 players, his final score for the gameweek.


Q4) Can this be done with some kind of mysql command or php script or should I be looking to another language?


Other functions I'll be looking into in the future:
- Transfer of players from 1 team to another
- Deletion of one player, and addition of another player not currently in another users team
- Calculation and presentation of points and table for a specific league
- Cookies and sessions in general
- Team selection (only 11 players from your team will be counted towards your total points for a Gameweek)

I'll be grateful for any response I get, and don't be afraid to give some general advice on what I should be doing and suggestions to functions I might not even have thought about.

Is This A Good Question/Topic? 0
  • +

Replies To: Using PHP and MySQL to manage a fantasy football league

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9494
  • View blog
  • Posts: 35,842
  • Joined: 12-June 08

Re: Using PHP and MySQL to manage a fantasy football league

Posted 16 September 2013 - 07:35 PM

Quote

Q1) Do you think I should develop my own script or is Usercake more than sufficient? (Let me know if you want me to paste the code here)


I think you should get the bulk of it working before you tackle gold plating like implementing 'usercake'.



Quote

user_id player_id1 player_id2 player_id3 player_id4 (up to 15, the max amount of players per team)
1 2
2 3
3 1
4
etc
[...]
Q3) Do you have a suggestion for an alternative way of connecting the players to a team?


Are you ever going ot need more than 15 players? If not then I would suggest having a table matching up user ids to player ids.. the count of how many players a user has would determine if they can add another player or not on the client.


Quote

Q2) Is this something you think I can manage within a reasonable time frame? And where should I look to get started on this? Any specific examples to help me get on the right track are also much appreciated.


Sure. Read up on relational database.



Quote

The thing is that I need to be able to calculate and store every players score for each gameweek.
[...]
Q4) Can this be done with some kind of mysql command or php script or should I be looking to another language?


So.. you have an entry form and some sort of table view (or procedure) that takes in a user id and spits out their tallied up points.
Was This Post Helpful? 0
  • +
  • -

#3 berbatov  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 15-September 13

Re: Using PHP and MySQL to manage a fantasy football league

Posted 17 September 2013 - 12:35 PM

So you are suggesting I make a table called "league" something like this:

UserID PlayerID
3 ------- 54
3 ------- 32
3 ------- 2
3 ------- 79
3 ------- 65
3 ------- 15
3 ------- 23
3 ------- 4
3 ------- 71
3 ------- 63
3 ------- 13
3 ------- 42
3 ------- 81
3 ------- 89
3 ------- 50
1 ------- 16
1 ------- 44

etc

Where you are only allowed to add a new player if you have <15 players? I'm guessing I can just use some IF statements for this?


How do I make the PlayerID and UserID in the "League" table hold value? I've been reading up on relational databases, and understand the concept in theory, but I can't seem to find a tutorial to teach me how the relations work in practice.

Is this what inner join is for? If so, should the inner join be applied to the PHP code or to the MySQL database directly and how would I go about doing that?


You see, I'm looking for this to be scalable for future additions of other leagues who want to use the platform, so I want to do it right, even if it won't be necessary when it's just 1 league.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9494
  • View blog
  • Posts: 35,842
  • Joined: 12-June 08

Re: Using PHP and MySQL to manage a fantasy football league

Posted 17 September 2013 - 12:45 PM

Quote

Where you are only allowed to add a new player if you have <15 players? I'm guessing I can just use some IF statements for this?

Yeah.. you would control the "no more than 15" on the client / GUI side.


Quote

How do I make the PlayerID and UserID in the "League" table hold value? I've been reading up on relational databases, and understand the concept in theory, but I can't seem to find a tutorial to teach me how the relations work in practice.

I don't follow. Are you asking how to use an INSERT or UPDATE statement?


Quote

Is this what inner join is for? If so, should the inner join be applied to the PHP code or to the MySQL database directly and how would I go about doing that?

Where did innerjoins come into the discussion? Innerjoins are a method taking two database table and relating them together in the SQL statement.
Was This Post Helpful? 0
  • +
  • -

#5 berbatov  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 15-September 13

Re: Using PHP and MySQL to manage a fantasy football league

Posted 17 September 2013 - 01:29 PM

No, I'm not asking how to use an INSERT or UPDATE statement.

It's very hard for me to explain using the correct syntax because I don't yet know what I need.
And the purpose of this thread is basically to figure out what I need, so that in the future I can use the correct syntax when posting more specific questions.


But I'll try again, hoping it's a little bit clearer.


The "league" table looks like the one above.
In the "league" table, there is a column called "PlayerID"
If I just type in a number in the "PlayerID" column without doing anything else, the "PlayerID" will have no value, other than e.g the number "79".
This number in itself doesn't refer to anything other than the numeric value that it holds.
I want the numbers in the "PlayerID" column to reference the fields in the "Player" table where the ID field has the same number as the PlayerID in the "league" table.


Is this also done on the client side? Or do I need to query the database in PHPMyAdmin somehow?

Googling this is what led me to innerjoin.

There is something I'm just not understanding, but I don't know what it is...

Thanks for helping Modi
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9494
  • View blog
  • Posts: 35,842
  • Joined: 12-June 08

Re: Using PHP and MySQL to manage a fantasy football league

Posted 17 September 2013 - 01:42 PM

The "league" table looks like the one above.
In the "league" table, there is a column called "PlayerID"
If I just type in a number in the "PlayerID" column without doing anything else, the "PlayerID" will have no value, other than e.g the number "79".
This number in itself doesn't refer to anything other than the numeric value that it holds.
I want the numbers in the "PlayerID" column to reference the fields in the "Player" table where the ID field has the same number as the PlayerID in the "league" table.


Correct.. the concept of a primary (or foreign) key is just that.. a concept. The concept of relational databases is what make that jump.


Say I have a table 'foo' by itself.

id          sval
----------- ------------------------------
1           one
2           two
3           three


You really don't get a sense of what is going on.. but if you also have a table call 'bar':


id          mPrice
----------- ---------------------
1           5.25
2           6.10
1           12.51
3           9.00
1           52.40



Now things become interesting. Either table, by themselves, doesn't help but through the CONCEPT of relational databasing we can see some unique data show up.

Using a join we can jump from one table to another in a query.

id          sval                           mPrice
----------- ------------------------------ ---------------------
1           one                            5.25
2           two                            6.10
1           one                            12.51
3           three                          9.00
1           one                            52.40






Spoiler










Quote

Is this also done on the client side? Or do I need to query the database in PHPMyAdmin somehow?

You would do this in a query.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1