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

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.