Jump to content
Jim R

Setting up databases to produce game schedules...

Recommended Posts

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";

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.  

Share this post


Link to post
Share on other sites

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>';

 

Share this post


Link to post
Share on other sites
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>';

 

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
Posted (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 by Jim R

Share this post


Link to post
Share on other sites

Got it...I missed that you added gamedate to the top part of the query.  

Share this post


Link to post
Share on other sites
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;

 

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.