In above my head - Sports League fixture and results system

[geordiewire]

I have phpmyadmin setup on my localhost through xampp

The current query is

SELECT fixture_date, fixtype_id, age_group_id, home_id, away_id, kick_off, referee_id
FROM fixtures
WHERE fixtures.fixtype_id =1
AND fixtures.age_group_id =15
AND fixtures.finalised =0
LIMIT 0 , 30

Fixtrues Table

fixture_id int(11) NO PRI NULL auto_increment
fixture_date date NO
fixtype_id int(11) NO
round_id int(11) YES NULL
age_group_id int(10) unsigned NO
home_id int(10) unsigned NO
away_id int(10) unsigned NO
kick_off time NO
referee_id int(10) unsigned NO
result varchar(10) NO
finalised int(11) NO

Does that give you all the info you require?

[Cups]

Does that give you all the info you require?

Well, enough to see that getting the team names will not be possible without another select - unless some guru comes and puts me right.

How about something a bit more trivial till then?

SELECT date_format('%a %d %b %Y', fixture_date) as nice_fixture_date, fixtype_id, age_group_id, home_id, away_id, kick_off, referee_id

FROM fixtures LEFT JOIN referees on 
fixtures.referee_id= referees.referee_ID

WHERE fixtures.fixtype_id =1
AND fixtures.age_group_id =15
AND fixtures.finalised =0
LIMIT 0 , 30

Does that bring out ref's names and a prettier date?

[Cups]

The problem is that this fixtures table is storing too much.

I rightly stores meta data about the fixture, but the references to the teams and their actual scores should ideally be in another table.

This feature of extracting fields is known as normalization. Each instance of a team and a match should be kept in a separate table.

fixtures
======
fixture_id int(11) NO PRI NULL auto_increment
fixture_date date NO
fixtype_id int(11) NO
round_id int(11) YES NULL
age_group_id int(10) unsigned NO
kick_off time NO
referee_id int(10) unsigned NO
finalised int(11) NO

|
links
|

team_fixtures
==========
tf_id
home_id int(10) unsigned NO
away_id int(10) unsigned NO
home_score
away_score

|
links
|


teams
=====
team_ID
age_group_id (FK)
team

With real data team_fixtures would look something like this after each match.

team_fixtures
==========
1| tf_id (pk)
27 | home_id (fk)
134|away_id (fk)
22 | home_score
16 |away_score

Every match (perhaps thats a better name for the table ...) would contain a link to its fixture meta data (time age grp etc) AND a link to each team.

Its also probably a good place to keep the scores. So linking team number "27" with all of its results would be pretty trivial.

Now this normalization might be the correct thing to do after April, but for now, you are likely going to have to do 2 selects to get the names of each team on your webpage. Long winded reply, sorry.

[geordiewire]

Ok,

Tried the SQL Query that you posted and it didnt work at first until I changed some column names etc. It is now outputting however has made no difference.

The date column now shows null and the referee column still shows the ID.

[Cups]

Brill!

Here's the principle on the referees: on this line:

FROM fixtures LEFT JOIN referees on
fixtures.referee_id= <referees table name>. <unique_key_for_referees>

I am presuming your table for referees is called "referees", so yeah, you want to select referees name - let me guess is that called "referee" ? I forgot to add that column name into your select, sorry..

So add it to your select here:

SELECT date_format('&#37;a %d %b %Y', fixture_date) as nice_fixture_date, fixtype_id, age_group_id, home_id, away_id, kick_off, referee_id, referee
...

Make sure you are pasting in and looking at the results via your copy of PMA (phpmyadmin) then you should see a column called nice_fixture_date

[Cups]

SELECT date_format('&#37;a %d %b %Y', fixture_date) WRONG

Got those arguments the wrong way round, dude, sorry.

SELECT date_format( fixture_date, '%a %d %b %Y' ) RIGHT

[geordiewire]

Ok, this is the latest query I have used

SELECT date_format( fixture_date, '%a %d %b %Y' ) as nice_fixture_date, fixtype_id, age_group_id, home_id, away_id, kick_off, referee_id, referee

FROM fixtures LEFT JOIN referee on
fixtures.referee_id = referee.id

WHERE fixtures.fixtype_id =1
AND fixtures.age_group_id =15
AND fixtures.finalised =0
LIMIT 0 , 30

The response is

Error

SQL query:

