Jump to content
Jim R

Setting up databases to produce game schedules...

Recommended Posts

14 hours ago, gizmola said:

Hey Jim R,

I just want to say that I'm pleased to see you letting go of some of your preconceived notions.

There are few people with both the expertise and patience Barand brings to these forums.  

In my career I've designed and engineered systems for companies like Paramount Pictures, Industrial Light & Magic, Blizzard Entertainment, Sierra Online and many more I won't elaborate.  Some of the systems have had millions of users and 100's of millions of rows in individual tables.  Please take it from me that you're getting amazing advice from a professional who is an authority on anything having to do with relational database design, SQL or web systems based on a relational database.

 

 

 

 

I come here to learn.  A lot of times I’m not even sure which question I need to ask, but I usually work my way around to it. 

Share this post


Link to post
Share on other sites

I've tried a few variations of this query, and this is the closest I can get it.  😶

Finally getting around to printing the game schedule.  

 

Game Table:

696669654_ScreenShot2019-05-17at1_37_43PM.png.f8b73459804ed0a7dc5efc5abc65c6bf.png

 

Schools Table:

1346817531_ScreenShot2019-05-17at1_37_25PM.png.1a1e425102834a54daf6362820f42e93.png

 

Query...

$query_game = "SELECT * FROM a_games1920 as g
join a_schools as s 
on s.ID = (g.homeID or g.awayID)
WHERE s.sectional = '". $sectional ."' 
ORDER BY date";


