• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

Creating a Head to Head league system PHP/MySQL

Hi there,

I am looking to create a database and PHP script that allows me to create a Head to Head Fantasy League Game.

On paper I have this vision where for example (10 members sign up to a league) When the league is full the admin can click (create match-ups) which will then store all the match-ups into the database for each week.

For example...
League name: My League
Teams in the league:

Match-ups for (My League)
First week:
user1 VS user10 (for example)
user2 VS user9 (for example)
user3 VS user8 (for example)
user4 VS user7 (for example)
user5 VS user6 (for example)

second week everyone plays someone different and so on... over 9 weeks. Everyone plays everyone else once.

So im a bit stuck #1 setting up the database correctly. #2 creating the PHP to work with the database.

If anyone has any ideas or knows a good way to do something like this please help. Thanks.
  • 5
  • 4
1 Solution
Ray PaseurCommented:
You will want at least two tables.  Teams would be one, and Results would be another.  The level of detail you want to carry will determine how much stuff you need to put into these tables.  I would expect that the Teams table would just have the team name and a numeric key.  And the Results table would have the contest date, the  team keys and team scores.  Initially there would be no scores - you would fill them in after the contests.

This code snippet shows how to distribute the teams into contests.  See it in action here.

That's pretty much the entire design pattern.  If you print out the $matches array (line 60) you will be able to see an easy way to create the Results data base table.

Best regards, ~Ray
<?php // RAY_temp_james130c.php
echo "<pre>";


