In above my head - Sports League fixture and results system

[geordiewire]

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

[cranial-bore]

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.

[geordiewire]

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

[Cups]

...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?

[geordiewire]

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

[Cups]

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?

[geordiewire]

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

[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.

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

[Cups]

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...

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.

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.))

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!

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.

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.

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.

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 ... )

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.

[Cups]

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...

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.

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.))

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!

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.

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.

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.

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 ... )

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.

[geordiewire]

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

[Cups]

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".

[geordiewire]

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

[Cups]

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:

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

#
# updates scores
#

"sql statement here";

[ahundiak]

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.

[Cups]

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.

[geordiewire]

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

[geordiewire]

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?

[geordiewire]

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!!

[ahundiak]

You will want to use left joins

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
;

[geordiewire]

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

[geordiewire]

I have read through some tutorials on this but just dont seem to be able to make it work in my database.

Help?

[geordiewire]

You will want to use left joins

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

[ahundiak]

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.

[Cups]

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