$result_game = mysqli_query($con,$query_game);
echo mysqli_error($con);
while($game = mysqli_fetch_assoc($result_game)) {

echo '<div>' . $game['date'] . ' | </div>';

Right now, I'm just printing the game date.  I'll fill in the rest after I get this right.  

 

Each team SHOULD have two games listed, one as a home team, one as the away team.  It's printing the dates of all three games.  It should only be printing two dates.  The plan is for each team to have their schedule listed via Date, Opponent and game time.

 

Ultimately the output should kind of look like this...

1222465207_ScreenShot2019-05-17at1_42_36PM.png.e10d7cb5a1cc4e2ffe71db3bf40f5aee.png

 

As for data entry, in theory, each row in the table would add a game instance to two teams, a home team and an away team.  That would lessen the chance of duplicate entries, make it easier to check for duplicates.   I'm open to a different structure.

Share this post


Link to post
Share on other sites

It is likely that you are looking for something like this

SELECT team
     , gamedate
     , DATE_FORMAT(gamedate, '%d-%b-%Y') as date
     , opponent
     , hora
FROM (
        SELECT h.schoolname as team
             , gamedate
             , a.schoolname as opponent
             , 'H' as hora
        FROM game g 
             JOIN school h ON g.home_id = h.school_id
             JOIN school a ON g.away_id = a.school_id
        WHERE h.sectional = 8 
          AND a.sectional = 8
        
        UNION ALL
        
        SELECT a.schoolname as team
             , gamedate
             , h.schoolname as opponent
             , 'A' as hora
        FROM game g 
             JOIN school a ON g.away_id = a.school_id
             JOIN school h ON g.home_id = h.school_id
        WHERE h.sectional = 8 
          AND a.sectional = 8
      ) x
ORDER BY team, gamedate;

+-----------+------------+-------------+-----------+------+
| team      | gamedate   | date        | opponent  | hora |
+-----------+------------+-------------+-----------+------+
| School 1  | 2018-11-10 | 10-Nov-2018 | School 13 | H    |
| School 1  | 2018-11-17 | 17-Nov-2018 | School 5  | A    |
| School 1  | 2018-11-24 | 24-Nov-2018 | School 2  | H    |
| School 1  | 2018-12-01 | 01-Dec-2018 | School 7  | H    |
| School 1  | 2018-12-15 | 15-Dec-2018 | School 9  | A    |
| School 1  | 2018-12-22 | 22-Dec-2018 | School 8  | H    |
| School 1  | 2019-01-19 | 19-Jan-2019 | School 11 | A    |
| School 1  | 2019-01-26 | 26-Jan-2019 | School 4  | H    |
| School 1  | 2019-02-02 | 02-Feb-2019 | School 8  | A    |
| School 1  | 2019-02-23 | 23-Feb-2019 | School 6  | H    |
| School 1  | 2019-03-09 | 09-Mar-2019 | School 12 | H    |
| School 1  | 2019-03-30 | 30-Mar-2019 | School 9  | H    |
| School 1  | 2019-04-06 | 06-Apr-2019 | School 6  | A    |
| School 1  | 2019-04-13 | 13-Apr-2019 | School 10 | A    |
| School 1  | 2019-04-20 | 20-Apr-2019 | School 13 | A    |
| School 1  | 2019-04-27 | 27-Apr-2019 | School 7  | A    |
| School 1  | 2019-05-04 | 04-May-2019 | School 11 | H    |
| School 1  | 2019-05-18 | 18-May-2019 | School 12 | A    |
| School 1  | 2019-05-25 | 25-May-2019 | School 10 | H    |
| School 1  | 2019-06-01 | 01-Jun-2019 | School 4  | A    |
| School 1  | 2019-06-08 | 08-Jun-2019 | School 2  | A    |
| School 1  | 2019-06-15 | 15-Jun-2019 | School 5  | H    |

 

Share this post


Link to post
Share on other sites
Posted (edited)

I wondered if that was the table structure you'd recommend.  How will that impact teams that play each other and duplication as I go back and enter scores?  

I should've also said the Team is determined dynamically, based on the Sectional* printed to the page.  Each page will have 6-8 team listed on it.  

 

*Sectional is a post season round of our state tournament and has no direct impact on anyone's schedule.  It's just how most group teams when discussing/previewing the season.

Edited by Jim R

Share this post


Link to post
Share on other sites

Do you mean you want to add a "WHERE team = '$teamname' "

Share this post


Link to post
Share on other sites

The Page takes the team from the database, but yes.  It would be school ID, no?

I have this schedule on the Page via an INCLUDE.  

Share this post


Link to post
Share on other sites
Posted (edited)

Also, you have school name, opponent and hora (home or away) columns, as well as home_id and away_id.  Redundant?

Which columns do you have in the game table?

I currently have (after restructuring) id, gamedate, schoolname, opponent and hora.

 

Getting this error:

Unknown column 'g.home_id' in 'on clause'
 

Here is the link:

https://www.courtsideindiana.com/season-preview/19-20/sectional1920/?sectional=8

Edited by Jim R

Share this post


Link to post
Share on other sites

My game table is

CREATE TABLE `game` (
  `game_id` int(11) NOT NULL AUTO_INCREMENT,
  `gamedate` date DEFAULT NULL,
  `home_id` int(11) DEFAULT NULL,
  `away_id` int(11) DEFAULT NULL,
  `home_score` int(11) DEFAULT NULL,
  `away_score` int(11) DEFAULT NULL,
  `tournament_id` int(11) DEFAULT NULL,
  `round` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`game_id`),
  KEY `fk_game_school1_idx` (`home_id`),
  KEY `fk_game_school2_idx` (`away_id`),
  KEY `fk_game_tourn_idx` (`tournament_id`),
  KEY `idx_game_gamedate` (`gamedate`),
  CONSTRAINT `fk_game_school1` FOREIGN KEY (`home_id`) REFERENCES `school` (`school_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_school2` FOREIGN KEY (`away_id`) REFERENCES `school` (`school_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_tourn` FOREIGN KEY (`tournament_id`) REFERENCES `tournament` (`tournament_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=241 DEFAULT CHARSET=utf8;

 

Share this post


Link to post
Share on other sites

Where are school, opponent and hora ?

 

Share this post


Link to post
Share on other sites

Got rid of the errors (created by thinking I had a few new columns), but right now it's only printing the games of the teams involved in Sectional 8.  

 

The Sectional designation is just how the Page Views are organized.  When I remove...

WHERE h.sectional = 8 
          AND a.sectional = 8

...it prints all the games, regardless of who is playing in them.  

Share this post


Link to post
Share on other sites
1 minute ago, Jim R said:

...it prints all the games, regardless of who is playing in them.  

Is that a good thing or a bad thing?

(I put in the "WHERE sectional = " bits in because you had it in your query. "8" just happened to be the value I had put in that column in my data.)

Share this post


Link to post
Share on other sites
1 minute ago, Barand said:

Is that a good thing or a bad thing?

(I put in the "WHERE sectional = " bits in because you had it in your query. "8" just happened to be the value I had put in that column in my data.)

I mean, it prints all three dates, even though the team is just listed in two of them.  I just want the games they're playing in.  

 

(That's a fair point on the Sectional part.)

Share this post


Link to post
Share on other sites
Posted (edited)

Somehow I think it needs to =   $row['schoolname'], which comes from the query defining the Page View.  

Edited by Jim R

Share this post


Link to post
Share on other sites
Posted (edited)
6 minutes ago, Jim R said:

I mean, it prints all three dates,

What 3 dates? You have home games and away games so what is the third - do you also have meet-in-the-middle games?

Edited by Barand

Share this post


Link to post
Share on other sites
Just now, Barand said:

What 3 dates? You have home games and away games ro what is the third - do you also have meet-in-the-middle games?

Game instance 1:  Carmel (h) vs Zionsville

Game instance 2:  North Central (h) vs Carmel

Game instance 3:  Zionsville (h) vs North Central

Share this post


Link to post
Share on other sites

I take it you just want the Carmel games, yes?

What is you current query?

Share this post


Link to post
Share on other sites

I want the Carmel games in the Carmel section, the Zionsville games in the Zionsville section. (if you looked at that link)

https://www.courtsideindiana.com/season-preview/19-20/sectional1920/?sectional=8

 

$query_game = "SELECT team
     , gamedate
     , DATE_FORMAT(gamedate, '%d-%b-%Y') as date
     , opponent
     , hora
FROM (
        SELECT h.schoolname as team
             , gamedate
             , 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 ."'
          AND a.schoolname = '".$school ."'
        
        UNION ALL
        
        SELECT a.schoolname as team
             , gamedate
             , 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 h.schoolname = '".$school ."'
          AND a.schoolname = '".$school ."'
      ) x
ORDER BY team, gamedate";

$school is defined from another query.  

Share this post


Link to post
Share on other sites

I am surprised you are getiing any results at all. Those where clauses should give only those matches where the home team and away team are the same team ($school)

The first SELECT clause (home matches) should have "WHERE h.schoolname = '$school' " and the second should have "WHERE a.schoolname = '$school' " (Or use school_id , preferably)

Share this post


Link to post
Share on other sites
4 hours ago, Barand said:

I am surprised you are getiing any results at all. Those where clauses should give only those matches where the home team and away team are the same team ($school)

The first SELECT clause (home matches) should have "WHERE h.schoolname = '$school' " and the second should have "WHERE a.schoolname = '$school' " (Or use school_id , preferably)

 

It's still giving me a blank result:

$query_game = "SELECT team
     , gamedate
     , DATE_FORMAT(gamedate, '%d-%b-%Y') as date
     , opponent
     , hora
FROM (
        SELECT h.schoolname as team
             , gamedate
             , 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
             , 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
4 hours ago, Barand said:

What does $school contain?

I had forgotten to define $school inside the loop.  (I had originally defined it from the URL via  $_GET for testing purposes.)

Now it’s:  $school = $row[‘schoolname’] (from the main loop)

I think it’s working now that I’ve changed it.  So now I’m going expand what is printed.  

Share this post


Link to post
Share on other sites

One thing you shouldn't do is run an outer query then loop through the results running further queries. It is inefficient and heavy on resources.

You should use JOINS to get all the data you need in a single query

  • Like 1

Share this post


Link to post
Share on other sites

Definitely something more to talk about.  I'd have more to learn to get that done.

I'm coaching basketball this weekend, so I'll be scarce.  I'll DM you more about what we talked about there.  I have a thought on something I'd like to share.  

Share this post


Link to post
Share on other sites

I want to separate the game time from the game date.  Since 95% of game times are 7:30 PM, local time, I would have that as the default value in a separate column then change it when necessary.  

 

I added the column gametime, data type:  TIME.  Then I added gametime to the query (just below all the gamedate instances), but I'm getting 'unknown column' errors. 

$query_game = "SELECT team
     , gamedate
     , gametime
     , 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

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.