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