Jim R Posted May 19, 2019 Author Share Posted May 19, 2019 So I removed the initial gamedate and gametime instances from the query, and it all works. (I don't know why.) ? I don't know why it didn't work with the gametime lines, since I was essentially mirroring the gamedate lines, and I don't know why it works now taking gamedate and gametime out of the top part of the query altogether. $query_game = "SELECT team , DATE_FORMAT(gamedate, '%b %d') as date , TIME_FORMAT(gametime, '%l:%i %p') as time , opponent , hora FROM ( SELECT h.schoolname as team , gamedate , gametime , a.schoolname as opponent , 'h' as hora FROM a_games1920 g JOIN a_schools h ON g.home_id = h.id JOIN a_schools a ON g.away_id = a.id WHERE h.schoolname = '". $school ."' UNION ALL SELECT a.schoolname as team , gamedate , gametime , h.schoolname as opponent , 'a' as hora FROM a_games1920 g JOIN a_schools a ON g.away_id = a.id JOIN a_schools h ON g.home_id = h.id WHERE a.schoolname = '".$school ."' ) x ORDER BY team, gamedate"; Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566885 Share on other sites More sharing options...
Barand Posted May 19, 2019 Share Posted May 19, 2019 Query looks OK. I see the game table is named "a_games1920". How many game tables do have and do they all have a gametime column? Having a game table for each season is another of your bad ideas. You mentioned earlier about getting a coach's record over, say, the last 10 years. You will now have to query 10 different game tables to acclomplish that. All the games have a date so you know which seaon they belong to without adding another problem. Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566897 Share on other sites More sharing options...
Jim R Posted May 20, 2019 Author Share Posted May 20, 2019 On 5/19/2019 at 10:53 AM, Barand said: Query looks OK. I see the game table is named "a_games1920". How many game tables do have and do they all have a gametime column? Having a game table for each season is another of your bad ideas. You mentioned earlier about getting a coach's record over, say, the last 10 years. You will now have to query 10 different game tables to acclomplish that. All the games have a date so you know which seaon they belong to without adding another problem. I have a lot of bad ideas. ? That's what the table is named for now. I wouldn't have a table for each season. At worst, I had planned a main table and current season table, then updating the main table after each year. As I learn more, I'm seeing I won't have to do that. I'm slowly getting there. Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566944 Share on other sites More sharing options...
Jim R Posted May 21, 2019 Author Share Posted May 21, 2019 This is an aesthetic issue. As the season progresses, there will be games which have been played and upcoming games. Having added a *winner* column to the games table (a_games1920), I figured that's a good trigger to determine games played vs. upcoming games. I'd like there to be a separation between those blocks of games (past vs. future). I have tried various ways (none of which are reflected in the code below), and I can't wrap my head around how to trigger the separation just once. I'm assuming I'd be printing </table> <table> somewhere to end the first and start the second. echo '<table>'; $query_game = "SELECT team , DATE_FORMAT(gamedate, '%b %d') as date , TIME_FORMAT(gametime, '%l:%i %p') as time , home_score , away_score , winner , opponent , hora FROM ( SELECT h.schoolname as team , gamedate , gametime , home_score , away_score , winner , a.schoolname as opponent , 'h' as hora FROM a_games1920 g JOIN a_schools h ON g.home_id = h.id JOIN a_schools a ON g.away_id = a.id WHERE h.schoolname = '". $school ."' UNION ALL SELECT a.schoolname as team , gamedate , gametime , home_score , away_score , winner , h.schoolname as opponent , 'a' as hora FROM a_games1920 g JOIN a_schools a ON g.away_id = a.id JOIN a_schools h ON g.home_id = h.id WHERE a.schoolname = '".$school ."' ) x ORDER BY team, gamedate"; $result_game = mysqli_query($con,$query_game); echo mysqli_error($con); while($game = mysqli_fetch_assoc($result_game)) { // define home or away $hora = $game['hora']; if ($hora == 'h') { $hora = 'vs'; } else { $hora = '@'; } // Print the schedule echo '<div><tr><td>' . $game['date'] . '</td><td>'. $hora .' ' . $game['opponent'] . '</td>'; If (isset($game['winner'])) { if ($hora =='vs'){ echo '<td>' . $game['home_score'] . '-' . $game['away_score'] . '</td>'; } else { echo '<td>' . $game['away_score'] . '-' . $game['home_score'] . '</td>'; } } else { echo '<td>'. $game['time'] . '</td>'; } echo '</tr></div>'; } echo '</table>'; Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566956 Share on other sites More sharing options...
Barand Posted May 21, 2019 Share Posted May 21, 2019 1 hour ago, Jim R said: Having added a *winner* column to the games table Why? If you know who's playing and the scores then you know the winner. (BTW, what if there is a draw?). Don't store derived data. 1 hour ago, Jim R said: I figured that's a good trigger to determine games played vs. upcoming games. The fixture date is good indicator of those played and those not yet played. try this echo '<table>'; $query_game = "SELECT team , DATE_FORMAT(gamedate, '%b %d') as date , TIME_FORMAT(gametime, '%l:%i %p') as time , home_score , away_score , winner , opponent , hora , gamedate -- required for date comparisons FROM ( SELECT h.schoolname as team , gamedate , gametime , home_score , away_score , winner , a.schoolname as opponent , 'h' as hora FROM a_games1920 g JOIN a_schools h ON g.home_id = h.id JOIN a_schools a ON g.away_id = a.id WHERE h.schoolname = '". $school ."' UNION ALL SELECT a.schoolname as team , gamedate , gametime , home_score , away_score , winner , h.schoolname as opponent , 'a' as hora FROM a_games1920 g JOIN a_schools a ON g.away_id = a.id JOIN a_schools h ON g.home_id = h.id WHERE a.schoolname = '".$school ."' ) x ORDER BY team, gamedate"; $result_game = mysqli_query($con,$query_game); echo mysqli_error($con); $prevdate = ''; $today = date('Y-m-d'); while($game = mysqli_fetch_assoc($result_game)) { // define home or away $hora = $game['hora']; if ($hora == 'h') { $hora = 'vs'; } else { $hora = '@'; } // Print the schedule if ($prevdate <= $today && $game['gamedate'] > $today) { // HAVE WE CHANGED TO FUTURE FIXTURES? echo "<tr><td colspan='3' style='background-color:black; color:white'>Future fixtures...</td></tr>\n"; $prevdate = $game['gamedate'] ; } echo '<div><tr><td>' . $game['date'] . '</td><td>'. $hora .' ' . $game['opponent'] . '</td>'; If (isset($game['winner'])) { if ($hora =='vs'){ echo '<td>' . $game['home_score'] . '-' . $game['away_score'] . '</td>'; } else { echo '<td>' . $game['away_score'] . '-' . $game['home_score'] . '</td>'; } } else { echo '<td>'. $game['time'] . '</td>'; } echo '</tr></div>'; } echo '</table>'; Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566965 Share on other sites More sharing options...
Jim R Posted May 21, 2019 Author Share Posted May 21, 2019 14 minutes ago, Barand said: Why? If you know who's playing and the scores then you know the winner. (BTW, what if there is a draw?). Don't store derived data. I didn't necessarily know how to do that... 16 minutes ago, Barand said: The fixture date is good indicator of those played and those not yet played. ...and I agree the fixture dates would be a good indicator. We're talking basketball here. No draws. What you tried didn't work, but I see the trigger you defined. (I've done this on a previous project, and even look at it for reference. I couldn't for the life of me get my head around. https://www.courtsideindiana.com/season-preview/19-20/sectional1920/?sectional=8 In the Carmel example (first one), the first two dates are set for 2018. The final two dates are set up for 2020. Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566966 Share on other sites More sharing options...
Jim R Posted May 21, 2019 Author Share Posted May 21, 2019 (edited) It doesn't appear $prevdate is keeping any values. I put... echo $prevdate . ' ' . $today; ...in the loop, and all it prints is $today each time. UPDATE: I gave it a value to start, 1980-01-01, and it just prints that date next to today's date. Edited May 21, 2019 by Jim R Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566967 Share on other sites More sharing options...
Jim R Posted May 21, 2019 Author Share Posted May 21, 2019 Got it...I missed that you added gamedate to the top part of the query. Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566968 Share on other sites More sharing options...
Barand Posted May 21, 2019 Share Posted May 21, 2019 2 hours ago, Jim R said: I didn't necessarily know how to do that... SELECT CASE WHEN home_score > away_score THEN home_id ELSE away_id END as winner FROM game; 1 Quote Link to comment https://forums.phpfreaks.com/topic/308705-setting-up-databases-to-produce-game-schedules/page/3/#findComment-1566969 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.