SELECT date_format( fixture_date, '%a %d %b %Y' ) AS nice_fixture_date, fixtype_id, age_group_id, home_id, away_id, kick_off, referee_id, referee
FROM fixtures
LEFT JOIN referee ON fixtures.referee_id = referee.id
WHERE fixtures.fixtype_id =1
AND fixtures.age_group_id =15
AND fixtures.finalised =0
LIMIT 0 , 30

MySQL said:

#1054 - Unknown column 'referee' in 'field list'

I dont think this is ever going to work!

[Cups]

I dont think this is ever going to work!

Of course it will ... it just seems like it never will.

Show me the makeup of the referee table.

DESCRIBE referees

[ahundiak]

Sure it will. Just need to understand a few concepts. First off, it's generally better to try and keep the queries as simple as possible when trying to understand new things. Fewer things to go wrong.

I'm assuming you have a table called referee and it contains a column called id and maybe one called referee? Lets assume for now we have a table called referee with columns called id and name.

One you start pulling data from multiple table you have to be careful to specify exactly which table which column comes from. You could easily have the same column name in multiple tables. SQL tries to guess at what you want but it doesn't always work. So it's best to use the tableName.columnName format when bulding queries. That way you remove all possible ambiguity.

It's also best to alias all columns (and in some cases table names) again to prevent name collusions and to remove any possible ambiguities.

So to join fixtures and referee try:

SELECT
    fixtures.fixture_date AS fixture_date,
    referee.id AS referee_id,
    referee.name AS referee_name
FROM fixtures
LEFT JOIN referee ON fixtures.referee_id = referee.id
WHERE blah blah

Get this working then add in your pretty date stuff and the other fields.

[geordiewire]

OK, That works

I have tried expanding it to do the same with the other fields in the fixtures table and it goes belly up

Arrrrgh

[Jake Arkinstall]

what SQL are you using (which messes up)?

[Cups]

OK, That works

What? Do you mean you did your first join? You got the refs name out just by specifying the fixture details?

Bravo if you did - and to ahundiak for explaining it so clearly.

Maybe you should sleep on that and give it time to trickle into your brain... it is a profound thing to come to terms with.

[ahundiak]

Just take it one field at a time and make sure you keep track of your commas when specifying columns.

[geordiewire]

Hi, Arkinstall

You will regret asking that

SELECT
fixtures.fixture_date AS fixture_date,
competitions.id AS fixtype_id,
competitions.competition AS competion_name,
age_group_id,
teams.team_id AS home_id,
teams.team AS home_team,
teams.team_id AS away_id,
teams.team AS away_team
kick_off,
referee.id AS referee_id,
referee.name AS referee_name
FROM fixtures
LEFT JOIN competitions ON fixtures.fixtype_id = competions.id,
LEFT JOIN teams ON fixtures.home_id = teams.team_id,
LEFT JOIN teams ON fixtures.away_id = teams.team_id,
LEFT JOIN referee ON fixtures.referee_id = referee.id
WHERE fixtures.fixtype_id = 1 AND fixtures.age_group_id = 15 AND fixtures.finalised = 0

[Cups]

LEFT JOIN teams ON fixtures.home_id = teams.team_id,
LEFT JOIN teams ON fixtures.away_id = teams.team_id,

Well, you are picking it up pretty quick. But you see, that's the bit I balked at when I first looked, I cannot see how you can select 2 different team names and even if you did - I cannot see how you expect to know which is the home and away team.

Hence my comments about normalization.

I hope I am wrong, and someone teaches me something I didn't know.

There's lots you can do to make your screens look right, but I fear you will have to do 2 subselects to get the team names.

[geordiewire]

Are we now suggesting that I have hit another brick wall and need to restructure the database again.

I am becoming really frustrated now as I just dont seem to be able to pick this up!

Looking at the system that you showed before 'Normalisation', I can see why you would build all the seperate tables however I wouldnt even know how to build an insert fixture form to insert the data into all the different tables never mind how to get the data back out and displayed on the site.

I am more confused now than I was when I started this on Tuesday.

Paul

[ahundiak]

Nope. You just need to add aliases to your joins. Like i showed in my first post.

LEFT JOIN teams AS home_team ON fixtures.home_id = teams.team_id  No comma!
LEFT JOIN teams AS away_team ON fixtures.away_id = teams.team_id