$teams = array
( 'user0'
, 'user1'
, 'user2'
, 'user3'
, 'user4'
, 'user5'
, 'user6'
, 'user7'
, 'user8'
, 'user9'

$weeks = 9;

$array = array_merge($teams, $teams);

$matches = array();
while ($weeks)
    foreach ($teams as $ptr => $team)
        $link = $ptr + $weeks;

        $home = $team;
        $away = $array[$link];
        $matches[$team][$weeks] = array('home' => $home, 'away' => $away);

    // NEXT WEEK

foreach ($matches as $team => $contests)
    $matches[$team] = $contests;

// print_r($matches);

$out = NULL;
$out .= "<table>";
$out .= PHP_EOL;

$weeknums = end($matches);

$out .= "<tr>";
$out .= '<th> HOME TEAM </th>';
foreach ($weeknums as $week => $matchup)
    $out .= "<th> WEEK $week </th>";
$out .= '</tr>';
$out .= PHP_EOL;

foreach ($matches as $team => $contests)
    $out .= "<tr><td> <b>$team</b> </td>";
    foreach ($contests as $week => $matchup)
        $out .= "<td> {$matchup["away"]} </td>";
    $out .= "</tr>";
    $out .= PHP_EOL;
$out .= "</table>";
$out .= PHP_EOL;

echo "</pre>";
echo $out;

Open in new window

james130cAuthor Commented:
Hi thanks,

Im sort of getting the idea.. However I wondering so how does the database or website know when to display week 1 and then on a certain date for example friday 14th oct stop displaying week 1 and start displaying week 2 match-ups. Also although week 1 is no longer displayed.. id like full details held in database so they can go back and veiw them at anytime within their account. I believe I can do all this... Im just having a hard time understanding how the database will function how i need it to with the PHP.

My idea was when the user logs in they can see all match-ups shedules... And also be able to see the other head to head games.. but also have a game in play page where there is just their team VS another team on the page.

So for example.. Week 1 starts Today. 12/10/11
Lets say user 1 is playing vs user 2 etc.. (match finishes 13/10/11) for example. Results sorted and stored in database.

How does it then change on 14/10/11 to display week 2
user 1 is playing user 3 this week.

Sorry to be a pain.

james130cAuthor Commented:
For some reason im thinking i will need all the 9 weeks as tables in the database? For example...
week3, etc... with the other tables..

So in week 1 table I have all the 5 match ups stored somehow? Same for all the other weeks... Or am I totally making this more complicated than it needs to be lol
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Ray PaseurCommented:
It's not a pain to help you at all, but there are some fairly large computer-science knowledge gaps to fill in.  Time = Money, and in respect of your time and money you might want to turn this over to a web development firm.  I am reminded of the story about the sign in the auto-repair shop.  Price $50 per hour.  $100 if you watch. $500 if you already tried to fix it yourself. :-)

With contest schedules, the design pattern is to show all of the contest results.  You might show the completed contests in bold and the upcoming contests in normal weight.  As the season goes on, the display grid will show all the results to date, with the upcoming contests showing that they have not been played yet.

The Results table will contain a DATETIME that gives that information about the contest, the keys of the home and away teams, the location of the contest (in case the home team is not playing on their own field) and the scores of each of the teams in the contest.  That is really all you need.  One row per contest.  If you print out that multi-dimensional array $matches on line 60 it will probably go a long way toward clarifying what you want to store in the data base.

If you have read Peter Norvig's thoughtful and wise article and still want to try to do this by yourself, get started with the SitePoint book.  But don't expect it to be fast or easy.  We don't do software development because it is easy; we do it because it is hard and therefore very highly paid.  I spend two hours a day - every day - studying before I take up the first line of paid work.  As the saying goes, "We do today what others will not, so we can do tomorrow what others can not."  The theory works in sports and also in music, programming, dance, any of our  high-order human activities.
Norvig: http://norvig.com/21-days.html
SitePoint: http://www.sitepoint.com/books/phpmysql4/

If I were dealing with this and I were you, here is what I might do.  Find a local college that teaches IT courses. Go to a professor or graduate assistant and propose your league web site as a challenge for a senior project.  Offer to pay a stipend or prize to the student who creates the best solution.. A prize of $5,000 would get you some pretty creative academic thinking for almost nothing compared to the cost of hiring a pro to develop your application.  Fair warning: this is a little like going to a barber school to get your haircut.  The results could be suboptimal.  But the students will give all-out effort and fall all over themselves trying to win the prize.

If you are interested in hiring me to develop the site I can tell you the general business terms I have used with other bespoke web sites.  I will build the infrastructure of the site, "plumbing and electrical" including the data base and the PHP scripts at no cost to you.  I am a minimally competent stylist but I can work with designers if you have one you prefer.  I will host the site for you.  You will have administrative control over the posting of results and schedules, and email + telephone access to me for assistance or advice.  I would probably charge about $200 per month ($2,400 per year) for something like this, for as long as you want to use my services.  You retain copyright in the data.  I retain copyright in the code and data base.  If you think something like that makes sense let me know.  With 10 teams, the cost would work out to something like $20 per team per month.  Most teams can spill that much on their uniforms in the pub, so you are not talking about any significant expense.  In the long run you may come out ahead, financially, if you use the college students, but there will not be anyone there to help with changes, if needed, after the launch.  

I respect any choice you make, including doing it yourself as a learning project!

Best regards, ~Ray
james130cAuthor Commented:
Hi there,

Thanks for the reply, The Fantasy Head to Head league happens to be just a tiny part of the overal project. It's not actually based on winning football matches but rather based on picking players from (English Premier League) so you have a team of 11 players who score you points. Basically then at the end of the week if your total points accumulated by all your players that week beats your opponents you win that match-up. So in the league standings you would be W1 - L0 - Tie0 for example. The loser will be W0 - L1 - Tie0.

The overal idea is that someone can sign up and create a league. Invite their buddys to it and when its full they can draft their players for their teams. On a chosen draft date. Draft order would have to be random.. So everyone has a fair chance of being 1st pick. When draft is finished and teams are chosen, create the match-ups. So ASAP they will know who they are playing every week etc.. Then when the start day comes.. the fixtures kick in.

So not just one league, hopefully many leagues created by people within their account to enjoy with their buddys etc.. We have done something similar but its just been a standard league where there is like 200-300 teams or more in one league and that way its just boring and your chances aint great to win. Where as in small leagues head to head your playing someone new each week and each week you have a chance to get a win to help you towards being top overall of the league standings.

We would actually like to have open leagues for anyone to join and also private leagues through invite or password only. Also we would probably easily do a free for all open league where its just one big league and top 5 or 10 etc.. depending on entrants get a prize at end of the season. That won't work weekly obviously that will just be all season long.

So there is a few different game types for users to choose from etc..

I mean I would considder hiring you under the terms you stated if #1 we could host it ourselves as we would be constantly updating points etc and we are familiar with our own hosting etc.. for ease of use. And #2 we design our own site. The talent behind making it all work etc and the rest would be all you. lol

But I totally understand if this is too much of a task for you to take on now you understand the full idea behind it all... As it's a bit different to what you was thinking.

We are just crazy about Fantasy Football, we are from england and we love the american Fantasy Football NFL... its great. So we just thought hey why not try do something like this but for english premier league not NFL. We love the whole head to head leagues etc but there isnt really many if any that do it for english fantasy football. So we figured we could maybe turn the idea into something that will be fun to play and maybe even make a successful website out of it. I.E not just us playing it but others too.

What are your thoughts on this Ray?
Ray PaseurCommented:
I agree with you about Fantasy Football - it's enormously popular in the USA and generates hundreds of millions of dollars a year from all sorts of revenue sources.  I know many of the players in the field.

But as to writing bespoke application code and turning it over, well, that does not make business sense to me.   I would not be interested in writing code for you and creating a data base that you would host.  The business model does not work if I do not keep 100% control over the code and data base that generates the web site.  Ownership ensures my revenue stream.  You have the site as long as you pay, and if you do not pay the consequence is that you do not have the site any more.  This is not rocket science, and I am willing to engage a long-term, fixed price contract.  But I am not a fool.  I'm sure you can understand.  Go look for the college kids, and if you change your mind and want professional services, please think of me.

It's a great idea - I hope someone will take the ball and run with it!

All the best, over and out, ~Ray
james130cAuthor Commented:
Thanks for the reply. In terms of you hosting it... Am I right in thinking there will be complete control for an admin then to update things through the website .. for example points for players etc. The only reason we thought to host it was we used to update straight through the db.  However if there was fully functional admin panel etc that wouldn't be nesscessary.

I mean we are definitely interested if you are able to do it.. then we can talk further and maybe I can write up a detailed plan of what we would like etc and go from there?

What do you think?

Ray PaseurCommented:
In any case (me, you or college kids) you will need a fairly detailed, written, plan.  I already know the design patterns and the table maintenance that would be required, but reducing it to writing will give you some additional clarity about what you want to see.  The most important part, from the data base standpoint, will be the relationships between the tables.  There are three numbers that matter: zero, one, and infinity.  Relationships are either non-existent, one-to-one, one-to-many or many-to-many.  

The kinds of questions you need to clarify are, "Can a player be in more than one team at once?"  In real life this answer is "no" so a player has a one-to-one relationship to teams.  Teams have a one-to-many relationship with players.  But in fantasy life, the rules can be different.  You'll need clarity about those rules before the contests can begin, and before the data base can be designed.  The rules will need to be there no matter who might be part of the web site.

As part of your research, make a Google search for fantasy soccer and see if it gives you any ideas.  Best, ~Ray
james130cAuthor Commented:
Thanks Ray,

Ill do some research and come up with a detailed plan, this way you will have a full understanding of how we would like it to be. And for ourselves we will know for sure if that's what we want once it's all planned out.

What you pointed out is correct though... In real life a player can only play for one team. But with 99% of fantasy soccer games out there they allow every team in the league to pick the same player. This is not someone we want. (At least in head to head formats). For example if there is a 12 man league, once a player is chosen he is now gone from the list of players available (in that perticuar league). In the standard format (one big league) there will be way too many teams to work this way so there is no choice but to allow the same players to be picked by all teams.

However does this mean you'd be interested in doing this for us if after looking over our detailed plan you believe you can do it for us?

Thanks for your time.

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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now