SitePoint Sponsor

User Tag List

Page 2 of 3 FirstFirst 123 LastLast
Results 26 to 50 of 60
  1. #26
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  2. #27
    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)
    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?
    Code:
    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?

  3. #28
    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)
    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.

  4. #29
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #30
    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)
    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

  6. #31
    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)
    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

  7. #32
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  8. #33
    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)
    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

  9. #34
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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.

  10. #35
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #36
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    what SQL are you using (which messes up)?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  12. #37
    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)
    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.

  13. #38
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Just take it one field at a time and make sure you keep track of your commas when specifying columns.

  14. #39
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  15. #40
    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)
    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.

  16. #41
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  17. #42
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Nope. You just need to add aliases to your joins. Like i showed in my first post.
    Code:
    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.

  18. #43
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  19. #44
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  20. #45
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  21. #46
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

  22. #47
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  23. #48
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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!

  24. #49
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  25. #50
    SitePoint Enthusiast
    Join Date
    Jun 2006
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'


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
  •