Using Sports Bench data with MySQL and PHP

Welcome to the second post in a series on how to use MySQL and PHP to get data from Sports Bench so you can further customize your site to be awesome.

Last time we talked about what MySQL is and how you can use it to create, read, update and delete data. Today we’ll be talking about how to take that data from the MySQL database into PHP so you can print it on the page.

Let’s get started!

Objects in use

So we’re going to be using a few of objects in order to accomplish our task. If you don’t know about classes and objects in PHP, you’re going to want to check out the section on them on W3Schools first.

The first object we’ll be using today is the $wpdb object. This object plays a vital role in the every day use in WordPress as it pulls all of the data for posts and whatnot. And it’s going to be our main class for our work today.

The main method from the class we need to grab the data to display is the get_results function. This function will take a MySQL statement, like we talked about last week, and return the results into a variable of our choosing.

So if we were wanting to grab all of the columns for every team, we would write the following lines of code.

$querystr = "SELECT * FROM wp_sb_teams;";
$team_info = $wpdb->get_results( $querystr );

Of course, the MySQl could be anything you want to get the results you want. So play around with it. Next, we’re going to go through each bit of data.

Foreach statements

So the data that we get from the code above is an array of objects with the results of the search. This means we’re going to have to loop through the data to get it out onto the page. So if you don’t know what loops are in PHP, please read the section on this on W3Schools.

The most efficient way to walk through the array we’re given is a foreach loop. We won’t have to worry about writing any code to check the length of the array or increasing an counter. So after the code getting the data, put the following lines of code.

foreach ( $team_info as $team ) {
}

So here, the code will loop through each item in the array. And the foreach part essentially turns each item of the array into an object, which we’ll use in the next section.

Putting the data on the page

So finally, it’s time to put it all together and print some data onto the page. And while it might seem difficult at first, you’ll have no issue using this in the future.

Before get to the data, let’s first talk about what we’re going to be doing with the data. We’re going to display the teams with their data in a table on the page. We’ll show team name, team stadium and team location, just to make this easier. Let’s get going.

So first, on the outside of the loop, echo out <table> and </table> so that we can set up the HTML for the table. Then on the inside of the loop echo out <tr> and </tr> to create the table row. Next, write four echo '<td></td>'; statements inside of the rows to create our table cells. That’s the easy part.

Now to get the data, we’ll use the $object->attribute format to grad the data. The four attribute’s we’ll be using are team_name, team_stadium, team_city, team_state. So with that in mind, add it ' . $object->attribute . ' in between each <td></td>.

So now our code should look like this:

$querystr = "SELECT * FROM wp_sb_teams;";
$team_info = $wpdb->get_results( $querystr );
echo '<table>';
foreach ( $team_info as $team ) {
  echo '<tr>;
    echo '<td>' $team->team_name . '</td>';
    echo '<td>' $team->team_stadium . '</td>';
    echo '<td>' $team->team_city . '</td>';
    echo '<td>' $team->team_state . '</td>';
  echo '</tr>';
}
echo '</table>';

And our outputted table should look like this:

And that’s it. You’ve started . Next time we’ll get a bit more complicated with what we want to do with the data.

MySQL Tutorials

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

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