I have these two tables...
schedule (gameid, homeid, awayid, weekno, seasonno)
teams (teamid, location, nickname)
This mysql query below gets me schedule info for ALL 32 teams...
$sql = "SELECT
h.nickname AS home,
a.nickname AS away,
h.teamid AS homeid,
a.teamid AS awayid,
s.weekno
FROM schedule s
INNER JOIN teams h ON s.homeid = h.teamid
LEFT JOIN teams a ON s.awayid = a.teamid
WHERE s.seasonno =2014";
$schedule= mysqli_query($connection, $sql);
if (!$schedule) {
die("Database query failed: " . mysqli_error($connection));
} else {
// Placeholder for data
$data = array();
while($row = mysqli_fetch_assoc($schedule)) {
if ($row['away'] == "") {$row['away']="BYE";}
$data[$row['homeid']][$row['weekno']] = $row['away'];
$data[$row['awayid']][$row['weekno']] = '@ '.$row['home'];
}
}
However, I only want to get info for one specific team, which is stored in the $teamid variable. This should be very easy, right? I have tried multiple things, including this one below (where I added an AND statement), but this one still outputs too much...
$sql = "SELECT
h.nickname AS home,
a.nickname AS away,
h.teamid AS homeid,
a.teamid AS awayid,
s.weekno
FROM schedule s
INNER JOIN teams h ON s.homeid = h.teamid
LEFT JOIN teams a ON s.awayid = a.teamid
WHERE s.seasonno =2014
AND h.teamid=$teamid OR a.teamid=$teamid";
$schedule= mysqli_query($connection, $sql);
if (!$schedule) {
die("Database query failed: " . mysqli_error($connection));
} else {
// Placeholder for data
$data = array();
while($row = mysqli_fetch_assoc($schedule)) {
if ($row['away'] == "") {$row['away']="BYE";}
$data[$row['homeid']][$row['weekno']] = $row['away'];
$data[$row['awayid']][$row['weekno']] = '@ '.$row['home'];
}
}
Below is the array that the above outputs. It's better, but all I want is that 2nd array ([1]) which has, in this example, the Eagles full schedule. It's not giving me too much else and I guess I could live with it and just ignore the other stuff, but I'd rather be as efficient as possible and only get what I need...
Array
(
[24] => Array
(
[1] => Eagles
)
[1] => Array
(
[1] => Jaguars
[3] => Redskins
[5] => Rams
[9] => @ Texans
[11] => @ Packers
[13] => @ Cowboys
[14] => Seahawks
[17] => @ Giants
[4] => @ 49ers
[6] => Giants
[10] => Panthers
[15] => Cowboys
[2] => @ Colts
[7] => BYE
[8] => @ Cardinals
[12] => Titans
[16] => @ Redskins
)
[16] => Array
(
[3] => Eagles
[8] => Eagles
)
[23] => Array
(
[5] => Eagles
)
[10] => Array
(
[9] => Eagles
)
[4] => Array
(
[11] => Eagles
[1] => Eagles
[3] => @ Eagles
[16] => Eagles
)
[2] => Array
(
[13] => Eagles
[17] => Eagles
[15] => @ Eagles
)
[9] => Array
(
[14] => Eagles
[6] => Eagles
)
[3] => Array
(
[17] => Eagles
[5] => Eagles
[6] => @ Eagles
)
[29] => Array
(
[4] => Eagles
)
[31] => Array
(
[9] => Eagles
)
[7] => Array
(
[10] => Eagles
[11] => Eagles
)
[6] => Array
(
[15] => Eagles
)
[27] => Array
(
[1] => @ Eagles
)
[28] => Array
(
[2] => Eagles
)
[14] => Array
(
[4] => Eagles
)
[15] => Array
(
[5] => @ Eagles
)
[] => Array
(
[7] => @ Eagles
)
[25] => Array
(
[9] => Eagles
)
[11] => Array
(
[10] => @ Eagles
)
[26] => Array
(
[12] => @ Eagles
)
[13] => Array
(
[14] => @ Eagles
)
)
UPDATE: I added parentheses per Abhik (thanks!) and it helped a little, but is still giving too much, i.e...
Array
(
[1] => Array
(
[1] => Jaguars
[2] => @ Colts
[3] => Redskins
[4] => @ 49ers
[5] => Rams
[6] => Giants
[7] => BYE
[8] => @ Cardinals
[9] => @ Texans
[10] => Panthers
[11] => @ Packers
[12] => Titans
[13] => @ Cowboys
[14] => Seahawks
[15] => Cowboys
[16] => @ Redskins
[17] => @ Giants
)
[27] => Array
(
[1] => @ Eagles
)
[28] => Array
(
[2] => Eagles
)
[4] => Array
(
[3] => @ Eagles
[16] => Eagles
)
[14] => Array
(
[4] => Eagles
)
[15] => Array
(
[5] => @ Eagles
)
[3] => Array
(
[6] => @ Eagles
[17] => Eagles
)
[] => Array
(
[7] => @ Eagles
)
[16] => Array
(
[8] => Eagles
)
[25] => Array
(
[9] => Eagles
)
[11] => Array
(
[10] => @ Eagles
)
[7] => Array
(
[11] => Eagles
)
[26] => Array
(
[12] => @ Eagles
)
[2] => Array
(
[13] => Eagles
[15] => @ Eagles
)
[13] => Array
(
[14] => @ Eagles
)
)
You need to use parentheses for separating the conditions
WHERE s.seasonno =2014
AND h.teamid=$teamid OR a.teamid=$teamid
to
WHERE
s.seasonno =2014
AND
(h.teamid=$teamid OR a.teamid=$teamid)
OR's are slowing down queries, you could use:
... WHERE s.seasonno =2014 AND $teamid IN (h.teamid, a.teamid)
or
... WHERE s.seasonno=2014 AND $teamid IN (s.homeid, s.awayid)