SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 60
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    In above my head - Sports League fixture and results system

    Hi,

    I started developing a site for my Local Amateur Rugby League generally where they could post news etc.

    One of the new functionalities they would like is a system where they can add fixtures for each of the age groups.

    U15s - 3 Leagues of 8 Teams
    u14s - 4 Leagues of 8 Teams
    u13s - 4 Leagues of 8 teams.

    Currently I have implemented a system where by every tuesday they add the fixtures together with referee information, kick off times etc.

    This works fine and whilst I am sure it probably isnt implemented the correct way it works.

    To follow on from that they would like a searchable results page as well, problem is that when the next weeks fixtures are added, the previous weeks are deleted.

    Is there a way that I can have the information in Fixtures moved to a Results table and the Fixtures table emptied ready to have the following weeks fixtures entered?

    I really would appreciate any help with this as I am only new to PHP, MYSQL and its one of those things where you are involved with a group and are asked to build a site, then they want more and you find it difficult to say no.

    Anyone any Ideas?

    The site is here - http://www.nwc13-15arl.co.uk

    The admin panel can be found here http://www.nwc13-15arrl.co.uk/admin

    Many thanks in advance

    Paul

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about a DB column which indicates the state of a given match?
    For example you could have a played column which defaults to 0 for upcoming matches, then just update to 1 once the game has been played and the result known.

    Modify the various parts of your system to select only played/unplayed matches as necessary.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response

    Just to take that one further, There is a field in the database that stays blank until the game has been played when the final score is inputted.

    Could I make that work as the switch instead of adding another fields using the 0/1 system as suggested.

    If the result field is left blank it shows on the fixtures page but if it has a score in it it shows on the results page?

    Thanks

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    ...until the game has been played when the final score is inputted.
    Can you show an example of what you mean by that?

    I mean, is it a single text field that summarises the score, or does each team/game have its own final score integer input?

  5. #5
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When the fixture is added, the result field will be left blank and then after the fixture someone will edit the record to input the final score as say 22 - 16

    Also does anyone know of a good tutorial for conditional dropdowns. I would like to build 1 form to input fixtures for the 3 age groups. So when say under 13s is selected as the 1st drop down, then only the teams recorded as under 13s become available in the home and away team drop down?

    many Thanks

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    By reading your comments I'd say you were new to this. We all started somewhere though!

    Judging by your reply that the scores are in a text field "22 - 16", then you are missing a huge opportunity.

    Surely you'd want to be entering the equivalent of this:

    Team_number "21" in game_number "317" scored "22" points and final_score - "true"

    The fact that the final score was 22-16 and team 21 won team 07 should be worked out by the computer.

    By associating single scores to individual teams and games that way , you can then go on and work out aggregated scores, highest scores so far this season, average scores, etc etc.

    Year on year comparisons and loads more.

    Isn't that what you want to do, ultimately?

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Erm yes please, is there a tutorial!

    In all honesty I am very new to it hence the title of this post!!

    They wanted a real basic system and then added more, and more, and more to the point where I am way in above my head.

    I am sure you all had these projects when you where new to it, helping out a friend etc.

    I even looked for a script that I could possibly canibalise but found nothing which leaves me thinking 2 things either there is no call for one or it is bloomin hard to do!!

    So will what I proposed work for me?

    I would love to chuck this a programmer but its a kids rugby league completely self funded etc etc and there is nowt in the pot so I will struggle on!

    Many Thanks

    Paul

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Cape Cod
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had a bunch of these when I first started and I found that there are times when you need to re-evaluate the system and possible redo the basic structure of the DB. This is a good time to sit down with the people who are telling you what they want and try to figure out what they may want in the future as well.

    DB's really need to be thought out in the beginning so you don't paint yourself into a corner in the end.

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by geordiewire View Post
    Erm yes please, is there a tutorial!
    There are tutorials for everything, the problem is identifying what you need to learn next. See further down...

    Quote Originally Posted by geordiewire View Post
    They wanted a real basic system and then added more, and more, and more to the point where I am way in above my head.
    They always do that.

    Quote Originally Posted by geordiewire View Post
    I am sure you all had these projects when you where new to it, helping out a friend etc.
    Yes, if you are lucky you do. The thing is this, you have already done the hard part - honestly, you have.
    You have got your head round php, mysql and html. The level is not that important.
    You have the system working, and you can store and extract data.
    You have people for whom you 'work' who keep stretching your skills.
    ... and most important - what you are doing makes a difference to real people. Do it right and more kids will be interested and want to play rugby ... (less mental problems, healthier, better influences around them - its important (ok, more bruises - yes I played - once only.))

    Quote Originally Posted by donniep
    I had a bunch of these when I first started and I found that there are times when you need to re-evaluate the system and possible redo the basic structure of the DB. This is a good time to sit down with the people who are telling you what they want and try to figure out what they may want in the future as well.
    DonnieP is exactly right here, but of course this realisation only comes with the experience of doing. The first project featuring a database is always going to be the hardest, after that your list of "potholes to avoid" really ramps up pretty fast - as does your questioning skills!

    Quote Originally Posted by donniep
    DB's really need to be thought out in the beginning so you don't paint yourself into a corner in the end.
    Nicely put.

    Quote Originally Posted by geordiewire View Post
    I even looked for a script that I could possibly canibalise but found nothing which leaves me thinking 2 things either there is no call for one or it is bloomin hard to do!!
    Yes, you could probably find a script which does this for soccer teams - what you will find ( like almost everything in software ) it that it is not until you use it that you find its doesn't quite do what you want, and has features you - well, you just don't need - and when you start to want to extend it you have to learn from scratch all the same.

    Quote Originally Posted by geordiewire View Post
    So will what I proposed work for me?
    What you should do is tell us a bit more about what you have already, and ask how it could be improved. Without doubt your database schema (setup) needs improving, hence my earlier questions.

    There are plenty of database dabblers like me, and some true sql gurus who inhabit this forum who are very happy to help someone like you who has shown he is willing to learn.

    You will probably get introduced to some new terms and get links to articles to read up on, but you can learn at as leisurely a pace as you need.

    Quote Originally Posted by geordiewire View Post
    I would love to chuck this a programmer but its a kids rugby league completely self funded etc etc and there is nowt in the pot so I will struggle on!
    Nah, its more fun to do it yourself and learn as you are going along. Doing it for nowt is the php way. ( till you add up all the hours of course ... )

    Quote Originally Posted by geordiewire View Post
    Paul
    Me too, I am a Paul.
    If you want, start off by showing us your db schema - just the structure of the important tables.

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by geordiewire View Post
    Erm yes please, is there a tutorial!
    There are tutorials for everything, the problem is identifying what you need to learn next. See further down...

    Quote Originally Posted by geordiewire View Post
    They wanted a real basic system and then added more, and more, and more to the point where I am way in above my head.
    They always do that.

    Quote Originally Posted by geordiewire View Post
    I am sure you all had these projects when you where new to it, helping out a friend etc.
    Yes, if you are lucky you do. The thing is this, you have already done the hard part - honestly, you have.
    You have got your head round php, mysql and html. The level is not that important.
    You have the system working, and you can store and extract data.
    You have people for whom you 'work' who keep stretching your skills.
    ... and most important - what you are doing makes a difference to real people. Do it right and more kids will be interested and want to play rugby ... (less mental problems, healthier, better influences around them - its important (ok, more bruises - yes I played - once only.))

    Quote Originally Posted by donniep
    I had a bunch of these when I first started and I found that there are times when you need to re-evaluate the system and possible redo the basic structure of the DB. This is a good time to sit down with the people who are telling you what they want and try to figure out what they may want in the future as well.
    DonnieP is exactly right here, but of course this realisation only comes with the experience of doing. The first project featuring a database is always going to be the hardest, after that your list of "potholes to avoid" really ramps up pretty fast - as does your questioning skills!

    Quote Originally Posted by donniep
    DB's really need to be thought out in the beginning so you don't paint yourself into a corner in the end.
    Nicely put.

    Quote Originally Posted by geordiewire View Post
    I even looked for a script that I could possibly canibalise but found nothing which leaves me thinking 2 things either there is no call for one or it is bloomin hard to do!!
    Yes, you could probably find a script which does this for soccer teams - what you will find ( like almost everything in software ) it that it is not until you use it that you find its doesn't quite do what you want, and has features you - well, you just don't need - and when you start to want to extend it you have to learn from scratch all the same.

    Quote Originally Posted by geordiewire View Post
    So will what I proposed work for me?
    What you should do is tell us a bit more about what you have already, and ask how it could be improved. Without doubt your database schema (setup) needs improving, hence my earlier questions.

    There are plenty of database dabblers like me, and some true sql gurus who inhabit this forum who are very happy to help someone like you who has shown he is willing to learn.

    You will probably get introduced to some new terms and get links to articles to read up on, but you can learn at as leisurely a pace as you need.

    Quote Originally Posted by geordiewire View Post
    I would love to chuck this a programmer but its a kids rugby league completely self funded etc etc and there is nowt in the pot so I will struggle on!
    Nah, its more fun to do it yourself and learn as you are going along. Doing it for nowt is the php way. ( till you add up all the hours of course ... )

    Quote Originally Posted by geordiewire View Post
    Paul
    Me too, I am a Paul.
    If you want, start off by showing us your db schema - just the structure of the important tables.

  11. #11
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HE HE, You will like this, Not a lot

    First of all, the league tables are all stored in seperate tables as follows

    13PREM
    13DIV1
    13DIV2
    13DIV3
    14PREM
    14DIV1
    14DIV2
    14DIV3
    15PREM
    15DIV1
    15DIV2

    Each of these tables are as follows

    ID int
    Team Varchar 60
    Played int
    won int
    drawn int
    lost int
    for int
    against int
    diff int
    points int

    Each table has an add form and an update form and all updates are done manually through this, the computer working everything out would be much easier.

    Teams

    Team_ID int
    Age_group_id (Foriegn Key)
    Team

    Fixtures & Results

    fixture_id int (Primary Key)
    date date
    fixture_type (Foreign key from competition table) (Pick friendly, League, Cup etc)
    round_id (Foreign key from round table) (Picks 1st, 2nd, semi, etc)
    age_id (Foriegn Key from Age Group Table)
    Home-id (Foreign Key from Team Table)
    Away_id (Foreign Key from Team Table)
    kick_off
    Referee_id (Foreign Key from Referees table)
    result
    finalised (Hopefully remvoes from fixtures and turns into result)

    Can anyone make sense of that?

    Paul

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Without delving too deeply into your schema, what strikes me first is why doesn't fixturesandresults have a field called something like home-score and away-score?

    That way you'd be able to get all scores like (pseudo sql) " ..where home-id =27 and away-id = 27 ..." - that'd give you all points scored for team 27 for the season - then lead onto, for example, highest score for team 27 highest score so far this season, closest match this season etc.

    Once you have the data, it just a matter of finding an sql query, then trying to make a better sql query. As you realise which queries are most frequently used then you can work backwards and go back and try and change your schema to facilitate these frequent queries - make them more succinct - and faster.

    At this point you just want it work though.

    Getting all from fixturesandresults where fixture id = 1234 would allow you to work out the winner by comparing home-score with away-score - wouldn't it?

    Is there something I haven't understood maybe?

    Do individual players feature in your schema? Isn't that what your users want too? - or is that "later on".
    Last edited by Cups; Jan 9, 2008 at 10:03. Reason: some edits

  13. #13
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suppose it would be fairly easy to collect a home score and an away score where I am a little flumoxed is how you would then link each to the individual teams.

    Individual players will not feature as it is a site for the controlling league and not individual clubs.

    We also have to be careful what is shown on the web due to child protection.

    FWIW I now have a functioning form you will be pleased to know that I can add the fixtures with and it is succesfuly entering the foreighn keys for all the different parts such as team id1 is home and team id6 is away and it was a fixture type1

    I am actually quite chuffed to have got as far as I have in a llittle over a week but you are correct I could do so much more.

    I suppose the answer is to get the fixtures and results system working and then see what I can add next.

    Your really pushing my thought process for which I am grateful just wish I had more answers than questions.

    Paul

  14. #14
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Right, got your point re individuals. You are right to be chuffed, its no mean feat getting anything working at all, especially if its your first time doing something.
    I suppose it would be fairly easy to collect a home score and an away score where I am a little flumoxed is how you would then link each to the individual teams.
    Well, lets stick with original example. Team27 lets call them "Scallypool" has just scored 22 points.

    pseudo sql:
    Something similar to this should work - just need to match the match day with a proper statement:

    "UPDATE
    fixturesandresults set home-score = 22
    WHERE match_day = today and home-id=27 and home-score=0
    OR
    WHERE match_day = today and away-id=27 and home-score=0"

    // i.e. Here I am suggesting you try to update just by knowing the team id
    // and the date - and to reduce reliance on home/away pre-knowledge
    // being needed - would that work?

    Then :
    "update 13Prem set points_for = points_for + 22 where team = 27".

    //So how would you know Team 27 "Scallypool" was in division 13Prem? Do a select first? This needs rethinking.

    Later, in PHP, you will be using variables like $score and $team instead of 27 and 22 - get me?

    Read in the manual the bit on UPDATE and the simple arithmetic- search the manual for this phrase:
    Code:
    mysql> UPDATE persondata SET age=age+1;
    Manual sections:
    12.4. Numeric Functions
    12.4.1. Arithmetic Operators

    Example of how to create things simple arithmetic, things like averages, not in PHP but using mysql native functions - these are blindingly fast compared to collecting things from the db, into PHP manipulating and then saving back into mysql.

    Also, here's a few simple practices to follow when doing this kind of work.

    Use a dbadmin package like PhpMyAdmin.

    Copy your existing tables as they are to another database, say called "test", copy the data and the structure - the first task would typically to get the database fiddled around with, and make sure you can do the sql queries - and they come out with results that you expect.

    Every time your make significant changes to the database, then copy it again to another database, then you can at least "roll back" a bit.

    You would touch very little PHP at this point.

    You might save the sql statements that do work into a text file, perhaps with a simple note
    Code:
    #
    # updates scores
    #
    
    "sql statement here";

  15. #15
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    If you are really in this for the long haul then consider taking the home_id and the away_id out of your fixture tables and using another table to assign teams to games. In my soccer system I have

    team
    team_id
    team_name

    game
    game_id
    game_date

    game_team
    game_id
    team_id
    type Home or Away
    score

    I actually have a bunch more fields but you can see that by moving information about a specific game-team into it's own table removes a certain amount of duplication and makes many queries easier.

    I use another table called game_official to allow assigning multiple referees to a particular game.

  16. #16
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    You are no doubt correct.

    I was working on the principal that we are part way through the season and geordiewire just wanted to take a few steps to enhancing his website while keeping the whole thing working. He has an audience to satisfy.

    Hence I thought - lets just add a few fields for now, and work to incrementally a) improve what he has and b) explore some new functionality of php/mysql that might help him work out how to improve what he has.

    Together with your ideas, by the end of the rugby season - he'd be in a better position to appreciate your solution and know how to rewrite things himself.

  17. #17
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really appreciate all the ideas and it is really making me think about what I am doing!

    What I have decided is to implement a functional system that I can roll out and put live as the season only runs until April and then doesnt resume until September so that gives me a great window to work on new stuff for next season.

    One of the big things I forsee is adding a division cell to the teams table so I can automatically pull the correct team into each league table. Then I need to add the functionality for it to work out Played, won, drawn, lost etc

    For now I have my form built to add fixtures to the database, I have left it with just a result cell rather than a home and away as discussed just due to time constraints of getting this rolled out.

    I now have to go ahead and build the fixtures page and also the results page with search functionality so that people can look at results by date, age group, individual team so expect me to be back asking questions.

    First question is how do I write the SQL query to Show say under 13s league games with results = null? I understand how to get it to show u13 league, u13 cup etc but how do i put the switch in so that only games with the result cell being empty are shown?

    Cups, Are you UK based?

    Once again

    Many Thanks

    Paul

  18. #18
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just come across another problem and think I may have done something wrong,

    Obvously the fixtures table holds all the links to the other tables through forigen keys.

    When I build my sql query in dreamweaver just to show all results of the database it comes out as follows

    Date Home Away Kick Off Referee
    2007-01-13 111 4 10:30:00 14
    2007-01-13 1 9 10:30:00 9

    How do I get the display to show the corosponding values for 111, 4 and 14 rather than the actual ID Value?

  19. #19
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can see what I mean here

    www.nwc13-15arl.co.uk/fixtures1.php

    The add Fixture form that I have built is here

    http://www.nwc13-15arl.co.uk/admin/add_fixture.php

    The display page now works on displaying where age group =15 and where finalised =0

    The finalised cell, defaults to 0 and when a result is added that zero is changed to meaning it is no longer a fixture but a result.

    Just need to get it displaying the words correctly now!!

  20. #20
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You will want to use left joins
    Code:
    SELECT
      Fixtures.date AS fixtures_date,
      away_team.team AS away_team_name,
      home_team.team AS home_team_name
    FROM
      fixtures
    LEFT JOIN teams AS away_team ON away_team.team_id = fixtures.away_id
    LEFT JOIN teams AS home_team ON home_team.team_id = fixtures.home_id
    ;
    Last edited by ahundiak; Jan 11, 2008 at 13:57. Reason: Removed comma from left join

  21. #21
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Left Joins, you said I would learn some new terms!!

    I am going to have a look on the web and see if I can get my head around them however if possible I have uploaded my SQL Query here

    http://www.nwc13-15arl.co.uk/sqlquery.gif

    Would it be possible for you to Restructure it with the left joins in the right place just so I can use it as a real world example?

    Thanks for your help

    Paul

  22. #22
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have read through some tutorials on this but just dont seem to be able to make it work in my database.

    Help?

  23. #23
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    You will want to use left joins
    Code:
    SELECT
      Fixtures.date AS fixtures_date,
      away_team.team AS away_team_name,
      home_team.team AS home_team_name
    FROM
      fixtures
    LEFT JOIN teams AS away_team ON away_team.team_id = fixtures.away_id,
    LEFT JOIN teams AS home_team ON home_team.team_id = fixtures.home_id
    ;
    I tried copying and pasting that code but it does not work, I have also tried writing it from picking snippets up from tutuorials but I am constantly getting errors

  24. #24
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I didn't want to try writing it for you working off a screen copy. Paste your current query and I'll take a look at it. No PHP stuff. Just the query like I posted before.

  25. #25
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by geordiewire View Post
    You can see what I mean here

    www.nwc13-15arl.co.uk/fixtures1.php

    Just need to get it displaying the words correctly now!!
    I am pretty sure I am asking the same as ahundiak ...

    So what is the sql statement which is getting the data for that table, why isn't it getting the name of the team instead of just the team ref number?

    also, use the command "describe <table name here>" and post the output so we can see exactly what kind of columns your table contains.

    Hope you don't mind me saying so, but you will find a tool like PhpMyAdmin a godsend when it comes to manipulating and viewing data, copying tables etc etc. It is a set of PHP scripts and is pretty straightforward to set up and use on say, your "localhost".

    www.phpmyadmin.net


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •