SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Multiple print_r columns? Why?

    I have this query:
    Code:
    SELECT DISTINCT
    g.home_id, g.guest_id,
    sp.sport, sp.id, st.state, st.id,
    th.school, tg.school
    FROM ws_games as g
    JOIN ws_teams as th 
        ON g.home_id = th.id
    JOIN ws_teams as tg 
        ON g.guest_id = tg.id
    JOIN ws_state as st 
        ON st.id = g.state 
    JOIN ws_sport as sp 
        ON sp.id = g.sport 
    WHERE g.sport = $sport
    AND g.year = $year
    AND g.state = $state
    AND g.week = $week
    I can find all the information I need within it, but not with useful names on some of them.

    Code:
    Array (
      [0]        => 18
      [home_id]  => 18
      [1]        => 19
      [guest_id] => 19
      [2]        => Football
      [sport]    => Football
      [3]        => 1
      [id]       => 4
      [4]        => Hawaii
      [state]    => Hawaii
      [5]        => 4
      [6]        => Oceanfront
      [school]   => Spring Beach
      [7]        => Spring Beach
    )
    The table rows look like:

    Code:
    ID: 1, home_id: 18, guest_id: 19, state: 4, sport: 1
    Why are there multiples? What's with the number like [3] which links to the sport table and withdraws two different columns? [2] and [sport]?

    And why does [6] Oceanfront not have its own [school] like Spring Beach?

    This makes me have to use $row[6] to print out Oceanfront and $row["school"] or $row[7] to print out Spring Beach.

    Could someone help me understand this, please?
    I can get by using the numbers like they are, but is there a way to give each school a different key that is more useful than a number?

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    When selecting fields from more then one table in a query, try to always give every field being selected an Alias, then when your dealing with the result set, you can then see easily what each field is. Also when using INNER JOINS always type them as INNER JOIN.

    Code SQL:
    SELECT
          DISTINCT g.home_id AS game_id
        , g.guest_id AS away_team_id
        , sp.sport AS sport
        , sp.id AS sport_id
        , st.state AS state
        , st.id AS state_id
        , th.school AS home_team
        , tg.school AS away_team
    FROM
        ws_games AS g
    INNER JOIN
        ws_teams AS th 
            ON g.home_id = th.id
    INNER JOIN
        ws_teams AS tg 
            ON g.guest_id = tg.id
    INNER JOIN
        ws_state AS st 
            ON st.id = g.state 
    INNER JOIN
        ws_sport AS sp 
            ON sp.id = g.sport 
    WHERE
        g.sport = $sport
        AND
            g.YEAR = $year
        AND
            g.state = $state
        AND
            g.week = $week
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Zealot gregs's Avatar
    Join Date
    Aug 2002
    Posts
    161
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks SpacePhoenix. I never thought about using AS on fields like so. I thought it was only for tables.

    Another thing. Was I wrong going back to this table TWICE for the home team and the guest team?

    Code:
    INNER JOIN
        ws_teams AS th 
            ON g.home_id = th.id
    INNER JOIN
        ws_teams AS tg 
            ON g.guest_id = tg.id
    In one record, I have the home and guest team within it. It was the only way I could think of linking the teams names and it work.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gregs View Post
    Was I wrong going back to this table TWICE for the home team and the guest team?
    no, you did good, that's the way to do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    nothing from r937 regarding the uselessness of AS… slackin
    The only code I hate more than my own is everyone else's.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    "uselessness of AS" ???

    please, you must be more specific

    in the case of joining to the same table more than once in a single query, i would have to say you're nuts if you think the table aliases are useless

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    AS

    Code sql:
    SELECT
          DISTINCT g.home_id game_id
        , g.guest_id away_team_id
        , sp.sport sport
        , sp.id sport_id
        , st.state state
        , st.id state_id
        , th.school home_team
        , tg.school away_team
    FROM
        ws_games g
    INNER JOIN
        ws_teams th
            ON g.home_id = th.id
    INNER JOIN
        ws_teams tg
            ON g.guest_id = tg.id
    INNER JOIN
        ws_state st
            ON st.id = g.state
    INNER JOIN
        ws_sport sp
            ON sp.id = g.sport
    WHERE
        g.sport = $sport
        AND
            g.YEAR = $year
        AND
            g.state = $state
        AND
            g.week = $week
    The only code I hate more than my own is everyone else's.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oddz View Post
    AS the word
    what are you, an oracle developer?

    in any case, you're right, there were some unnecessary aliases...
    Code:
    , sp.sport AS sport 
    , st.state AS state
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •