Need to query only current season instances...

[Jim R]

Querying data for Notes that will have new instances each season (November 1-March 31), and as each season passes by I want the information produced to reflect the information at the time the query was made.  (This is for scouting)

Columns are...

• id
• schoolID (copied from another table)
• players
• season

So for example, I want each time I use this query for a Note from November 1, 2019 through March 31, 2020, to use rows where season = '19-20', and as we move into next season '20-21' I'll create new instances for each school I use.  The previous seasons of information will be archived but still viewable, so when someone goes back to read information from 2019-2020 season, I want the 19-20 rows to still be showing. 

 

I'm thinking this through as I'm typing...

As I'm creating these Notes, they do leave a post date yyyy-mm-dd, I'd assume I could use that as a reference point. 

 

Edited December 18, 2019 by Jim R

[Barand]

I would datestamp the note records rather than store the season code. At least you then know exactly when you made the note.

Have a "season" table to define the seasons
 

Season  From         To
18-19 | 2018-11-01 | 2019-03-31
19-20 | 2019-11-01 | 2020-03-31 

Then if you want this season's notes

SELECT note_date
     , note
FROM note n
     JOIN
     season s ON n.note_date BETWEEN s.From and s.To
WHERE CURDATE() between s.From and s.To
ORDER BY note_date

 

[Jim R]

Makes sense!    I'll play around with it today.  (I'll likely be back.)

That will likely help me with the scheduling query you helped me with, as there will definitely be some archiving there as well. 

Edited December 18, 2019 by Jim R

[Jim R]

Question first:

I'll have a Notes table, which timestamps when the note is made.

Then i'll have this Players table (columns noted above), which I will have timestamp when each entry is made.

Then I'll have a Seasons table, defining each season.

 

So I will query the Notes and Players table, relevant to the date range set up by the Seasons table, right?

[Barand]

I would need to review your model before committing.

To me, a "player" table would contain player attributes such as PlayerID, Name | Address | Phone | Dob | etc.

[Jim R]

It's just a CSV list for each team I've been typing out as I need it.  It's not a comprehensive list of players.

Out of 410 schools, there will be about 30 of them I write about repeatedly.

 

 

