kyleo92 Posted February 7, 2011 Share Posted February 7, 2011 I can get this code to return numbers, just not the right ones. Let me know if you need more information...I'll supply whatever you want...I'm borderline desperate! LOL (SELECT COUNT(*) FROM sportsdb_wins WHERE winner = teamid AND (winortie = 1 or winortie = 2) AND (SELECT teamid FROM sportsdb_teams WHERE teamid = loser AND teamdiv = teamdiv)) AS divwins, (SELECT COUNT(*) FROM sportsdb_wins WHERE loser = teamid AND (winortie = 4 or winortie = 5) AND (SELECT teamid FROM sportsdb_teams WHERE teamid = winner AND teamdiv = teamdiv)) AS divlosses, I'm customizing a current script, here is an explanation of what I need it to do, written by the script owner: "...A quick note, though: winortie value of 3 = play; 1 = beat; 2 = beat by forfeit; 0 = tied; 4 = lost; 5 = lost by forfeit. So it looks like the on-the-fly query is quite complicated: you have to check for winortie of 1 or 2 when the team ID is in the winner (aka Home) field, as well as winortie of 4 or 5 when the team ID is in the loser (aka Away) field, and then match up the other team in that game to the original team's division, just to get divwins." Quote Link to comment Share on other sites More sharing options...
btherl Posted February 7, 2011 Share Posted February 7, 2011 Indenting is just as important in SQL as it is in PHP: (SELECT COUNT(*) FROM sportsdb_wins WHERE winner = teamid AND (winortie = 1 or winortie = 2) AND (SELECT teamid FROM sportsdb_teams WHERE teamid = loser AND teamdiv = teamdiv)) AS divwins, (SELECT COUNT(*) FROM sportsdb_wins WHERE loser = teamid AND (winortie = 4 or winortie = 5) AND (SELECT teamid FROM sportsdb_teams WHERE teamid = winner AND teamdiv = teamdiv)) AS divlosses, "AND teamdiv = teamdiv". Which teamdiv? From the inner query or the outer query? If both are from the inner query then all this condition does is exclude null values from the result, which I'm guessing isn't what you intended. If one teamdiv is from the outer query, the table needs to be specified, preferable for both. You can alias the tables to make the names shorter. I don't see any other obvious problems, but maybe if you deal with this teamdiv condition it'll start giving you the right result. Quote Link to comment Share on other sites More sharing options...
kyleo92 Posted February 7, 2011 Author Share Posted February 7, 2011 (SELECT COUNT(*) FROM sportsdb_wins WHERE winner = teamid AND (winortie = 1 or winortie = 2) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = loser) = teamdiv) AS divwins, Okay, that version was one I was messing with...this one maybe will help more... So, we look at all the times team one was the winner and another team in the same conference (with the same teamdiv) were listed as the loser. Quote Link to comment Share on other sites More sharing options...
btherl Posted February 8, 2011 Share Posted February 8, 2011 I can't make much sense of that query without knowing where each column is coming from. The best way to show it is to give each table an alias, and include the alias with each column. For example: SELECT * FROM sportsdb_wins sw WHERE st.winner = win.teamid AND ... FROM sportsdb_teams st This is especially important if you query the same table multiple times in one query (which I'm not sure if you're doing, maybe you should post the entire query as well as the section you're working on). Quote Link to comment Share on other sites More sharing options...
kyleo92 Posted February 8, 2011 Author Share Posted February 8, 2011 It's Peter's Sports League Standings script...I'm just adding this customization (in addition to a few others that I already have working...) The script itself is a year or two old, so the coding probably isn't up-to-date and current... Here is the entire contents of the standings file that serves many things...but first, the portion that I'm working on... <?php } if ($result_exist) { $query="SELECT teamid, teamname, teamwins, teamties, teamlosses, teamforfeits, teamrf, teamra, (teamwins + teamties + teamlosses + teamforfeits) AS gamesplayed, (teamwins * $points_win + teamforfeits * $points_forfeit + teamties * $points_tie) AS points, (teamrf / (teamwins + teamties + teamlosses + teamforfeits)) AS teamppg, (SELECT COUNT(*) FROM sportsdb_wins WHERE winner = teamid AND (winconf = 3) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = loser) = teamdiv - 2) AS divwins, (SELECT COUNT(*) FROM sportsdb_wins WHERE loser = teamid AND (winconf = 3) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = winner) = teamdiv -2) AS divlosses, (teamrf - teamra) AS teamdiff, $winning_pct_formula AS winningpct, ((({$results['leadervalue']}) - (teamwins - teamlosses - teamforfeits)) / 2) AS gamesbehind FROM sportsdb_teams WHERE teamdiv = {$divs['divid']}"; if ($standings_sort == 9) { $query .= " AND teamid != {$results['teamid']}"; } $query .= " AND active = 1" . $division_sort . " ORDER BY $sort_order"; $result=mysql_query($query); $num=mysql_num_rows($result); while ($results = mysql_fetch_array($result, MYSQL_ASSOC)) { $winningpct=number_format($results['winningpct'], 3); $teamppg=number_format($results['teamppg'], 2); $gamesbehind = number_format($results['gamesbehind'],1); if ($gamesbehind == "0.0") { $gamesbehind = "---"; } ?> <?php require "settings.php"; $confid = intval($_GET['conf']); $team = intval($_GET['team']); $history = intval($_GET['history']); mysql_connect($sportsdb_host,$sportsdb_user,$sportsdb_pass); @mysql_select_db($sportsdb_db) or die( "Unable to select database"); include 'standings_functions.php'; if ($history > 0) { // We need the team ID and the conference ID too if ($confid < 1 || $team < 1) { die ('Not enough information supplied'); } print '<p><a href="?conf=' . $confid . '">Back to the conference standings</a></p>' . "\n"; print '<p><a href="?conf=' . $confid . '&team=' . $team . '">Back to the team schedule</a></p>' . "\n"; $query = 'SELECT teamname, masterteam FROM sportsdb_teams WHERE teamid = ' . $team . ' LIMIT 1'; $result = mysql_query($query); if (mysql_num_rows($result) == 0) { die('No such team exists'); } else { $teamname = mysql_result($result,0,"teamname"); $masterteam = mysql_result($result,0,"masterteam"); } print '<h3>' . $teamname . ' history</h3>' . "\n"; $query = 'SELECT sportsdb_teams.teamid, sportsdb_teams.teamname, sportsdb_teams.teamwins, sportsdb_teams.teamlosses, sportsdb_teams.teamties, sportsdb_teams.teamforfeits, sportsdb_divs.divname, sportsdb_conferences.confname, sportsdb_conferences.confid, sportsdb_seasons.seasonname FROM sportsdb_teams, sportsdb_divs, sportsdb_conferences, sportsdb_seasons WHERE sportsdb_teams.masterteam = ' . $masterteam . ' AND sportsdb_teams.teamdiv = sportsdb_divs.divid AND sportsdb_divs.conference = sportsdb_conferences.confid AND sportsdb_conferences.season = sportsdb_seasons.seasonid ORDER BY sportsdb_seasons.seasonorder'; $result = mysql_query($query) or die ('Error in query: ' . $query); ?> <table class="teamhistory"> <tr> <th>Season</th> <th>Team</th> <th>Conference</th> <th>Division</th> <th>Wins</th> <th>Losses</th> <?php if ($show_ties) { ?><th>Ties</th><?php } ?> <?php if ($forfeits) { ?><th>Forfeits</th><?php } ?> </tr> <?php while ($teams = mysql_fetch_array($result, MYSQL_ASSOC) ) { $seasonname = htmlspecialchars($teams['seasonname'], ENT_QUOTES); print "\t" . '<tr>' . "\n"; print "\t\t" . '<td>' . $seasonname . '</td>' . "\n"; print "\t\t" . '<td><a href="?team=' . $teams['teamid'] . '&conf=' . $teams['confid'] . '">' . $teams['teamname'] . '</td>' . "\n"; print "\t\t" . '<td><a href="?conf=' . $teams['confid'] . '">' . $teams['confname'] . '</a></td>' . "\n"; print "\t\t" . '<td>' . $teams['divname'] . '</td>' . "\n"; print "\t\t" . '<td>' . $teams['teamwins'] . '</td>' . "\n"; print "\t\t" . '<td>' . $teams['teamlosses'] . '</td>' . "\n"; if ($show_ties) { print "\t\t" . '<td>' . $teams['teamties'] . '</td>' . "\n"; } if ($forfeits) { print "\t\t" . '<td>' . $teams['teamforfeits'] . '</td>' . "\n"; } print "\t" . '</tr>' . "\n"; } print '</table>' . "\n"; } elseif ($team > 0) { // We need the conference ID too if ($confid < 1) { die ('Not enough information supplied'); } print '<p><a href="?conf=' . $confid . '">Back to the conference standings</a></p>' . "\n"; print '<h3>' . get_team_name($team). ': full schedule and scores</h3>' . "\n"; print '<p><a href="?history=1&team=' . $team . '&conf=' . $confid . '">Team history</a></p>' . "\n"; sls_team_schedule($team); } elseif ($confid > 0) { // Allow sorting to occur $standings_sort = intval($_GET['sort']); switch ($standings_sort) { /* Available sort fields: Name: teamname Wins: teamwins Losses: teamlosses Ties: teamties Forfeits: teamforfeits Runs for: teamrf Runs against: teamra Games behind: gamesbehind Points: points Winning percentage: winningpct Custom tie break: teamorder Separate the fields by commas and always specify a sort order as DESC for descending and ASC for ascending */ case 1: // Sort by team name $sort_order = "teamname ASC"; break; case 2: // Sort by wins $sort_order = "teamwins DESC, teamties DESC, teamlosses ASC, teamorder DESC"; break; case 3: // Sort by losses $sort_order = "teamlosses DESC, teamwins ASC, teamties ASC, teamorder DESC"; break; case 4: // Sort by divwins $sort_order = "divwins DESC, divties DESC, divlosses ASC, teamorder DESC"; break; case 5: // Sort by forfeits $sort_order = "teamforfeits DESC, teamwins ASC, teamties ASC, teamorder DESC"; break; case 6: // Sort by games played $sort_order = "gamesplayed DESC, teamwins DESC, teamties DESC, teamlosses ASC, teamorder DESC"; break; case 7: // Sort by runs for $sort_order = "teamrf DESC, teamwins ASC, teamties DESC, teamlosses ASC, teamorder DESC"; break; case 11: // Sort by runs per game $sort_order = "teamppg DESC, teamwins ASC, teamties DESC, teamlosses ASC, teamorder DESC"; break; case 8: // Sort by runs differential $sort_order = "teamra DESC, teamwins ASC, teamties DESC, teamlosses ASC, teamorder DESC"; break; case 12: // Sort by runs per game $sort_order = "teamdiff DESC, teamwins DESC, teamties ASC, teamlosses DESC, teamorder DESC"; break; case 9: // Sort by games behind $sort_order = "gamesbehind ASC, teamties DESC, teamwins DESC, teamlosses ASC, teamorder DESC"; break; case 10: // Sort by points $sort_order = "points DESC, teamwins DESC, winningpct DESC, teamorder DESC"; break; default: // Sort by GamesBehind $standings_sort = 0; $sort_order = "gamesbehind ASC, teamties DESC, teamwins DESC, teamlosses ASC, teamorder DESC"; break; } $division_sort = ""; // Different winning percentage calculations switch ($winning_pct_calc) { case 2: // Treating a tie as a half win $winning_pct_formula = '((teamwins + (teamties / 2)) / (teamwins + teamties + teamlosses + teamforfeits))'; break; case 3: // Treating winning percentage as number of points relative to total possible points $winning_pct_formula = '((teamwins * ' . $points_win . ' + teamforfeits * ' . $points_forfeit . ' + teamties * ' . $points_tie . ') / ((teamwins + teamties + teamlosses + teamforfeits) * ' . $points_win . '))'; break; default: // Ignoring ties $winning_pct_formula = '(teamwins / (teamwins + teamlosses + teamforfeits))'; } // print the conference name $resultconf = mysql_query("SELECT sportsdb_conferences.confname, sportsdb_seasons.seasonid, sportsdb_seasons.seasonname FROM sportsdb_conferences, sportsdb_seasons WHERE sportsdb_conferences.confid = $confid AND sportsdb_conferences.season = sportsdb_seasons.seasonid LIMIT 1"); $confname = @mysql_result($resultconf, 0, 'confname') or die ('No such conference'); $seasonid = mysql_result($resultconf, 0, 'seasonid'); $seasonname = mysql_result($resultconf, 0, 'seasonname'); $seasonname = htmlspecialchars($seasonname, ENT_QUOTES); print '<h2>' . $confname . ' (' . $seasonname . ')</h2>' . "\n"; print '<p><a href="' . $_SERVER['PHP_SELF'] . '?season=' . $seasonid . '">Back to conference list</a></p>'; print "\n" . '<hr />' . "\n"; // Get the divisions $resultconf = mysql_query("SELECT divname, divid FROM sportsdb_divs WHERE conference = $confid ORDER BY divorder"); $numconf = mysql_num_rows($resultconf); if ($numconf == 0) { print "<p>No divisions in this conference</p>\n"; } else { while ($divs = mysql_fetch_array($resultconf, MYSQL_ASSOC)) { // Loop through each division $divname = $divs['divname']; ?> <h3><?php print $divname; ?></h3> <table class="standingstable"> <tr> <td></td> <td></td> <td colspan="2"><center>Overall</center></td> <td colspan="2"><center>Conference</center></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> </tr> <tr> <td width="25%"><?php if ($standings_sort != 1) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=1\">"; ?><strong>Team Name</strong><?php if ($standings_sort != 1) print '</a>'; ?></td> <td><?php if ($standings_sort != 6) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=6\">"; ?><strong><center>GP</center></strong><?php if ($standings_sort != 2) print '</a>'; ?></td> <td><?php if ($standings_sort != 2) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=2\">"; ?><strong><center>Wins</center></strong><?php if ($standings_sort != 2) print '</a>'; ?></td> <td><?php if ($standings_sort != 3) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=3\">"; ?><strong><center>Losses</center></strong><?php if ($standings_sort != 3) print '</a>'; ?></td> <td><?php if ($standings_sort != 3) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=4\">"; ?><strong><center>Wins</center></strong><?php if ($standings_sort != 4) print '</a>'; ?></td> <td><?php if ($standings_sort != 3) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=4\">"; ?><strong>Losses</strong><?php if ($standings_sort != 4) print '</a>'; ?></td> <?php if ($show_ties) { ?> <td><?php if ($standings_sort != 4) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=4\">"; ?><strong><center>Wins</center></strong><?php if ($standings_sort != 4) print '</a>'; ?></td> <?php } ?> <?php if ($forfeit) { ?> <td><?php if ($standings_sort != 5) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=5\">"; ?><strong><center>Forfeits</center></strong><?php if ($standings_sort != 5) print '</a>'; ?></td> <?php } ?> <?php if ($show_rfra) { ?> <td><?php if ($standings_sort != 7) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=7\">"; ?><strong><center>PS</center></strong><?php if ($standings_sort != 7) print '</a>'; ?></td> <td><?php if ($standings_sort != 11) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=11\">"; ?><strong><center>PPG</center></strong><?php if ($standings_sort != 11) print '</a>'; ?></td> <td><?php if ($standings_sort != print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=8\">"; ?><strong><center>PA</center></strong><?php if ($standings_sort != print '</a>'; ?></td> <td><?php if ($standings_sort != 12) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=12\">"; ?><strong><center>DIFF</center></strong><?php if ($standings_sort != 12) print '</a>'; ?></td> <?php } ?> <?php if ($show_gb) { ?> <td><?php if ($standings_sort != 0) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=9\">"; ?><strong><center>GB</center><?php if ($standings_sort != 9) print '</a>'; ?></td> <?php } ?> <td><?php if ($standings_sort != 9) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=0\">"; ?><strong><center>PCT</center><?php if ($standings_sort != 0) print '</a>'; ?></td> <?php if ($show_points) { ?> <td><?php if ($standings_sort != 10) print '<a href="' . $_SERVER['PHP_SELF'] . "?conf=$confid&sort=10\">"; ?><strong><center>Points</center></strong><?php if ($standings_sort != 10) print '</a>'; ?></td> <?php } ?> </tr> <?php $query = "SELECT COUNT(*) FROM sportsdb_teams WHERE teamdiv = {$divs['divid']}"; $result = mysql_query($query); $result_exist = mysql_result($result,0); if ($result_exist) { $query = "SELECT teamid, teamname, teamwins, teamties, teamlosses, teamforfeits, teamrf, teamra, (teamwins + teamties + teamlosses + teamforfeits) AS gamesplayed, (teamwins * $points_win + teamforfeits * $points_forfeit + teamties * $points_tie) AS points, $winning_pct_formula AS winningpct, (teamwins - teamlosses - teamforfeits) AS leadervalue FROM sportsdb_teams WHERE teamdiv = {$divs['divid']} ORDER BY leadervalue DESC, teamties DESC LIMIT 1"; $result = mysql_query($query) or die ("Error in query: $query"); $results = mysql_fetch_array($result, MYSQL_ASSOC); $winningpct = number_format($results['winningpct'], 3); } // Show the leader at the top for default sorting (games behind) if ($standings_sort == 9) { ?> <tr> <td><a href="?conf=<?php print $confid; ?>&team=<?php print $results['teamid']; ?>" title="View scores for <?php print $results['teamname']; ?>"><em><?php print $results['teamname']; ?></em></a></td> <td><center><?php print $results['gamesplayed']; ?></center></td> <td><center><?php print $results['teamwins']; ?></center></td> <td><center><?php print $results['teamlosses']; ?></center></td> <td style="background-color: rgb(228, 227, 227);"><center><?php print $results['divwins']; ?></center></td> <td style="background-color: rgb(228, 227, 227);"><center><?php print $results['divlosses']; ?></center></td> <?php if ($show_ties) { ?> <?php } if ($forfeit) { ?> <td><center><?php print $results['teamforfeits']; ?></center></td> <?php } if ($show_rfra) { ?> <td><center><?php print $results['teamrf']; ?></center></td> <td><center><?php print $teamppg; ?></center></td> <td><center><?php print $results['teamra']; ?></center></td> <td><center><?php print $results['teamdiff']; ?></center></td> <?php } if ($show_gb) { ?> <td><center>---</center></td> <?php } ?> <td><center><?php print $winningpct; ?></center></td> <?php if ($show_points) { ?> <td><center><?php print $results['points'] ?></center></td> <?php } ?> </tr> <?php } if ($result_exist) { $query="SELECT teamid, teamname, teamwins, teamties, teamlosses, teamforfeits, teamrf, teamra, (teamwins + teamties + teamlosses + teamforfeits) AS gamesplayed, (teamwins * $points_win + teamforfeits * $points_forfeit + teamties * $points_tie) AS points, (teamrf / (teamwins + teamties + teamlosses + teamforfeits)) AS teamppg, (SELECT COUNT(*) FROM sportsdb_wins WHERE winner = teamid AND (winconf = 3) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = loser) = teamdiv - 2) AS divwins, (SELECT COUNT(*) FROM sportsdb_wins WHERE loser = teamid AND (winconf = 3) AND (SELECT teamdiv FROM sportsdb_teams WHERE teamid = winner) = teamdiv -2) AS divlosses, (teamrf - teamra) AS teamdiff, $winning_pct_formula AS winningpct, ((({$results['leadervalue']}) - (teamwins - teamlosses - teamforfeits)) / 2) AS gamesbehind FROM sportsdb_teams WHERE teamdiv = {$divs['divid']}"; if ($standings_sort == 9) { $query .= " AND teamid != {$results['teamid']}"; } $query .= " AND active = 1" . $division_sort . " ORDER BY $sort_order"; $result=mysql_query($query); $num=mysql_num_rows($result); while ($results = mysql_fetch_array($result, MYSQL_ASSOC)) { $winningpct=number_format($results['winningpct'], 3); $teamppg=number_format($results['teamppg'], 2); $gamesbehind = number_format($results['gamesbehind'],1); if ($gamesbehind == "0.0") { $gamesbehind = "---"; } ?> <tr> <td><a href="?conf=<?php print $confid; ?>&team=<?php print $results['teamid']; ?>" title="View scores for <?php print $results['teamname']; ?>"><em><?php print $results['teamname']; ?></em></a></td> <td><center><?php print $results['gamesplayed']; ?></center></td> <td><center><?php print $results['teamwins']; ?></center></td> <td><center><?php print $results['teamlosses']; ?></center></td> <td style="background-color: rgb(228, 227, 227);"><center><?php print $results['divwins']; ?></center></td> <td style="background-color: rgb(228, 227, 227);"><center><?php print $results['divlosses']; ?></center></td> <?php if ($show_ties) { ?> <td><center><?php print $results['teamties']; ?></center></td> <?php } if ($forfeit) { ?> <td><center><?php print $results['teamforfeits']; ?></center></td> <?php } if ($show_rfra) { ?> <td><center><?php print $results['teamrf']; ?></center></td> <td><center><?php print $teamppg; ?></center></td> <td><center><?php print $results['teamra']; ?></center></td> <td><center><?php print $results['teamdiff']; ?></center></td> <?php } if ($show_gb) { ?> <td><center><?php print $gamesbehind; ?></center></td> <?php } ?> <td><center><?php print $winningpct; ?></center></td> <?php if ($show_points) { ?> <td><center><?php print $results['points']; ?></center></td> <?php } ?> </tr> <?php } } ?> </table> KEY: GP = Number of Games Played ... PS = Total Points Scored ... PPG = Points Scored Per Game (average) ... PA = Total Points Allowed ... DIFF - Point differential (PS - PA). A positive number means the team scores more points than they allow, a negative number means the team generally allows more points than they score. ... GB = Games back ... PCT = Winning percentage (W/GP) <?php } ?> <a name="pastscores"></a><h2>Past Scores</h2> <form name="teamsort" method="post" action="<?php print $_SERVER['REQUEST_URI']; ?>#pastscores"> <p> Show only <select name="teamtosort"> <option value="all">All teams</option> <?php // Populate the list of teams $queryteams = "SELECT sportsdb_teams.teamname, sportsdb_teams.teamid FROM sportsdb_teams, sportsdb_divs WHERE sportsdb_teams.teamdiv = sportsdb_divs.divid AND sportsdb_divs.conference = $confid ORDER BY teamname"; $resultteams=mysql_query($queryteams); $numteams=mysql_num_rows($resultteams); while ($teams = mysql_fetch_array($resultteams, MYSQL_ASSOC)) { print "\t" . '<option value="' . $teams['teamid'] . '"'; if ($teams['teamid'] == $_POST['teamtosort']) { print ' selected="selected"'; } print '>' . $teams['teamname'] . "</option>\n"; } ?> </select> <input type="submit" value="Go" /> </p> </form> <table width="100%" class="scorestable"> <tr> <th>Date</th> <th>Time</th> <th colspan="2">Away</th> <th></th> <th colspan="2">Home</th> <?php if ($show_fields) { ?> <th>Field</th> <?php } ?> </tr> <?php $queryscores="SELECT sportsdb_wins.winid, sportsdb_wins.windate, sportsdb_wins.wintime, sportsdb_wins.rf, sportsdb_wins.ra, sportsdb_wins.winner, sportsdb_wins.loser, sportsdb_wins.wincomments, sportsdb_wins.field, sportsdb_wins.winortie, sportsdb_teams2.teamname AS winningteam, sportsdb_teams.teamname AS losingteam FROM sportsdb_wins, sportsdb_teams AS sportsdb_teams2, sportsdb_teams WHERE sportsdb_wins.winconf = $confid AND sportsdb_teams2.teamid = sportsdb_wins.winner AND sportsdb_teams.teamid = sportsdb_wins.loser AND sportsdb_wins.winortie != 3"; // Sort by team if specified if (isset($_POST['teamtosort']) && $_POST['teamtosort'] != 'all') { $teamtosort = intval($_POST['teamtosort']); $queryscores .= " AND (sportsdb_wins.winner = $teamtosort OR sportsdb_wins.loser = $teamtosort)"; } $queryscores .= ' ORDER BY wintime DESC'; $resultscores=mysql_query($queryscores); $numscores=mysql_num_rows($resultscores); while ($scores = mysql_fetch_array($resultscores, MYSQL_ASSOC)) { $windateheader=$scores['windate']; $windateformatted=date("F d, Y",$windateheader); $wintime=$scores['wintime']; $hour=date('g',$wintime); $minute=date('i',$wintime); $ampm=date('a',$wintime); ?> <tr> <td><?php print $windateformatted; ?></td> <td><?php print $hour . ':' . $minute . $ampm; ?></td> <td><?php if ($scores['ra'] > $scores['rf']) { echo "<strong>"; print $scores['losingteam']; echo "</strong>"; } elseif ( print $scores['losingteam'] ) ?></td> <td><?php // Show the score unless the game has not been played yet if ($scores['winortie'] != 3) { print $scores['ra']; } print '</td>'; ?> <td><center>@</center></td> <td><?php if ($scores['ra'] < $scores['rf']) { echo "<strong>"; print $scores['winningteam']; echo "</strong>"; } elseif ( print $scores['winningteam'] ) ?></td> <td><?php // Show the score unless the game has not been played yet if ($scores['winortie'] != 3) { print $scores['rf']; if ($scores['winortie'] == 2 || $scores['winortie'] == 5) { print ' (ff)'; } } ?> <?php if ($show_fields) { ?> <td><?php print $scores['field']; ?></td> <?php } ?> </tr> <?php } ?> </table> <a name="upcominggames"></a><h2>Upcoming Games</h2> <form name="teamsort" method="post" action="<?php print $_SERVER['REQUEST_URI']; ?>#upcominggames"> <p>Show only <select name="teamtosort"> <option value="all">All teams</option> <?php $queryteams = "SELECT sportsdb_teams.teamname, sportsdb_teams.teamid FROM sportsdb_teams, sportsdb_divs WHERE sportsdb_teams.teamdiv = sportsdb_divs.divid AND sportsdb_divs.conference = $confid ORDER BY teamname"; $resultteams=mysql_query($queryteams); $numteams=mysql_num_rows($resultteams); while ($teams = mysql_fetch_array($resultteams, MYSQL_ASSOC)) { print "\t" . '<option value="' . $teams['teamid'] . '"'; if ($teams['teamid'] == $_POST['teamtosort']) { print ' selected'; } print '>' . $teams['teamname'] . "</option>\n"; } ?> </select> <input type="submit" value="Go" /> </p> </form> <table class="gamestable"> <tr> <th>Date</th> <th>Time</th> <th>Home</th> <th>Away</th> <?php if ($show_fields) { ?> <th>Field</th> <?php } ?> <th> </th> </tr> <?php $queryscores = "SELECT sportsdb_wins.winid, sportsdb_wins.windate, sportsdb_wins.wintime, sportsdb_wins.winner, sportsdb_wins.loser, sportsdb_wins.wincomments, sportsdb_wins.field, sportsdb_teams2.teamname AS winningteam, sportsdb_teams.teamname AS losingteam FROM sportsdb_wins, sportsdb_teams AS sportsdb_teams2, sportsdb_teams WHERE sportsdb_wins.winconf = $confid AND sportsdb_teams2.teamid = sportsdb_wins.winner AND sportsdb_teams.teamid = sportsdb_wins.loser AND sportsdb_wins.winortie = 3"; // Sort by team if specified if (isset($_POST['teamtosort']) && $_POST['teamtosort'] != 'all') { $teamtosort = intval($_POST['teamtosort']); $queryscores .= " AND (sportsdb_wins.winner = $teamtosort OR sportsdb_wins.loser = $teamtosort)"; } $queryscores .= ' ORDER BY wintime ASC'; $resultscores = mysql_query($queryscores); $numscores = mysql_num_rows($resultscores); while ($scores = mysql_fetch_array($resultscores, MYSQL_ASSOC)) { $windateheader=$scores['windate']; $windateformatted=date("F d, Y",$windateheader); $wintime=$scores['wintime']; $hour=date('g',$wintime); $minute=date('i',$wintime); $ampm=date('a',$wintime); ?> <tr> <td><?php print $windateformatted; ?></td> <td><?php print $hour . ':' . $minute . $ampm; ?></td> <td><?php print $scores['winningteam']; ?></td> <td><?php print $scores['losingteam']; ?></td> <?php if ($show_fields) { ?> <td><?php print $scores['field']; ?></td> <?php } ?> <td><?php print $scores['wincomments'] ?></td> </tr> <?php } ?> </table> <?php } } // List the conference in a specified season elseif (($show_season || isset($_GET['season'])) && !isset($_GET['seasons']) ) { if (!$show_season || isset($_GET['season']) ) { $show_season = intval($_GET['season']); } $query = 'SELECT seasonname FROM sportsdb_seasons WHERE seasonid = ' . $show_season . ' LIMIT 1'; $result = mysql_query($query) or die('Error in query ' . $query); if (mysql_num_rows($result) == 0) { die ('There is no such season.'); } $seasonname = mysql_result($result,0); $seasonname = htmlspecialchars($seasonname, ENT_QUOTES); print '<p><a href="' . $_SERVER['PHP_SELF'] . '?seasons">Click here to view a list of seasons</a></p>' . "\n"; print '<h3>' . $seasonname . ' season</h3>' . "\n"; ?> <p>Click on a conference name to view its standings</p> <?php $query = "SELECT * FROM sportsdb_conferences WHERE season = $show_season ORDER BY conforder"; $result = mysql_query($query) or die('Error in query ' . $query); $num = mysql_num_rows($result); while ($confs = mysql_fetch_array($result, MYSQL_ASSOC)) { print '<p><a href="' . $_SERVER['PHP_SELF'] . "?conf={$confs['confid']}\">{$confs['confname']}</a></p>\n"; } } else { ?> <p>Click on a season name to view its conferences</p> <?php // List the seasons $query = 'SELECT * FROM sportsdb_seasons ORDER BY seasonorder'; $result = mysql_query($query) or die('Error in query ' . $query); $num = mysql_num_rows($result); while ($seasons = mysql_fetch_array($result, MYSQL_ASSOC)) { $seasonname = htmlspecialchars($seasons['seasonname'], ENT_QUOTES); print '<p><a href="' . $_SERVER['PHP_SELF'] . "?season={$seasons['seasonid']}\">$seasonname</a></p>\n"; } } ?> Quote Link to comment Share on other sites More sharing options...
kyleo92 Posted February 8, 2011 Author Share Posted February 8, 2011 And, here is the database structures for all of the tables this script uses... -------------------------------------------------------- -- -- Table structure for table `sportsdb_conferences` -- CREATE TABLE IF NOT EXISTS `sportsdb_conferences` ( `confid` int(11) NOT NULL auto_increment, `confname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `conforder` int(11) NOT NULL default '0', `season` int(11) NOT NULL default '1', KEY `confid` (`confid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ; -- -------------------------------------------------------- -- -- Table structure for table `sportsdb_divs` -- CREATE TABLE IF NOT EXISTS `sportsdb_divs` ( `divid` int(11) NOT NULL auto_increment, `divname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `divorder` int(11) NOT NULL default '0', `conference` int(11) NOT NULL default '1', KEY `divid` (`divid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ; -- -------------------------------------------------------- -- -- Table structure for table `sportsdb_limitations` -- CREATE TABLE IF NOT EXISTS `sportsdb_limitations` ( `limitation_id` int(11) NOT NULL auto_increment, `type` enum('conf','div','team') character set utf8 collate utf8_unicode_ci NOT NULL, `policy_id` int(11) NOT NULL, `limitation_value` int(11) NOT NULL, UNIQUE KEY `limitation_id` (`limitation_id`), UNIQUE KEY `type` (`type`,`policy_id`,`limitation_value`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Table structure for table `sportsdb_masterteams` -- CREATE TABLE IF NOT EXISTS `sportsdb_masterteams` ( `masterteamid` int(11) NOT NULL auto_increment, `masterteamname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL, `masterteamactive` tinyint(1) NOT NULL default '1', UNIQUE KEY `masterteamid` (`masterteamid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ; -- -------------------------------------------------------- -- -- Table structure for table `sportsdb_permissions` -- CREATE TABLE IF NOT EXISTS `sportsdb_permissions` ( `policy_id` int(11) NOT NULL auto_increment, `userid` int(11) NOT NULL, `level` enum('manage_score','manage_team','manage_div','manage_conf') character set utf8 collate utf8_unicode_ci NOT NULL, UNIQUE KEY `policy_id` (`policy_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -------------------------------------------------------- -- -- Table structure for table `sportsdb_seasons` -- CREATE TABLE IF NOT EXISTS `sportsdb_seasons` ( `seasonid` int(11) NOT NULL auto_increment, `seasonname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL, `seasonorder` int(11) NOT NULL, UNIQUE KEY `seasonid` (`seasonid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -------------------------------------------------------- -- -- Table structure for table `sportsdb_teams` -- CREATE TABLE IF NOT EXISTS `sportsdb_teams` ( `teamid` int(11) NOT NULL auto_increment, `teamname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `teamwins` int(11) NOT NULL default '0', `teamlosses` int(11) NOT NULL default '0', `teamties` int(11) NOT NULL default '0', `teamforfeits` int(11) NOT NULL default '0', `teamrf` int(6) NOT NULL default '0', `teamra` int(6) NOT NULL default '0', `teamdiv` int(11) NOT NULL default '1', `teamorder` int(2) NOT NULL default '0', `active` int(11) NOT NULL default '1', `masterteam` int(11) NOT NULL, UNIQUE KEY `teamid` (`teamid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ; -- -------------------------------------------------------- -- -- Table structure for table `sportsdb_users` -- CREATE TABLE IF NOT EXISTS `sportsdb_users` ( `userid` int(4) NOT NULL auto_increment, `firstname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `lastname` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `emailaddy` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `password` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `username` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `comments` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL, `active` tinyint(1) NOT NULL default '1', `is_admin` tinyint(1) NOT NULL default '0', KEY `userid` (`userid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -------------------------------------------------------- -- -- Table structure for table `sportsdb_wins` -- CREATE TABLE IF NOT EXISTS `sportsdb_wins` ( `winid` int(11) NOT NULL auto_increment, `windate` int(20) NOT NULL default '0', `wintime` int(11) NOT NULL default '0', `field` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `winner` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '0', `loser` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '0', `rf` int(3) NOT NULL default '0', `ra` int(3) NOT NULL default '0', `wincomments` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '', `winortie` int(1) NOT NULL default '3', `winconf` int(11) NOT NULL, KEY `winid` (`winid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=306 ; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.