David Patrick McKenzie

Digital Public Historian

Clio 3 Long (enough, I hope) Tutorial: Creating a MySQL Database Listing in Your WordPress Site

Note: I have cross-posted this on the class blog. Should any changes be made, they will appear on that site in the future.

Lesson Goals & Reasons

Goals

In this lesson, you will learn how to create a listing of data from a MySQL database, and have it display in WordPress.

You will need:

  • A WordPress site (often used for a blog), hosted on your own server. In other words, not a blog/site hosted on WordPress.com.
  • A MySQL database, separate from your WordPress site.
  • A text editor, such as Komodo Edit or TextWrangler. These are available for free.
  • A FTP client, such as CyberDuck, for connecting to your server.

You will do the majority of the editing in the separate text editor; you may also use WordPress’s native interface.

Why?

Many historians have own own blogs–perhaps even as part of our own websites–and separate databases that we use for research. This tutorial will show you how to display the data from your MySQL database on your WordPress site, so that you don’t need a separate site for that purpose. In the long run, this will save you time, as you won’t have to develop a separate CSS for your database listing (and other related pages). Your listing will simply follow the style of your WordPress site.

This tutorial is for those who have an existing MySQL database and would like not to have to create a custom website for displaying, modifying, and/or querying the data. In other words, you can make your database pages follow the style of your WordPress template and live in your already-existing website.

As you may know, WordPress offers a plethora of plugins. There are two (here and here) that offer the option of integrating a MySQL database into your WordPress. What they do, however, is require you to copy your already-existing database into WordPress’s MySQL database. Doing this increases the possibility of error–for instance, if you make an alteration to your data and click the wrong place, you may break something in WordPress. So, we want to keep our MySQL database separate from our WordPress database; in this tutorial, we are simply using WordPress to display data from your MySQL database.

What Will You Do?

This tutorial has four parts:

  • Understanding WordPress: Providing a basic explanation of relevant parts of WordPress and how it works.
  • Creating the Connection: How to connect your separate database into WordPress.
  • Creating a New Template: How to create a page to hold your listing.
  • Creating Your Listing: How to list the contents of your database, right in your WordPress page.

Understanding WordPress

Basics

WordPress is a content management system. Many historians, in particular, use it to host a blog; indeed, that’s its original function. But WordPress is also a great system to use for an entire website.

WordPress divides into two basic “units”: posts and pages. To make a technical explanation short, posts are your typical blog posts. Pages, meanwhile, act like the static pages you find on any website. In this tutorial, you will create custom pages to display the data from your MySQL database.

WordPress, like many other content management systems, is coded in PHP. In fact, it is connected to its own MySQL database; anything you put into it–a page, a post, a picture–is stored in a specified spot within WordPress’s database. Each page or post fishes out that content and displays it.

Themes

A theme is what controls the appearance of your WordPress site. Each theme (of which there are, now, literally thousands) contains several PHP files for different parts of WordPress. There are several page templates within each WordPress theme. In this tutorial, we will duplicate and edit a page template.

An important note: If you haven’t done any modifications before to your WordPress template, you should create a child theme. Here is how to do that and why it’s important. Any pages that we create here will be saved into the child theme.

We will be working in the folders for your parent and child themes: duplicating files from your parent theme folder into your child theme, and making changes there. To find your theme folders, open your FTP software and navigate to the folder where you have your installation of WordPress. The file structure of WordPress is the same for each installation. The themes can be found under wp_content, then themes. The folder holding your parent theme houses the different page files for that theme.

Creating the connection

In this tutorial, we will just be making database listing. However, you may, in the long run, want to use your WordPress site for multiple functions related to your MySQL database. For example, you may want to have a data entry form. Or you may want to have a page where you, or your users, can query the database. As such, in this section we are going to set up a function that will allow you to connect to your separate MySQL database with just one line of code, in any page.

Create a child theme Functions.php file

Each WordPress theme contains a file called functions.php. This file is essential to WordPress. A function is a set of code in PHP containing a series of commands. Generally, you want to create functions for a sequence of commands you may use frequently. WordPress stores all of its functions–called up throughout your site–in functions.php.

We will set up a separate functions.php file for our child theme. This will augment, but not alter, the functions.php file that came installed with your theme. The reason: less room to break the entire site by accidentally altering or deleting an essential function.

Create your function

Create a new file called “functions.php,” and save it in your child theme folder. Open that file.

Now we will create a function to connect to our database. Begin with the basic opening and closing command for PHP:

 <?php

?>

All of our commands will be within those brackets. Next, create the basis for the function. Let’s call it open_mysql_db:

 <?php 
