Jump to content

Setting up databases to produce game schedules...


Jim R

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. 

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

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

 

Link to comment
Share on other sites

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

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

 

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

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

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

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

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

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

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

 

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

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

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

 

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.