Getting advanced player and team stats

So we’ve learned so far how to get data, how to print it out and how to search for it with MySQL and PHP. And if you’ve been following along, hopefully you feel confident with your MySQL skills.

But if you’ve noticed, Sports Bench does a lot of cool things, and those don’t just happen with these simple commands. Instead, there’s more complicated stuff going on behind the scenes.

Now let’s start combining tables so we can do complicated and really cool things with our data.

Joining tables

So before we get to grabbing player stats, we need to go over joining tables together. You see, the information for the players and the stats are in two different tables. And in order to grab the stats for each player, we need to join the two tables together.

To do this, we’ll use JOIN in MySQL. There are a number of different variations on this like LEFT JOIN and XX JOIN, which you can learn about on W3Schools, but we’re not going to worry about those today. Instead, we’ll just focus on the simple JOIN statement.

To show how this works, we’re going to join the teams and players tables so that our rows will show the team name for the players. So to do this, we’ll use the following MySQL statement.

SELECT * FROM wp_sb_players as P JOIN wp_sb_teams as T ON P.team_id = T.team_id

Then if we were to print it out like we did in the last two posts, our printed table would look like this:

Now let’s take it up a notch.

Getting player stats for the season

Okay, so now that we know how to join two tables, let’s use that to get some real hard data. For this example, we’re going to get the goals, assists and minutes for Kellyn Acosta. (Disclaimer: This is just dummy data, FYI.)

Before we begin, let’s talk about using the SUM function in MySQL. This will allow us to total up all of the goals, assists and minutes. There’s also the COUNT function which counts the number of rows being joined.

So to get the sum of goals, we would use the following.

SUM (game_player_goals) AS GOALS

Now that we know how to sum up the totals, here’s the SQL statement to get our desired results.

SELECT p.player_id, p.player_first_name, p.player_last_name, p.team_id, game.game_id, game.game_season, g.game_id, g.game_team_id, g.game_player_id, SUM( g.game_player_minutes ) as MINUTES, SUM( g.game_player_goals ) as GOALS, SUM( g.game_player_assists ) as ASSISTS
 FROM wp_sb_players as p LEFT JOIN wp_sb_game_stats as g
 ON p.player_id = g.game_player_id
 JOIN wp_sb_games as game
 ON game.game_id = g.game_id
 WHERE g.game_player_id = 1 AND game.game_status = 'final' AND game.game_season = "2017"
 GROUP BY g.game_player_id;

And now we’ll put this into our HTML from the previous tutorials. Note that the way you call the totals is by the aliases we created in the statement.

$querystr = "SELECT p.player_id, p.player_first_name, p.player_last_name, p.team_id, game.game_id, game.game_season, g.game_id, g.game_team_id, g.game_player_id, SUM( g.game_player_minutes ) as MINUTES, SUM( g.game_player_goals ) as GOALS, SUM( g.game_player_assists ) as ASSISTS
 FROM wp_sb_players as p LEFT JOIN wp_sb_game_stats as g
 ON p.player_id = g.game_player_id
 JOIN wp_sb_games as game
 ON game.game_id = g.game_id
 WHERE g.game_player_id = 1 AND game.game_status = 'final' AND game.game_season = "2017"
 GROUP BY g.game_player_id;";
$player_info = $wpdb->get_results( $querystr );
echo '<table>';
foreach ( $player_info as $player ) {
  echo '<tr>;
    echo '<td>' $player->player_first_name . '</td>';
    echo '<td>' $player->player_last_name . '</td>';
    echo '<td>' $player->MINUTES . '</td>';
    echo '<td>' $player->GOALS . '</td>';
   echo '<td>' $player->ASSISTS . '</td>';
  echo '</tr>';
}
echo '</table>';

And our printed table should look like this:

Getting player stats for the season for a team

And finally, we’re going to do almost the same thing, except now we’re going to to do it for a team. And again, these stats are essentially dummy data. We’re more worried about the code.

So the only alteration to the code that we’re going to make is adding a check for the team id. That way we only pull in players for a specific team. Here’s the new MySQL statement.

SELECT p.player_id, p.player_first_name, p.player_last_name, p.team_id, game.game_id, game.game_season, g.game_id, g.game_team_id, g.game_player_id, SUM( g.game_player_minutes ) as MINUTES, COUNT( g.game_player_minutes ) as GP, SUM( g.game_player_goals ) as GOALS, SUM( g.game_player_assists ) as ASSISTS
 FROM wp_sb_players as p JOIN wp_sb_game_stats as g
 ON p.player_id = g.game_player_id
 LEFT JOIN wp_sb_games as game
 ON game.game_id = g.game_id
 WHERE g.game_team_id = 1 and game.game_season = "2017"
 GROUP BY g.game_player_id
 ORDER BY MINUTES DESC;

And again, here’s how we roll that into our PHP to print the data out onto the page.

$querystr = "SELECT p.player_id, p.player_first_name, p.player_last_name, p.team_id, game.game_id, game.game_season, g.game_id, g.game_team_id, g.game_player_id, SUM( g.game_player_minutes ) as MINUTES, COUNT( g.game_player_minutes ) as GP, SUM( g.game_player_goals ) as GOALS, SUM( g.game_player_assists ) as ASSISTS
 FROM wp_sb_players as p JOIN wp_sb_game_stats as g
 ON p.player_id = g.game_player_id
 LEFT JOIN wp_sb_games as game
 ON game.game_id = g.game_id
 WHERE g.game_team_id = 1 and game.game_season = "2017"
 GROUP BY g.game_player_id
 ORDER BY MINUTES DESC;";
$player_info = $wpdb->get_results( $querystr );
echo '<table>';
foreach ( $player_info as $player ) {
  echo '<tr>;
    echo '<td>' $player->player_first_name . '</td>';
    echo '<td>' $player->player_last_name . '</td>';
    echo '<td>' $player->player_home_state . '</td>';
    echo '<td>' $player->player_position . '</td>';
  echo '</tr>';
}
echo '</table>';

So, our printed table should look like this.

So that’s it for this tutorial. Next time we’ll look into even more advanced things you can do with MySQL, PHP and Sports Bench.

MySQL Tutorials

• MySQL Tutorial: The basic introduction
• Using Sports Bench data with MySQL and PHP
• Getting players information with Sports Bench and MySQL
• Getting more complicated player stats with MySQL and PHP

[sports-bench-other-posts category_slug=”tutorial”]