Jump to content

Setting up databases to produce game schedules...


Jim R

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

 

Link to comment
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.

Link to comment
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.  

Link to comment
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>';

 

Link to comment
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>';

 

Link to comment
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. 

Link to comment
Share on other sites

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.