function open_mysql_db() {

}
?>

As you can see, PHP calls on us to use parentheses after the name of the function. All of our commands to execute the function, meanwhile, go within the curly braces.

Next, within the function, create a PHP variable–many of the tutorials I’ve seen use $mysqli for this purpose, so I’ve used it here. With this variable, you tell your file to connect to your MySQL database, located separately on your server. You input the address for your database, your login name, your password, and the specific database you are using. Make sure to put those things in single quotation marks, like below:

<?php 
function open_mysql_db() {
$mysqli = new mysqli('address of database', 'username', 'password', 'database name'); 
}
?>

Finally, we want to know if the connection does not work. This code will tell us:

<?php
  function open_mysql_db() {
    $mysqli = new mysqli('address of database', 'username', 'password', 'database name'); 
		
    // check connection 
    if (!$mysqli)
    throw new Exception('Could not connect to database');
    else
    return $mysqli;
  };
?>

What does this do? Essentially, it tests the connection (represented by the variable $mysqli), and gives you an error message if it doesn’t connect.

Now that we have our function defined, save and close your functions.php file. This function will now be available for any part of your site to use!

Creating a New Template

Now we will create the page to house your database listing. We will do this in a text editor (such as TextWrangler), but we can also do it in WordPress’s editor on your site. For this tutorial, I’m using the text editor, because it uses colors to help us understand the code we are inputting, and even indicates where we need to close brackets!

Create a new template

To create the page to display your data, you can pick any of the pre-existing page templates. Each page lives in the directory for your parent theme. In the parent theme directory, find the template you wish to use. Copy (do not move!) it into your child directory. Rename the duplicate file; for this tutorial, let’s call it “database_list.php.” Open that file in your text editor.

This next portion will vary depending on the theme. In some themes, at the very top of the page you will see a line saying “Template Name:”. If it is there, on this line input the name you want to call the template. This is important, as this is how you will choose this particular template for your page. For what we’re doing in this tutorial, let’s call our template “Database Listing.”

If the code is not there, insert the following at the top of the file–some themes offer this option, some do not:

<?php /**  
* Template Name: Database Listing  
*/ ?>

Connect to your MySQL database

Every WordPress page template has similar elements, the most important being “the_content.” This displays the content that you create for a particular page in WordPress’s scheme. Look for this line:

<?php the_content(); ?>

You will place everything that you need to for your database listing beneath this line, and above everything else–especially this line:

 <?php endwhile; ?> 

The reason? The key to how WordPress does this is a concept called “The Loop.” Basically, this is what tells tells WordPress to continue fishing content out of the database to display. Without The Loop, you could only display one blog post at a time. You can learn more about The Loop here and here. For our purposes, the most important part to know: any code that we add to our pages must be above that particular line, which is what makes The Loop stop running. If your code is below the line, WordPress will not know to use it, and thus will not.

Now, we tell the template to run the function that we just created. To do that, we give the function a variable. In this case, we’ll use “$conn” for “connection”–you can use whatever variable you want, as long as you remember it and use the same one! Insert this line:

<?php $conn = open_mysql_db(); ?>

Each time that variable is used, WordPress will know to run the function.

Creating Your Listing

Now we are ready to create our database listing. We will create a table using basic HTML commands–except that we are telling it to display the contents of our database. For purposes of this tutorial, we’ll use a database with four fields (called “field1,” “field2,” “field3,” and “field4”–creative, I know) in a table called “table.”

Query the database

We begin, as always, with our opening and closing PHP brackets:

<?php

?>

Now connect to the database and select the fields that we want to display in our listing. We start with an if-else statement. Essentially, we are telling the template to display the table if it can connect and get results from the query. Otherwise, it will tell you that there is an error:

<?php
// get the records from the database
if ()
  {

  }
// show an error if there is an issue with the database query
else
  {
    echo "Error: " . $conn->error;
  }
?>

Next, we give the query a variable–“$result” in this case–and run the query using regular MySQL commands:

<?php
// get the records from the database
if ($result = $conn->query("SELECT field1,field2,field3,field4 FROM table;"))
  {

  }
// show an error if there is an issue with the database query
else
  {
    echo "Error: " . $conn->error;
  }
?>

Now, within our curly braces after we query the database, we include another if-else statement. If the query works, we want to display our results. Otherwise, it displays an error message. So first, set up the if-else statement:

<?php
// get the records from the database
if ($result = $conn->query("SELECT field1,field2,field3,field4 FROM table;"))
  {
    // display records if there are records to display
    if ($result->num_rows > 0)
      {

      }
    // if there are no records in the database, display an alert message
    else
      {
        echo "No results to display!";
      }
  }
