Multiple print_r columns? Why?

Databases
1

I have this query:


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.


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:


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

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.

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
3

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?


[COLOR=#993333][B]INNER[/B][/COLOR] [COLOR=#993333][B]JOIN[/B][/COLOR]
    ws_teams [COLOR=#993333][B]AS[/B][/COLOR] th 
        [COLOR=#993333][B]ON[/B][/COLOR] g.home_id = th.id
[COLOR=#993333][B]INNER[/B][/COLOR] [COLOR=#993333][B]JOIN[/B][/COLOR]
    ws_teams [COLOR=#993333][B]AS[/B][/COLOR] tg 
        [COLOR=#993333][B]ON[/B][/COLOR] 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

no, you did good, that’s the way to do it :slight_smile:

5

nothing from r937 regarding the uselessness of AS… slackin :wink:

6

“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

:smiley:

7

AS


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
8

what are you, an oracle developer? :stuck_out_tongue:

in any case, you’re right, there were some unnecessary aliases…

, sp.sport AS sport 
, st.state AS state