Use the table alias when picking columns
home_team.team AS home_team_name,
away_team.team AS away_team_name,

And don't use commas after join clauses, seems a bit unnatural but thats the way it is.

[geordiewire]

This is the SQL statement I have now but it is still showing an error

SELECT
fixtures.fixture_date AS fixture_date,
competitions.id AS fixtype_id,
competitions.competition AS competion_name,
age_group_id,
teams.team_id AS home_id,
home_team.team AS home_team_name,
teams.team_id AS away_id,
away_team.team AS away_team_name,
kick_off,
referee.id AS referee_id,
referee.name AS referee_name
FROM fixtures
LEFT JOIN competitions ON fixtures.fixtype_id = competions.id
LEFT JOIN teams AS home_team ON fixtures.home_id = teams.team_id
LEFT JOIN teams AS away_team ON fixtures.away_id = teams.team_id
LEFT JOIN referee ON fixtures.referee_id = referee.id
WHERE fixtures.fixtype_id = 1 AND fixtures.age_group_id = 15 AND fixtures.finalised = 0

The error is

#1052 - Column 'age_group_id' in field list is ambiguous

[Jake Arkinstall]

You'll need to give it a table name prefix, so it knows which table to select age_group_id from.

Technically you shouldn't need to select it anyway, as you specify the age group in the WHERE statment.

[geordiewire]

Ok 2 questions there, why would it not pick the table name up from the FROM statement

and also I didint realise that the WHERE statement would work without the colum being selected in the SQL statement first.

So fingers crossed I run it without and it should work yes?

[ahundiak]

These will also cause problems:
teams.team_id AS home_id,
teams.team_id AS away_id,
Should be
home_team.team_id AS home_team_id,
away_team.team_id AS away_team_id,

And age_group_id is ambiguous because one of your other tables must have an age_group_column as well. Just use tableName.columnName everywhere and the problem goes away.

[geordiewire]

Updated Query

SELECT fixtures.fixture_date AS fixture_date, competitions.id AS fixtype_id, competitions.competition AS competion_name, home_team.team_id AS home_team_id, home_team.team AS home_team_name, teams.team_id AS away_id, away_team.team_id AS away_team_id, fixtures.kick_off, referee.id AS referee_id, referee.name AS referee_name
FROM fixtures
LEFT JOIN competitions ON fixtures.fixtype_id = competions.id
LEFT JOIN teams AS home_team ON fixtures.home_id = teams.team_id
LEFT JOIN teams AS away_team ON fixtures.away_id = teams.team_id
LEFT JOIN referee ON fixtures.referee_id = referee.id
WHERE fixtures.fixtype_id =1
AND fixtures.age_group_id =15
AND fixtures.finalised =0


New Error Message

#1054 - Unknown column 'teams.team_id' in 'field list'

Should this realy be this complicated or have I over complicated this part by not breaking down the tables in the first place?

[ahundiak]

You might error out on these as well:
LEFT JOIN teams AS home_team ON fixtures.home_id = teams.team_id
LEFT JOIN teams AS away_team ON fixtures.away_id = teams.team_id
I'm confident yu can figure out why. Almost there!

[geordiewire]

You are indeed correct, SQL statement now stands at

SELECT
fixtures.fixture_date AS fixture_date,
competitions.id AS fixtype_id,
competitions.competition AS competition_name,
home_team.team_id AS home_team_id,
home_team.team AS home_team_name,
away_team.team_id AS home_team_id,
away_team.team AS away_team_name,
fixtures.kick_off,
referee.id AS referee_id,
referee.name AS referee_name
FROM fixtures
LEFT JOIN competitions ON fixtures.fixtype_id = competitions.id
LEFT JOIN teams AS home_team ON fixtures.home_id = teams.team_id
LEFT JOIN teams AS away_team ON fixtures.away_id = teams.team_id
LEFT JOIN referee ON fixtures.referee_id = referee.id
WHERE fixtures.fixtype_id =1
AND fixtures.age_group_id =15
AND fixtures.finalised =0

With error message

#1054 - Unknown column 'teams.team_id' in 'on clause'

Every chaange I have made in the LEFT JOIN statements so far have caused an error to come up about field not being in list!

[geordiewire]

I expected if I removed the 2 team LEFT JOINS then the rest of the query would execute but alas it does not it errors with

#1054 - Unknown column 'home_team.team_id' in 'field list'