How to Create a WordPress Database Table

I recently launched a new website called My Dog’s Name where people can find dog names for their new puppies. I’ve been building a lot of sites on WordPress and wanted to do the same with this one. The trick was creating a table in the WordPress database and being able to call to it from within my page templates. Fortunately, it was actually pretty easy to do with a little research and testing. This article will cover how you can use the $wpdb class to build your own WordPress web app.

Creating a WordPress Custom Database Table

Once you have WordPress up and running the first step is creating the new table in our WordPress database. I’ve got phpMyAdmin setup on my server so that’s how I went in and edited the database, but you can use any tool to do this. You’ll want to find your WordPress database, which is typically named something like ‘_wrdp1’, but you can also check the wp-config.php file in your WordPress root director to find the value for ‘DB_NAME’.

Once you’re in that database you should see a list of the different tables (wp_posts, wp_users, etc). Add a new table for your site and give it a name. For my dog site I named the table ‘wp_dognames’. Then I added the columns that I needed and imported my CSV file from Excel with my list of names.

Creating a Custom WordPress Template

Next, you’ll probably want to have a custom template for your page that will be querying the table we just created. The quickest way to do this is to make a duplicate of your page.php file that is inside of your theme folder (/wp-content/themes/your-theme/). Then rename the file to something that you’ll remember (for my site I named it results-page.php) and open it in an editor so we can add some code to let WordPress know what the name of the template is. After the initial <?php we’ll add in a comment with the template name like this:

<?php
/* Template Name: Results */
?> 

Make sure that the edited file is up on your server in your theme’s folder. Now you’ll be able to edit it from within the WordPress admin and it will show up as a template option when you create a new page.

You can go ahead and create a new page and assign this template to it. This will be our test page.

Pulling Data Back with $wpdb Query

WordPress uses the $wpdb class to access data within the WordPress database (and the table we just created). WordPress has a good reference for $wpdb that will be helpful in addition to what I cover here.

In your page template we’ll add in our custom PHP after the WordPress call for the page content:

<?php the_content(); ?>

To start off we’ll set $wpdb as a global so we can access the database and then using $wpdb we’ll run the get_results function to pull all our table’s data into an array called $results and print them to the screen:

<?php
global $wpdb;
$results = $wpdb->get_results("SELECT * FROM wp_names");
print_r($results);
?>

You can add your own SQL query in there to get the desired results. On my site I’ve got a column called ‘names’ that I pull from to get only the list of dog names. Then I’m using a loop to output each name with some additional formatting. For example to find Cute Girl Dog Names my query looks like this:

<?php	 	 
global $wpdb;	 	 
$results = $wpdb->get_results("SELECT name FROM wp_names WHERE girl = 1 and cute = 1 ORDER BY name");	 	 

if(!empty($results)) { 
     foreach($results as $r) {	 
          echo "<p>".$r->name."</p>"; 
     }
} else {
     echo "<p>Boo, we couldn't find anything that is in all these groups. Try removing a category!</p>";	 	 
} 
?> 

You can use this code example to list out any rows you might be pulling back into your ‘$results’ array. If you only want to pull a specific row or column you can use get_row() or get_col() (checkout the wordpress reference for more info).

Using Variables with $wpdb Queries

The next step to making this even better is using variables to make the queries. Then a single results page can run a variety of searches. For my dogs site, I’m passing the queries in using parameters from the url (e.g. http://www.mydogsname.com/names/?gender=boy&q=tough). Once this page loads, I’m just setting each of these parameters to variables and then using them to query the WordPress database.

<?php
global $wpdb;

$gender = esc_sql($_GET["gender"]);
$query = esc_sql($_GET["q"]);

$results = $wpdb->get_results("SELECT name FROM wp_names WHERE $gender = 1 and $query = 1 ORDER BY name");

Adding to the WordPress Database with $wpdb Insert

For my site I don’t have a way for users to insert data into my table, but it’s not hard if you’d like to. The code example below adds a new name to the ‘wp_names’ table. The insert function includes the table name, an array of data to enter, and the format the data is in. The first array begins with a column name (‘name’) and then inserts a variable into it ($name). These could also be values themselves instead of variables, but in most cases you’ll be using variables that you captured via a form. The second array contains the variable type for each column inserted into the row. In this example, the first two are strings (%s) and the second two are numbers (%d). You could also have a floating point number (%f).

global $wpdb;

$name = 'Toby';
$gender = 'boy';
$category1 = 1;
$category2 = 0;

$wpdb->insert('wp_names',
     array(
          'name'=>$name,
          'gender'=>$gender,
          'category1'=>$category1,
          'category2'=>$category2
     ),
     array( 
          '%s',
          '%s',
          '%d',
          '%d'
     )
);