Phailak

Hi, I need to know how to make a while loop with two conditions from a query. There might be a better way to do this if any one has a suggestion. Basically I want to display the scores from a hockey game (hockey league I run) and the idea is to have Home Team scorers on the left, Away Team Scorers on the right. I don't want to skip lines from Home Team scorers to Away Team scorers unless one side has more results (see [a href=\"http://www.lshl.net/Results_20060119_2040_233.html\" target=\"_blank\"]http://www.lshl.net/Results_20060119_2040_233.html[/a] which was generated through static pages) Anyway, here's part of the code, please keep in mind I have little knowledge of PHP except through trial and error AND I put in RED the areas I thought might need some work (IFs and WHILE): [code]$queryH="SELECT PlayerName, Penalty, PIM, Period, Time FROM penalties INNER JOIN players ON penalties.PlayerID = players.PlayerID WHERE GameID = $tgID AND penalties.TeamID = $HomeTeam UNION SELECT players.PlayerName, ifnull(AssistPlayer.PlayerName,'Unassisted') as Assist, '' as Other, Period, Time FROM goals INNER JOIN players ON goals.PlayerID = players.PlayerID LEFT JOIN players AssistPlayer ON goals.AssistID = AssistPlayer.PlayerID WHERE GameID = $tgID AND goals.TeamID = $HomeTeam ORDER BY Period, Time DESC"; $resultH=mysql_query($queryH); $queryA="SELECT PlayerName, Penalty, PIM, Period, Time FROM penalties INNER JOIN players ON penalties.PlayerID = players.PlayerID WHERE GameID = $tgID AND penalties.TeamID = $AwayTeam UNION SELECT players.PlayerName, ifnull(AssistPlayer.PlayerName,'Unassisted') as Assist, '' as Other, Period, Time FROM goals INNER JOIN players ON goals.PlayerID = players.PlayerID LEFT JOIN players AssistPlayer ON goals.AssistID = AssistPlayer.PlayerID WHERE GameID = $tgID AND goals.TeamID = $AwayTeam ORDER BY Period, Time DESC"; $resultA=mysql_query($queryA); [color=#FF0000]while($rowH=mysql_fetch_array($resultH) OR $rowA=mysql_fetch_array($resultA))[/color] { echo "<tr>\n";     [color=#FF0000]if($rowY=mysql_fetch_array($resultH))[/color]     {     echo "<td align='center'width='15%'>".$rowH['PlayerName']."</td>";     echo "<td align='center' width='15%'>".$rowH['Penalty']."</td>";     echo "<td align='center' width='15%'>".$rowH['Time']."</td>";     }     ELSE     {     echo "<td align='center'width='15%'></td>";     echo "<td align='center' width='15%'></td>";     echo "<td align='center' width='15%'></td>";         }     [color=#FF0000]if($rowZ=mysql_fetch_array($resultA))[/color]     {     echo "<td align='center'width='15%'>".$rowA['PlayerName']."</td>";     echo "<td align='center' width='15%'>".$rowA['Penalty']."</td>";     echo "<td align='center' width='15%'>".$rowA['Time']."</td>";     }     ELSE     {     echo "<td align='center'width='15%'></td>";     echo "<td align='center' width='15%'></td>";     echo "<td align='center' width='15%'></td>";         } echo "</tr>\n"; } echo "</table>\n";[/code]

[!--quoteo(post=374406:date=May 16 2006, 03:53 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 16 2006, 03:53 PM) [snapback]374406[/snapback][/div][div class=\'quotemain\'][!--quotec--] I don't even want to guess how slow that query might be... btw, you should be using IFNULL(), instead of and if() with an isnull() inside. It's cleaner, easier and read, and the parser doesn't have to guess. e.g. [code]ifnull(HomeGoalQuery.Goals,0) [/code] instead of: [code]if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals) [/code] [/quote] Oh, well this query is from trial and error on my part so it might look very complicated for no reason, thanks for the suggestion, although the query itself is quite fast, considering the amount of data is pretty low, I guess I don't see the effects of any slowdowns. Still anything I can do to make it clearer is appreciated

[!--quoteo(post=374316:date=May 16 2006, 12:03 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ May 16 2006, 12:03 PM) [snapback]374316[/snapback][/div][div class=\'quotemain\'][!--quotec--] [code] WHERE players.Position = "G" GROUP BY gamesplayed.PlayerID HAVING GP > 0 [/code] [a href=\"http://dev.mysql.com/doc/refman/4.1/en/select.html\" target=\"_blank\"]SELECT SYNTAX[/a] (Do a "find" throughout the text for instances of "HAVING") [/quote] Yep, that worked, thanks!

Hail, I have this query I'm building for my hockey league website, so far I'm getting prety much what I want but for some reason I can't narrow it down. Basically, the query gives me the stats of the goalies in the league, in this case some of the goalies returned have 0 games played (because they played in previous seasons but not current one) so I simply want to add to my WHERE statement GP > 0 (I also tried the long version of sum(if (ResultQuery.result="W", 1, 0))+sum(if (ResultQuery.result="L", 1, 0))+sum(if (ResultQuery.result="OTL", 1, 0))+sum(if (ResultQuery.result="T", 1, 0)) > 0) which doesn't work. It sounds like a simple thing I'm missing so I'm hoping someone here can point it out (the WHERE I'm targetting is in red, I want only records which are > than 0): SELECT gamesplayed.PlayerID, players.PlayerName, [!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]sum(if (ResultQuery.result="W", 1, 0))+sum(if (ResultQuery.result="L", 1, 0))+sum(if (ResultQuery.result="OTL", 1, 0))+sum(if (ResultQuery.result="T", 1, 0)) as GP[!--colorc--][/span][!--/colorc--], sum(if (ResultQuery.result="W", 1, 0)) as Wins, sum(if (ResultQuery.result="L", 1, 0)) as Losses, sum(if (ResultQuery.result="OTL", 1, 0)) as OTL, sum(if (ResultQuery.result="T", 1, 0)) as Ties, sum(ResultQuery.TGA) as GA FROM gamesplayed INNER JOIN players ON gamesplayed.PlayerID = players.PlayerID LEFT JOIN (SELECT calendar.CalendarID, if(teams.TeamID=calendar.HomeTeam, calendar.HomeTeam, calendar.AwayTeam) as Team, if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals) as TeamGoals, if(teams.TeamID=calendar.AwayTeam, calendar.HomeTeam, calendar.AwayTeam) as OppTeam, if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) as OppTeamGoals, (if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) - if(isnull(OT.OTgoals),0,OT.OTgoals)) as TGA, OT.OT, if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)>if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals),"W",if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)=if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals),"T",if(if(isnull(HomeGoalQuery.Goals),0,HomeGoalQuery.Goals)<if(isnull(AwayGoalQuery.Goals),0,AwayGoalQuery.Goals) AND OT.OT=1, "OTL","L"))) as Result FROM teams INNER JOIN calendar ON teams.TeamID = calendar.HomeTeam OR teams.TeamID = calendar.AwayTeam LEFT JOIN (SELECT goals.GameID, goals.TeamID, count(goals.GameID) as Goals FROM goals GROUP BY goals.GameID, goals.TeamID) AS HomeGoalQuery ON calendar.CalendarID = HomeGoalQuery.GameID AND teams.TeamID= HomeGoalQuery.TeamID LEFT JOIN (SELECT goals.GameID, goals.OppTeamID, count(goals.GameID) as Goals FROM goals GROUP BY goals.GameID, goals.TeamID) AS AwayGoalQuery ON calendar.CalendarID = AwayGoalQuery.GameID AND teams.TeamID = AwayGoalQuery.OppTeamID LEFT JOIN (SELECT calendar.CalendarID, count(calendar.CalendarID) as OTgoals, '1' as OT FROM calendar INNER JOIN goals on calendar.CalendarID = goals.GameID WHERE Period = 4 GROUP BY calendar.CalendarID) as OT ON calendar.CalendarID = OT.CalendarID WHERE calendar.Season = 3 AND calendar.Playoffs = 0 AND Cancellation = 0 AND teams.TeamID <> 17 AND teams.TeamID <> 18 ORDER BY teams.TeamID, calendar.CalendarID) AS ResultQuery ON gamesplayed.GameID = ResultQuery.CalendarID AND gamesplayed.TeamID = ResultQuery.Team WHERE players.Position = "G" GROUP BY gamesplayed.PlayerID