(Side note:  I do have a table for players, but it's way more extensive than my needs for the above, and it would require me to distinguish which ones to list.   If I get to a point where I find that would be more efficient from a work flow perspective, I'd likely be able to adapt what we're doing here to access the main players table.  I just really needed help on how to reference a date range without hard coding it into the query or having to note the season each time I access the query.)

 

Edited December 18, 2019 by Jim R

[Barand]

Are you talking about making notes about players during a game?

• id
• PlayerID
• TeamID
• Timestamp
• Comments

[Jim R]

So I need to refer to the timestamp of the Note, then match it to the timestamp of entry into the Players (a_game_preview_players) table, relative to the date range provided for reach season.  

 

This is what I had before getting to this timestamp issue.

$query = "SELECT * FROM a_game_preview_players
 where teamID = '" . $team . "'";
 
$results = mysqli_query($con,$query);
echo mysqli_error($con);
while($row = mysqli_fetch_assoc($results)) {
 
	return '<b>' . $row['school'] . '</b><br/>'
	. $row['players'];

	}

 

[Jim R]

6 minutes ago, Barand said:

Are you talking about making notes about players during a game?

• id
• PlayerID
• TeamID
• Timestamp
• Comments

No...these are preview notes, where I write about the team.  I created a shortcode in WordPress to access the data for each team.  I want to make sure each instance/row stays true to its season as time marches on.

 

I'm just in need of the time/season issue.  

Edited December 18, 2019 by Jim R

[Barand]

Are you making notes other than the player notes?

(Remember, there is only you who knows what you are trying to achieve, what your process are and what your data looks like)

[Jim R]

Here is a sample Note/preview:

Quote

Coming off a loss vs. Lawrence North, LC is looking to get back on the winning track. Crispus Attucks is taking a step up into 4A this March, but they come into this game 0-1 vs. 4A teams early in the season.

LC: DeAndre Davis, 6'6" senior guard; Nijel Pack, 5'11" senior guard; DeAnte Davis, 6'6" sophomore forward

CA:  Sincere McMahon, 6’1” senior guard; Jaylen Carson, 6’6” junior forward; Donovan Barnett, 6'3" junior guard; Qushawn Ware, 6'4" freshman guard

That's what is what the reader sees.  

 

Here is what I type:

Quote

Coming off a loss vs. Lawrence North, LC is looking to get back on the winning track. Crispus Attucks is taking a step up into 4A this March, but they come into this game 0-1 vs. 4A teams early in the season.

LC:  [players team='46']

CA: [players team='47']

Then that team number in the shortcode is run the query above to produce the list.  I now want to add a time element to it, based on the timestamp of the Note posting so as the seasons change, it accesses new instances each year. 

 

I only need to focus on the date stamp of the Note/preview with the date stamp in my Players table and make sure they come from the same Season (range of dates).  

[Barand]

Looks like all your note table should have is

• note_id
• Timestamp
• Comment (1st line from above examples)
• fixture_id

Your data model should provide the teams and their players for any given date/fixture

[Jim R]

12 minutes ago, Barand said:

Looks like all your note table should have is

• note_id
• Timestamp
• Comment (1st line from above examples)
• fixture_id

Your data model should provide the teams and their players for any given date/fixture

I'm not drawing from my own games/fixture table.  There are several games on each preview, and I'm just using the table to put out a consistent list of players for the team I write briefly about.  

I'm getting what I need/want right now other than matching date stamps based on a the applicable season.  

Notes table:  date stamp

Players table:  date stamp

Seasons table:  start_date, end_date

[Barand]

6 minutes ago, Jim R said:

I'm not drawing from my own games/fixture table

If that's the case, how do you get from

team = 46

to

DeAndre Davis, 6'6" senior guard; Nijel Pack, 5'11" senior guard; DeAnte Davis, 6'6" sophomore forward

[Jim R]

I copy the team ID from the teams table over to the player_previews table as I need them over from the team tables, and the WordPress shortcode I created matches it.  

 

Edited December 18, 2019 by Jim R

[Barand]

Then for the note table I'd suggest...

• note_id
• Timestamp
• Comment (1st line from above examples)
• home_team_id
• away_team_id

[Jim R]

Table structure isn’t at issue, other than adding a Seasons table.  Can we please just focus on the date stamps and seasons?

[Barand]

29 minutes ago, Jim R said:

Can we please just focus on the date stamps and seasons

I thought that was settled. The timestamp in the notes contains the date. The from and to dates in the season table tell you the season. Job done.

[Jim R]

10 hours ago, Barand said:

I thought that was settled. The timestamp in the notes contains the date. The from and to dates in the season table tell you the season. Job done.

It didn't include the Players table.  Unless I missed something, it was just joining seasons and notes.

[Barand]

17 hours ago, Barand said:

Your data model should provide the teams and their players for any given date/fixture

EG

+---------------+                                
|  school       |                                
+---------------+                                
| school_id     |---+                            
| name          |   |    +--------------+                        
+---------------+   |    |  team        |                                 +---------------+
                    |    +--------------+                                 |  player       |
                    |    | team_id      |--+                              +---------------+
                    |    | team_name    |  |                          +---| player_id     |
                    +---<| school_id    |  |                          |   | name          |
                         | sport_id     |  |                          |   | height        |
                         +--------------+  |                          |   | weight        |
                                           |                          |   | email         |
                                           |                          |   | dob           |
                                           |                          |   +---------------+
                                           |    +-----------------+   |
                                           |    | team_player     |   |
                                           |    +-----------------+   |
                                           |    | id              |   |
                                           +---<| team_id         |   |
                                                | position        |   |
                                                | player_id       |>--+
                                                | played_from     |
                                                | played_until    |
                                                +-----------------+