Getting more complicated player stats with MySQL and PHP

Okay, so we've gone through a lot with MySQL, PHP and Sports Bench. But before this tutorial ends, let put together everything that we've learned so far.

In this final tutorial, we're going to create a stats leaderboard with the top five goal scorers in the league and print them out in a table. So let's get started!

Ordering players by stats

So in order to create a stats leaderboard, obviously we need to order the players by whatever stat that we're going to display. Fortunately, with MySQL, there's a very easy way to do that.

To accomplish this, we'll use the ORDER BY statement in MySQL. With this statement, you can order the results by the name of the column or the alias that you've assigned to it like we did in the last tutorial. And you can also make the order ascending (Z-A) or descending (A-Z).

So, if I'm ordering a list of players by goals, then I would use the following MySQL.

SELECT g.game_player_id, g.game_player_goals FROM wp_sb_games
ORDER BY g.game_player_goals DESC

See, simple as that.

The limit does, in fact, exist

But, if you're going to create a list of players for a stat, you don't want to print every player in the league. I mean, technically, you can do that, but it wouldn't look great visually and could cause an issue with timeouts.

So, we need to place a limit on the number of items returned with our query. And to do that, we need to use the LIMIT statement. So we can change the query above to this to limit the players returned.

SELECT g.game_player_id, g.game_player_goals FROM wp_sb_games
ORDER BY g.game_player_goals DESC
LIMIT 10

This is also the same way that WordPress limits posts per page on your website as well.

Also, you can use OFFSET to change the starting point for the list. If you use the stats leaderboard included in Sports Bench and you hit load more, the code that runs uses an offset to load more players.

Now with those statements out of the way, let's put it all together.

Putting it all together

After all that we've done so far, putting together a list of leaders in the goals category should be pretty simple.

So for this project, we're going to grab the top five leaders in goals scored and then print them out onto the page. And to do that, we use this code.

global $wpdb;
$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_goals ) AS GOALS
 FROM wp_sb_players as p LEFT JOIN wp_sb_game_players 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 game.game_season = $season
 GROUP BY g.game_player_id, game.game_season
 ORDER BY STAT DESC
 LIMIT 10;";
$players = $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->GOALS . '</td>';
  echo '</tr>';
}
echo '</table>';

And then our printed table will look like this.

Maxi	Urruti	10
Jordan	Morris	9
Jozy	Altidore	7
Cyle	Larin	5
Mauro	Diaz	5

And now we have a printed stats leader table. Pretty cool, right?

In conclusion

So, where can you go from here?

Well there's a lot that you can do with Sports Bench, PHP and MySQL. You can create your own stats leaderboards, custom schedule layouts, standings with more information and so, so much more.

So if you want to do more with your sports site outside of the Sports Bench plugin, you can use what you’ve learned here and elsewhere to do just that. And if you have any question, always please feel free to ask.