Getting players information with Sports Bench and MySQL

So last time, we went through how to get information for a team and how to print it it out onto the page. In this post, we’ll go over how to do that with players.

There won’t be too many new items here, but we’ll reinforce what we talked about last time before moving on to more complicated later on. So let’s get started.

Searching for players

So searching for players is very similar to how to search for teams, but there are a couple of key differences, especially with the type of searches you’ll want to do.

For example, getting a player based on first or last name is the same as format for getting a team based off of the team name.

SELECT * FROM wp_sb_players WHERE player_first_name = 'Mauro' AND player_last_name = 'Diaz';

But, if you want to get all of the players on a team, you can’t just search for team name. Instead, you need the team id, which can be found in the database or in the url for the edit team page in the admin area.

So, say we want to get the players for FC Dallas. We know that they were the first team we entered, so we know the team id is 1. So, we’ll write our SQL statement like this.

SELECT * FROM wp_sb_players WHERE team_id = 1;

It’s that easy. Now, like the teams, let’s print the info out onto the page.

Printing the data

So the PHP set up is going to be exactly the same as what we did with the teams. The only change is the table name and the column names that we’ll eventually output.

For this tutorial, we’re going to be pulling the roster for a specific team. Here’s what are SQL statement is going to be.

SELECT * FROM wp_sb_players WHERE team_id = 1 LIMIT 2;

So that looks good except for the use of “LIMIT 2” at the end. We’re not going to print the entire roster, so we limit our results to the first two players. This is like how you can enter in how many posts you want to show in WordPress. Pretty simple.

So with that statement, here’s our entire PHP code to get this to work.

$querystr = "SELECT * FROM wp_sb_players WHERE team_id = 1 LIMIT 2;";
$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>';

And here’s our outputted data in all of it’s glory.

Mauro	Diaz	Argentina	Midfielder
Kellyn	Acosta	Texas	Midfielder

Available columns for you to use

These are just a few of the examples of what you can do with the players and their basic information. There’s a lot of cool things you could do with all of this information.

To help you do those amazing thing, here’s a list of the available columns you can search and use, which you can also find on the Codex page.