// show an error if there is an issue with the database query
else
  {
    echo "Error: " . $conn->error;
  }
?>

Now give a style to your table:

<?php
// get the records from the database
if ($result = $conn->query("SELECT field1,field2,field3,field4 FROM table;"))
  {
    // display records if there are records to display
    if ($result->num_rows > 0)
      {
        // display records in a table
        echo "<table border='1' cellpadding='10'>";

        echo "</table>";

      }
    // if there are no records in the database, display an alert message
    else
      {
        echo "No results to display!";
      }
  }
// show an error if there is an issue with the database query
else
  {
    echo "Error: " . $conn->error;
  }
?>

Next, set up the headers for your table:

<?php
// get the records from the database
if ($result = $conn->query("SELECT field1,field2,field3,field4 FROM table;"))
  {
    // display records if there are records to display
    if ($result->num_rows > 0)
      {
        // display records in a table
        echo "<table border='1' cellpadding='10'>";

          // set table headers
          echo "<tr><th>Field 1:</th><th>Field 2:</th><th>Field 3:</th><th>Field 4:</th></tr>";

        echo "</table>";

      }
    // if there are no records in the database, display an alert message
    else
      {
        echo "No results to display!";
      }
  }
// show an error if there is an issue with the database query
else
  {
    echo "Error: " . $conn->error;
  }
?>

Next, we query the database and pull out the data from each record (row) in the database. We set up a while statement–while each row of data is being fetched, will be put into the table. Begin with the while statement:

<?php
// get the records from the database
if ($result = $conn->query("SELECT field1,field2,field3,field4 FROM table;"))
  {
    // display records if there are records to display
    if ($result->num_rows > 0)
      {
        // display records in a table
        echo "<table border='1' cellpadding='10'>";

        // set table headers
          echo "<tr><th>Field 1:</th><th>Field 2:</th><th>Field 3:</th><th>Field 4:</th></tr>";
          while ($row = $result->fetch_object())
            {

            }
        echo "</table>";

    }
  // if there are no records in the database, display an alert message
  else
    {
      echo "No results to display!";
    }
  }
// show an error if there is an issue with the database query
else
  {
    echo "Error: " . $conn->error;
  }
?>

Finally, we put in our table, which will display each record of our database. To do this, we use the HTML code to create rows of the table. Then for each row, we input the variable “$row”, then “->”, then the name of each field. This tells the file to cycle through all of the records (i.e., each row) of the database until there are no more. Here is the code:

<?php
// get the records from the database
if ($result = $conn->query("SELECT field1,field2,field3,field4 FROM table;"))
  {
    // display records if there are records to display
    if ($result->num_rows > 0)
      {
        // display records in a table
        echo "<table border='1' cellpadding='10'>";

          // set table headers
          echo "<tr><th>Field 1:</th><th>Field 2:</th><th>Field 3:</th><th>Field 4:</th></tr>";
            while ($row = $result->fetch_object())
              {
                // set up a row for each record
                echo "<tr>";
                  echo "<td>" . $row->field1 . "</td>";
                  echo "<td>" . $row->field2 . "</td>";
                  echo "<td>" . $row->field3 . "</td>";
                  echo "<td>" . $row->field4 . "</td>";
                echo "</tr>";
              }
        echo "</table>";

      }
    // if there are no records in the database, display an alert message
    else
      {
        echo "No results to display!";
      }
  }
// show an error if there is an issue with the database query
else
  {
    echo "Error: " . $conn->error;
  }
?>

Now you have your template, which will include your listing. But we’re not done yet; all we’ve done is create a template for your eventual page.

Create a new page for the data

Next we will create the actual page for displaying this data.

On the left side of your WordPress dashboard, go to Pages>Add New. Name the page whatever you would like, e.g., “[Database Name]: Listing.” Next, write any explanatory text you would like in the content box. This will go above your listing. Here is what the interface looks like:

Now, we want to connect this page to its template. On the right side, you see a box for “Page Attributes.” Click on the dropdown menu for Template, and select the template you just created. This will call up the code that you just wrote!

Now, publish the page, and have a look at the finished product. You will see a database listing that now conforms to your WordPress theme, with no need for extra styling!

Here is what it looked like in mine:

1 Comment

  1. i think this is MUCH better than the previous version. my only recommendation now is to hold off on explaining the loop (and the 1 line of php code) so early on in the tutorial. i think it should go in the template section when you are actually modifying the code there. otherwise, nice work.

Leave a Reply

%d bloggers like this: