Setting up databases to produce game schedules...

[Jim R]

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. 

[Jim R]

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:

 

Schools Table:

 

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...

 

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.

[Barand]

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    |

 

[Jim R]

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 May 17, 2019 by Jim R

[Barand]

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

[Jim R]

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.  

[Jim R]

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 May 17, 2019 by Jim R

[Barand]

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;

 

[Jim R]

Where are school, opponent and hora ?

 

[Barand]

[Jim R]

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.  

[Barand]

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.)

[Jim R]

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.)

[Jim R]

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

Edited May 17, 2019 by Jim R

[Barand]

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 May 17, 2019 by Barand

[Jim R]

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

[Barand]

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

What is you current query?

[Jim R]

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.  

[Barand]

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)

[Jim R]

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

 

[Barand]

What does $school contain?

[Jim R]

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.  

[Barand]

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

[Jim R]

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.  

[Jim R]

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