Jump to content

Need to query only current season instances...


Jim R

Recommended Posts

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

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

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

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'];

	}

 

Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

 

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.